execute immediate gives error for statement that runs fine in SQL*PLUS

From: Lalo Salvalus <lalosal_at_hotmail.com>
Date: 5 Sep 2003 08:43:06 -0700
Message-ID: <2a6fb1fd.0309050743.2bcf0359@posting.google.com>

I am trying to run the COPY command from PL/SQL and am not getting it to work. My current script is:

---

#!/usr/bin/sh

user=`echo "$1" | tr '[:lower:]' '[:upper:]'`

sqlplus $user/$user_at_SID <<myEnd

declare
v_user varchar2(100);
begin
v_user:='$user';
execute immediate '
SET LONG 6000000
set arraysize 200
set copycommit 10
 COPY FROM '||v_user||'/'||v_user||'@SID TO - ' ||v_user||'/'||v_user||'@SID INSERT -
MYNEWTABLE USING SELECT * FROM MYTABLE'; end;
/
exit
myEnd -- end input

---

and gives:

---

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production JServer Release 8.1.7.4.0 - Production

SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 declare *
ERROR at line 1:

ORA-00922: missing or invalid option
ORA-06512: at line 15


--------------------------

I also tried using this in place of the 'execute immediate' line:

---

sys.dbms_utility.exec_ddl_statement('
COPY FROM '||v_user||'/'||v_user||'@SID TO - '||v_user||'/'||v_user||'@SID INSERT -
MYNEWTABLE USING SELECT * FROM MYTABLE');

---

and that gave:

---

ERROR at line 1:

ORA-00900: invalid SQL statement
ORA-06512: at "SYS.DBMS_SYS_SQL", line 824
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "SYS.DBMS_UTILITY", line 409
ORA-06512: at line 7


-------------------------

Both iterations seem to run into trouble in a 'declare' line, i.e. the output right before the error looks like:

---

SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 declare

---

and I'm guessing I'm trying to use the above two for something they weren't meant for. Am I?

Oh, yes, if I make the substitutions in the COPY command and run it in SQL*PLUS, it runs fine.

Is there a way to run COPY from PL/SQL? I would just call a SQL script with the COPY command except that I need to run the COPY command, or not, based on a value in the PL/SQL script.

thanks,

Lalo Received on Fri Sep 05 2003 - 10:43:06 CDT