Oracle PL/SQL-如何对冒号(:)进行转义，将其误解为绑定变量 [英] Oracle PL/SQL - How to escape colon (:), being misinterpreted for bind variable

问题描述

我有一个小的PL/SQL脚本，可用于尝试在两个Oracle数据库实例之间复制数据.

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

我用(已清除)调用SQL脚本:

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

sqlplus username/[e-mail]:1434/SERVICENAME @copyTables.sql source_username source_password source_connstring destination_username destination_password destination_connstring

copyTables.sql脚本:

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.

此外-作为附录-我在上面的操作方式，这是将命令行参数传递给PL/SQL脚本的最佳实践吗?我愿意就更好的方法提出建议.

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.

干杯， 维克多

推荐答案

在分配位置变量时，您需要在位置变量两边加上引号，以便在此时将整个值解释为字符串:

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';

我不相信PL/SQL变量赋值在LIKE的意义上支持转义，如果这样做，则您必须在调用脚本之前修改输入，这并不理想.

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...

您还需要使用某种形式的动态SQL来根据传递的参数和游标值执行操作；并且COPY是SQL * Plus命令，因此无论如何您不能从PL/SQL调用它.我建议您使用PL/SQL块通过spool和dbms_output生成包含所有命令的单独的SQL脚本，然后在块完成后执行该脚本.像这样:

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...

您甚至不需要为此使用PL/SQL，除非您想使用动态SQL和EXECUTE IMMEDIATE.这将与前面的示例相同:

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;

这篇关于Oracle PL/SQL-如何对冒号(:)进行转义，将其误解为绑定变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！