Oracle PL/SQL-如何对冒号(:)进行转义，将其误解为绑定变量

本文介绍了Oracle PL/SQL-如何对冒号(:)进行转义，将其误解为绑定变量的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个小的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/[email protected]: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-如何对冒号(:)进行转义，将其误解为绑定变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！