Oracle PL/SQL - How to escape colon (:), being misinterpreted for bind variable

Question

I have a small PL/SQL script that I'm using to try and copy data between two Oracle database instances.

I'm calling the SQL script with (sanitised):

sqlplus username/password@server.com:1434/SERVICENAME @copyTables.sql source_username source_password source_connstring destination_username destination_password destination_connstring

The copyTables.sql script:

SET SERVEROUTPUT ON;
DECLARE
  source_username VARCHAR2(20) := &1
  source_password VARCHAR2(20) := &2
  source_connstring VARCHAR2(2) := &3
  destination_username VARCHAR2(20) := &4
  destination_password VARCHAR2(20) := &5
  destination_connstring VARCHAR(20) := &6
  CURSOR user_table_cur IS
  SELECT table_name
  FROM user_tables
  ORDER BY table_name DESC;

BEGIN
  FOR user_table IN user_table_cur LOOP
    dbms_output.put_line(source_username);
    dbms_output.put_line(user_table.table_name);
    COPY FROM {source_username}/{source_password}@{source_connstring} TO {destination_username}/{destination_password}@{destination_connstring} APPEND user_table.table_name user_table.table_name USING SELECT* FROM user_table.table_name;
  END LOOP;
END;

The only issue is that when I run this, it seems to misinterpret a colon (:) in the connection string for something to do with bind variables:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

old   2:   source_username VARCHAR2(20) := &1
new   2:   source_username VARCHAR2(20) := SANITISED
old   3:   source_password VARCHAR2(20) := &2
new   3:   source_password VARCHAR2(20) := SANITISED
old   4:   source_connstring VARCHAR2(2) := &3
new   4:   source_connstring VARCHAR2(2) := server.com:3630/SANITISED
old   5:   destination_username VARCHAR2(20) := &4
new   5:   destination_username VARCHAR2(20) := SANITISED
old   6:   destination_password VARCHAR2(20) := &5
new   6:   destination_password VARCHAR2(20) := SANITISED
old   7:   destination_connstring VARCHAR(20) := &6
new   7:   destination_connstring VARCHAR(20) := server.com:3630/SANITISED
SP2-0552: Bind variable "3630" not declared.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

I've already escape the above with braces ({}), but it still seems to complain about bind variables.

Also - as a addendum - the way I'm doing above, is this the best practice in passing command-line arguments through to a PL/SQL script? I'm open to suggestions on better methods of doing this.

Cheers, Victor

Alex Poole · Accepted Answer · 2011-09-09T09:22:35.857

You need to put quotes around the positional variable when you assign it, so the whole value is interpreted as a string at that point:

destination_connstring VARCHAR(20) := '&6';

I don't believe PL/SQL variable assignment supports escaping in the sense that LIKE does, and if it did you'd have to modify your inputs before you called the script which wouldn't be ideal.

Moving away from your original question a bit...

You'll also need to use some form of dynamic SQL to take action based on the passed parameters and cursor values; and COPY is an SQL*Plus command so you can't call it from PL/SQL anyway. I'd suggest you use the PL/SQL block to generate a separate SQL script containing all the commands, via spool and dbms_output, which you then execute after the block completes. Something like:

SET SERVEROUTPUT ON SIZE 100000 FORMAT WRAPPED;
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
SET LINES 1024

SPOOL tmp_copy_commands.sql
SET TERMOUT OFF
SET FEEDBACK OFF

DECLARE
    src_username VARCHAR2(20) := '&1';
    src_password VARCHAR2(20) := '&2';
    src_connstring VARCHAR2(40) := '&3';
    dest_username VARCHAR2(20) := '&4';
    dest_password VARCHAR2(20) := '&5';
    dest_connstring VARCHAR(40) := '&6';

    CURSOR user_table_cur IS
        SELECT table_name
        FROM user_tables
        ORDER BY table_name DESC;

BEGIN
    FOR user_table IN user_table_cur LOOP
        dbms_output.put_line('COPY FROM '
            || src_username ||'/'|| src_password ||'@'|| src_connstring
            || ' TO '
            || dest_username ||'/'|| dest_password ||'@'|| dest_connstring
            || ' APPEND ' || user_table.table_name
            || ' USING SELECT * FROM '
            || user_table.table_name ||';');
    END LOOP;
END;
/

SPOOL OFF
SET TERMOUT ON
SET FEEDBACK ON

@tmp_copy_commands

EXIT 0;

Moving even further away from your original question...

You don't even need to use PL/SQL for this, unless you want to use dynamic SQL and EXECUTE IMMEDIATE. This will do the same as the earlier example:

SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
SET LINES 1024
SET PAGES 0
SET HEAD OFF

SPOOL tmp_copy_commands.sql
SET TERMOUT OFF
SET FEEDBACK OFF

SELECT 'COPY FROM &1./&2.@&3. TO &4./&5.@&6. APPEND '
    || table_name || ' USING SELECT * FROM ' || table_name || ';'
FROM user_tables
ORDER BY table_name DESC;

SPOOL OFF
SET TERMOUT ON
SET FEEDBACK ON

@tmp_copy_commands

exit 0;

heya, Hmm, I'm getting this error ORA-06502: PL/SQL: numeric or value error: character string buffer too small — victorhooi, Sep 09 '11 at 05:40
@victorhooi - then increase the size of the `VARCHAR`s to match your expected data, preferably as big as the largest possible value. I already bumped 3 and 6 up to handle your sanitised data. Alternatively, you could use the positional variables directly in the `dbms_outout` call, and don't bother with `src_username` etc. at all. — Alex Poole, Sep 09 '11 at 06:35
@victorhooi - added a version that doesn't use PL/SQL, and also shows the position paramaters being used directly. — Alex Poole, Sep 09 '11 at 09:24

score 1 · Answer 2 · answered Sep 09 '11 at 09:32

to try and copy data between two Oracle database instances.

You're mixing up SQL*Plus commands with PL/SQL. But, there is no need to write code yourself for that. You can use Oracle Data Pump Export and Import. Use it with the CONTENT=DATA_ONLY option to mimic SQL*Plus' COPY command.

Regards,
Rob.

Oracle PL/SQL - How to escape colon (:), being misinterpreted for bind variable

2 Answers2

Linked