thats the problem, I do not know for sure that the tables are identical in each
schema. Sorry, I left that out. I have to check for that before I even get to that
point. If there are 'NOT NULL' columns in the destination table, then I throw insert
to an error table, if not then I build the comma delimited string of columns.
>
> From: "Jamadagni, Rajendra" <[EMAIL PROTECTED]>
> Date: 2003/03/11 Tue AM 11:29:03 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: need help with dynamic sql
>
> if you at-least know the source and destination tables will have same number
> of columns and data types ... you could just do something like
>
> insert into target.table
> select from source.table ....
>
> Raj
> -------------------------------------------------------------
> Rajendra dot Jamadagni at espn dot com
> Any views expressed here are strictly personal.
> QOTD: Any clod can have facts, having an opinion is an art !!
>
>
> -----Original Message-----
> Sent: Tuesday, March 11, 2003 10:24 AM
> To: Multiple recipients of list ORACLE-L
>
>
> g_colName is a string of column names that I build earlier in the algorithm.
> My problem is how do I do the 'INTO' part of this cursor, since I do not
> know how many or of what type my columns are. The query is on the fly and
> totally dynamic.
>
> Anyway to do this without using DBMS_SQL package?
>
>
> OPEN cur_values FOR 'SELECT '||g_colName ||
> ' FROM ' || p_sourceSchema||'.'||v_tableName ||
> ' WHERE PARENT_RESOURCE_ID BETWEEM :1 AND :2'
>
> USING p_startResourceID, p_endResourceID;
> LOOP
> FETCH cur_values
> INTO v_values;
> EXIT WHEN cur_values%NOTFOUND;
>
> EXECUTE IMMEDIATE 'INSERT INTO
> '||p_targetSchema||'.'||v_tableName||
> '('||g_colName||')'||
> '(VALUES)';
>
> --
> 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).
>
>
>
> *********************************************************************This e-mail
> message is confidential, intended only for the named recipient(s) above and may
> contain information that is privileged, attorney work product or exempt from
> disclosure under applicable law. If you have received this message in error, or are
> not the named recipient(s), please immediately notify corporate MIS at (860)
> 766-2000 and delete this e-mail message from your computer, Thank
> you.*********************************************************************1
>
>
Title: RE: need help with dynamic sqlif you at-least know the source and destination tables will have same number of columns and data types ... you could just do something like
insert into target.table
select from source.table ....Raj
-------------------------------------------------------------
Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !!
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 11, 2003 10:24 AM
To: Multiple recipients of list ORACLE-L
Subject: need help with dynamic sql
g_colName is a string of column names that I build earlier in the algorithm. My problem is how do I do the 'INTO' part of this cursor, since I do not know how many or of what type my columns are. The query is on the fly and totally dynamic.
Anyway to do this without using DBMS_SQL package?
OPEN cur_values FOR 'SELECT '||g_colName ||
' FROM ' || p_sourceSchema||'.'||v_tableName ||
' WHERE PARENT_RESOURCE_ID BETWEEM :1 AND :2'USING p_startResourceID, p_endResourceID;
LOOP
FETCH cur_values
INTO v_values;
EXIT WHEN cur_values%NOTFOUND;EXECUTE IMMEDIATE 'INSERT INTO '||p_targetSchema||'.'||v_tableName||
'('||g_colName||')'||
'(VALUES)';--
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).
