英文:
SQLPLUS use optional parameters
问题
CMD代码：
REM 这个部分有效
sqlplus !dbuser! @!some_dir!\some_sql_file.sql test_text >> !log!
REM 这个部分会停止并等待用户交互
sqlplus !dbuser! @!some_dir!\some_sql_file.sql >> !log!
SQL代码：
set serveroutput on
SET LINESIZE 10000
declare
l_some_text varchar2(1000);
begin
select nvl('&3','no_text_given') into l_some_text from dual;
dbms_output.enable;
dbms_lock.sleep(1);
dbms_output.put_line('SQL uses: ' || l_some_text);
end;
英文:
Currently, I call an SQL File from a CMD file and transfer some parameters during the call. The code below works if I actually pass a value or press enter. However, my CMD/SQL will stop and wait for the parameter if there was none given. In such a case I would like to continue with default values instead.
CMD Code:
REM this works
sqlplus !dbuser! @!some_dir!\some_sql_file.sql test_text >> !log!
REM this STOPS and waits until user interaction happens
sqlplus !dbuser! @!some_dir!\some_sql_file.sql >> !log!
SQL Code:
set serveroutput on
SET LINESIZE 10000
declare
l_some_text varchar2(1000);
begin
select nvl('&&3','no_text_given') into l_some_text from dual;
dbms_output.enable;
dbms_lock.sleep(1);
dbms_output.put_line('SQL uses: ' || l_some_text );
end ;
答案1
得分: 2
参数不能为3；只有一个参数，所以应该为1。
以下是您可能感兴趣的选项。
a.sql 文件：
set serveroutput on
set ver off
set termout off
column 1 new_value 1
select null as "1" from dual where 1 = 2;
set termout on
declare
l_some_text varchar2(1000);
begin
select nvl('&1', 'no_text_given') into l_some_text from dual;
dbms_output.put_line('SQL uses: ' || l_some_text );
end ;
/
exit;
让我们进行测试：传递参数：
c:\temp>sqlplus -s scott/tiger@pdb1 @a.sql littlefoot
SQL uses: littlefoot
PL/SQL procedure successfully completed.
不带参数：
c:\temp>sqlplus -s scott/tiger@pdb1 @a.sql
SQL uses: no_text_given
PL/SQL procedure successfully completed.
c:\temp>
如果需要，请进行调整（例如，重定向到文件；删除我使用的各种set设置等等），但总的来说，这就是您要求的内容。
英文:
Parameter can't be 3; there's only one, so it is supposed to be 1.
Here's option you might be interested in.
a.sql file:
set serveroutput on
set ver off
set termout off
column 1 new_value 1
select null as "1" from dual where 1 = 2;
set termout on
declare
l_some_text varchar2(1000);
begin
select nvl('&1', 'no_text_given') into l_some_text from dual;
dbms_output.put_line('SQL uses: ' || l_some_text );
end ;
/
exit;
Let's test it: with parameter passed to it:
c:\temp>sqlplus -s scott/tiger@pdb1 @a.sql littlefoot
SQL uses: littlefoot
PL/SQL procedure successfully completed.
Without parameter:
c:\temp>sqlplus -s scott/tiger@pdb1 @a.sql
SQL uses: no_text_given
PL/SQL procedure successfully completed.
c:\temp>
Adjust it, if you have to (e.g. redirect to file; remove various settings I used, ...) but - generally - that's what you asked for.
答案2
得分: 0
在 cmd 中，
如果未定义参数，则设置 "parameter=defaultvalue"
sqlplus !dbuser! @!some_dir!\some_sql_file.sql !parameter! >> !log!
如果 parameter 未设置，将使用默认值。您尚未向我们展示如何将 test_text 提供给 cmd 过程；您需要将其值分配给 parameter。
英文:
In cmd,
if not defined parameter set "parameter=defaultvalue"
sqlplus !dbuser! @!some_dir!\some_sql_file.sql !parameter! >> !log!
if parameter has not been set, uses default. You haven't shown us how you provide test_text to the cmd procedure; you would need to assign its value to parameter
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论