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

"Lalo Salvalus" <lalosal_at_hotmail.com> wrote in message news:2a6fb1fd.0309050743.2bcf0359_at_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

Lalo

COPY is an SQL*Plus command.
You could have invoked COPY in your shell script provided you didn't put in within
PL/SQL.
I believe COPY will disappear after 9i so not recommended for new use. You could use INSERT INTO table_a SELECT * FROM table_b.

HTH eric

--
Remove the dross to contact me directly