Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 240 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187.1K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 443 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
sql*plus COL[UMN] command for substitution variables

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