A Quick 4.1 Trick: SET SQLFORMAT

thatjeffsmith SQL Developer 50 Comments

Tell Others About This Story:

One of your favorite SQL Developer ‘tricks’ is the ability to pre-format query output. So instead of getting standard output back, maybe you want query results to come back as CSV.

But using that requires you to add code to your existing SQLs. Maybe instead it would be cool to set the overall script output format?

Now that we have our own SQL*Plus command line interface (AKA SQLcl), the commands that are available there are now also available in SQL Developer proper.

For example: instead of hacking up your individual statements to get your query results to be formatted to CSV, HTML, XML, etc., you can use the SET SQLFORMAT command to set the desired script output format for your SQL queries.

For example.

Instead of running: SELECT /*CSV*/ * from HR.EMPLOYEES;

I can run

SET SQLFORMAT CSV
SELECT * FROM HR.EMPLOYEES;

When I’m done with getting the output in that format, I can ‘UNSET’ it…and get the standard output back.

No need to hack up your queries anymore with the format you want - you can now set it as a default for ALL of your script output.

No need to hack up your queries anymore with the format you want – you can now set it as a default for ALL of your script output.

In addition to the normal formats, we now have a new one, ANSICONSOLE. One of the benefits, we bring the results back all at once, and check the column widths, and then resize the output such that it’s easier to read. No need to set column widths with various SQL*Plus formatting commands.

we try to make the query output as 'pretty' as possible

we try to make the query output as ‘pretty’ as possible

Available Formats

  • csv
  • html
  • xml
  • json
  • ansiconsole – ‘smart’ formats the output to best fit the screen based on width of data per page of results and the width of your output panel
  • insert – INSERT statements
  • loader – sql*loader
  • fixed
  • default
  • delimited – defaults to ‘,’ but can be set via SET SQLFORMAT DELIMITED delimiter left-enclosure right-enclosure
    SET SQLFORMAT DELIMITED ; ‘ ‘ – will give you semicolon delimited fields with strings single quoted. You’ll need v4.2 for this to be available.
Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 50

  1. s normal db user:

    SQL>desc dba_users

    hangs:-(

    SQL>desc v$session

    SP2-0749: Cannot resolve circular path of synonym “v$session”

    SQLcl: Release 4.2.0

    1. thatjeffsmith Post
      Author

      yesterday’s update works for me, both cases

      I am HR ON orcl > SHOW version
      Oracle SQLDeveloper Command-Line (SQLcl) version: 4.2.0.16.355.0402
       
      I am HR ON orcl > DESC dba_users
      Name                        NULL?    TYPE
      --------------------------- -------- ---------------------------
      USERNAME                    NOT NULL VARCHAR2(128)
      USER_ID                     NOT NULL NUMBER
      PASSWORD                             VARCHAR2(4000)
      ACCOUNT_STATUS              NOT NULL VARCHAR2(32)
      LOCK_DATE                            DATE
      EXPIRY_DATE                          DATE
      DEFAULT_TABLESPACE          NOT NULL VARCHAR2(30)
      TEMPORARY_TABLESPACE        NOT NULL VARCHAR2(30)
      CREATED                     NOT NULL DATE
      PROFILE                     NOT NULL VARCHAR2(128)
      INITIAL_RSRC_CONSUMER_GROUP          VARCHAR2(128)
      EXTERNAL_NAME                        VARCHAR2(4000)
      PASSWORD_VERSIONS                    VARCHAR2(12)
      EDITIONS_ENABLED                     VARCHAR2(1)
      AUTHENTICATION_TYPE                  VARCHAR2(8)
      PROXY_ONLY_CONNECT                   VARCHAR2(1)
      COMMON                               VARCHAR2(3)
      LAST_LOGIN                           TIMESTAMP(9) WITH TIME ZONE
      ORACLE_MAINTAINED                    VARCHAR2(1)
      I am HR ON orcl > DESC v$session
      Name                          NULL? TYPE
      ----------------------------- ----- --------------
      SADDR                               RAW(8 BYTE)
      SID                                 NUMBER
      SERIAL#                             NUMBER
      AUDSID                              NUMBER
      PADDR                               RAW(8 BYTE)
      USER#                               NUMBER
      USERNAME                            VARCHAR2(30)
      COMMAND                             NUMBER
      OWNERID                             NUMBER
      TADDR                               VARCHAR2(16)
      LOCKWAIT                            VARCHAR2(16)
      STATUS                              VARCHAR2(8)
      SERVER                              VARCHAR2(9)
      SCHEMA#                             NUMBER
      SCHEMANAME                          VARCHAR2(30)
      OSUSER                              VARCHAR2(30)
      PROCESS                             VARCHAR2(24)
      MACHINE                             VARCHAR2(64)
      PORT                                NUMBER
      TERMINAL                            VARCHAR2(30)
      PROGRAM                             VARCHAR2(48)
      TYPE                                VARCHAR2(10)
      SQL_ADDRESS                         RAW(8 BYTE)
      SQL_HASH_VALUE                      NUMBER
      SQL_ID                              VARCHAR2(13)
      SQL_CHILD_NUMBER                    NUMBER
      SQL_EXEC_START                      DATE
      SQL_EXEC_ID                         NUMBER
      PREV_SQL_ADDR                       RAW(8 BYTE)
      PREV_HASH_VALUE                     NUMBER
      PREV_SQL_ID                         VARCHAR2(13)
      PREV_CHILD_NUMBER                   NUMBER
      PREV_EXEC_START                     DATE
      PREV_EXEC_ID                        NUMBER
      PLSQL_ENTRY_OBJECT_ID               NUMBER
      PLSQL_ENTRY_SUBPROGRAM_ID           NUMBER
      PLSQL_OBJECT_ID                     NUMBER
      PLSQL_SUBPROGRAM_ID                 NUMBER
      MODULE                              VARCHAR2(64)
      MODULE_HASH                         NUMBER
      ACTION                              VARCHAR2(64)
      ACTION_HASH                         NUMBER
      CLIENT_INFO                         VARCHAR2(64)
      FIXED_TABLE_SEQUENCE                NUMBER
      ROW_WAIT_OBJ#                       NUMBER
      ROW_WAIT_FILE#                      NUMBER
      ROW_WAIT_BLOCK#                     NUMBER
      ROW_WAIT_ROW#                       NUMBER
      TOP_LEVEL_CALL#                     NUMBER
      LOGON_TIME                          DATE
      LAST_CALL_ET                        NUMBER
      PDML_ENABLED                        VARCHAR2(3)
      FAILOVER_TYPE                       VARCHAR2(13)
      FAILOVER_METHOD                     VARCHAR2(10)
      FAILED_OVER                         VARCHAR2(3)
      RESOURCE_CONSUMER_GROUP             VARCHAR2(32)
      PDML_STATUS                         VARCHAR2(8)
      PDDL_STATUS                         VARCHAR2(8)
      PQ_STATUS                           VARCHAR2(8)
      CURRENT_QUEUE_DURATION              NUMBER
      CLIENT_IDENTIFIER                   VARCHAR2(64)
      BLOCKING_SESSION_STATUS             VARCHAR2(11)
      BLOCKING_INSTANCE                   NUMBER
      BLOCKING_SESSION                    NUMBER
      FINAL_BLOCKING_SESSION_STATUS       VARCHAR2(11)
      FINAL_BLOCKING_INSTANCE             NUMBER
      FINAL_BLOCKING_SESSION              NUMBER
      SEQ#                                NUMBER
      EVENT#                              NUMBER
      EVENT                               VARCHAR2(64)
      P1TEXT                              VARCHAR2(64)
      P1                                  NUMBER
      P1RAW                               RAW(8 BYTE)
      P2TEXT                              VARCHAR2(64)
      P2                                  NUMBER
      P2RAW                               RAW(8 BYTE)
      P3TEXT                              VARCHAR2(64)
      P3                                  NUMBER
      P3RAW                               RAW(8 BYTE)
      WAIT_CLASS_ID                       NUMBER
      WAIT_CLASS#                         NUMBER
      WAIT_CLASS                          VARCHAR2(64)
      WAIT_TIME                           NUMBER
      SECONDS_IN_WAIT                     NUMBER
      STATE                               VARCHAR2(19)
      WAIT_TIME_MICRO                     NUMBER
      TIME_REMAINING_MICRO                NUMBER
      TIME_SINCE_LAST_WAIT_MICRO          NUMBER
      SERVICE_NAME                        VARCHAR2(64)
      SQL_TRACE                           VARCHAR2(8)
      SQL_TRACE_WAITS                     VARCHAR2(5)
      SQL_TRACE_BINDS                     VARCHAR2(5)
      SQL_TRACE_PLAN_STATS                VARCHAR2(10)
      SESSION_EDITION_ID                  NUMBER
      CREATOR_ADDR                        RAW(8 BYTE)
      CREATOR_SERIAL#                     NUMBER
      ECID                                VARCHAR2(64)
      SQL_TRANSLATION_PROFILE_ID          NUMBER
      PGA_TUNABLE_MEM                     NUMBER
      CON_ID                              NUMBER
      EXTERNAL_NAME                       VARCHAR2(1024)
      I am HR ON orcl >
    1. thatjeffsmith Post
      Author
  2. I’m trying to avoid another app to create a script. so I’m using sqlcl to create a script and then using again to run the script

    problem is that if I use set SQLFORMAT TEXT or any other combination… it add ” or something worse

    just doing set feedback off
    almost get where I need but it does add 2 extra columns for ever 12 or so lines of output as text below… any ideas to just get a clean file?

    CMD
    ——————————————————-

    I need the file to be like:

    SET SQLFORMAT loader
    SPOOL C:\out\oracle_backup\tracker_views_2016_09_30.sql
    DDL VW_TRACKER_PSR;
    DDL VW_TRACKER_PROJECTS_SUMMARY_02;
    DDL VW_TRACKER_PROJECTS_SUMMARY_01;
    DDL VW_TRACKER_PROJECTS_SUMMARY;
    DDL VW_TRACKER_PM_TL_TASKS;
    DDL VW_TRACKER_PM_TL_PROJECTS;
    DDL VW_TRACKER_PM_TL_MONTHS;
    DDL VW_TRACKER_PM_TASK_RESOURCES;
    DDL VW_TRACKER_PM_TASKS;
    DDL VW_TRACKER_HSE_TOT_HOURS_SPEC;
    DDL VW_TRACKER_HSE_TOT_HOURS_SEED;
    DDL VW_TRACKER_HSE_TOT_HOURS;
    DDL VW_TRACKER_GBS_REV_REPORT_SEED;
    DDL VW_TRACKER_GBS_PRJ_BY_PHASE;
    DDL VW_TRACKER_GBS_PERIOD_PER_COA_;
    DDL VW_TRACKER_GBS_DELTA;
    DDL VW_TRACKER_EPVL_S;
    SPOOL OFF
    QUIT;

    1. thatjeffsmith Post
      Author

      problem is that if I use set SQLFORMAT TEXT or any other combination… it add ” or something worse

      I can’t tell what your problem is – instead of just showing me your script, show me your script output, specifically the ‘bad’ part.

      1. when I use SQLFORMAT TEXT it adds ”

        sample_______________________________________________________________

        “CMD”
        “SET SQLFORMAT loader”
        “SPOOL C:\out\oracle_backup\tracker_views_2016_09_30.sql”
        “DDL VW_TRACKER_PSR;”
        “DDL VW_TRACKER_PROJECTS_SUMMARY_02;”
        “DDL VW_TRACKER_PROJECTS_SUMMARY_01;”
        “SPOOL OFF”
        “QUIT;”

        1. thatjeffsmith Post
          Author
  3. Hi Jeff,

    I’ve been reading about – and playing with – the various sqlformat settings, such as ansiconsole. I am looking for a way to simply avoid truncating column headings in the Script Output because I do a lot of pasting and manipulating of data in text files. The ansiconsole does this, but it also adds unwanted formatting to the data. Is there an easy way to show the full column heading and leave the data in a default format?

    1. thatjeffsmith Post
      Author
      1. Thanks, Jeff. This is good to know for additional data formatting, but I am not seeing how this addresses my question. I am looking for full column headings in the Script Output.

        Thanks – I appreciate your responses.

        1. thatjeffsmith Post
          Author
          1. Example – I run the following query:

            select * from T_RE_AID_ELIG_RSN
            where SAK_AID_ELIG = 674008026

            Which displays these results in the Script with sqlformat set to default:

            SAK_AID_ELIG CDE DTE_ADDED
            ————- — ———
            674008026 052 20121218
            674008026 404 20160706

            And these results with sqlformat set to ansiconsole:

            SAK_AID_ELIG CDE_AID_ELIG_REASON DTE_ADDED
            674,008,026 052 20,121,218
            674,008,026 404 20,160,706

            I would like to be able to display the full column names as the ansiconsole setting does, but with the unformatted data as the default setting does.

            Thanks,
            Dave

          2. thatjeffsmith Post
            Author


            SQL> set sqlformat ansiconsole
            SQL> /
            SAK_AID_ELIG CDE_AID_ELIG_REASON DTE_ADDED
            674008026 052 21121218
            674008026 404 21160706

            SQL> col cde_aid_elig_reason format a20
            SQL> set sqlformat
            SQL Format Cleared
            SQL> /

            SAK_AID_ELIG CDE_AID_ELIG_REASON DTE_ADDED
            ------------ -------------------- ----------
            674008026 052 21121218
            674008026 404 21160706

            SQL>

            We changed the default number display for column headers in ansiconsole. You can refer to the link I shared previously if you want to at some point format them differently.

            In the above code sample, it would behave the exact same way in SQL*Plus.

  4. Hi Jeff,
    nice feature but could it be that there is one bug.
    When I export with sqlformat insert the generated output is not executable as the table names are lower case enclosed with double quotes.
    Best regards
    Rudolf

    1. thatjeffsmith Post
      Author

      What version are you running?

      Version 4.1.3 –

      SELECT /*insert*/ * FROM hr.employees
       
      INSERT INTO hr.employees (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,REVIEW,EMP_DEVICE,CLOB_STUFF) VALUES (193,'Britney','Everett','BEVERETT','650.501.2876',to_date('03-MAR-97 12.00.00','DD-MON-RR HH.MI.SS'),'SH_CLERK',4485,NULL,123,50,'{"comments": []}',NULL, EMPTY_CLOB());
      INSERT INTO hr.employees (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,REVIEW,EMP_DEVICE,CLOB_STUFF) VALUES (194,'Samuel','McCain','SMCCAIN','650.501.3876',to_date('01-JUL-98 12.00.00','DD-MON-RR HH.MI.SS'),'SH_CLERK',3680,NULL,123,50,'{"comments": []}',NULL, EMPTY_CLOB());
      INSERT INTO hr.employees (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,REVIEW,EMP_DEVICE,CLOB_STUFF) VALUES (195,'Vance','Jones','VJONES','650.501.4876',to_date('17-MAR-99 12.00.00','DD-MON-RR HH.MI.SS'),'SH_CLERK',3220,NULL,123,50,'{"comments": []}',NULL, EMPTY_CLOB());
      INSERT INTO hr.employees (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,REVIEW,EMP_DEVICE,CLOB_STUFF) VALUES (196,'Alana','Walsh','AWALSH','650.507.9811',to_date('24-APR-98 12.00.00','DD-MON-RR HH.MI.SS'),'SH_CLERK',3565,NULL,124,50,'{"comments": []}',NULL, EMPTY_CLOB());
      INSERT INTO hr.employees (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,REVIEW,EMP_DEVICE,CLOB_STUFF) VALUES (197,'Kevin','Feeney','KFEENEY','650.507.9822',to_date('23-MAY-98 12.00.00','DD-MON-RR HH.MI.SS'),'SH_CLERK',3450,NULL,124,50,'{"comments": []}',NULL, EMPTY_CLOB());
       
       106 ROWS selected
  5. Wonderful tool!!!

    But I don’t get the colors to work as expected. After my expected output, I always get the “command”. I don’t want to see that. I only want to see “Hello World!” in colors.

    SQL> set sqlformat ansiconsole
    SQL> select ‘@|fg_green Hello|@ @|fg_red World!|@’ from dual;
    ‘@|FG_GREENHELLO|@@|FG_REDWORLD!|@’
    Hello World! @|fg_green Hello|@ @|fg_red World!|@

    SQL> select ‘@|fg_green Hello |@’, ‘@|fg_red World!|@’ from dual;
    ‘@|FG_GREENHELLO|@’ ‘@|FG_REDWORLD!|@’
    Hello @|fg_green Hello [email protected]! @|fg_red World!|@

    I downloaded this file today sqlcl-4.2.0.16.175.1027-no-jre.zip. Which I guess is the latest release.

    Please help /Jocke

    1. thatjeffsmith Post
      Author
  6. Is there an option using sqlcl to specify the encoding to UTF-8 when exporting data to a text file? I can specify the encoding within SQL Developer and it works fine, however, I could not find any option to do this in SQLcl. If you don’t currently support it, are there any plans to add this functionality. This tool would work nicely for a project that I am working on except for the encoding which would be a showstopper for us.

    Thanks in advance!

  7. Hi Jeff,

    I need my query output as data should come in single line without being wrapped up and 3000 characters need to be displayed .

    I am using
    Set linesize 3000;

    However, only 2000 charcters are displayed with wrapped out .

    Can you resolve it please.

    Appreciate your help!

    1. thatjeffsmith Post
      Author
      1. Hi Jeff,

        output is spooled but then too I am facing the issue.
        I am running query via toad.

        Can running a query through toad cause issue of truncation?

        1. thatjeffsmith Post
          Author

          Toad? We’re talking about Toad? No can help you there.

          But in our command-line, SQLcl…

           
          [email protected]?? >SET long 3000
          [email protected]?? >spool 3000.txt
          [email protected]?? >SELECT lots_of_text FROM three3cols;
           
          LOTS_OF_TEXT                                                                                               
          -----------------------------------------------------------------------------------------------------------
          zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
          zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
          zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
          zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
          zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
          zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
          zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
          zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
          zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
          zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
          zzzzzzzzzzzzzzzzzzzzzzzzzzzzz... -- to 3000 characters
           
          [email protected]?? >spool off;
          [email protected]?? >exit
          Disconnected FROM Oracle DATABASE 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
          WITH the Partitioning, OLAP, Advanced Analytics AND REAL Application Testing options
           
          ┌─[10:09:54][wvu1999][MacBook-Air-Smith]:/Applications/sqlclOTN/bin$
          └─>wc -c 3000.txt
              3486 3000.txt
          ┌─[10:10:27][wvu1999][MacBook-Air-Smith]:/Applications/sqlclOTN/bin$
          └─>more 3000.txt
          @|red HR|@@@|green orcl|@@|blue <U+1F37B><U+1F37A> >|@SELECT lots_of_text FROM three3cols;
           
          LOTS_OF_TEXT                                                                                               
          -----------------------------------------------------------------------------------------------------------
          zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
          zzzzzzzzzzzzzzzzzzzzzzzzz -- to the 2,999 z chars i put in the column

          So, it should be working – but you haven’t shown me exactly what you’re doing, so I’m guessing.

  8. Hi Jeff,
    I love the SQLFORMAT DELIMITED feature. I’m able to paste a TAB character one space after the DELIMITED keyword for a tab delimited file, but I don’t see a way to *not* include any start/end string qualifiers. Is there some trick to avoid quotes around the data? Otherwise, I will strip out the quotes after the output file is generated.

    set sqlformat delimited

  9. I like the addition of SET SQLFORMAT DELIMITED option. However, at this time the option doesn’t appear in the output of HELP SET SQLFORMAT.

    I only see these:
    SET SQLFORMAT { csv,html,xml,json,ansiconsole,insert,loader,fixed,default}

    Thanks.

    1. thatjeffsmith Post
      Author
      1. Hi, is it possible to specify that {open string char} and {close string char} be nothing (i.e. no quotes surrounding strings)?

        1. thatjeffsmith Post
          Author
  10. Hi Jeff
    SQLcl looks really nice. the “set sqlformat” is very usefull. We just tried to use “set sqlformat insert” to export the table data on the client side to an text file. As there are BLOBs in the table, I was really impressed, that it just worked (with encoding ..) (it looks that it is the only format who supports BLOB).
    But it looks that there are some issues with it.
    Here a little test case:


    SQL> col for binary_lob a50
    SQL> create table blob_test (id number, text varchar2(10), binary_lob blob);
    SQL> insert into blob_test (id,text,binary_lob) values(1,’TEST 123′,utl_raw.cast_to_raw(‘TEST 123′));

    SQL> set sqlformat insert
    SQL> select * from blob_test;

    REM INSERTING into BLOB_TEST
    SET DEFINE OFF;
    Insert into BLOB_TEST (ID,TEXT,BINARY_LOB) values (1,’TEST 123′,’626567696E2030207575656E636F64652E6275660D0A2B35243533352220512C432C200D0A0A656E64′);

    SQL> set sqlformat
    SQL> SET DEFINE OFF
    SQL> Insert into TESTLI (ID,TEXT,BINARY_LOB) values (1,’TEST 123′,’626567696E2030207575656E636F64652E6275660D0A2B35243533352220512C432C200D0A0A656E64′);

    SQL> select * from blob_test;

    ID TEXT BINARY_LOB
    ———- ———- ——————————————————————————–
    1 TEST 123 5445535420313233
    1 TEST 123 626567696E2030207575656E636F64652E6275660D0A2B35243533352220512C432C200D0A0A656E

    select id, text, utl_raw.cast_to_varchar2(binary_lob) as binary_lob from blob_test;

    SQL> select id, text, utl_raw.cast_to_varchar2(binary_lob) as binary_lob from blob_test

    ID TEXT BINARY_LOB
    ———- ———- ————————————————–
    1 TEST 123 TEST 123
    1 TEST 123 begin 0 uuencode.buf
    +5$535″ Q,C,

    end

    SQL Session Trace shows:
    BEGIN :1 := utl_encode.uuencode(utl_raw.cast_to_raw(:2 ),’1′,’uuencode.buf’) ; END;

    —–

    With “bigger” BLOBs the “sqlformat insert” shows something like:
    TO_BLOB(HEXTORAW(‘626567696E2030207575656E636F64652E6275660D0A6C4F5B5C2021372D5220205D413B47314C3C4259233B56554D3B5659’))
    || TO_BLOB(HEXTORAW(‘21345531524F555A5F4F534C202550282020444420213731543E372145332020243D2635583D2730202444514A383739410D’))
    || TO_BLOB(HEXTORAW(‘0A6C2B5651413B463C4F345731523A3659472E574152202055413B47314C3C42592238372D4530352D34272B5C4D262B5C40’))
    || TO_BLOB(HEXTORAW(‘275B5C222020292C202031443B573D4E3D20202F3326254E3D2651520D0A6C2B5429413C5635213455305B332020253C4645’))
    || TO_BLOB(HEXTORAW(‘473A27315120275820205741503C5724203F4020203C5724203F4020203C272D512027582020272150202020202127302021’))
    || TO_BLOB(HEXTORAW(‘2731523D3634200D0A6C202020453D20202D354455493C573134383659443936553633372D512027582020272D5120275820’))
    || TO_BLOB(HEXTORAW(‘20272D5120275820202721533C30215E202021503C20202020215D54202048532C3258500D0A6C2C3258522C232450202020’))
    || TO_BLOB(HEXTORAW(‘2029373020255539453C47315238363D533836594C38363D45392625543D36553F334635553C5724203F4020203C272D5120’))
    || TO_BLOB(HEXTORAW(‘2758202027215020202020293730200D0A6C26244D4F3B4631493D26454F3B455D213B4731453A3651343836594439365536’))
    || TO_BLOB(HEXTORAW(‘33302020202235542021392B3B5659443A3731493B56593F3526254E3926354D3544553F33463555202020200D0A6C225730’))
    || TO_BLOB(HEXTORAW(‘20203359533C30215E202021503C2020202021595420202450202020202927302023244526375531283134593F3134513331’))
    || TO_BLOB(HEXTORAW(‘302020202059542020285D2F372020202020443D20202C0D0A303234393F3524412533455D2533252D250D0A0A656E64′))

    This does not work in SQL.

    SQL> insert into blob_test (id,text,binary_lob) values(1,’TEST 123’,to_blob(utl_raw.cast_to_raw(‘TEST 123’)) || to_blob(utl_raw.cast_to_raw(‘TEST 123′)));

    Error starting at line : 1 in command –
    insert into blob_test (id,text,binary_lob) values(1,’TEST 123’,to_blob(utl_raw.cast_to_raw(‘TEST 123’)) || to_blob(utl_raw.cast_to_raw(‘TEST 123′)))
    Error at Command Line : 1 Column : 64
    Error report –
    SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
    00932. 00000 – “inconsistent datatypes: expected %s got %s”
    *Cause:
    *Action:

    SQL> insert into blob_test (id,text,binary_lob) values(1,’TEST 123’,to_blob(utl_raw.cast_to_raw(‘TEST 123’)));
    1 row inserted.

    cheers

  11. Hi Jeff,

    just want to share an experience maybe it will help someone . I’m starting to use more and more the sqlcl client . more specifically I create a bash script to generate reference file in csv that I use to transfer to other liquibase projet. The bash script call a sql script file using both commands set sqlformat csv and spool.

    the 1rst observation and you mention that on one of your comment in the past is the the spool file for some reason will create the first line empty. This whatever set options you will try to specify ( feedback off, echo off , verify off etc,,,) believe me I tried almose all the combinations. Not big deal but good thing to know to avoid searching.

    2nd, like you mention Jeff with sqlcl command line tool you cannot specity the delimiter ( comma ) or the enclosed character ( double quote ). When you export any query with string / int fields you ll never have any problem. Sqlcl will know when to enclosed in case there is a comma inside a column value. The issue that I faced was when you query numeric and timestamp columns that both could have the decimal character.

    basically those values won t be enclosed automatically and therefore will create inconsistency in terms of number of columns inside the csv. The temporary solution that I have found to be the cleanest was to change some session values.
    ALTER SESSION SET nls_timestamp_format=’YYYY-MM-DD’;
    ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ‘. ‘;

    my recommandation for complete flexibility :
    choice 1 let us choose the delimiter character as in sqldeveloper and enclosed character
    choice 2 always encode the columns whatever their type or their content.

    Here you go hope that helps.

    Ricardo

    1. thatjeffsmith Post
      Author
  12. Hi,

    A sqlformat “pivot” would be nice (similar to Tom Kyte’s “print_table” function) for displaying rows with many columns.

    Cheers, Markus

    1. thatjeffsmith Post
      Author
      1. Thanks. I’m always getting “error in line 1, unknown command” when I try “script printtab select * from dual;”. Any idea?

        Cheers, Markus

        1. thatjeffsmith Post
          Author
    1. thatjeffsmith Post
      Author
  13. Hi Jeff,
    It is possible to set Delimiter, Header use/not, Encoding to SQLDEVELOPER CLI to make batch export file as
    select /*delimited*/ * from table;

    Thanks.
    Peter

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  14. Hi Jeff:

    Nice work. Kudos to you and the whole SQL Developer team. I noticed that when outputting JSON all of the strings have a leading space. Is this by design, or is this a small bug?

    Keith

  15. thatjeffsmith Post
    Author

    I was a bad boy – I don’t think the json formatter is in the 4.1 Early Adopter release, but it will be in EA2 and the 4.1 final version…

    Run this to ‘clear’ the SQLFORMAT
    SET SQLFORMAT

    And you’ll see your output return to ‘normal’ with this message as well.
    ‘SQL Format Cleared’

Leave a Reply

Your email address will not be published. Required fields are marked *