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

Create a read only connection
Data Modeller: compare models
No data in trace file view panels
ORA-12541: TNS:no listener in debugging mode of sql developer
Certain data columns not showing up during a query
java.lang.NullPointerException when connecting to DB
SQL Developer 2.1 - Associate Migration Repository errors
404 error when downloading Oracle SQL Developer 2.1 (2.1.0.63.73) for Mac
Spool from SQL Developer 2.1
SQL Developer vs TOAD - query performance question
Viewing objects on lefthand side of SQL Developer.
problem: query tab autocloses when tab leaved
Starting up SQL Developer 2.1 opens MS-DOS command window on XP
How to compile all invalid objects in SQL Developer
Data refresh on dev instance
problem: sqldev opens new datatab each time i run a query.

Categories

ORACLE DEV
Reports
XML
WebCenter Content
Replication
Java and JavaScript in...
Certification Community
Discrete Manufacturing
Integration and Events
Applications Desktop I...
RDF Semantic Graph
Berkeley DB XML
Database (Korean)
Applications (Korean)
SQL*Net (Korean)
SQL Developer (Korean)
Contact Center and Ser...
Master Data Management...
Siebel Mobile
Advanced Compression
On Demand: SaaS and Ma...
Service Architecture L...
weblogic.developer.int...
weblogic.developer.int...
weblogic.developer.int...
weblogic.developer.int...
AquaLogic Service Bus ...
Ensemble
Pages
应用软件
Primavera Customer Com...
Primavera Systems Tech...
2007 BEA TMT创新大赛
Mobile Workforce Manag...
Communications Data Model
Concurrency
JavaFX 1.x and JavaFX ...
Enterprise JavaBeans
Other Topics [ARCHIVE]
Java Secure Socket Ext...
Abstract Window Toolki...
Accessibility
JavaBeans [ARCHIVE]
Solaris 11
Sun Java System Access...
Java Real-Time
Java System Portal Ser...
Sun ONE Certificate Se...
Java Enterprise System...
ONE Application Server 6
Oracle Commerce
Business
Oracle Linux and UEK B...
Oracle SOA Suite (Japa...
Mix Feedback
Single Sign On
Sun SPOT
Israel Oracle User Gro...
Oracle Coherence (MOSC...
Oracle PartnerNetwork ...
Build
Oracle Instantis User ...
Eloqua User Group - Ba...
Eloqua Users - Virginia
Partner Group
Non-profits and Member...
Eloqua-Netsuite Integr...
Canadian B2B Marketers
Systems Track
OMC - Industry Solutio...
Oracle and TCS Initiat...
Oracle Supply Chain Ma...
Move Goods
Oracle EMEA Partner Sales
Synopsis
Eclipse開発ツール
México
Kansas City Eloqua Use...
New Zealand Oracle Use...
Group Verticurl
Archived Forums
Sales and Marketing
Upgrades and Migration
개발자 (Korean)
Products - Application...
PeopleSoft General Dis...
Customer Hub
Administration
Tuxedo
weblogic.aqualogic.dat...
weblogic.SIP.server
Developer Studio C/C++...
Storage General Discus...
Systems Management Bes...
Oracle SQL Developer (...
Duke's Choice Awards
Test Partner Industry ...

Resources

Database Users
RDBMS discuss
Database Dev&Adm
javascript
java
csharp
php
android
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App