Need help with a dynamic query

From: <rgaffuri_at_cox.net>
Date: Mon, 10 Mar 2003 10:49:17 -0800
Message-ID: <F001.00564DF5.20030310104917@fatcity.com>

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: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.net

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 - 12:49:17 CST