I have would like to customize my SQLcl prompt and I call a javascript file from my login.sql I tried this but without success. Variable dbIdentifier contains the string '&_CONNECT_IDENTIFIER' var dbIdentifier = util.executeReturnOneCol("select '&_CONNECT_IDENTIFIER'"); This one works after login: SQL> select '&_CONNECT_IDENTIFIER'; old:select '&_CONNECT_IDENTIFIER' new:select 'jdbc:oracle:thin:@DEMODB_HIGH' 'JDBC:ORACLE:THIN:@DEMODB_HIGH' __________________________________ jdbc:oracle:thin:@DEMODB_HIGH Is there a way to assign the value of CONNECT_IDENTIFIER into a SQLcl JavaScript variable? I would like my prompt to include the string '*DEMODB_HIGH*'. sqlcl.setStmt('set sqlprompt "🙊 @|yellow _USER|@@@|green ' + someStringManipulation(dbIdentifier) + '|@@|yellow >|@ "'); Thank you

For reasons, I am using the following here-doc approach to export data to CSV. $ /oracle/sqlcl/bin/sql -s username/password@//host.mybiz.com.my:1521/dwh > arca.csv <psql tool when I want to load to Postgres Below error happens because the CSV file has blank row as first line. The column names are read as data instead, causing type error. $ psql -c "\COPY raw_data.arca FROM arca.csv CSV HEADER" ERROR: invalid input syntax for type date: "FILE_DATE" CONTEXT: COPY arca, line 2, column file_date: "FILE_DATE" If I remove the blank row manually, the following error then occurs because of carriage return character at the end of data $ psql -c "\COPY raw_data.arca FROM arca.csv CSV HEADER" ERROR: unquoted carriage return found in data HINT: Use quoted CSV field to represent carriage return. CONTEXT: COPY arca, line 101 How do I correctly use sqlcl with here-doc approach to produce CSV file without first blank line, and carriage return ending?

I'm trying to come up with a code that would count the number of digits in one field. in this case it's passports I want to make sure they have valid digits and characters. select p.passport_nbr, CASE WHEN Number = 0 THEN 1 ELSE FLOOR(LOG10(ABS(p.passport_nbr))) + 1 AS NDigits , is there a different code I can use. I use oracle sql

I'm trying to write a oracle SQL query to return if the values are not present then return the count as zero. My query : select company, sum(case when company IN ('ABC','DEF','GHI','JKL','MNO') then 1 else 0 end) count from table_name where trunc(msg_date) = trunc(sysdate) group by soc_id; Actual output | COMPANY | COUNT | | -------- | -------------- | | ABC | 1| | DEF | 2| | GHI| 3| | JKL | 7| Expected Output: | COMPANY | COUNT | | -------- | -------------- | | ABC | 1| | DEF | 2| | GHI| 3| | JKL | 7| | MNO | 0| Expected Output doesnot print any values of 'MNO' company. Is there any chance, we have oracle SQL query to solve this issue ? your response is much appreciated.

I've been using Oracle's SQLcl, specifically the lb command to use Liquibase , for the last day or so to use for versioning my development, staging and production databases, which are using Oracle's Autonomous Data Warehouse (version 19). It works fine when doing the tutorial changes, like the ones listed by Jeff Smith in his video on it here , although the syntax of the commands has changed a bit. I'm reaching a problem when I want to drop an index, if I just execute the SQL statement DROP INDEX EXAMPLE_IDX in my development environment and then run the lb genschema statement, it doesn't drop the index on my staging environment when I run lb update -changelog controller.xml. I think this is because it's generating a fresh Liquibase changelog every time I run lb genschema which seems to overwrite the previous changelog. I'm also having the same behaviour when trying to drop tables etc, and I'm not sure how I am supposed to propogate DROP statements on tables and indexes with the lb tool. Here's what I'm doing (users/passwords etc all changed). Open SQLcl to create the staging database's structure (v1)

{plain}
set cloudconfig wallets/dev.zip
connect user/password@dev_medium
lb genschema

set cloudconfig wallets/staging.zip
connect user/password@staging_medium
lb update -changelog controller.xml

Open SQL Developer on the dev database and delete the index.

{sql}
DROP INDEX INDEXNAME;

Open SQLcl to update the changelog and the staging database.

{plain}
set cloudconfig wallets/dev.zip
connect user/password@dev_medium
lb genschema

set cloudconfig wallets/staging.zip
connect user/password@staging_medium
lb update -changelog controller.xml

After doing these steps, I'm still noticing that the tables and indexes are still there, which means my database behaves differently to what it should be doing! If anyone has knowledge or experience of using the lb tool, SQLcl, or can let me know some best practices that would be amazing.

I want to execute a query that will produce an output that are SQL commands too to be executed later. Something like: SELECT 'DROP ' || OBJECT_TYPE || ' "' || owner || '"."' || object_name || '";' as command FROM ALL_OBJECTS; The problem is that the output always have the header. In this case "command". I tried the documentation , but its very simple and brief in its explanations. How can I disable the header, or where can I find a more complete documentation on how the formatting in SQLcl works?

I feel I'm probably missing something obvious here, but I can't see an easy way to have a separate login.sql for sqlplus and sqlcl This is problematic for me as the errors thrown up by sqlplus for the

commands it doesn't understand interfere with various scripts. I think I could do it with aliases that change the SQLPATH prior to starting sqlcl, but would prefer a cleaner way if possible. Is there a separate file name that only sqlcl will look for? Or separate location?

This is my login.sql SET sqlprompt "_user'@'_connect_identifier > " SET sqlformat ansiconsole SET serveroutput on SET lines 3000 How to set the sqlprompt to color red for Prod (and green color for testing)?

Version sql -version SQLcl: Release 18.4.0.0 Production I type this: SQL> exec dbaspace.long_ops; SID % Done Start Time Rem [s] Elapsed Message ==== ======= =================== ======= ======= ====================================================================================== There are currently no long running operations. PL/SQL procedure successfully completed. I press ARROW UP key to get the last command and SQLcl modifies my history like this: SQL> BEGIN dbaspace.long_ops; END;; Error starting at line : 1 in command - BEGIN dbss.long_ops; END;; Error report - ORA-06550: line 1, column 26: PLS-00103: Encountered the symbol ";" 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action: The rewritten history command does not work

this is our classic tnsnames.ora test1= (DESCRIPTION= (CONNECT_TIMEOUT=4) (TRANSPORT_CONNECT_TIMEOUT=3) (ENABLE=BROKEN) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=example1.example.com)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=example2.example.com)(PORT=1521)) ) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=EXAMPLE.EXAMPLE.DBS) ) ) I use this SQLcl command: sql -nohistory -noupdates -S $username/$password@$hostname:$port/$servicename @$filename How to specifiy multiple hostnames? Not only one? It's some kind of active passive cluster (Exadata). *Edit after first answer:* I added in shell script (in this dir tnsnames.ora): TNS_ADMIN=/example/example I call $sqlcl -nohistory -noupdates -S $username/$password@"MY-DB" @$filename and get back the error: ./script.sh USER = MY_USER URL = jdbc:oracle:thin:@MY-DB Error Message = IO Error: Unknown host specified USER = MY_USER URL = jdbc:oracle:thin:@MY-DB:1521/MY-DB Error Message = IO Error: Invalid connection string format, a valid format is: "host:port:sid"

I ran below script to get data from Oracle, and then load to Postgres with psql. SET SQLFORMAT INSERT SET FEEDBACK OFF SET TERMOUT OFF SPOOL asset_file.sql SELECT * FROM FINANCE.ASSET; SPOOL OFF QUIT However in SQLcl output, the first few lines are: REM INSERTING into finance.asset SET DEFINE OFF; Insert into finance.asset( ... which results in error because the first two lines not recognized by Postgres, using psql client with -f option. What are options that I can further put in SQLcl script to ensure those first two lines not being output? I don't want REM INSERTING and SET DEFINE .. in the output.