1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Script to Spool table data

Discussion in 'SQL PL/SQL' started by hiswapna, Aug 7, 2009.

  1. hiswapna

    hiswapna Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    I am trying to automate a script which spools the table data .
    The script accepts 2 parameters --> 1) table name 2) where condition

    The script is working perfect. But if I want the format of the date field to be in MMDDYYYY instead of the standard 'DD-MM-YYYY' what changes needs to be done to this script

    I tried using a case or decode to verify a.data_type=DATE
    Code (Text):

    select 'CHR(34)||'|| decode(a.data_type,'DATE', 'to_char'||a.column_name||',''MMDDYYYY'')',
    LOWER(a.column_name) END)  ||'||chr(34)'||(case when a.column_id < max(b.column_id) then '||chr(44)||' END)
    but this not works....
    Code (Text):

    set wrap off
    set linesize 100
    set feedback off
    set pagesize 0
    set verify off
    set termout off

    spool  c:\temp_&1..sql

    prompt set linesize 500
    prompt set trimspool ON
    prompt SET wrap off
    prompt SET feedback off
    prompt SET pagesize 0
    prompt SET verify off
    prompt set termout OFF

    prompt spool c:\&1..DAT

    prompt  select
    select chr(39)||'"'||TRIM(a.column_name) ||'"'||(case when a.column_id < max(b.column_id) then ','||chr(39)||',' ELSE chr(39) END)
    from user_tab_columns a, user_tab_columns b
    where a.table_name = upper('&1')
    AND b.table_name = upper('&1')
    group by a.column_name, a.column_id
    order by a.column_id
    /
    prompt   from    dual
    prompt  /

    prompt  SELECT
    select 'CHR(34)||'|| lower(a.column_name) ||'||chr(34)'||(case when a.column_id < max(b.column_id) then '||chr(44)||' END)
    from user_tab_columns a, user_tab_columns b
    where a.table_name = upper('&1')
    AND b.table_name = upper('&1')
    group by col_name, a.column_id
    order by a.column_id
    /
    prompt  from    &1
    prompt   &2
    prompt  /

    prompt spool off
    prompt exit
    spool off

    set termout on
    @C:\temp_&1..sql
    --exit


    Also please let me know if there is any other efficient way of writing this script.

    I also need a script to automatic generation of control file with taking the inputs as table name and file name along with extension. (say abc.dat or abc.csv)
    some how when I am passing . in the file extension the & parameter is not able to recognise the dot(.) properly and my script is failing...
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    No, because it isn't coded correctly. It should be this:

    Code (Text):
    select 'CHR(34)||''''|| decode(a.data_type,'DATE', 'to_char('||a.column_name||',''MMDDYYYY'')',
    LOWER(a.column_name))  ||''''||chr(34)||''''||case when a.column_id < max(b.column_id) then ''''||chr(44)||'''' END
    I cannot believe that script works because it creates a cartesian product since there is no join between alias a and b. Additionally 'col_name' is not a valid column in user_tab_columns. I can expect the following query to at least execute:

    Code (Text):

    set wrap off
    set linesize 100
    set feedback off
    set pagesize 0
    set verify off
    set termout off

    spool  c:\temp_&1..sql

    prompt set linesize 500
    prompt set trimspool ON
    prompt SET wrap off
    prompt SET feedback off
    prompt SET pagesize 0
    prompt SET verify off
    prompt set termout OFF

    prompt spool c:\&1..DAT

    prompt  select
    select chr(39)||'"'||TRIM(a.column_name) ||'"'||(case when a.column_id < max(b.column_id) then ','||chr(39)||',' ELSE chr(39) END)
    from user_tab_columns a, user_tab_columns b
    where a.table_name = upper('&1')
    AND b.table_name = a.table_name
    group by a.column_name, a.column_id
    order by a.column_id
    /
    prompt   from    dual
    prompt  /

    prompt  SELECT
    select ''''||CHR(34)||''''|| lower(a.column_name) ||''''||chr(34)||''''||(case when a.column_id < max(b.column_id) then ''''||chr(44)||'''' END)
    from user_tab_columns a, user_tab_columns b
    where a.table_name = upper('&1')
    AND b.table_name = a.table_name
    group by a.column_name, a.column_id
    order by a.column_id
    /
    prompt  from    &1
    prompt   &2
    prompt  /

    prompt spool off
    prompt exit
    spool off

    set termout on
    @C:\temp_&1..sql
    --exit
     
    Then show us that failing script as well and possibly someone can discover what you've coded incorrectly.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Code (SQL):
    SELECT 'CHR(34)||'|| LOWER(a.column_name) ||'||chr(34)'||(CASE WHEN a.column_id < MAX(b.column_id) THEN '||chr(44)||' END)
    FROM user_tab_columns a, user_tab_columns b
    WHERE a.TABLE_NAME = UPPER('&1')
    AND b.TABLE_NAME = UPPER('&1')
    GROUP BY col_name, a.column_id
    ORDER BY a.column_id
     
    Code (SQL):
    SELECT chr(39)||'"'||TRIM(a.column_name) ||'"'||(CASE WHEN a.column_id < MAX(b.column_id) THEN ','||chr(39)||',' ELSE chr(39) END)
    FROM user_tab_columns a, user_tab_columns b
    WHERE a.TABLE_NAME = UPPER('&1')
    AND b.TABLE_NAME = a.TABLE_NAME
    GROUP BY a.column_name, a.column_id
    ORDER BY a.column_id
     
    Here both will work in same manner and both will give cartesian product, because

    Code (SQL):
    WHERE a.TABLE_NAME = UPPER('&1')
    AND b.TABLE_NAME = UPPER('&1')
    is same as
    Code (SQL):

    WHERE a.TABLE_NAME = UPPER('&1')
    AND b.TABLE_NAME = a.TABLE_NAME
    Cartesian is needed in this query to get the max(b.column_id).

    There are is another way for doing it without cartesin product (using SYS_CONNECT_BY_PATH ).

    See a hint below.

    Code (SQL):
    SQL> var tn varchar2(10);
    SQL> EXEC :tn := 'EMP'

    PL/SQL PROCEDURE successfully completed.

    SQL> SELECT  'SELECT '||
      2             SUBSTR (SYS_CONNECT_BY_PATH ( cname , ','),
      3                     2
      4                    ) ||' FROM '|| :tn  column_list
      5        FROM (SELECT   a.TABLE_NAME tname,
      6                       a.column_name cname,
      7                       a.column_id cid,
      8                       MAX (column_id) OVER (PARTITION BY a.TABLE_NAME) mxid
      9                  FROM user_tab_columns a
     10                 WHERE a.TABLE_NAME = UPPER (:tn)
     11              ORDER BY a.column_id)
     12       WHERE cid = mxid
     13  START WITH cid = 1
     14  CONNECT BY PRIOR cid + 1 = cid AND PRIOR tname = tname;

    COLUMN_LIST
    --------------------------------------------------------------------------------

    SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP

    SQL>
    Just incorporate in your script..
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    And that code appears to be quite inefficient:

    Code (SQL):
    SQL> var tn varchar2(10);
    SQL> EXEC :tn := 'EMP';

    PL/SQL PROCEDURE successfully completed.

    SQL>
    SQL> SELECT  'SELECT '||
      2  SUBSTR (SYS_CONNECT_BY_PATH ( cname , ','),
      3          2
      4  ) ||' FROM '|| :tn  column_list
      5  FROM (SELECT   a.TABLE_NAME tname,
      6          a.column_name cname,
      7          a.column_id cid,
      8          MAX (column_id) OVER (PARTITION BY a.TABLE_NAME) mxid
      9          FROM user_tab_columns a
     10          WHERE a.TABLE_NAME = UPPER (:tn)
     11          ORDER BY a.column_id)
     12  WHERE cid = mxid
     13  START WITH cid = 1
     14  CONNECT BY PRIOR cid + 1 = cid AND PRIOR tname = tname;

    COLUMN_LIST
    ------------------------------------------------------------------------------------------------------------------------------------
    SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP

    Statistics
    ----------------------------------------------------------
            701  recursive calls
             74  db block gets
            362  consistent gets
              1  physical reads
          29880  redo SIZE
            472  bytes sent via SQL*Net TO client
            396  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
             44  sorts (memory)
              0  sorts (disk)
              1  ROWS processed

    SQL>
    when compared to this:

    Code (SQL):
    SQL> WITH a AS (
      2  SELECT column_name, column_id FROM user_tab_columns
      3  WHERE TABLE_NAME = UPPER('&1')
      4  ),
      5  b AS (
      6  SELECT MAX(column_id) maxcolid
      7  FROM user_tab_columns
      8  WHERE TABLE_NAME = UPPER('&1')
      9  )
     10  SELECT 'CHR(34)||'|| LOWER(a.column_name) ||'||chr(34)'||(CASE WHEN a.column_id < maxcolid THEN '||chr(44)||' END)
     11  FROM a, b
     12  ORDER BY a.column_id
     13  /
    Enter VALUE FOR 1: emp
    OLD   3: WHERE TABLE_NAME = UPPER('&1')
    NEW   3: WHERE TABLE_NAME = UPPER('emp')
    Enter VALUE FOR 1: emp
    OLD   8: WHERE TABLE_NAME = UPPER('&1')
    NEW   8: WHERE TABLE_NAME = UPPER('emp')

    'CHR(34)||'||LOWER(A.COLUMN_NAME)||'||CHR(34)'||(CASEWHENA.
    -----------------------------------------------------------
    CHR(34)||empno||chr(34)||chr(44)||
    CHR(34)||ename||chr(34)||chr(44)||
    CHR(34)||job||chr(34)||chr(44)||
    CHR(34)||mgr||chr(34)||chr(44)||
    CHR(34)||hiredate||chr(34)||chr(44)||
    CHR(34)||sal||chr(34)||chr(44)||
    CHR(34)||comm||chr(34)||chr(44)||
    CHR(34)||deptno||chr(34)

    8 ROWS selected.

    Statistics
    ----------------------------------------------------------
            644  recursive calls
             48  db block gets
            107  consistent gets
              0  physical reads
          24532  redo SIZE
            830  bytes sent via SQL*Net TO client
            396  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              1  sorts (memory)
              0  sorts (disk)
              8  ROWS processed

    SQL>
     
  5. hiswapna

    hiswapna Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    I tried the decode function but it is not working.....

    I want the data fileds to be displayed in MMDDYYYY format...
    Can any one help me in this.....
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Do this in your script before you execute any queries:

    alter session set nls_date_format='MMDDYYYY';

    Run your queries, then set the date format back to the default at the end:

    alter session set nls_date_format = 'DD-MON-RR';