Re: EXECUTE IMMEDIATE & COPY FROM

i think u should use the DECLARE statement for defining the Cursor and then
use the OPEN statement.
-san



                                                                                                                   
                    "THEODORE                                                                                      
                    PAPADOPOULOS"        To:     "Oracle" <[email protected]>                                    
                    <[email protected]>        cc:                                                                       
                                         Subject:     [oracle] EXECUTE IMMEDIATE & COPY FROM                       
                    04/08/03                                                                                       
                    09:03 PM                                                                                       
                    Please                                                                                         
                    respond to                                                                                     
                    "Oracle"                                                                                       
                                                                                                                   
                                                                                                                   




Is it possible to execute a "COPY FROM" sql statement within a EXECUTE
IMMEDIATE ??
I tried it several times but i always get an ORA-00900 error "Invalid sql
statment" .

I am using the follwing code , but i also tried to do it with a hardcoded
sql statment without success.




OPEN l_cursor0 FOR 'SELECT tbl.table_name FROM  user_tables@' ||
TARGET_DBNAME  ||  ' tbl';
LOOP
    FETCH l_cursor0 INTO l_str_table_name;
    EXIT WHEN l_cursor0%NOTFOUND;
    --l_str_sql:='COPY FROM '|| SOURCE_USERNAME || '/'|| SOURCE_PASSWORD
|| '@'|| SOURCE_DBNAME|| '  INSERT '|| l_str_table_name || '@' ||
TARGET_DBNAME ||   ' USING SELECT * FROM ' || l_str_table_name || ' WHERE
MARKETDATE BETWEEN ' || FROMDATE || ' AND ' || TODATE ;

END LOOP;    -- l_curosr0
CLOSE l_cursor0;



Thanks in advance...
---
Change your mail options at http://p2p.wrox.com/manager.asp or
to unsubscribe send a blank email to %%email.unsub%%.





---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].