Running COPY commnad from PL/SQL block??

[aljubicic]

Hi,

I want to be able to run SQLPlus's COPY command from within a PL/SQL block of code. Is this possible and how would I go about doing this?? Any examples would help greatly..

DECLARE

{Variable Declarations}

BEGIN

{Code.....}

{Be able to RUN COPY command}

{Code}

END;
/

 

[Thargy]

aljubicic,

no, you can't, because a pl/sql routine can (subject to caveats below) only work within its own database. The copy command in sql plus connects to two databases, to do what it does.

In principle, you could achieve something similar in pl/sql, by creating a db link to the other database, and then moving data via the link. Provided you have the necessary privileges and permissions, you could run some PL/SQL against the linked db (and hence its tables), which could simulate a sqlplus copy command.

I recommend that you use sqlplus for the job.

Regards

Tharg

Grinding away at things Oracular

 

[taupirho]

Could you not do:

declare
begin
code
end

SQL COPY COMMAND

declare
begin
more code
end

 

[Thargy]

taupirho,

No,

I've just checked the pl/sql and sqlplus command reference.

sqlplus copies by connecting to two databases, as I originally mentioned. PL/SQL only has one copy command that I'm aware of, and that's part of the DBMS_LOB utility, to copy from one LOB to another.

You have to have two connections to simultaneously access two different databases. Where (in your example) is the second connection? Remember, PL/SQL won't run SQLPLUS commands, e.g. you'd never say "SET SERVEROUTPUT ON" in PL/SQL and expect it to do something besides raise an exception, would you?

For PL/SQL to access two databases, you'd need two connections in the pl/sql context.

Regards

Tharg



Grinding away at things Oracular

 

[taupirho]

Consider the following as proof of concept (I've always wanted to say that - Santa) . O/S and sqlplus commands are in bold to distinguish them. I've only changed the connect strings for security but the rest is a straight copy/paste from my terminal.

$ sqlplus user1/pass1@db1

SQL*Plus: Release 9.2.0.2.0 - Production on Thu Aug 10 15:25:41 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> select * from tomxxx;

X
----------
1

SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

$ sqlplus user2/pass2@db2

SQL*Plus: Release 9.2.0.2.0 - Production on Thu Aug 10 15:27:33 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production

SQL> select * from tom;

X
----------
999

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production
$

$ type tom.sql

variable x varchar2(30);
variable y varchar2(30);
begin
select to_char(sysdate,'dd-mon-yy hh24:mi:ss')
into :x
from dual;
end;
/
connect user2/pass2@db2
copy to user1/pass1@db1 -
append tomxxx using -
select * from tom;
begin
select to_char(sysdate,'dd-mon-yy hh24:mi:ss')
into :y
from dual;
end;
/
print x
print y
exit

$ sqlplus / @tom

SQL*Plus: Release 9.2.0.2.0 - Production on Thu Aug 10 15:38:29 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production


PL/SQL procedure successfully completed.

Connected.

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
1 rows selected from DEFAULT HOST connection.
1 rows inserted into TOMXXX.
1 rows committed into TOMXXX at bond@dbond01.


PL/SQL procedure successfully completed.


X
--------------------------------------------------------------------------------
10-aug-06 15:38:32


Y
--------------------------------------------------------------------------------
10-aug-06 15:38:38

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production
$
$ sqlplus user1/pass1@db1

SQL*Plus: Release 9.2.0.2.0 - Production on Thu Aug 10 15:40:58 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> select * from tomxxx;

X
----------
1
999

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Producti
on
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
$

 

[Thargy]

taupirho,

well done for making the effort to be clear (I really do appreciate it).

Notwithstanding your elegant 'proof of concept', which I don't dispute, I still do dispute that it's proving what you think it is.

when you used

$ sqlplus / @tom

SQL*Plus: Release 9.2.0.2.0 - Production on Thu Aug 10 15:38:29 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production


PL/SQL procedure successfully completed.

Connected.

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
   1 rows selected from DEFAULT HOST connection.
   1 rows inserted into TOMXXX.
   1 rows committed into TOMXXX at bond@dbond01.


PL/SQL procedure successfully completed.


X
--------------------------------------------------------------------------------
10-aug-06 15:38:32


Y
--------------------------------------------------------------------------------
10-aug-06 15:38:38

you ran an anonymous block, followed by the sqlplus copy command, followed by another anonymous block. This was invoked from the sql plus command line, and was therefore not a pl/sql routine. I believe that the original question was to run sql plus from within pl/sql, and not to intermingle sql plus commands with anonymous blocks.

The very fact that you invoked the 'proof' from the sql plus command prompt demonstrates that it is not PL/SQL. I believe that this was the original question posed.

I've just reviewed the first question, and it does appear to want this intermingling, although the text of the question clearly states "from within a PL/SQL block of code".

I may have gone off the deep end here, so please let me know what you think Taupirho.

Regards

Tharg



Grinding away at things Oracular

 

[taupirho]

I'll let others decide - suffice it to say in my original answer I said

Could you not do:

declare
begin
code
end

SQL COPY COMMAND

declare
begin
more code
end

and you said No

I then posted a solution which did a

declare
begin
code
end

SQL COPY COMMAND

declare
begin
more code
end


Note the similarities

 

[Thargy]

I surrender!

Grinding away at things Oracular