RE: Need help with a dynamic query

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bob Metelsky
  INET: bmetelsky_at_cps92.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).