Oh, another post took only 11 months ;) Anyways, please just don’t do it, and here’s another good reason why.

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

create or replace package pkg as
end pkg;
/

declare
    l_pkg_handle           number;
    l_pkg_modify_handle    number;
    l_pkg_transform_handle number;
    l_ddl                  clob;
begin
    l_pkg_handle := dbms_metadata.open('PACKAGE');
    dbms_metadata.set_filter(l_pkg_handle, 'NAME', 'PKG');
    dbms_metadata.set_filter(l_pkg_handle, 'SCHEMA', user);
    l_pkg_modify_handle := dbms_metadata.add_transform(l_pkg_handle, 'MODIFY');
    dbms_metadata.set_remap_param(l_pkg_modify_handle, 'REMAP_NAME', 'PKG', 'PKG2');
    l_pkg_transform_handle := dbms_metadata.add_transform(l_pkg_handle, 'DDL');
    l_ddl := dbms_metadata.fetch_clob(l_pkg_handle);
    dbms_metadata.close(l_pkg_handle);
    DBMS_OUTPUT.put_line(l_ddl);
end;
/

The name of the package after end does not get changed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

sql> create or replace package pkg as
     end pkg;
[2019-11-28 20:43:03] completed in 17 ms
sql> declare
         l_pkg_handle           number;
         l_pkg_modify_handle    number;
         l_pkg_transform_handle number;
         l_ddl                  clob;
     begin
         l_pkg_handle := dbms_metadata.open('PACKAGE');
         dbms_metadata.set_filter(l_pkg_handle, 'NAME', 'PKG');
         dbms_metadata.set_filter(l_pkg_handle, 'SCHEMA', user);
         l_pkg_modify_handle := dbms_metadata.add_transform(l_pkg_handle, 'MODIFY');
         dbms_metadata.set_remap_param(l_pkg_modify_handle, 'REMAP_NAME', 'PKG', 'PKG2');
         l_pkg_transform_handle := dbms_metadata.add_transform(l_pkg_handle, 'DDL');
         l_ddl := dbms_metadata.fetch_clob(l_pkg_handle);
         dbms_metadata.close(l_pkg_handle);
         DBMS_OUTPUT.put_line(l_ddl);
     end;
[2019-11-28 20:43:03] completed in 65 ms
[2019-11-28 20:43:03] 
[2019-11-28 20:43:03] CREATE OR REPLACE EDITIONABLE PACKAGE "SCOTT"."PKG2" as
[2019-11-28 20:43:03] end pkg;

Hardest part about blogging is to do it regularly. So let’s start, again ;)

SQLcl: Release 4.2.0.16.260.1205 Production appends some junk bytes when spooling under certain conditions.
Works fine with SQLcl: Release 4.2.0.16.175.1027 RC and SQL*Plus: Release 12.1.0.2.0 Production

1
2
3
4

1. git clone https://github.com/s-oravec/sqlsn -b bug/sqlcl-spools-junk-bytes sqlsn
2. cd sqlsn
3. sql /nolog @sqlsnrc.sql
4. hexdump sqlsn_modules/sqlsn-run/lib/command/module_config.sql

On both macOS and Windows (in Parallels on macOS) with SQLcl: Release 4.2.0.16.175.1027 RC and SQL*Plus: Release 12.1.0.2.0 Production

1
2
3
4
5

0000000 64 65 66 69 6e 65 20 72 75 6e 5f 6d 6f 64 75 6c
0000010 65 5f 70 61 74 68 20 3d 20 22 73 71 6c 73 6e 5f
0000020 6d 6f 64 75 6c 65 73 2f 73 71 6c 73 6e 2d 72 75
0000030 6e 22 0a                                       
0000033

vs.

On both macOS and Windows (in Parallels on macOS) with SQLcl: Release 4.2.0.16.260.1205 Production

1
2
3
4
5

0000000 64 65 66 69 6e 65 20 72 75 6e 5f 6d 6f 64 75 6c
0000010 65 5f 70 61 74 68 20 3d 20 22 73 71 6c 73 6e 5f
0000020 6d 6f 64 75 6c 65 73 2f 73 71 6c 73 6e 2d 72 75
0000030 6e 22 0a 1b 5b 6d 0a                           
0000037

(macOS and Windows dumps differs only in LF vs. CRLF)

Also there seems to be some issue with thread synchronization between host I/O and processing rest of the script.
Script continues before I/O call is returned/file is written/closed. This is especially visible when you

1. spool script created with prompts
2. call created script using @

For the script to work in SQLcl: Release 4.2.0.16.175.1027 RC, you have to “make some delay” - like calling host : as noop (see sqlsn_modules/sqlsn-stack/lib/command/push.sql in repo, lines 16,17)

I haven’t noticed such a behavior in SQLcl: Release 4.2.0.15.349.0706 RC.

Sometimes when DBMS_SCHEDULER job is STOPPED and the USER_SCHEDULER_JOB_LOG.ADDITIONAL_INFO says only that "Job slave process was terminated" you may find the real reason in ERR$ table

1

ORA-00028: your session has been killed
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

if your session has been killed during execution of sme DML statement.

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)