Forum Stats

  • 3,872,251 Users
  • 2,266,411 Discussions
  • 7,911,109 Comments

Discussions

sql*plus COL[UMN] command for substitution variables

sethionic
sethionic Member Posts: 3 Red Ribbon

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

Tagged:

Answers

  • Hi,

    Are you trying to do something like this?

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

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,915 Red Diamond
    edited Jan 7, 2022 9:16PM

    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
    sethionic Member Posts: 3 Red Ribbon
    edited Jan 7, 2022 10:28PM

    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.


    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
    Solomon Yakobson Member Posts: 19,915 Red Diamond
    edited Jan 8, 2022 12:59PM

    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.

  • 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.

    sethionic