Comments 67

  1. Hello,

    I’m having issues with the script output on SQL 4.1.5.

    The original output shows an example below:-

    C_ELI
    ————
    N

    Then when I set format to ansiconsole I get the whole field name as below:-

    C_ELIGIBLE
    N

    This is partly what I want, but still want to keep the heading split separately with the ———— and the results shown below that.

    However sql 4.1.5 currently only shows part of the field name. Is there anyway to combine the full name and the line breaker between heading and results?

    1. thatjeffsmith Post
      Author

      Use SQLcl – the script output area in SQL Developer isn’t an actual command line interface environment, so certain things don’t work 100% there, like what you’re seeing.

      Also, 4.1.5 is old. Time to upgrade?

  2. Hi Jeff,

    I’m running SQL Developer Version 4.2.0.17.089.

    My ‘LOGIN.SQL’ contains the following:-

    SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WORD_WRAPPED
    SET TIMING ON
    SET PAGESIZE 205
    SET LONG 1000000
    SET SQLFORMAT ANSICONSOLE

    I have configured SQL Developer’s Preferences to look for ‘LOGIN.SQL’ from a specific folder on C:\Users\…

    The issue is that SET TIMING ON is not getting into effect for some odd reason. When I launch SQL Developer and query SHOW TIMING, it displays “TIMING OFF”.

    All the other settings are being reflected correctly when I use SHOW command except for TIMING.

    Is there any other setting somewhere in the Preferences that I need to enable?

    1. thatjeffsmith Post
      Author
    2. That’s what I thought so as I was spinning around for 30 mins trying to figure out why all other settings were getting into effect but not TIMING one.

      My team uses TIMING setting a lot in order to see “Elapsed Time” when they execute queries to figure out how long it takes.

      I have also noticed that the “Elapsed Time” value displayed (when I manually run SET TIMING ON on the worksheet before running a query) does not match with the execution time displayed on the results grid where it says on top something like “All Rows Fetched: N in M seconds”.

      Elapsed Time displayed does not match with M seconds.

      Is this a bug as well?

    3. thatjeffsmith Post
      Author
  3. hi all,

    does anybody know a way to disable all this and get back the very simple, but very fast script-output behavior like in e.g. version 4.0.3.16 BUILD-16.84.

    because even with all the tweaks to optimize sql-developers sqlplus-output-emulation, it takes roughly 3 to 4 times the ammount of time to display the same information.

    i’m not interested in any formats or pretty looking. i’m interested in high data throughput. if this feature cuts my throughput by two thirds, three quarters or even more, i wanna know if there is a chance to get rid of it completely.

    thx, stefan

    1. thatjeffsmith Post
      Author

      Disable WHAT exactly?

      >>get back the very simple, but very fast script-output behavior
      Can you elaborate on this? Like, can you share a script that’s not doing what you expect/want and share any performance hits between 4.0 and 17.4?

    2. hi Jeff,

      how to explain without exactly knowing what has changed and when – but i’ll try.

      after looking at the result of the “show all” command i’ve found what i meant:
      (show all 4.0.3.16):

      appinfo is OFF and set to “SQL Developer”
      arraysize default
      autocommit OFF
      autoprint OFF
      autotrace OFF
      colsep ” ”
      copycommit 0
      define “&”
      echo OFF
      escape OFF
      FEEDBACK ON for 6 or more rows
      heading ON
      headsep “|” (hex 7c)
      linesize -1
      long 80
      null “”
      numformat “”
      numwidth 10
      pagesize -1
      serveroutput OFF
      spool OFF
      sqlcode 0
      termout ON
      timing OFF
      USER is xxxx
      verify ON
      wrap : lines will be wrapped

      (show all 17.4.0.355):

      _prelim OFF
      appinfo ist OFF und auf “SQL Developer” gesetzt
      arraysize Standard
      autocommit OFF
      autoprint OFF
      autotrace OFF
      BTitle OFF
      oracle.net.tns_admin=null
      javax.net.ssl.trustStore=null
      javax.net.ssl.trustStorePassword=null
      javax.net.ssl.keyStore=null
      javax.net.ssl.keyStorePassword=null
      oracle.net.ssl_server_dn_match=null
      oracle.net.ssl_version=null
      colinvisible OFF
      colsep ” ”
      concat “.” (hex 2e)
      copycommit 0
      define “&” (hex 26)
      show desc is obsolete.
      echo OFF
      errorlogging is OFF
      escape OFF
      FEEDBACK ON für 6 oder mehr Zeilen
      Überschrift ON
      headsep “|” (hex 7c)
      linesize 1559
      long 80
      longchunksize 80
      newpage 1
      null /*notset*/
      numformat “”
      numwidth 10
      pagesize 14
      PAUSE ist OFF
      Release 1102000400
      scan ON
      secureliterals DEFAULT
      serveroutput OFF
      showmode OFF
      space ” ”
      spool OFF
      sqlcode 0
      sqlprompt “SQL> ”
      suffix “sql”
      termout ON
      timing OFF
      TNS Lookup locations
      […]
      Available TNS Entries
      […]
      trimout ON
      trimspool OFF
      ttitle OFF and is the first few characters of the next SELECT statementUSER ist “xxxx”
      verify ON
      Kein Wallet-Speicherort definiert

      wrap: Zeilen werden umgebrochen

      pagesize as well as linesize are set to -1 in version 4.0.3.16 !!!

      all i need is a possibility to get to set those values back to -1 which won’t work in 17.4.0.355 with the following messages:
      set linesize -1
      SP2-0267: Linesize-Option -1 außerhalb des gültigen Bereichs (1 bis 32767)

      set pagesize -1
      SP2-0267: pagesize-Option -1 außerhalb des gültigen Bereichs (0 bis 50000)

      please excuse the german error messages, i haven’t switched the language in the new installation yet (would be nice if one could change it via preferences ;))

      but it shows already another problem: pagesize has a maximum allowed value of 50000. how shall i get 100000 rows output with only one occurrence of the header? with “set pagesize 0” i don’t get no header at all.

      but back to the performance issue – i have quite a simple sample. one *.csv-file with an external table on top with the following definition:

      CREATE TABLE ELT_SONDERFILTER
      (
      STICHTAG DATE
      , BG_NUMMER CHAR(14 CHAR)
      , KUNDENNUMMER CHAR(10 CHAR)
      , PRUEFGRUND VARCHAR2(255)
      )
      ORGANIZATION EXTERNAL
      (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY OPDS_SOFI
      ACCESS PARAMETERS
      (
      RECORDS DELIMITED BY 0X’0D0A’
      NOLOGFILE
      NOBADFILE
      NODISCARDFILE
      FIELDS TERMINATED BY ‘;’
      (
      stichtag CHAR(10) date_format DATE mask “DD.MM.YYYY”,
      bg_nummer CHAR(14),
      kundennummer CHAR(10),
      pruefgrund CHAR(255)
      )
      )
      LOCATION
      (
      OPDS_SOFI: ‘sonderfilter.csv’
      )
      )
      REJECT LIMIT 0;

      the file has 93100 rows. there are no NULL values, only column PRUEFGRUND has less data than it could have. everey row of PRUEFGRUND uses only 5 characters of the 255 possible ones.

      there is no limit to the number of rows displayed in script output (Tools.Preferences.Database.Worksheet.”Max Rows to print in a script” = )

      in version 4.0.3.16 build MAIN-16.84 run:

      SELECT * FROM elt_sonderfilter;

      result is complete as a block of 93105 rows (2 rows header, 3 rows for “93100 rows selected” and surrounding new lines) after 5.3s on av. 0.938 SD (10 runs, each run starts with a freshly cleared script output pane)

      in version 17.4.0.355 build 355.2349 run:

      SET LINESIZE 32000
      SET PAGESIZE 0
      SELECT * FROM elt_sonderfilter;

      result is complete as a block of 93103 rows (0 rows header, 3 rows for “93100 rows selected” and surrounding new lines) after 19.9s on av. 0.222 SD (10 runs, each run starts with a freshly cleared script output pane)

      after viewing the results of my little test set i must say: duration increases by almost factor 4, for only an almost identical result (the header is missing) that looks like a huge drop in performance to me

      ps: i checked the results of both versions with a diff tool and except for the missing header in the 17.4.0.355 output, the results are identical

    3. sry for double post,
      once i see my reply, next time i don’t see it – repost it and then see both of my identical posts.

      so the second one can be removed without further comment, as well as this one of course 😉

      thx, stefan

    4. thatjeffsmith Post
      Author

      That’s way too much for me to consume in a blog post comment. can you please open an SR with MOS for the performance issue? BTW, does the perf issue only rear its head when this is an EXTERNAL table?

      The primary change in 4.0 to what you see today is to have the default script output behavior mirror what SQL*Plus is doing. That resulted in MANY changes, one of which is default setting for LINESIZE…I think.

    5. i already thought it might be too much 😉

      for i’m quite new here, can you give me a hint what “SR with MOS” means?

      the issue is definetely not limited to the use of external tables, for i first discovered it as a colleague showed me his new version (17.4.0.355) and i tried to explain to him why i wouldn’t use even this newest edition. and as it happens, he has not a single external table, that is accessable to him. but the slow performance showed even with only 5000 rows (default max rows in script output) due to default page-and linesize. his test object was quite a wide table, so the header took at least half a page to display. even after cancelling the execution we had to wait a good fewof seconds until the output finished.

      just now i had another idea, how to describe the performance issue even without comparing it to another version.

      take any statement you like, as long as it produces some thousand rows of output.

      execute it as script via scriptrunner (F5) and then compare elapsed time to execution and complete fetch of all rows via execute Statement. without a long test series i just compared my former test object (external table with the 93100 rows).

      version 17.4.0.355:
      duration via scriptrunner: 21.285s
      duration via execute statement + fetch all: 5.796s (maybe + 0.009s execution time, i don’t know if “all rows fetched”-time includes execution time)

      so the problem isn’t the fetching of the data, but the emulation of the sqlplus output.

    6. thatjeffsmith Post
      Author

      due to default page-and linesize
      run select * from all_objects – how long does it take? I got 5,000 records back in 2.098 seconds.

      a hint what “SR with MOS” means?
      Sorry, we do have an issue with acronyms and abbreviations around here.
      A Service Request with My Oracle Support. If you pay for support on your database, you can open cases for SQL Developer.

  4. Hi Jeff,
    We are using SQL Developer 17.4. We have the following issue (Same behavior in 17.2 too).
    SQL PLus Command:

    SET PAGES 80 LINES 132 verify off

    COLUMN CTIME NEW_VALUE C_TIME NOPRINT;
    SELECT TO_CHAR (SYSDATE, ‘HH24:MI:SS AM’) CTIME FROM DUAL;

    TTITLE ON
    TTITLE RIGHT ‘TIME : ‘ C_TIME ‘ ‘ SKIP 2

    SELECT ‘&C_TIME’ CTIME1 FROM DUAL;

    Output: TTitle output has issue: It print twice once with the value –> TIME : 17:21:40 PM
    skips 2
    and next with the literal value: TIME : C_TIME

    Where as SQL PLus prints only once with the value: TIME : 17:21:40 PM
    Do you know if there is some thing wrong with SQL Dev’s SQL Plus functionality

    Thank you for your help.
    Kishan

  5. Hi Jeff, thanks for this great article but I am still unable to format the break on a column without repeating the column name. I am using SQL Developer Version 4.1.5.21.

    My code is like this

    set linesize 80
    break on line_manager skip 1

    select line_manager , column1, column2, clolumn3, column4 from table a

    output
    line_manager 1 column1, column2, clolumn3, column4
    line_manager 1 column1, column2, clolumn3, column4
    line_manager 1 column1, column2, clolumn3, column4
    line_manager 1 column1, column2, clolumn3, column4

    i.e. line_manager is still repeating.

    This is my “show all” output

    appinfo is OFF and set to “SQL Developer”
    arraysize default
    autocommit OFF
    autoprint OFF
    autotrace OFF
    BTitle OFF
    colsep ” ”
    concat “.” (hex 5c)
    copycommit 0
    define “&”
    echo OFF
    escape OFF
    FEEDBACK ON for 6 or more rows
    heading ON
    headsep “|” (hex 7c)
    linesize 1448
    long 80
    longchunksize 80
    null null
    numformat “”
    numwidth 10
    pagesize 14
    PAUSE is OFF
    release 1201000200
    scan ON
    serveroutput ON SIZE 1000000
    space ” ”
    spool OFF
    sqlcode 0
    sqlprompt “SQL> ”
    suffix “sql”
    termout ON
    timing OFF
    trimout OFF
    trimspool OFF
    TTitle OFF
    USER is “XXXXXXXXX”
    verify ON
    wrap : lines will be wrapped

    Thanks for your help.

    James

    1. thatjeffsmith Post
      Author

      Time to upgrade – version 4.1.5 is old. I’m on version 17.3.

      You didn’t share with me your table, so I used one of my own. Here’s SQLDev’s output next to SQL*Plus’s in Database 12.2.

    2. Hi Jeff,

      Many thanks for getting back on this. Apologies for troubling you with this basic functionality. I know that I can do this in SQL Plus but I am just curious why this is not working for me in SQL Developer. I have upgraded my version to Version 17.3.1.279.

      I use the Emp table for this query but it is still not working for me. I am beginning to think it is something to do with my preference but I can figure out what I have turned off.

      set linesize 80
      col job format a20
      break on job skip 1

      select job, empno,deptno
      from emp
      order by job

      ANALYST 7788 20
      ANALYST 7902 20
      CLERK 7934 10
      CLERK 7900 30
      CLERK 7369 20
      CLERK 7876 20
      MANAGER 7698 30
      MANAGER 7566 20
      MANAGER 7782 10
      PRESIDENT 7839 10
      SALESMAN 7844 30
      SALESMAN 7654 30
      SALESMAN 7521 30
      SALESMAN 7499 30

    3. thatjeffsmith Post
      Author

      I’m in 17.3, same code as you

      JOB                       EMPNO     DEPTNO
      -------------------- ---------- ----------
      ANALYST                    7788         20
                                 7902         20
       
      CLERK                      7934         10
                                 7900         30
                                 7369         20
                                 7876         20
       
      MANAGER                    7698         30
                                 7566         20
                                 7782         10
       
      JOB                       EMPNO     DEPTNO
      -------------------- ---------- ----------
       
      PRESIDENT                  7839         10
       
      SALESMAN                   7844         30
                                 7654         30
                                 7521         30
                                 7499         30
       
       
      14 rows selected.

      If I run SHOW ALL in a worksheet, I see this
      _prelim OFF
      appinfo is OFF and set to “SQL Developer”
      arraysize default
      autocommit OFF
      autoprint OFF
      autotrace OFF
      BTitle OFF
      oracle.net.tns_admin=null
      javax.net.ssl.trustStore=null
      javax.net.ssl.trustStorePassword=null
      javax.net.ssl.keyStore=null
      javax.net.ssl.keyStorePassword=null
      oracle.net.ssl_server_dn_match=null
      oracle.net.ssl_version=null
      colinvisible OFF
      colsep ” ”
      concat “.” (hex 2e)
      copycommit 0
      define “&” (hex 26)
      show desc is obsolete.
      echo OFF
      errorlogging is OFF
      escape OFF
      FEEDBACK ON for 6 or more rows
      heading ON
      headsep “|” (hex 7c)
      linesize 1280
      long 80
      longchunksize 80
      newpage 1
      null /*notset*/
      numformat “”
      numwidth 10
      pagesize 14
      PAUSE is OFF
      release 1202000100
      scan ON
      secureliterals DEFAULT
      serveroutput OFF
      showmode OFF
      space ” ”
      spool OFF
      sqlcode 0
      sqlprompt “SQL> ”
      suffix “sql”
      termout ON
      timing OFF
      TNS Lookup locations
      ——————–
      1. USER Home dir
      C:\Users\jdsmith
      2. ORACLE_HOME
      C:\Oracle\product\12.1.0\client_1\network\admin

      Location used:
      ————-
      C:\Oracle\product\12.1.0\client_1\network\admin

      Available TNS Entries
      ———————
      DevDay11
      DevDay12CDB
      DevDay12CDB
      DevDay12PBJ
      NoWay
      WHOA
      trimout ON
      trimspool OFF
      ttitle OFF and is the first few characters of the next SELECT statementUSER is “HR”
      verify ON
      No Wallet location defined

      wrap : lines will be wrapped

    4. Hi Jeff,

      Thanks for this. I forgot that I have to run this as a script to work. This works now on F5 but I now face 2 problems;

      1. Not able to export the query using right-click like when you run as a statement.
      Copy and paste to excel removes the column separators. I have read your
      previous tips on using the hint select /* csv */ but that does not get rid of
      duplicates on the ‘break on’
      2. How to remove the column headers after printing 11 rows.

      Many thanks Jeff

    5. thatjeffsmith Post
      Author
  6. This is great and exactly what I need, however I now have lost the ‘——-‘ column header when running a script. I am using SQL Developer Version 4.1.5.21. I can’t seem to find a way to get the ——- header back in between my column name and the queried data. I tried SET UNDERLINE ‘-‘ but I got back SP2-0158: unknown SET option “underline”. Does anyone know how to get back the ——— header separators?

    1. thatjeffsmith Post
      Author
  7. I found an issue with BREAK and COMPUTE. It seems it works only if the query is simple enough on the outermost level. E.g.
    break on x
    compute sum of y on x
    select a.x, count(*) y from …
    will not work becaues of the table prefix for x and because of the “complex” expression for y.
    workaround for table prefix is to use alias:
    select a.x x, …
    workaround for count (I guess pretty much any expression) is to use embedded query:
    select x, y from (
    select a.x, count(*) y from …)

    hope this helps others.

  8. Hi Jeff,

    Been struggling for a while to create a report where repeated values are hidden and there’s a total. It sounds like BREAK and COMPUTE are exactly what I need, but when I try them in script output, they don’t seem to work. I’m using version 4.1.0.19

    Here is a meta-query for what I’m using. If I try ‘break’ it shows the column value I expect (‘break on prd nodup’). Is there a session setting or preference that might prevent this working (ideally I want to export my result as html but I’m not trying that yet)?

    break on prd
    select prd, p_year, count(1)
    from prd p
    inner join prd_year py on py.prd_id = p.id
    group by prd, p_year
    order by 1,2;

  9. Hi Jeff

    Running Version 4.1.3.20

    Am trying to run a SQL script that’s on my local drive by typing ‘@C:\’ in a session window. The script generates several output files with a series of ‘spool’ commands.

    The script has a ‘set pagesize 0’ in it and it is generating an error message and is not being recognized in the output.

    It’s obviously a legal setting in SQL*Plus.

    How do I get around this?

    Thanks very much for your help!!!

    -gary

    1. thatjeffsmith Post
      Author
  10. I have the following code, and using sql developer Version 4.1.3.20,

    set linesize 180
    set pagesize 100

    col course_fee for 999,990.90 heading “Fee”
    col course_id for a35
    col course_institution_name for a5 heading “Type”
    col display_title for a10 heading “Lesson”
    col instructor for a45
    col student for a22
    col title for a10
    col user_student_id for a15 heading “PID”

    break on instructor skip page on course_institution_name on course_id on student
    compute sum of course_fee on instructor

    select
    instructor_lastname || ‘, ‘ || instructor_firstname || ‘(‘ || instructor_email || ‘)’ instructor
    , course_id
    , student_lastname || ‘, ‘ || student_firstname student
    , user_student_id
    , title, display_title
    , attempt_date
    , course_fee
    , course_institution_name

    But “break on instructor skip page on course_institution_name on course_id on student
    compute sum of course_fee on instructor” does not work. It does not break on instructor.

    How do I format break on …….

    Thanks for your help in advance

  11. Hi, i wanna get information format file export on sqldeveloper. Because i have writing shell script export to csv, but don’t have true format the same export by sqldeveloper.

    Please tell me that. Thanks so much.

    1. thatjeffsmith Post
      Author
  12. I am using version 4.1.3.20 and I have an issue when using the COMPUTE command with certain queries. Here is an example: If I run this on SQL PLUS I get the summary report line just fine, but I don’t get it with SQL Developer:

    COL owner FORMAT a15
    BREAK ON report
    COMPUTE SUM of count(1) ON report
    SELECT owner, count(1) FROM dba_tables WHERE owner = ‘SCOTT’ GROUP BY owner;

    — SQL Plus:
    OWNER COUNT(1)
    ————— ———-
    SCOTT 4
    ———-
    sum 4

    1 row selected.

    — SQL Developer:
    OWNER COUNT(1)
    ————— ———-
    SCOTT 4

    1 rows selected

  13. I’m using 4.1.1.19 and connect using 2 separate VPNs. On the first VPN, login.sql works just fine. But when I connect using the 2nd VPN, it does not. Any ideas why? (Two VPNs required because I support 2 different customers.)

  14. HI,

    I am using sql developer version 4.1.X

    I want to export as fixed width and also mention the size I want the columns. If I export as fixed width or spool as fixed, I get extra quotes and the column width size doesn’t work as well. I tried spool as ansiconsole which helped removing the quotes in every column but its still not conforming to column width.
    I tried set column width using column format.
    e.g.

    col COMPANY_NAME format a250;
    col FIRST_NAME format a45;
    col LAST_NAME format a45;

    Any help would be greatly appreaciated.

    Thanks,
    Mike

    1. thatjeffsmith Post
      Author
    2. but can I use col format in sql developer. My intention is to have –
      – a fixed width file
      – with fixed width for each of the columns

      I am hoping to spool the output to a text file but run the script inside sql developer. I can do that on sqlplus prompt but I think sql developer would be more friendly for the export.
      Does SQL developer support, col command that is used to format query results in sqlplus. like – col id heading app_id format a10

    3. thatjeffsmith Post
      Author
    4. Hi Jeff,

      Sorry, am unable to use the col format, number format to export the data, it just doesnt honour the column width if I spool the file. e.g.
      column format emp_id a10
      spool c:\test\test.txt
      select /*fixed*/ * from emp;
      spool off;

      Another issue I am facing is if I choose default format as excel, and run my query as a script (f5), it doesnt create an excel file at the preference set location. Preference is set to save to a file.

      Any help would be greatly appreciated.

      thanks,
      Mike

    5. thatjeffsmith Post
      Author

      once you use the /*fixed*/ – all of the previous column formatting settings go out the windows. use the hardcoded ones, or go back to setting the column widths yourself

      we don’t support /*xls*/ or /*xlsx*/ – if you want an excel file, use /*csv*/ and open in excel, or use the GUI to export to XLSX

  15. Oracle SQL developer script output does not display large numbers.
    the following:
    select ‘1234567890’ as char_n, 1234567890 as num_n from dual
    union select ‘9876543210’ as char_n, 9876543210 as num_n from dual;

    works fine when run as a query, but the large number is dropped when run as a script:
    CHAR_N NUM_N
    ———- —–
    1234567890 1234567890
    9876543210
    is there a setting or something to correct this? (same issue when the large number comes from the database). No problem with update/insert statements, just in the output

    1. thatjeffsmith Post
      Author
    2. for the large number not showing in script output, I am using :
      Java(TM) Platform 1.6.0_11
      Oracle IDE 3.2.10.09.57
      Versioning Support 3.2.10.09.57
      Thanks

    3. thatjeffsmith Post
      Author
  16. Hi, I have the SQL query output in below format :-
    Name Tel_no alt_tel_no Mobile_no Home_no
    ABC 01234
    ABC 45678
    ABC 457123

    But I would like to format them in the below format.
    Name Tel_no alt_tel_no Mobile_no Home_no
    ABC 01234 45678 457123

    1. i had mentioned alt tel no and others in the first row as and tel_no,monile_no and home_no as null in the second row. And in the third row tel_no,alt_tel_no,monile_no was mentoined as null.. i guess it ignored the null tags. and the input is not looking as i had expected it to be.
      01234 – tel_no (first row)
      45678 – alt-tel_no (second row)
      457123 – home_no (third row)

    2. thatjeffsmith Post
      Author
    3. thatjeffsmith Post
      Author
    4. thatjeffsmith Post
      Author
    5. So I want to know how do we merge the data (tel nos in this case) in different columns for the same Name?

    6. thatjeffsmith Post
      Author
  17. I’ve been searching and searching for how to change the display of the output in SQL developer. For example, how to get all the columns to display across instead of wrapping awkwardly like this:

    ISBN TITLE PUBDATE PUBID COST
    ———- —————————— ——— ———- ———-
    RETAIL CATEGORY
    ———- ————
    9247381001 HOW TO MANAGE THE MANAGER 09-MAY-99 1 15.4
    31.95 BUSINESS

    2147428890 SHORTEST POEMS 01-MAY-01 5 21.85
    39.95 LITERATURE

    How to get the columns “Retail” and “Category” on the same line as the other columns?

    Total noob here, as you probably can tell by my question! Any help greatly appreciated.

    1. thatjeffsmith Post
      Author

      the easiest things you can try

      add ‘set linesize 1000’ – get ready for lots of scrolling

      or if you have a newer version of SQLDev

      add ‘set sqlformat ansiconsole’ – will do a best fit per page of results

      add either of those lines to a login.sql script that you configure in the preferences.

      you can try both by executing either in the sqlworksheet before running your isbn, title, pub…query

    2. Wow, a helpful and quick response!

      What is supposed to happen is that after a few days, another reader of this blog posts a reply full of typos that is unintelligible, then a few months or a year later someone else posts explaining the first reply is total idiocy, with no one getting any further forward lol

      Seriously, thanks, your help was exactly what I was needing!

    3. thatjeffsmith Post
      Author
  18. Hey Jeff, I love your blog. It’s been very helpful in helping me “find things” in SQL Developer. Got a question for ya. I just upgraded to V4.1.1. Now when I run scripts the Script Output window is including extra spaces. EX:
    –begin
    1 row inserted.

    1 row inserted.

    1 row updated.

    Rollback complete.
    –end

    Any idea why that’s happening and/or I can simply get no extra spaces:
    Rollback complete.

    1. Sorry submitted before I was finished. How can I get rid of extra line/spaces in the output? Like this:
      1 row inserted.
      1 row inserted.
      1 row updated.
      Rollback Complete.

    2. thatjeffsmith Post
      Author

      Sorry, that behavior is hardcoded, no way to change it.

      If you look at SQL*Plus it does the same thing. I’m pretty sure we change the behavior to emulate SQL*Plus.

    3. Ah Ok. So this was changed b/c of the SQL*Plus updates then. The reason I was asking is b/c v4 or prior has no line breaks and I’ve been using SQL Developer for a lot of imports lately. No worries, I suspected that it might be intentional, but I was hoping there might be a setting to tweak. thanks!

    1. thatjeffsmith Post
      Author
  19. Hi Jeff,

    In SQL+ I could do something like:

    Prompt Query Path to Datafiles
    col “data_file_path” format a100 new_value data_file_path
    select substr(file_name,1,instr(file_name, ‘/’, -1)) data_file_path
    from dba_data_files
    where rownum =1

    And then use the data_file_path variable as follows:
    Prompt Create tablespace doe_data in &data_file_path.
    create tablespace doe_data
    datafile ‘&data_file_path./doe_data01.dbf’
    size 25M;

    So, this allows me to base a variable in a script based on something I query in the database. I found that this does not work in SQL Dev, but do you have an alternative, or do you plan to support this in the near future?

    Also in PLSQlDeveloper I could create script templates, for instance to create a user, tablespace etc. where some parts could be filled based on a pop-list that is based on a select. In Reports for instance you can create bind variables. But it would be neat if you can based them on a select as well.

Leave a Reply

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