Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: "WHERE 1 = 2" does strange things

Re: "WHERE 1 = 2" does strange things

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 28 Sep 2005 15:32:53 -0700
Message-ID: <1127946773.327832.101090@f14g2000cwb.googlegroups.com>


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))

 11
scott_at_ORA92> /

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)

 12 /

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  	       || ',';

 50 END LOOP;
 51 v_sql := RTRIM (v_sql, ',') || ')';  52 EXECUTE IMMEDIATE v_sql;
 53 END;
 54 /

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US