Rem
Rem                      Copyright (C) 2016 by Michel CADOT
Rem  ========================================================================
Rem
Rem  Fichier : DescSQL.sql
Rem
Rem  Version Oracle : V10/V11
Rem
Rem  Description : Donne les colonnes retournées par une requete SQL et leur
Rem                description
Rem
Rem  Paramètres : Requête à décrire
Rem
Rem  Notes : Voir descSQLCr.sql pour creer une fonction pipeline pour la meme chose
Rem
Rem  Modification :
Rem     MCadot      06/10/2016 - Creation
Rem
Rem  Mots-Cle : describe sql query select
Rem

Def lines=200
Set lines &lines trimout on trimspool on feedback off serveroutput on format wrap
Prompt
Spool DescSQL
Prompt &1
Prompt
Declare
  l_query  varchar2(32767) := q'[&1]';
  l_cursor pls_integer default dbms_sql.open_cursor;
  l_status pls_integer;
$IF DBMS_DB_VERSION.VER_LE_10_2 $THEN
  l_cols   dbms_sql.desc_tab;
$ELSE
  l_cols   dbms_sql.desc_tab3;
$END
  l_colnb  pls_integer;  
  l_lg     pls_integer;
  Function getType(i in pls_integer) return varchar2 is
    l_type pls_integer  := l_cols(i).col_type;
    l_lg   pls_integer  := l_cols(i).col_max_len;
    l_pre  pls_integer  := l_cols(i).col_precision;
    l_sca  pls_integer  := l_cols(i).col_scale;
    l_csf  pls_integer  := l_cols(i).col_charsetform;
    l_sch  varchar2(32) := l_cols(i).col_schema_name;
    l_res  varchar2(32767);
  Begin
    l_res := 
      Case
      when l_type = 0                    then ''
        When l_type = 1 and l_csf = 2    then 'NVARCHAR2('||l_lg||')'
        When l_type = 1                  then 'VARCHAR2('||l_lg||')'
        When l_type = 2 and l_sca = -127 and nvl(l_pre,0) != 0
          Then 'FLOAT('||l_pre||')' 
        When l_type = 2
          Then 
            'NUMBER' ||
            Case 
              When nvl(l_pre,0) = 0 and l_sca is not null and l_sca != -127
                Then '(38)'
              When nvl(l_pre,0) = 0 then ''
              When l_sca is null then '('||l_pre||')'
              Else '('||l_pre||','||l_sca||')'
            End
        When l_type = 3                  then 'NATIVE INTEGER'
        When l_type = 8                  then 'LONG'
        When l_type = 9 and l_csf = 2
          Then 'NCHAR VARYING('||l_lg||')'
        When l_type = 9                  then 'VARCHAR('||l_lg||')'
        When l_type = 11                 then 'ROWID'
        When l_type = 12                 then 'DATE'
        When l_type = 23                 then 'RAW('||l_lg||')'
        When l_type = 24                 then 'LONG RAW'
        When l_type = 29                 then 'BINARY_INTEGER'
        When l_type = 33                 then 'LIST OF ANY TYPE'
$IF DBMS_DB_VERSION.VER_LE_10_2 $THEN
        When l_type in (58, 109)         then l_sch||' DEFINED TYPE'
$ELSE
        When l_type in (58, 109)         then l_sch||'.'||l_cols(i).col_type_name
$END
        When l_type = 69                 then 'ROWID'
        When l_type = 96 and l_csf = 2   then 'NCHAR('||l_lg||')'
        When l_type = 96                 then 'CHAR('||l_lg||')'
        When l_type = 100                then 'BINARY_FLOAT'
        When l_type = 101                then 'BINARY_DOUBLE'
        When l_type = 102                then 'REF CURSOR'
        When l_type in (104, 208)        then 'UROWID'
        When l_type in (105, 106)        then 'MLSLABEL'
$IF DBMS_DB_VERSION.VER_LE_10_2 $THEN
        When l_type in (110, 111)        then ' REF '||l_sch||' DEFINED TYPE'
$ELSE
        When l_type in (110, 111)        then ' REF '||l_sch||'.'||l_cols(i).col_type_name
$END
        When l_type = 112 and l_csf = 2  then 'NCLOB'
        When l_type = 112                then 'CLOB'
        When l_type = 113                then 'BLOB'
        When l_type = 114                then 'BFILE'
        When l_type = 115                then 'CFILE'
        When l_type = 121                then 'OBJECT'
        When l_type = 122                then 'TABLE'
        When l_type = 123                then 'vARRAY'
        When l_type = 178                then 'TIME('||l_sca||')'
        When l_type = 179
          Then 'TIME('||l_sca||') WITH TIME ZONE'
        When l_type = 180                then 'TIMESTAMP('||l_sca||')'
        When l_type = 181
          Then 'TIMESTAMP('||l_sca||') WITH TIME ZONE'
        When l_type = 182
          Then 'INTERVAL YEAR(' ||l_pre||') TO MONTH'
        When l_type = 183
          Then 'INTERVAL DAY(' ||l_pre||') TO SECOND(' ||l_sca|| ')'
        When l_type = 231
          Then 'TIMESTAMP(' ||l_sca||') WITH LOCAL TIME ZONE'
        When l_type = 250                then 'PL/SQL RECORD'
        When l_type = 251                then 'PL/SQL TABLE'
        When l_type = 252                then 'PL/SQL BOOLEAN'
        Else '?'
    End;
    return l_res;
  End;
Begin
  dbms_sql.parse (l_cursor, l_query, dbms_sql.native);
$IF DBMS_DB_VERSION.VER_LE_10_2 $THEN
  dbms_sql.describe_columns (l_cursor, l_colnb, l_cols);
$ELSE
  dbms_sql.describe_columns3 (l_cursor, l_colnb, l_cols);
$END
  l_lg := 0;
  For i in 1..l_colnb loop
    l_lg := greatest(l_lg,length(l_cols(i).col_name));
  End loop;
  l_lg := least(l_lg, &lines-45);
  For i in 1..l_colnb loop
    dbms_output.put(to_char(i,'999')||' ');
    dbms_output.put(rpad(l_cols(i).col_name,l_lg/*,'.'*/)||' ');
    dbms_output.put(rpad(getType(i),30));
    If not l_cols(i).col_null_ok then
      dbms_output.put(' NOT NULL');
    End if;
    dbms_output.new_line;
  End loop;
End;
/
Spool off
Prompt
@@envint