SQL Developer
Searching for deailed info on code formatting rules
SQL Developer 3.2.20.09 Am looking for some detailed explanation of the various options for the SQL Formatting rules. The help, under 'Database: SQL Formatter' is pretty thin. I'd like to see something that explains the behavior of each discreet formatting option. While some may seem self-evident, not all are and even those that appear to be don't seem to behave the way someone might think or want. It would be nice if there were some detailed document to help with "that's not what I wanted" types of questions.
> Am looking for some detailed explanation of the various options for the SQL Formatting rules. > Good luck with that; I gave up long ago. The SQL Developer User's Guide is easier to use than the online help but, as far as I can tell, contains about the same limited info. http://docs.oracle.com/cd/E18464_01/doc.30/e17472/intro.htm#CHDCAGJI You can do the actual formatting offline if you want http://docs.oracle.com/cd/E18464_01/doc.30/e17472/intro.htm#sthref219 But that just runs the same code and doesn't explain anything.
rp0428 wrote: > Am looking for some detailed explanation of the various options for the SQL Formatting rules. > Good luck with that; I gave up long ago. The SQL Developer User's Guide is easier to use than the online help but, as far as I can tell, contains about the same limited info. http://docs.oracle.com/cd/E18464_01/doc.30/e17472/intro.htm#CHDCAGJI You can do the actual formatting offline if you want http://docs.oracle.com/cd/E18464_01/doc.30/e17472/intro.htm#sthref219Actually, I had seen that second one, and thought it was awfully dangerous to have a command named the same as a standard OS command. Especially an os command as destructive as 'format' . . . I won't go into details, but will say I dodged a bullet on that one ... But that just runs the same code and doesn't explain anything.Ok. I guess the important thing is to know I haven't overlooked the obvious. Thx.
We can always endeavor to improve the help, and I've noted this as an area of improvement for future releases. In the meantime, you can usually get a pretty good idea of what a preference does based on the code preview panel as you toggle it on and off. I'm assuming that's failed you, so are there a few preferences you had questions about? We can try to get you better answers on those right now.
> Ok. I guess the important thing is to know I haven't overlooked the obvious. > Maybe I overlooked the obvious. When you said this > I'd like to see something that explains the behavior of each discreet formatting option. While some may seem self-evident, not all are and even those that appear to be don't seem to behave the way someone might think or want. > I assumed you were looking for a detailed explanation of each check box or value and how to use it. Now I am wondering if you even got into the actual formatting specification dialog where you select the checkboxes or values. Did you? Tools -> Preferences -> SQL Formatter -> Oracle Formatting. Then in the Profile dropdown select SQL and then select Edit. Now the tree in the left pane has all of the options that you can set. In 3.2.20.09 there are 7 children 1. InputOutput 2. Alignment 3. Indentation 4. Line Breaks 5. CASE Linebreaks 6. White Space 7. Other You can expand each of those tree items to see the details. Is THAT what you were looking for? Or did you already find that and need more information about some or all of those options?
rp0428 wrote: > Ok. I guess the important thing is to know I haven't overlooked the obvious. > Maybe I overlooked the obvious. When you said this > I'd like to see something that explains the behavior of each discreet formatting option. While some may seem self-evident, not all are and even those that appear to be don't seem to behave the way someone might think or want. > I assumed you were looking for a detailed explanation of each check box or value and how to use it. Now I am wondering if you even got into the actual formatting specification dialog where you select the checkboxes or values. Did you? Tools -> Preferences -> SQL Formatter -> Oracle Formatting. Then in the Profile dropdown select SQL and then select Edit. Now the tree in the left pane has all of the options that you can set. In 3.2.20.09 there are 7 children 1. InputOutput 2. Alignment 3. Indentation 4. Line Breaks 5. CASE Linebreaks 6. White Space 7. Other You can expand each of those tree items to see the details. Is THAT what you were looking for? Or did you already find that and need more information about some or all of those options?Yes, I found the formatting options, though I thought their location to be a bit obtuse, and I had to dig a bit. I really would have expected them to be under "Code Editor" instead of "Database" (Tools > Preferences > Database > SQL Formatter). My questions start there. See my response to Jeff Smith for details.
Jeff Smith SQLDev PM wrote: We can always endeavor to improve the help, and I've noted this as an area of improvement for future releases. In the meantime, you can usually get a pretty good idea of what a preference does based on the code preview panel as you toggle it on and off. I'm assuming that's failed you, so are there a few preferences you had questions about? We can try to get you better answers on those right now.Yes, several questions. First, right off the bat is the profiles. It would seem rather obvious with this option that one can have different profiles, each with its own combination of rules. But how does one determine which profile is in use at any given time? I see nothing in the help about profiles. Yes, I noticed that the example pane would reflect some of the rules. And for those that are reflected in the example pane - some are pretty self-evident, but others fall under the category of "Ok, I see that, but fail to understand the meaning/logic behind it and how it might apply in a less simplistic example. Then there are the items that just leave me scratching my head on what they even mean. Like all three items under "InputOutput". Or under "Line Breaks, the term "Schema Type", "large SQL" "small SQL", "More Newlines". Or under "Other", the item "Force formatter output on difference". And under "Line Breaks" (which is the area I'm most interested in) things are not working as expected ... primarily and especially breaks before/after comma and before/after concatenation ||. Oh, and while we're at it, there's a glaring spelling error .. "Threashold for small SQL"
But how does one determine which profile is in use at any given time? The active profile in the Profile Selector seen in the Preferences dialog determines which profile is in use. things are not working as expected ... primarily and especially breaks before/after comma and before/after concatenation ||.Can you provide an example showing your expected vs actual? Here's a quick writeup on how to get leading commas when formatting your SQL http://www.thatjeffsmith.com/archive/2011/11/sql-developer-quick-tip-leading-comma-formatting/ If you set the 'Schema Type' to small SQL - you have to mind the 'Threshold for small SQL' as that allows you to leave code lines that don't meet that threshold untouched, which is nice if you want to avoid unnecessary line breaks for very small lines of code. Also, I think the reason we have the Formatting rules under Database and not Code Editor is b/c we employ the formatting in many places in the product - not just the code editors. Like all three items under "InputOutput".That's for the Advanced Format - which lets you take your SQL statement and have it re-formatted for ASP, C#, Java, etc.
Jeff Smith SQLDev PM wrote:
But how does one determine which profile is in use at any given time? The active profile in the Profile Selector seen in the Preferences dialog determines which profile is in use.check.
things are not working as expected ... primarily and especially breaks before/after comma and before/after concatenation ||.Can you provide an example showing your expected vs actual?Ok, here's some raw sql:
create or replace
PROCEDURE fix_jobs_timezone(
p_jobschema_in IN VARCHAR2 default null) authid current_user
IS
no_schema EXCEPTION;
no_mailrcpt exception;
type sched_jobs_tbl_type
IS
TABLE OF dba_scheduler_jobs.job_name%TYPE INDEX BY binary_integer;
t_sched_jobs sched_jobs_tbl_type;
c_program_name VARCHAR2(30) := 'fix_jobs_timezone';
l_mailto VARCHAR2(512) := 'estevens';
l_mailsubj VARCHAR2(80) := 'TEST - IGNORE';
l_mailmsg varchar2(1024);
l_sqlcode NUMBER;
l_errm VARCHAR2(64);
l_job VARCHAR2(128);
BEGIN
scott.set_job_run_ctx('JOB_BATCH_NUM', TO_CHAR(scott.PROGRAM_LOG_BATCH_SEQ.NEXTVAL));
scott.LOG (c_program_name, '*** Program Start - ' || c_program_name || ' ***');
IF p_jobschema_in IS NULL THEN
raise no_schema;
END IF;
scott.LOG (c_program_name, 'Processing jobs for schema ' ||p_jobschema_in);
SELECT job_name bulk collect INTO t_sched_jobs FROM dba_scheduler_jobs WHERE owner = p_jobschema_in ORDER BY job_name ;
FOR i IN t_sched_jobs.first .. t_sched_jobs.last
LOOP
l_job := '"' || p_jobschema_in || '"."'||t_sched_jobs(i) || '"';
scott.LOG (c_program_name, 'Processing job '|| l_job);
dbms_scheduler.set_attribute_null (l_job, 'START_DATE');
END LOOP;
scott.LOG (c_program_name, '*** Program End - ' || c_program_name || ' ***');
EXCEPTION
WHEN no_schema THEN
scott.LOG (c_program_name, 'Missing input parameter');
scott.LOG (c_program_name, '*** Program End - ' || c_program_name || ' ***');
l_mailsubj := l_mailsubj || ' - Missing input parameter';
l_mailmsg := 'Procedure requires a schema name as an input parameter.';
scott.send_email(l_mailto, l_mailto, l_mailsubj, l_mailmsg);
WHEN no_data_found OR value_error THEN
scott.LOG (c_program_name, 'No jobs selected for processing' ) ;
scott.LOG (c_program_name, '*** Program End - ' || c_program_name || ' ***');
l_mailsubj := l_mailsubj || ' - no jobs processed';
l_mailmsg := 'No jobs were found to process for schema ' || p_jobschema_in;
scott.send_email(l_mailto, l_mailto, l_mailsubj, l_mailmsg);
WHEN OTHERS THEN
l_sqlcode := SQLCODE;
l_errm := SUBSTR(SQLERRM, 1,64);
scott.LOG (c_program_name, l_errm);
l_mailsubj := l_mailsubj || ' - ' || l_errm;
l_mailmsg := 'Error code: '|| l_sqlcode|| ': ' ||l_errm;
scott.send_email(l_mailto, l_mailto, l_mailsubj, l_mailmsg);
scott.LOG (c_program_name, '*** Program End - ' || c_program_name || ' ***');
END;And here's after formatting with a profile using 'small sql' and breaks before commas and concatenation. Other values of that profile that look like they could be key are
- number of commas per line = 1
- max line witdth = 80
threshold for small sql = 20
CREATE OR REPLACE
PROCEDURE fix_jobs_timezone(
p_jobschema_in IN VARCHAR2 DEFAULT NULL) authid current_user
IS
no_schema EXCEPTION;
no_mailrcpt EXCEPTION;
type sched_jobs_tbl_type
IS
TABLE OF dba_scheduler_jobs.job_name%TYPE INDEX BY binary_integer;
t_sched_jobs sched_jobs_tbl_type;
c_program_name VARCHAR2(30) := 'fix_jobs_timezone';
l_mailto VARCHAR2(512) := 'estevens';
l_mailsubj VARCHAR2(80) := 'TEST - IGNORE';
l_mailmsg VARCHAR2(1024);
l_sqlcode NUMBER;
l_errm VARCHAR2(64);
l_job VARCHAR2(128);
BEGIN
scott.set_job_run_ctx('JOB_BATCH_NUM', TO_CHAR(
scott.PROGRAM_LOG_BATCH_SEQ.NEXTVAL));
scott.LOG (c_program_name, '*** Program Start - ' || c_program_name || ' ***'
);
IF p_jobschema_in IS NULL THEN
raise no_schema;
END IF;
scott.LOG (c_program_name, 'Processing jobs for schema ' ||p_jobschema_in);
SELECT
job_name bulk collect
INTO
t_sched_jobs
FROM
dba_scheduler_jobs
WHERE
owner = p_jobschema_in
ORDER BY
job_name ;
FOR i IN t_sched_jobs.first .. t_sched_jobs.last
LOOP
l_job := '"' || p_jobschema_in || '"."'||t_sched_jobs(i) || '"';
scott.LOG (c_program_name, 'Processing job '|| l_job);
dbms_scheduler.set_attribute_null (l_job, 'START_DATE');
END LOOP;
scott.LOG (c_program_name, '*** Program End - ' || c_program_name || ' ***');
EXCEPTION
WHEN no_schema THEN
scott.LOG (c_program_name, 'Missing input parameter');
scott.LOG (c_program_name, '*** Program End - ' || c_program_name || ' ***');
l_mailsubj := l_mailsubj ||
' - Missing input parameter';
l_mailmsg := 'Procedure requires a schema name as an input parameter.';
scott.send_email(l_mailto, l_mailto, l_mailsubj, l_mailmsg);
WHEN no_data_found OR value_error THEN
scott.LOG (c_program_name, 'No jobs selected for processing' ) ;
scott.LOG (c_program_name, '*** Program End - ' || c_program_name || ' ***');
l_mailsubj := l_mailsubj || ' - no jobs processed';
l_mailmsg := 'No jobs were found to process for schema ' || p_jobschema_in;
scott.send_email(l_mailto, l_mailto, l_mailsubj, l_mailmsg);
WHEN OTHERS THEN
l_sqlcode := SQLCODE;
l_errm := SUBSTR(SQLERRM, 1,64);
scott.LOG (c_program_name, l_errm);
l_mailsubj := l_mailsubj || ' - ' || l_errm;
l_mailmsg := 'Error code: '|| l_sqlcode|| ': ' ||l_errm;
scott.send_email(l_mailto, l_mailto, l_mailsubj, l_mailmsg);
scott.LOG (c_program_name, '*** Program End - ' || c_program_name || ' ***');
END;As you can see, none of the commas or '||' caused a line break. In fact, if I had manually formatted my code and then asked SQL Dev to apply formatting rules ... it actually removed line breaks. Using just a few lines of simplified test case things worked mostly as I would have expected, but the above block made me persue this line because I thought I was missing something.
Here's a quick writeup on how to get leading commas when formatting your SQL
http://www.thatjeffsmith.com/archive/2011/11/sql-developer-quick-tip-leading-comma-formatting/
If you set the 'Schema Type' to small SQL - you have to mind the 'Threshold for small SQL' as that allows you to leave code lines that don't meet that threshold untouched, which is nice if you want to avoid unnecessary line breaks for very small lines of code.Ok, but there again .. unless I've missed it somehow, the whole concept of what "schema type" means and how it is used is missing from the docs. How am I to know what I'm doing when I select 'small SQL' vs 'large SQL' vs 'customized sql' vs '1 line sql'.
>
Also, I think the reason we have the Formatting rules under Database and not Code Editor is b/c we employ the formatting in many places in the product - not just the code editors.OK.
>
Like all three items under "InputOutput".That's for the Advanced Format - which lets you take your SQL statement and have it re-formatted for ASP, C#, Java, etc.Other observations while collecting demo data
when in the Edit dialog for a given format profile ... "um, uh, which profile am I working with here? ... guess I'll have to move some windows around and see, since the edit window itself doesn't tell me."
Saw some other areas that made me say "Really?", but I don't want to get into a lengthy wish list - especially since I'm just starting to use this tool. I'd like to focus on what's there that I'm overlooking.
You're not seeing line breaks because the formatter is just applying those in SQL statements, not in PL/SQL. So this will format - select sysdate || 'stuff' || sydate || 'morestuff' from dual; --> SELECT sysdate || 'stuff' || sydate || 'morestuff' FROM dual; This will not - begin dbms_output.put_line(sysdate || 'stuff' || sysdate || 'more_stuff'); end; This discrepancy needs addressed. The preferences dialog and the Help needs updated to reflect the scope of the different formatting rules. it actually removed line breaks.Blank lines should be preserved - that's been targeted for a fix in a future release.
Jeff Smith SQLDev PM wrote: You're not seeing line breaks because the formatter is just applying those in SQL statements, not in PL/SQL. So this will format - select sysdate || 'stuff' || sydate || 'morestuff' from dual; --> SELECT sysdate || 'stuff' || sydate || 'morestuff' FROM dual; This will not - begin dbms_output.put_line(sysdate || 'stuff' || sysdate || 'more_stuff'); end; This discrepancy needs addressed. The preferences dialog and the Help needs updated to reflect the scope of the different formatting rules. it actually removed line breaks.Blank lines should be preserved - that's been targeted for a fix in a future release.Ok. At least I know I'm not going crazy or overlooking the obvious! ;-) Thanks for sticking with me on this.
Hi EdStevens, There is currently an option to put in newlines *"Line Breaks->More Newlines"* after the format (around blocks/loops) (and after the formatter has stripped the users original newlines). (The option is off by default) I added newlines where it seemed best and achievable in the time available. See the example below for formatted output I have not received any feedback on this - it is off by default. The code below(1) shows output before and after this option is applied uses the test PLSQL code already in the screens for setting the formatter options. -Turloch SQLDeveloper Team (1)Code after and before formatting follows: After: /* Comment... embedded in double quotes "select embedded_double_query from mytable" */ /* Embedded in single quotes 'select embedded_single_query from mytable' */ CREATE OR REPLACE PACKAGE BODY test1 IS g_column1 VARCHAR2(17) := NULL; g_column2 VARCHAR2(52) := NULL; g_column3_from_column22 VARCHAR2(25) := NULL; g_column_4711 VARCHAR2(11) := NULL; FUNCTION testfunction( p_column12 IN VARCHAR2) RETURN VARCHAR2 IS BEGIN IF NVL(g_emplid1,'X') <> p_emplid THEN BEGIN FOR emp_rec IN c_empl LOOP --Align on comments example SELECT 1 INTO var WHERE EXISTS (SELECT col1, -- first field longcol2, --second field midcol3, -- 3rd field FROM tble1 WHERE ((1 +1)=2) AND (22222*3 = 44) ); -- align || at end of line example SELECT 1 INTO var WHERE EXISTS (SELECT col1 || longcol2 || midcol3 || col4 , col1 FROM tbl ); IF emp_rec.empl_rcd# > 0 THEN INSERT INTO table1 /*--------- ---------*/ ( col1, col2, col3, col4, col5, col6, col7 ) SELECT price.col1 AS col1, price.col2 AS col2, price.col3 AS col3, MAX(price.col4) AS col4, MAX(price.col5) AS col5, MAX(price.col6) AS col6, -- comment1 MAX(price.col7) AS col7 /* comment2 */ FROM (SELECT store.column1, -- ========================================= -- -- ========================================= CAST (store.column2 AS INTEGER) AS column2, store.column3, store.column4, store.column5, SUBSTR(store.column6,11,1) AS column6, store.column7 AS column7 FROM (SELECT library.column1, library.column2, library.column3, CASE library.column4 WHEN cheap THEN digits(library.column27) concat library.column28 ELSE 123456 END AS column4, CASE library.column5 WHEN expensive THEN digits(library.column27) concat library.column28 ELSE 123456 END AS library.column6, CASE column7 WHEN free THEN digits(library.column27) concat library.column28 ELSE 123456 END AS column7 FROM (SELECT INTEGER(SUBSTR(onelibrarysales.column1,11,10)) AS column1, SUBSTR(onelibrarysales.column2,21,10) AS column2 , onelibrarysales.column3, onelibrarysales.column4, SUBSTR(onelibrarysales.column5,31,6) AS column5, SUBSTR(onelibrarysales.column6,37,2) AS column6, SUBSTR(onelibrarysales.column7,39,6) AS column7 FROM (SELECT alllibrarysales.column1, alllibrarysales.column2, MAX(alllibrarysales.column3) AS alllibrarysales.column3 , MAX(CHAR(alllibrarysales.column4,iso) concat CHAR(alllibrarysales.column5,iso) concat digits(alllibrarysales.column6) concat (alllibrarysales.column7)) AS column5 FROM (SELECT libraryprod.column1, libraryprod.column2, libraryprod.column3, libraryprod.column4, libraryprod.column5, libraryprod.column6, libraryprod.column7 FROM (SELECT tv.column1, tv.column2, MAX(digits(tv.column3) concat digits(tv.column4) ) AS librarymax FROM db1.v_table1 tv WHERE tv.column1 <> 'Y' AND tv.column1 IN ( 'a' , '1' , '12' , '123' , ' 1234' , '12345' , '123456' , '1234567' , '12345678' , '123456789' , '1234567890' , '1 12 123 1234 12345 123456 1234567 12345678' , 'b' , 'c' ) AND tv.column2 >= DATE(tv.column4) AND tv.column3 < DATE(tv.column15) GROUP BY tv.column1, tv.column2 ) AS libraryprod, db1.table2 th WHERE th.column1 =libraryprod.column1 AND th.column2 =libraryprod.column2 ) AS alllibrarysales GROUP BY alllibrarysales.column1, alllibrarysales.column2 ) AS onelibrarysales ) AS library LEFT OUTER JOIN db1.v_table3 librarystat ON librarystat.column1 = library.column1 AND librarystat.column2 = library.column2 OR ( librarystat.column4 = library.column4 AND librarystat.column5 = library.column5 ) AND ( librarystat.column5 = 'I' OR librarystat.column4 = 'Gold' OR librarystat.column5 = 'Bold' ) AND librarystat.column6 <= 'Z74' ) AS x ) AS price WHERE price.column1 < 'R45' OR ( price.column2 = 'R46' AND price.column3 = 6 ) GROUP BY price.column1, price.column2, price.column3, price.column4, price.column5, price.column6, price.column7 ; END IF; END LOOP; END; END IF; END testfunction; /************************************************************************ /* Multi line comment */ /************************************************************************/ /***********************************************************************/ -- -- ************************************************************************/ -- ** Several single line comments - -- ** -- ************************************************************************/ -- END PACKAGE; Before: /* Comment... embedded in double quotes "select embedded_double_query from mytable" */ /* Embedded in single quotes 'select embedded_single_query from mytable' */ CREATE OR REPLACE PACKAGE BODY test1 IS g_column1 VARCHAR2(17) := NULL; g_column2 VARCHAR2(52) := NULL; g_column3_from_column22 VARCHAR2(25) := NULL; g_column_4711 VARCHAR2(11) := NULL; FUNCTION testfunction( p_column12 IN VARCHAR2) RETURN VARCHAR2 IS BEGIN IF NVL(g_emplid1,'X') <> p_emplid THEN BEGIN FOR emp_rec IN c_empl LOOP --Align on comments example SELECT 1 INTO var WHERE EXISTS (SELECT col1, -- first field longcol2, --second field midcol3, -- 3rd field FROM tble1 WHERE ((1 +1)=2) AND (22222*3 = 44) ); -- align || at end of line example SELECT 1 INTO var WHERE EXISTS (SELECT col1 || longcol2 || midcol3 || col4 , col1 FROM tbl ); IF emp_rec.empl_rcd# > 0 THEN INSERT INTO table1 /*--------- ---------*/ ( col1, col2, col3, col4, col5, col6, col7 ) SELECT price.col1 AS col1, price.col2 AS col2, price.col3 AS col3, MAX(price.col4) AS col4, MAX(price.col5) AS col5, MAX(price.col6) AS col6, -- comment1 MAX(price.col7) AS col7 /* comment2 */ FROM (SELECT store.column1, -- ========================================= -- -- ========================================= CAST (store.column2 AS INTEGER) AS column2, store.column3, store.column4, store.column5, SUBSTR(store.column6,11,1) AS column6, store.column7 AS column7 FROM (SELECT library.column1, library.column2, library.column3, CASE library.column4 WHEN cheap THEN digits(library.column27) concat library.column28 ELSE 123456 END AS column4, CASE library.column5 WHEN expensive THEN digits(library.column27) concat library.column28 ELSE 123456 END AS library.column6, CASE column7 WHEN free THEN digits(library.column27) concat library.column28 ELSE 123456 END AS column7 FROM (SELECT INTEGER(SUBSTR(onelibrarysales.column1,11,10)) AS column1, SUBSTR(onelibrarysales.column2,21,10) AS column2 , onelibrarysales.column3, onelibrarysales.column4, SUBSTR(onelibrarysales.column5,31,6) AS column5, SUBSTR(onelibrarysales.column6,37,2) AS column6, SUBSTR(onelibrarysales.column7,39,6) AS column7 FROM (SELECT alllibrarysales.column1, alllibrarysales.column2, MAX(alllibrarysales.column3) AS alllibrarysales.column3 , MAX(CHAR(alllibrarysales.column4,iso) concat CHAR(alllibrarysales.column5,iso) concat digits(alllibrarysales.column6) concat (alllibrarysales.column7)) AS column5 FROM (SELECT libraryprod.column1, libraryprod.column2, libraryprod.column3, libraryprod.column4, libraryprod.column5, libraryprod.column6, libraryprod.column7 FROM �
Related Links
How SQL Developer works in database migration
Not able debeg plsql code in sqldeveloper when calling HTP.P or HTF.P
SQL Developer New Connection
unable to connect to database in sqldeveloper-1.5.4.59.40
Unable to connect to Oracle by using PL/SQL dev
question about sql statement
How to sort the grid column?
How to pass a list as bind variable in SQL Developer?
Large data spooling
Performance issues for sql developer
set define off.with ; or with out semicolen .
SQLDeveloper versioning issue
Using TOAD and SQL Developer to compare db objects in schemas in databases
SQL Developer export files not visible
Generate txt file with UTL_FILE
ORA-166512: string value too long for attribute "job_action"