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_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;
--
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).
