在 sqlplus 输入文件中参数化表名 [英] Parameterizing table name in sqlplus input file

问题描述

我正在尝试使用 sqlplus 和 Oracle 假脱机功能导出一些数据.问题是我定义导出的 SQL 输入文件不允许我参数化我从中导出数据的表名——它需要一个文字:(

I am trying to export some data using sqlplus and the Oracle spool functionality. The problem is that the SQL input file where I am defining my export is not letting me parameterize the table name from which I am exporting data -- it wants a literal :(

在shell脚本中调用sqlplus:

Calling sqlplus in a shell script:

sqlplus $USER/$PASSWD@$ORADB<<!

@export.sql $OUT_FILE $SOME_VAR $ENV

exit
!

export.sql:

export.sql:

set heading off
set head off
set term off
set tab off
set embedded on
set feedback off
set pagesize 0
set linesize 800
set trimspool on
set verify off

spool &1

SELECT '&2|' || some_col
FROM &3_TABLE
/
spool off

当 $ENV 设置为 'dev' 时，我得到

When $ENV is set to 'dev', I get

Enter value for 3_TABLE 

而我希望它使用 dev_TABLE.当我取消参数化 sql 文件中的表名时，输出运行良好.还要注意，有参数 &2，它是来自 shell 的 $SOME_VAR，它被很好地显示.唯一的问题是在 FROM 语句中.

whereas I want it to use dev_TABLE. When I unparameterize the table names in the sql file, the output runs fine. Also note that there is param &2, which is $SOME_VAR from the shell and it gets displayed evaluated fine. The only problem is in the FROM statement.

有没有什么办法告诉sql输入文件在运行SQL之前替换参数化的表名?

Is there any way to tell the sql input file to replace the parameterized table names before running SQL?

谢谢

推荐答案

问题是 SQL*Plus 处理 & 之后的整个字符串，直到下一个空格或类似的，作为替换变量名.显然，这不是您想要的.

The problem is that SQL*Plus is treating the whole string after the &, up to the next whitespace or simlar, as the substitution variable name. Clearly that isn't what you want here.

幸运的是他们已经想到了这一点，您可以用 表示变量名称的结尾.:

Fortunately they've thought of this, and you can denote the end of the variable name with a .:

FROM &3._TABLE

(至少，这适用于命名变量，我几乎可以肯定它适用于位置变量......如果不是，那么你需要定义一个新变量设置为 &3 作为一种解决方法).

(At least, that works for named variables, and I'm almost sure it will for positional ones... if not then you'd need to define a new variable set to &3 as a workaround).

它在文档中，但是眨眼你就会错过它:

It is in the documentation, but blink and you'll miss it:

如果您希望在替换后立即附加字符变量，使用句点将变量与字符分开.

If you wish to append characters immediately after a substitution variable, use a period to separate the variable from the character.

<小时>

您可能希望在未来牢记一个相关的影响.如果替换变量之后的下一个字符是 . 无论如何 - 例如在模式和表之间，或表和列之间 - 那么这将被解释为替换终止符.假设您将架构作为 &4 单独传递，值为 'scott';这个:

---

There's a related effect that you may want to bear in mind for the future. If the next character after the substitution variable is a . anyway - between the schema and table, or between table and column, for example - then that will be interpreted as the substitution terminator. Say you were passing the schema separately as &4, with value 'scott'; this:

FROM &4.&3._TABLE

看起来很合理，但会被替换为 scottdev_TABLE，它不会被识别.所以在那种情况下，你需要一个额外的:

looks reasonable but would be substituted as scottdev_TABLE, which won't be recognised. So in that instance you need to have an extra one:

FROM &4..&3._TABLE

将被替换为 scott.dev_TABLE.

这篇关于在 sqlplus 输入文件中参数化表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！