Thanks Martin.
I've finally got COPY to work. I was missing the ojdbc.properties file to set
the oracle.net.wallet_location parameter. Once in place COPY started working.
(Because all the other commands seemed to be working with the wallet without
this file I hadn't appreciated there was anything wrong with the setup).
I did try BRIDGE as well as you suggested but was getting a Invalid Syntax
error which I couldn't get to the bottom of, and couldn't afford any more time
on. However, since COPY meets my needs I'll just go with that for now.
Thanks everyone for your input.
Charlotte
On Friday, September 24, 2021, 06:49:17 AM GMT+1, Martin Berger
<martin.a.berger@xxxxxxxxx> wrote:
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.
ThanksCharlotte
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
^∆x http://berxblog.blogspot.com ;
