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"

Categories

ORACLE DEV
Reports
General Database Discu...
WebCenter
Recovery Manager (RMAN)
SCM - Process Manufact...
Industries Archived Fo...
Applications Desktop I...
SOA & Process Management
Oracle Developer Tools...
Email SDK
Secure Backup
XML (Korean)
SQL*Net (Korean)
WebCenter Portal
Python
JDeveloper and OC4J 11...
LCM: R12 Install/Upgrade
JD Edwards World Archi...
Siebel Archived Forums
SQR Reporting
Oracle Virtualization
SOA Suite and OC4J 11g...
LCM: Oracle iSetup
Test Management/Functi...
Application Integratio...
Communications Presence
Tuxedo Systems and App...
weblogic.developer.int...
weblogic.developer.int...
aqualogic.bpm.runtime
WebLogic Communication...
Business Services (BPM)
Liquid Infrastructure ...
liquid.developer.inter...
WebLogic Server - Upgr...
Business Intelligence ...
Design Pattern (Chinese)
Open Source (Chinese)
AIA 3.0 Early Adopters
Designer (Korean)
ORDS, SODA & JSON in t...
Java Essentials
New To Java
Java HotSpot Virtual M...
JavaFX 1.x and JavaFX ...
Other Security APIs, T...
Java ME SDK
Java Install / Autoupdate
Java Enterprise System...
Java System Applicatio...
HotSpot FAQs [ARCHIVE]
Java Composite Applica...
Oracle Communications ...
CMT Servers General Di...
Sun ONE Certificate Se...
CRM On Demand Marketing
JavaFX 2.0 and Later
R Technologies
Audit Vault and Databa...
Oracle Lite (Korean)
Vdbench
Oracle WebCenter/Oracl...
Java 8 Questions
My Oracle Support Sear...
Peer_Review
Oracle PartnerNetwork ...
About the Community
Build
General Database Discu...
Arena
Eloqua For Sales
Data Quality and Clean...
Colorado Eloqua Users
Eloqua User Group - Ba...
Utah Eloqua Users
Markies: Best Internat...
E9 Power Users
CBI Topliners Group
Systems Configuration ...
OMC - Industry Solutio...
OMC - Industry Solutio...
Insurance
Warehouse Management
Middleware Partner Com...
TROUG Turkish Oracle U...
UK Oracle User Group
Oracle Linux Networking
Oracle Learning Library
Data Visualization Des...
Marketing Elo[q]ution ...
MOOCs
Oracle Marketing Cloud...
Resources
New Zealand Oracle Use...
Brazil - Eloqua User G...
Eloqua Benelux User Group
Archived Forums
Application Server Arc...
Database and Applicati...
BPEL
On Demand: E-Business ...
Siebel Technology
Oracle Authentication ...
Automated Regression T...
aqualogic.enterprisese...
weblogic.SIP.server
bea.guardian
weblogic.realtime.core...
Solaris 10
Oracle Business Intell...
Do It
Demand Management

Resources

Encrypt Message