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;
