RE: Need help with a dynamic query

[Jacques Kilchoer]

Title: RE: Need help with a dynamic query





Since the target schema is the one whose columns matter most, I would think that you should do an outer join to the columns in the original schema, to see if any not null columns in the target schema are missing from the original schema.

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
 
 My goal is to select data from a table in one schema and 
 insert it into the same table in another schema. However, I 
 am not 100% certain that the tables exist in both schemas or 
 that the columns are the same.
 
 The columns can be different if I have all the data needed to 
 columns in my target schema that are set to 'NOT NULL'.
 
 Im trying to write a little TABLE_CHECK function to check 
 these. Im having problems with the SQL. Its going to be 
 dynamic and we have a few thousand tables between all the 
 schemas so the faster the better
 
 I apologize for the bad parsing. Im sending this from work 
 over the web and it doesnt parse well so the code will be a 
 bit messy
 
 FUNCTION tableCheck(p_tableName IN VARCHAR2,
 p_sourceSchema IN VARCHAR2,
 p_targetSchema IN VARCHAR2) 
 RETURN VARCHAR2 IS
 
 TYPE REF_TYPE IS REF CURSOR;
 cur_colName REF_TYPE;
 
 CURSUR cur_colName IS
 SELECT COLUMN_NAME
 FROM DBA_TAB_COLUMNS
 WHERE TABLE_NAME = p_tableName;
 
 v_colName DBA_TAB_COLUMNS.COLUMN_NAME%TYPE;
 v_null DBA_TAB_COLUMNS.NULLABLE%TYPE;
 v_owner DBA_TAB_COLUMNS.OWNER%TYPE;
 
 BEGIN
 
 OPEN cur_colName FOR ' SELECT COLUMN_NAME, 
 OWNER, NULLABLE
 ' FROM DBA_TAB_COLUMNS t, 
 DBA_TAB_COLUMNS t1'||
 ' WHERE t.TABLE_NAME = :1 '||
 ' AND t1.TABLE_NAME = 
 t.TABLE_NAME
 ' AND t.OWNER = 
 USING p_tableName, p_sourceSchema, p_targetSchema;
 LOOP
 FETCH cur_colname
 INTO v_colName, v_null;
 EXIT WHEN cur_colName%NOTFOUND;

RE: Need help with a dynamic query

[Bob Metelsky]

SELECT 
'CREATE TABLE TMP_'||table_name||' TABLESPACE tables AS '||chr(10)||
'SELECT * FROM '||table_name||';'
 from sys.all_tables where owner = 'yourowner';

Or add a @dblink


bob


 This probably isnt that hard, but Im having a brain dead moment. 
 
 My goal is to select data from a table in one schema and 
 insert it into the same table in another schema. However, I 
 am not 100% certain that the tables exist in both schemas or 
 that the columns are the same.
 
 The columns can be different if I have all the data needed to 
 columns in my target schema that are set to 'NOT NULL'.
 
 Im trying to write a little TABLE_CHECK function to check 
 these. Im having problems with the SQL. Its going to be 
 dynamic and we have a few thousand tables between all the 
 schemas so the faster the better
 
 I apologize for the bad parsing. Im sending this from work 
 over the web and it doesnt parse well so the code will be a 
 bit messy
 
 FUNCTION tableCheck(p_tableNameIN VARCHAR2,
 p_sourceSchema IN VARCHAR2,
 p_targetSchema IN VARCHAR2) 
 RETURN VARCHAR2 IS
 
TYPE REF_TYPE IS REF CURSOR;
  cur_colName REF_TYPE;
 
 CURSUR cur_colName IS
SELECT COLUMN_NAME
  FROM DBA_TAB_COLUMNS
 WHERE TABLE_NAME = p_tableName;
 
v_colName DBA_TAB_COLUMNS.COLUMN_NAME%TYPE;
v_nullDBA_TAB_COLUMNS.NULLABLE%TYPE;
v_owner   DBA_TAB_COLUMNS.OWNER%TYPE;
 
 BEGIN
 
  OPEN cur_colName FOR ' SELECT COLUMN_NAME, 
   OWNER, NULLABLE
   ' FROM   DBA_TAB_COLUMNS t, 
 DBA_TAB_COLUMNS t1'||
   ' WHERE t.TABLE_NAME = :1 '||
   '   AND t1.TABLE_NAME = 
t.TABLE_NAME
   '   AND t.OWNER = 
 USING p_tableName, p_sourceSchema, p_targetSchema;
  LOOP
FETCH cur_colname
 INTO v_colName, v_null;
   EXIT WHEN cur_colName%NOTFOUND;
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: [EMAIL PROTECTED]
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') 
 and in the message BODY, include a line containing: UNSUB 
 ORACLE-L (or the name of mailing list you want to be removed 
 from).  You may also send the HELP command for other 
 information (like subscribing).
 
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bob Metelsky
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).