Anexa

Acestia sunt pasii ce trebuie urmati :

Generati prima parte a comenzii CREATE TABLE. Retineti ca de asemenea noi selectam numele tabelei si o constanta. Aceste doua coloane vor fi folosite numai pentru a realiza sortarea. Ele nu vor mai fi tiparite la ultimul pas.

EX.

        set feedback off
        set echo off
        set head off

        select distinct table_name sort1,
              0             sort2,
              'CREATE TABLE '|| table_name ||' ('
        from use_tab_columns
        /

DEPT     0      CREATE TABLE DEPT (
EMP      0      CREATE TABLE EMP  (
SALGRADE 0      CREATE TABLE SALGRADE (

Acum obtinem definitia completa a fiecarei coloane pentru fiecare tabla.

EX.

break  on sort1 skip 1
select table_name sort1,
        column_id sort2,
        ' ' ||
        column_name ||
        decode(data_type, 'DATE',' DATE',
                          'LONG',' LONG'
                          'CHAR',' CHAR('||data_length||')',
                          'VARCHAR','VARCHAR('||data_length
                                              ||')',
                          'NUMBER',
                           decode(data_precision,null,' NUMBER',
                                ' NUMBER('||data_precision||','||
                                                data_scale||') '),
                        '*********** INVALID DATA TYPE **************')||
                  decode(nulltable ,'N',' NOT NULL',null)||','
          from user_tab_columns
          order by table_name,column_id
/

Acum sa urmarim un exemplu complet. El consta din trei SELECT-uri combinate cu operatorul UNION. Primul genereaza comanda "CREATE TABLE" ,al doilea defineste coloanele pentru fiecare tabela mai putin ultima intrare si ultimul selecteaza ultima coloana . Tiparirea primei si celei de-a doua coloane ('sort1' si 'sort2') a fost eliminata folosind optiunea NOPRINT a comenzii COLUMN.

EX.

set feedback off echo off head off
set pagesize 9999
col sort1 noprint
col sort2 noprint
break on sort1 skip 1

        spool sqlcreates.sql

select distinct table_name sort1,
                0          sort2,
                'CREATE TABLE '||table_name||' ('
from user_tab_columns
union
select table_name sort1,
        column_id sort2,
        ' '||
        column_name||
        decode(data_type,'DATE',' DATE',
                         'LONG',' LONG',
                         'CHAR',' CHAR('||data_length||')',
                         'VARCHAR', 'VARCHAR('||data_length||')',
                         'NUMBER',
                                decode(data_precision,null, 'NUMBER',
                                        'NUMBER('||data_precision||','||
                                                        data_scale||') '),
                        '******** INVALID DATA TYPE ********')||
                decode(nullable,'N',' NOT NULL',null)||','
from user_tab_columns c
where column_id <(select max(column_id)
                  from user_tab_columns
                  where table_name = c.table_name)

union
select table_nae sort1,
        column_id sort2,
        ' '||   column_table||
        decode(data_type,'DATE',' DATE',
                         'LONG',' LONG',
                         'CHAR',' CHAR('||data_length||')',
                         'VARCHAR', 'VARCHAR('||data_length||')',
                         'NUMBER',
                                decode(data_precision,null, 'NUMBER',
                                        'NUMBER('||data_precision||','||
                                                        data_scale||') '),
                        '******** INVALID DATA TYPE ********')||
                decode(nullable,'N',' NOT NULL',null)||','
/'
from user_tab_columns c
where column_id = (select max(column_id)
                   from user_tab_columns
                    where table_name = c.table_name)
order by 1,2
/
spool off
col sort1 clear
col sort2 clear
set head on feedback on echo on
set pagesize 24
Acest exemplu, relativ complicat produce urmatorul fisier :

CREATE TABLE DEPT (
        DEPTNO NUMBER (2,0) NOT NULL,
        DNAME CHAR (14) ,
        LOC CHAR (13) )
/

CREATE TABLE EMP (
        EMPNO NUMBER (4,0) NOT NULL,
        ENAME CHAR (10) ,
        JOB   CHAR (9),
        MGR NUMBER (4,0) ,
        HIREDATE DAATE,
        SAL NUMBER (7,2),
        COMM NUMBER (7,2),
        DEPTNO NUMBER (2,0) NOT NULL)
/

CREATE TABLE SALGRADE (
        GRADE NUMBER (1,0) NOT NULL,
        LOSAL NUMBER (5,0),
        HISAL NUMBER (5,0) )
/

GENERARE de Predicate "dinamice"

Este posibil sa capturam clauza "WHERE" intr-o substituire de variabila utilizind optiunea "NEW_VALUE" a comenzii "COLUMN". Exemplul permite utilizatorului sa genereze un raport al angajatilor din cadrul unui departament si/sau incepind cu o anumita data.

EX. Contens of dynamic.SQL accept deptno char prompt 'Enter deptno; for all' accept hiredate char prompt 'Enter date ; for all' set termout off col c1 new_value where_clause select decode('&hiredate', null,' ', 'where hiredate = '''||'&hiredate'||'''' ), decode('&hiredate', numm,'where deptno = '||'|| ' and hiredate = '''||'&hiredate'||'''' ) ) c1 from sys.dual / set verify on set termout on select ename from emp &where_clause / start dynamic.sql Enter deptno; for all Enter hiredate; for all 4-jun-94 SQL> select ename 2 from emp &where_clause 3 SQL> / old 2: from emp &where_clause new 2: from emp where hiredate = '4-jun-94'
ENAME ---------- TURNER ADAMS
start dynamic.sql Enter deptno; for all Enter hiredate; for all 4-jun-94 SQL> select ename 2 from emp &where_clause 3 SQL> / old 2: from emp &where_clause new 2: from emp where deptno = 20
ENAME ------------------ SMITH JONES SCOTT ADAMS

Aceasta tehnica poate fi utilizata de asemenea pentru a permite unui utilizator in timpul rularii sa specifice coloana dupa care sa se ordoneze fisierul de raport.
Retineti ca pentru a selecta un apostrof trebuie sa folositi doua.

Anexa

GENERARE de Predicate "dinamice"