Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: "WHERE 1 = 2" does strange things
As Steve suggested, you can create a procedure to extract the DDL
components from a data dictionary view. I have provided a
demonstration below. You may need to add some special handling for
additional datatypes. I just threw together a test of some common
ones.
scott_at_ORA92> CREATE TABLE test1
2 (col1 NUMBER,
3 col2 NUMBER(1), 4 col3 NUMBER(3,2), 5 col4 CHAR(4), 6 col5 NCHAR(5), 7 col6 NVARCHAR2(7), 8 col7 RAW(8), 9 col8 VARCHAR(10), 10 col9 VARCHAR2(11))
Table created.
scott_at_ORA92> CREATE OR REPLACE TYPE test_typ AS OBJECT
2 (col1 NUMBER);
3 /
Type created.
scott_at_ORA92> CREATE TABLE test2
2 (col10 NUMBER,
3 col11 test_typ, 4 col12 BFILE, 5 col13 BLOB, 6 col14 CLOB, 7 col15 DATE, 8 col16 FLOAT, 9 col17 LONG, 10 col18 ROWID, 11 col19 TIMESTAMP)
Table created.
scott_at_ORA92> CREATE VIEW test_view AS
2 SELECT test1.*, test2.*
3 FROM test1, test2
4 WHERE test1.col1 = test2.col10
5 /
View created.
scott_at_ORA92> DESC test_view
Name Null? Type
----------------------------- -------- --------------------
COL1 NUMBER COL2 NUMBER(1) COL3 NUMBER(3,2) COL4 CHAR(4) COL5 NCHAR(5) COL6 NVARCHAR2(7) COL7 RAW(8) COL8 VARCHAR2(10) COL9 VARCHAR2(11) COL10 NUMBER COL11 TEST_TYP COL12 BINARY FILE LOB COL13 BLOB COL14 CLOB COL15 DATE COL16 FLOAT(126) COL17 LONG COL18 ROWID COL19 TIMESTAMP(6)
scott_at_ORA92> CREATE OR REPLACE PROCEDURE create_empty_table_from_view
2 (p_table_name IN VARCHAR2,
3 p_view_name IN VARCHAR2)
4 AS
5 v_sql VARCHAR2(32767);
6 BEGIN
7 v_sql := 'CREATE TABLE ' || p_table_name || '(';
8 FOR r IN
9 (SELECT column_name, data_type, 10 data_length, data_precision, data_scale, 11 nullable 12 FROM user_tab_columns 13 WHERE table_name = UPPER (p_view_name) 14 ORDER BY column_id) 15 LOOP 16 v_sql := v_sql 17 || r.column_name 18 || ' ' 19 || r.data_type 20 || CASE 21 WHEN r.data_type = 'NUMBER' 22 THEN CASE 23 WHEN r.data_precision IS NULL 24 THEN NULL 25 ELSE '(' 26 || r.data_precision 27 || CASE 28 WHEN r.data_scale = 0 29 THEN NULL 30 ELSE ',' || r.data_scale 31 END 32 || ')' 33 END 34 WHEN r.data_type = 'CHAR' 35 OR r.data_type = 'NCHAR' 36 OR r.data_type = 'NVARCHAR2' 37 OR r.data_type = 'RAW' 38 OR r.data_type = 'VARCHAR' 39 OR r.data_type = 'VARCHAR2' 40 -- add additional datatypes here 41 -- for which length can be specified 42 THEN '(' || r.data_length || ')' 43 ELSE NULL 44 END 45 || CASE r.nullable 46 WHEN 'Y' THEN NULL 47 WHEN 'N' THEN ' NOT NULL' 48 END 49 || ',';
Procedure created.
scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> EXEC create_empty_table_from_view ('EMPTY_TABLE',
'TEST_VIEW')
PL/SQL procedure successfully completed.
scott_at_ORA92> DESC empty_table
Name Null? Type
----------------------------- -------- --------------------
COL1 NUMBER COL2 NUMBER(1) COL3 NUMBER(3,2) COL4 CHAR(4) COL5 NCHAR(10) COL6 NVARCHAR2(14) COL7 RAW(8) COL8 VARCHAR2(10) COL9 VARCHAR2(11) COL10 NUMBER COL11 TEST_TYP COL12 BINARY FILE LOB COL13 BLOB COL14 CLOB COL15 DATE COL16 FLOAT(126) COL17 LONG COL18 ROWID COL19 TIMESTAMP(6)
scott_at_ORA92> Received on Wed Sep 28 2005 - 17:32:53 CDT