sql*plus COL[UMN] command for substitution variables

sethionic

Is there any workaround or plan to support the COL command in Oracle extension for VS Code? I have not found any way to spool to a dynamic filename without substitution variables, which makes abstracted scripts that log differently based on environment challenging

Christian.Shay -Oracle

Hi,

Are you trying to do something like this?

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3581757800346555562

If so, let me talk to a SQL*Plus guru and see if there is any workaround.

Solomon Yakobson

You need o be more specific. Are you assembling file name on OS level and then calling SQL*Plus script? If so, just pass it as script parameter.

SY.

sethionic

https://community.oracle.com/tech/developers/discussion/comment/16821363#Comment_16821363

Yes. I've searched for quite a while for any way to dynamically assign a filename to spool to, but the only way to populate a substitution variable programmatically that I have seen involves using the "COL[UMN]" command, which is explicitly not supported by the VS Code extension at this time.

https://community.oracle.com/tech/developers/discussion/comment/16821396#Comment_16821396

I did not want to be overly verbose and get a tl;dr response. Using the Oracle VS Code extension for running pl/sql (ctrl+e,ctrl+r following connecting to target database) the client explicitly does not allow the use of the "COL[UMN]" command. I can run the same script from CLI just fine following examples like Christian Shay mentioned above, but within the confines of the extension executing the script it fails.

Without this feature it is hard to test modularized scripts that need to write to disk or make use of substitution variables at all (which I try to avoid in general, but, alas, spool can only use substitution vars for dynamic names). Since all the substitution variable magic happens on the client side and the only means of populating one requires the "COL[UMN]" command I do not see any viable alternatives for testing without jumping over to terminal and not running anything requiring substitution variables through the extension.

If you want an example to play around with, try adding the current timestamp or name of the database you're on to a filename and spool to it (while executing the script through the extension (ctrl+r)):

col output_filename new_val output_filename
select name||'_'||to_char(sysdate,'yyyymmdd_HH24MI')||'.sql' output_filename from v$database;
spool /full/path/&output_filename

Solomon Yakobson

Well, not much of a choice. What you could do is something like this. Say we want to spool EMP table to a file:

col output_filename new_val output_filename
select name||'_'||to_char(sysdate,'yyyymmdd_HH24MI')||'.sql' output_filename from v$database;
set termout off
set trimspool on
set linesize 132
set pagesize 1000
spool /tmp/&output_filename
select * from emp;
spool off

but we can't use COLUMN command. So we create SQL*Plus script get_spool_file_name.sql:

set pagesize 0
select name||'_'||to_char(sysdate,'yyyymmdd_HH24MI')||'.sql' output_filename from v$database;
exit

Then we modify your script to take parameter:

define output_filename = "&1"
set termout off
set trimspool on
set linesize 132
set pagesize 1000
spool /tmp/&output_filename
select * from emp;
spool off
exit

Now we can prepare output file name:

$ output_filename=$(sqlplus -s /@polc1d @/tmp/get_spool_file_name.sql)
$ echo $output_filename
CPOLC1D_20220108_0754.sql
$ ls -ltr /tmp/$output_filename
ls: cannot access /tmp/CPOLC1D_20220108_0754.sql: No such file or directory

As you can see we generated output file name and assigned it to environment variable. And we see spool file doesn't exist. Now we can call our adjusted script:

$ sqlplus /@polc1d @/tmp/test.sql $output_filename

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 8 07:54:23 2022

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
$

And spool file:

$ cat /tmp/CPOLC1D_20220108_0754.sql

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

$

SY.

Christian.Shay -Oracle

https://community.oracle.com/tech/developers/discussion/4493247/sql-plus-col-umn-command-for-substitution-variables

I talked to the SQL*Plus team and they don't have a workaround for us. I will add supporting COLUMN to our proposed feature list. I agree that this is pretty important. If anyone comes up with a workaround that works well in the context of VS Code and this extension, please post it here.