Re: [External] : sqlcl Copy

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/_at_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:/_at_' ||CONNECT_URL || '?TNS_ADMIN=/home/oracle/project/tns'

*Script*

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

*Parameter*

     jdbc:oracle:thin:/_at_' || 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_at_freelists.org>:

> 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_at_gmail.com> 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
>  /_at_(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* _at_DESTDB create test_table *USING* select * from dual;
>
>
> On Thu, 23 Sept 2021 at 11:54, angelo <angelolistas_at_gmail.com> wrote:
>
> Hello,
>
> _at_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 _at_DB1  *TO*   _at_db2  using  create test_table select * from dual;
>
> In all your examples, the TO parameter is not there.
>
> " SQL> copy from  /_at_(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  /_at_(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* _at_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_at_freelists.org> 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 /_at_SOURCEDB   <-- Checking passwordless connection with wallet to source
> Connected.
> SQL> connect /_at_DESTDB     <-- Passwordless connection with wallet to dest
> Connected.
> SQL> copy from /_at_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_at_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 /_at_(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 /_at_(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  /_at_(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_at_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_at_oracle.com> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *Charlotte Hammond
> *Sent:* Wednesday, September 22, 2021 6:12 PM
> *To:* oracle-l_at_freelists.org
> *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_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
^∆x      http://berxblog.blogspot.com

--
http://www.freelists.org/webpage/oracle-l