In response to Blaine Carter

Have you tried dbms_system.get_env()?

I didn’t before. Thanks for that! Now I did and still it doesn’t solve my problem as it

• needs connection to server
• has access only to server environment variables

1
2
3
4
5
6
7
8
9
10
11
12

$ export foo=bar
$ sql scott/tiger@some_remote_host:1521:orcl

SQL> var x varchar2(255);
SQL> exec dbms_system.get_env('foo', :x);

PL/SQL procedure successfully completed.

SQL> print x

X
------

What I’ve meant and didn’t clearly explain is client side functionality and that’s SQL*Plus/SQLcl for me.

What I need, and I think can be useful for many people, is something like this:

1
2
3
4
5

$ export foo=bar
$ sql /nolog

SQL> prompt &&__foo
bar

I don’t know how it can be done in SQL*Plus/SQLcl. Well I do, but it’s

• ugly
• not applicable for many security reasons
• isn’t OS independent
• it may break some legacy SQL*Plus code

1
2
3
4
5
6
7

$ export foo=bar
$ sql /nolog

SQL> host echo "define __foo = "$foo"" > .tmp
SQL> @.tmp
SQL> prompt &&__foo
bar

So the fallback is to use some scripting language like Python, Perl, … Which brings some issues

• security might not approve it
• developers need to learn some other language
• code get’s overcomplicated
• environment needed to run scripts is getting more complicated

@krisrice: It would be great feature new of SQLcl.

Don’t waste time googling “sqlplus set substitution variable from select”. It’s

1
2
3

COLUMN USER NEW_VALUE g_schema
SELECT USER FROM dual;
prompt &&g_schema

The best example to follow would be Java implementation of try-catch-finally. However in PL/SQL it would get messy and the code would remind good ol’ spaghetti western with epic theme - The Good, The Bad and The Ugly.

https://www.youtube.com/watch?v=pLgJ7pk0X-s

• The Good - catch an exception using try-catch block
• The Bad - code in finally block is executed even an exception is thrown
• The Ugly - if code has a return statement inside the try or catch block, the code inside the finally block will get executed before returning from the method

The Good

Exception handler in PL/SQL is quite simple

1
2
3
4
5
6
7
8
9
10

DECLARE
    e EXCEPTION;
BEGIN
    dbms_output.put_line('in try block');
    RAISE e;
EXCEPTION
    WHEN e THEN
        dbms_output.put_line('in catch block');
        dbms_output.put_line(SQLERRM);
END;

The Bad

To implement finally, we need a little workaround - define finally block as “inline” stored procedure.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

DECLARE
    e EXCEPTION;

    PROCEDURE finally IS
    BEGIN
        dbms_output.put_line('in finally block');
    END;

BEGIN
    dbms_output.put_line('in try block');
    RAISE e;
    finally;
EXCEPTION
    WHEN e THEN
        dbms_output.put_line('in catch block');
        dbms_output.put_line(SQLERRM);
        finally;
END;

The Ugly

But the implementation is not complete, until it gets the third feature. And now it gets ugly …
From countless implementations I’ve chosen these two

Implement as function

Option A

Pros

• all “special” logic is in minimalistic subprograms
  • you don’t need to change the_result method at all
  • cleanup code will be implemented in finally subprogram

Cons

• this the_result method - I like the syntactic “sugar” in form of RETURN the_result(value), but still its little bit weird

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

FUNCTION try_catch_finally RETURN typ_method_result IS

    PROCEDURE finally IS
    BEGIN
        --TODO: implement finally code here
        --TODO: do not change result here - BAD PRACTICE
        NULL;
    END;

    --ensures that finally is called before returning from method
    FUNCTION the_result(a_value_in IN typ_method_result) RETURN typ_method_result IS
    BEGIN
        finally;
        RETURN a_value_in;
    END;
    
BEGIN
    --TODO: implement try block code here
    
    RETURN the_result(<result_normal>);
EXCEPTION
    WHEN e_expected_exception_return THEN
        --TODO: handle caught exception
        RETURN the_result(<result_catch>);
        
    WHEN OTHERS THEN
        finally;
        RAISE;
        
END;

Option B

Pros

• single point of return - I consider it Best Practice to have only one

Cons

• implementation of try block in subprograms - I just don’t like subprograms

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

FUNCTION fb_try_catch_finally RETURN typ_method_result IS

    l_result typ_method_result;

    PROCEDURE finally IS
    BEGIN
        --TODO: implement finally code here
        --TODO: do not change result here - BAD PRACTICE
        NULL;
    END;

    PROCEDURE try_catch IS
    BEGIN
        --TODO: implement try block code here
        l_result := <result_normal>;
    EXCEPTION
        WHEN e_expected_exception_return THEN
            --TODO: handle caught exception
            l_result := <result_catch>;
    END;

BEGIN
    BEGIN
        try_catch;
    EXCEPTION
        WHEN OTHERS THEN
            finally;
            RAISE;
    END;
    finally;
    RETURN l_result;
END;

Last couple months spent developing JavaScript applications made me think about tools/applications that I would need for same comfort of development in PL/SQL. It always came down to one thing - packages = module = components.

The best name would be module as package has a different meaning in Oracle world.

By module I mean

• bundle of
  • stored procedures
  • DDL & DML scripts
  • install/uninstall/upgrade SQL*Plus scripts

There is no common, industry standard how to create modules of Oracle DB code (or am I missing something?). Oracle is developing the Cart thing, which is currently exposed through Oracle SQL Developer and sadly the only way to use (in SQL Developer) is to deploy it to Oracle Cloud.

Also what we need is definition of the way how to package, distribute, install/uninstall modules into your code base and then into Oracle Database. How to define inter-module dependencies and all that stuff that is provided by e.g. npmjs - which is great source of inspiration for me.

I’m working on draft for such a spec, which is based on npmjs’ definition for package.json. You can find it here. Any comments/PRs are greatly welcomed.

So I begin with OpenSource. 3…2…1…NOW!!! Actually I did couple months ago, but sometimes it takes a longer time to publish something, than to do it.

I’ve developed a lot. I’ve deleted more code with backspace than some of my more senior colleagues have ever written.

There’s only one catch. Almost all of it has been in PL/SQL or SQL*Plus. It’s ugly, but powerful. Even COBOL is more sexy than PL/SQL and programming in SQL*Plus can be even worse than this cutie. But still it can be fun.

There was huge reinventing of wheel on almost every project I came to. And I’ve got tired of that.

I had a long pause in my PL/SQL programming quest and spent some time developing in JavaScript. And I found that hidden world of OpenSource software. And I was amazed and decided to share some of my knowledge.

So here’s my contribution to my future self and to humanity - in PL/SQL and SQL*Plus.

Enjoy. Comment. Fork. Post PRs. Any form of interaction is greatly welcomed.

(most of my projects I maintain in Principal engineering organization, company I work for)