Oracle PL/SQL - 如何转义冒号 (:)，被误解为绑定变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7342642/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle PL/SQL - How to escape colon (:), being misinterpreted for bind variable
提问by victorhooi
I have a small PL/SQL script that I'm using to try and copy data between two Oracle database instances.
我有一个小的 PL/SQL 脚本，我用它来尝试在两个 Oracle 数据库实例之间复制数据。
I'm calling the SQL script with (sanitised):
我正在使用（已清理）调用 SQL 脚本：
sqlplus username/[email protected]:1434/SERVICENAME @copyTables.sql source_username source_password source_connstring destination_username destination_password destination_connstring
The copyTables.sql script:
copyTables.sql 脚本：
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.
另外 - 作为附录 - 我在上面所做的方式，这是将命令行参数传递给 PL/SQL 脚本的最佳实践吗？我愿意接受关于更好的方法的建议。
Cheers, Victor
干杯，维克多
采纳答案by Alex Poole
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 LIKEdoes, and if it did you'd have to modify your inputs before you called the script which wouldn't be ideal.
我不相信 PL/SQL 变量赋值支持从某种意义上说的转义LIKE，如果确实如此，您必须在调用脚本之前修改您的输入，这并不理想。
稍微远离你原来的问题......
You'll also need to use some form of dynamic SQL to take action based on the passed parameters and cursor values; and COPYis 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 spooland dbms_output, which you then execute after the block completes. Something like:
您还需要使用某种形式的动态 SQL 来根据传递的参数和游标值采取行动；andCOPY是一个 SQL*Plus 命令，所以无论如何你都不能从 PL/SQL 调用它。我建议您使用 PL/SQL 块生成一个单独的 SQL 脚本，其中包含所有命令，通过spool和dbms_output，然后在块完成后执行。就像是：
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:
您甚至不需要为此使用 PL/SQL，除非您想使用动态 SQL 和EXECUTE IMMEDIATE. 这将与前面的示例执行相同的操作：
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;
回答by Rob van Wijk
to try and copy data between two Oracle database instances.
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.
您将 SQL*Plus 命令与 PL/SQL 混为一谈。但是，没有必要为此自己编写代码。您可以使用Oracle 数据泵导出和导入。将它与 CONTENT=DATA_ONLY 选项一起使用以模拟 SQL*Plus 的 COPY 命令。
Regards,
Rob.
问候，
罗伯。
