Support for SQL*Plus functionality in SQL Developer has gotten better from release to release. In version 4.1 we took a giant leap forward, adding support for many SQL*Plus commands, and coming much closer to emulating script execution behavior.
One example, we added support for BREAK and COMPUTE commands.
Another change was adopting several of the SQL*Plus defaults, including pagesize.
It’s not just the number of rows on a query, you also count blank lines, the column headers, etc.
So querying for 15 rows on a pagesize of 14 gives me more than one page.
This is how SQL Developer version 4.1 will behave now as well.
Changing the Defaults
It’s very easy. You can use the same mechanism that SQL*Plus users have been using for decades – a ‘logon’ SQL script.
Apply preferences. Disconnect. Connect. Open Worksheet, run again.
Another ‘Trick’
Some of you don’t like having to toggle on DBMS Output support. So I’ve shown you how to get this output by default. It’s using the same mechanism as above.
Just show me all the settings, so I know what’s what
Ok, use ‘SHOW ALL’
You can change them for your session at any time using the SET command in a worksheet, or you can change them at logon using the script and preference method.
70 Comments
This information is incredibly useful. So much so that seemingly every Oracle database blogger has written extensively on the subject, each one with a list of their favourite variables and preferred settings for them. But the SQL Developer documentation contains absolutely no reference material to them at all. Nor could I find either SET or SHOW command documentation there. Also the SHOW ALL command doesn’t in fact show all the variables. I balked at spending hours trawling through dozens of blog posts to collate a set of unique variables. The best I could come up with was to look at the list in the SQL*Plus user guide and reference manual. Not all of these variables are supported in SQL Developer, and of course I have no way of knowing if there are any variables in SQL Developer that aren’t in SQL*Plus.
Is there a plan to add this information to the documentation for SQL Developer?
Show all+ should show everything.
The set commands are doc here, see section 2.9.3
Is there something in particular you’re looking for?
Trying to get resource manager statistics but i am getting query truncated row limit error.
I am trying to find in preferences where to modify that but can’t seem to find it.
Do you know by any chance?
Thanks,
AL
Hi Jeff
Unfortunately that’s the latest our company has in software centre. Thanks
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?
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?
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?
I see that in 17.4 too, it LOOKS like a bug.
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?
no, they’re measuring 2 different things, execution time and fetch time
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
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?
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
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
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.
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.
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.
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
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
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.
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
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
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
2 – set pagesize 100 – that will give you a new page with column headers every 100 rows. so set it has high as you need
Thanks for your help Jeff
Thank you very much !!!! It works!!!
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?
Are you running SET SQLFORMAT ANSICONSOLE? That doesn’t print the underline on column headers. If you turn that off, they’ll come back.
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.
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;
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
413 doesn’t support pagesize 0, so set it really high
42 will support that, at least sqlcl will, but I can check when I get back from vacation
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
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.
what information do you want?
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
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.)
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
if you use the col formats, then the sqlformat stuff goes out the window – pick one method or the other, but not both
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
>>Does SQL developer support, col command that is used to format query results in sqlplus. like – col id heading app_id format a10
yes
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
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
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
It prints fine for me – you’re not telling me what version of SQL Developer you’re running though.
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
Old SQLDev, Older Java – can you upgrade both?
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
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)
how do you know which number belongs to which type of phone?
I recieved the data in that format.
so it’s not in a table?
No.. its the output received from an SQL query.
right…so what’s your query
So I want to know how do we merge the data (tel nos in this case) in different columns for the same Name?
with SQL
Yes
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.
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
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!
No worries, I probably spend TOO much time on here 🙂 But I want our customers and users to be happy!
Just what I was looking for, too! Thanks…
Heya Jeff –
Prior versions had linesize and pagesize both set to -1.
Any way to recapture that?
Thanks.
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.
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.
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.
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!
Thanks Jeff,
It is exactly what I am looking for!
That’s my favorite type of response, thanks Mil4n!
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.