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