2016

SQLcl appends junks bytes when spooling

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.

2015

Using Environment Variables In SQL*Plus/SQLcl

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.