Re: [External] : sqlcl Copy

  • From: Martin Berger <martin.a.berger@xxxxxxxxx>
  • To: charlottejanehammond@xxxxxxxxx
  • Date: Fri, 24 Sep 2021 07:48:59 +0200

Hello Charlotte,

I used SQLCL with wallet connection to copy data with the BRIDGE command .
(I know I also tested COPY, but BRIDGE worked better, unfortunately I
didn't document the details wher COPY failed for my purpose)

My pieces are:

the script copy.sql:
-- SET FEEDBACK OFF
-- copy from "jdbc:oracle:thin:a/@PDB1a?TNS_ADMIN=/var/tmp/sql_tns" APPEND
BLUBBER_BLA using select * from TEST_TABLE where rownum < 2;
-- copy from "&1" APPEND &2 using select * from TEST_TABLE where rownum <
10;
--select q'[bridge &2 as "&1"(SELECT u.* FROM TEST_TABLE u)truncate;]' from
dual;

bridge &2 as "&1"(SELECT u.* FROM REMOTE_TABLE u)truncate;

exit

the script

sqlcl.sh:
#!/bin/bash
unset ORACLE_HOME
unset LD_LIBRARY_PATH

export JAVA_HOME=/home/oracle/project/jdk1.8.0_261
export TNS_ADMIN=/home/oracle/project/tns
/home/oracle/project/sqlcl/bin/sql $*

sql.sh is called with 4 parameters (where a "parameter" an be several
"parts":

*Options*
     '-L -S '

*Connection*
    'jdbc:oracle:thin:/@' ||CONNECT_URL ||
'?TNS_ADMIN=/home/oracle/project/tns'

*Script*
     '/home/oracle/project/scripts/copy.sql'

*Parameter*
     jdbc:oracle:thin:/@' || CONNECT_URL ||
'?TNS_ADMIN=/home/oracle/project/tns') || ' ' || v_table_name


of course the wallet needs a proper Credential for CONNECT_URL.

The whole concept is required in my project to copy data between databases
where a DB-Link doesn't work due to LOB limitations on ancient releases.
The sqlcl.sh is called as DBMS_SCHEDULER PROGRAM - it works without any
issue (from sqlcl side)

hth,
 Martin


Am Do., 23. Sept. 2021 um 17:21 Uhr schrieb Charlotte Hammond <
dmarc-noreply@xxxxxxxxxxxxx>:

Hi Angelo,

I believe the message "A missing FROM or TO clause uses the current
SQL*Plus connection" means that TO is *not* mandatory (provided there is a
FROM). (Interesting it mentions "SQL*Plus" even though this is SQLcl -
presume ported code?)

Anyway, just to be sure, I tried it specifying both FROM and TO but
unfortunately the error message was the same as before.

Thanks
Charlotte


On Thursday, September 23, 2021, 04:08:51 PM GMT+1, angelo <
angelolistas@xxxxxxxxx> wrote:


Hi,
me again

Charlotte

Look at this example below as a complementation   (I made a mistake with
the syntax in my before e-mail)

SQL> copy from
 
/@(DESCRIPTION=(LOAD_BALANCE=OFF)(FAILOVER=ON)(CONNECT_TIMEOUT=10)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=source_host1))(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=source_host2)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=source_host3)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SOURCEDB)))
*TO* @DESTDB create test_table *USING* select * from dual;


On Thu, 23 Sept 2021 at 11:54, angelo <angelolistas@xxxxxxxxx> wrote:

Hello,

@Charlotte

Could the TO parameter be missing ?  Let´s analyze the error message and
the syntax from the command.

"A missing FROM or TO clause uses the current SQL*Plus connection"

I think TO is mandatory, even if using a create table.

"usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel>"

again, it could be:  COPY FROM @DB1  *TO*   @db2  using  create test_table 
select * from dual;

In all your examples, the TO parameter is not there.

" SQL> copy from  
/@(DESCRIPTION=(LOAD_BALANCE=OFF)(FAILOVER=ON)(CONNECT_TIMEOUT=10)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=source_host1))(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=source_host2)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=source_host3)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SOURCEDB)))
  create test_table using select * from dual;  "

Maybe you can try it, obeying the syntax   from "usage: COPY FROM <db> TO 
<db> <opt> <table> { (<cols>) } USING <sel>"

SQL> copy from  
/@(DESCRIPTION=(LOAD_BALANCE=OFF)(FAILOVER=ON)(CONNECT_TIMEOUT=10)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=source_host1))(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=source_host2)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=source_host3)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SOURCEDB)))
  *TO* @DESTDB (or @description whatever)   *USING *create test_table using 
select * from dual;


So, you can try it.

regards,

angelo



On Thu, 23 Sept 2021 at 06:00, Charlotte Hammond <
dmarc-noreply@xxxxxxxxxxxxx> wrote:

Thanks Jeff.

I've tried both without success.   Here's the commands and output I've
tried (same result both with and without the -oci flag):

SQL> connect /@SOURCEDB   <-- Checking passwordless connection with wallet to 
source
Connected.
SQL> connect /@DESTDB     <-- Passwordless connection with wallet to dest
Connected.
SQL> copy from /@SOURCEDB create test_table using select * from dual;

usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel>
<db>   : database string, e.g., hr/your_password@d:chicago-mktg
<opt>  : ONE of the keywords: APPEND, CREATE, INSERT, REPLACE,
APPEND_BYTE,  CREATE_BYTE or REPLACE_BYTE
<table>: name of the destination table
<cols> : a comma-separated list of destination column aliases
<sel>  : any valid SQL SELECT statement
A missing FROM or TO clause uses the current SQL*Plus connection.


or

SQ>  connect 
/@(DESCRIPTION=(LOAD_BALANCE=OFF)(FAILOVER=ON)(CONNECT_TIMEOUT=10)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=source_host1)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=(HOST=source_host2)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=(HOST=source_host3)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SOURCEDB)))
Connected.
SQL> connect 
/@(DESCRIPTION=(LOAD_BALANCE=OFF)(FAILOVER=ON)(CONNECT_TIMEOUT=10)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=dest_host1)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=dest_host2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=DESTDB)))
Connected.
SQL> copy from  
/@(DESCRIPTION=(LOAD_BALANCE=OFF)(FAILOVER=ON)(CONNECT_TIMEOUT=10)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=source_host1))(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=source_host2)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=source_host3)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SOURCEDB)))
 create test_table using select * from dual;
usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel>
<db>   : database string, e.g., hr/your_password@d:chicago-mktg
<opt>  : ONE of the keywords: APPEND, CREATE, INSERT, REPLACE,
APPEND_BYTE,  CREATE_BYTE or REPLACE_BYTE
<table>: name of the destination table
<cols> : a comma-separated list of destination column aliases
<sel>  : any valid SQL SELECT statement
A missing FROM or TO clause uses the current SQL*Plus connection.









Thank You!
Charlotte




On Wednesday, September 22, 2021, 11:30:25 PM GMT+1, Jeff Smith <
jeff.d.smith@xxxxxxxxxx> wrote:


I’m honestly not sure..in your copy command are you using a fully
qualified jdbc URL that contains all the necessary wallet info, or are you
relying on an OCI (thick) connection for an $ORACLE_HOME that already has
TLS configured?

Jeff



*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> *On
Behalf Of *Charlotte Hammond
*Sent:* Wednesday, September 22, 2021 6:12 PM
*To:* oracle-l@xxxxxxxxxxxxx
*Subject:* [External] : sqlcl Copy



Hello Everyone,



Just a quick question - is the Oracle wallet supported by the COPY command
in SQLcl?   I can't get it to work but I'm not sure if it's not supported
or if I'm just doing something wrong.



Thank You!

Charlotte



-- 
Martin Berger                Oracle ♠
martin.a.berger@xxxxxxxxx @martinberx <https://twitter.com/martinberx>
^∆x      http://berxblog.blogspot.com

Other related posts: