Rem
Rem                      Copyright (C) 2016 by Michel CADOT
Rem  ========================================================================
Rem
Rem  Fichier : DescSQLCr.sql
Rem
Rem  Version Oracle : V9/V10/V11
Rem
Rem  Description : Cree 2 fonctions pipelines donnant les colonnes retournées par une 
Rem                requete SQL et leur description
Rem
Rem  Paramètres : Aucun
Rem
Rem  Notes :
Rem  * La première function retourne des lignes de caractères
Rem  * La seconde retourne 3 colonnes et necessite la creation de 2 types
Rem
Rem  Modification :
Rem     MCadot      06/10/2016 - Creation (d'apres DescSQL.sql) '
Rem
Rem  Mots-Cle : describe sql query select
Rem

Create or replace function descSQL1 (p_sql in varchar2)
Return sys.odcivarchar2list 
authid current_user pipelined
Is
  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;
  l_res    varchar2(32767);
  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, p_sql, 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;
  For i in 1..l_colnb loop
    l_res := to_char(i,'999')||' ';
    l_res := l_res || rpad(l_cols(i).col_name,l_lg/*,'.'*/)||' ';
    l_res := l_res || rpad(getType(i),30);
    If not l_cols(i).col_null_ok then
      l_res := l_res || ' NOT NULL';
    End if;
    Pipe row (l_res);
  End loop;
End;
/
Show errors
Drop type descSQLTable
/
Create or replace type descSQLRecord as object (
  num  integer,
  name varchar2(4000),
  type varchar2(30),
  nn   varchar2(8)
  )
/
Create or replace type descSQLTable as table of descSQLRecord
/
Create or replace function descSQL2 (p_sql in varchar2)
Return descSQLTable
authid current_user pipelined
Is
  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, p_sql, 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;
  For i in 1..l_colnb loop
    Pipe row (descSQLRecord(
                i, 
                l_cols(i).col_name, getType(i), 
                Case when not l_cols(i).col_null_ok then 'NOT NULL' end)
              );
  End loop;
End;
/
Show errors
Prompt