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: rgaffuri_at_cox.net [mailto:rgaffuri_at_cox.net]
>
> 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: Jacques Kilchoer
INET: Jacques.Kilchoer_at_quest.com
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: ListGuru_at_fatcity.com (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).
Received on Mon Mar 10 2003 - 14:05:18 CST
