Re: [cx-oracle-users] Copying tables between two schemas? SQ*PLUS

[Mark H. <mh...@pi...>]

On 11/6/11 5:29 PM, Robert Lucente wrote:
> Recommend using the Oracle built in export/pump functionality. Also,
> depending on size of the data you might consider using Oracle transportable
> tablespaces.
>
> I guess trying to get you to consider the built in functionality present in
> Oracle and operating system. Why add complexity by adding Python and
> cx-oracle?

Here's how to automate sqlplus in a shell script.  I'm not familiar with
the copy command, so it just uses a simple select command.  If you need to
do this in windows, you can use "sqlplus @foo" and put the commands in
the file foo.sql


$ cat auto-sqlplus
#!/bin/sh

sqlplus -S -L scott/tiger@myorcl <<.
select 2+2 from dual;
.

$ chmod +x auto-sqlplus
$ ./auto-sqlplus

        2+2
----------
	 4



BTW, don't forget to trim list digests...



> Message: 4
> Date: Wed, 2 Nov 2011 14:22:27 +1100
> From: Victor Hooi<vic...@ya...>
> Subject: [cx-oracle-users] Copying tables between two schemas? SQ*PLUS
> 	COPY?
> To: cx-...@li...
> Message-ID:
> 	<CAM...@ma...>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Hi,
>
> We have two separate schemas on different Oracle database instances, and we
> have a need to copy the records from certain tables from one to the other.
>
> Due to internal regulations, creating a database link between them isn't an
> option - so I suppose that rules our using INSERT INTO...SELECT, right?
>
> Previously, we were using SQL*PLUS's COPY extension:
>
> http://www.oracleutilities.com/SQLPLus/copy.html
>
> and this has worked really well.
>
> However, now we'd like to automate this, and so I figured cx_Oracle would
> be the way to go. However, I don't think I can use COPY from cx_Oracle
> since it's SQL*PLUS specific (
> http://stackoverflow.com/questions/7963441/using-sqlplus-copy-from-inside-cx
> -oracle
> ).
>
> Is there any way at all to get COPY to work from cx_Oracle? Or any other
> way to achieve the same thing just as elegantly, and not iterate through
> records?
>
> Cheers,
> Victor