英文:
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 onSET LINESIZE 10000declarel_some_text varchar2(1000);beginselect 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 workssqlplus !dbuser! @!some_dir!\some_sql_file.sql test_text >> !log!REM this STOPS and waits until user interaction happenssqlplus !dbuser! @!some_dir!\some_sql_file.sql >> !log!
SQL Code:
set serveroutput onSET LINESIZE 10000declarel_some_text varchar2(1000);beginselect 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 onset ver offset termout offcolumn 1 new_value 1select null as "1" from dual where 1 = 2;set termout ondeclarel_some_text varchar2(1000);beginselect 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 littlefootSQL uses: littlefootPL/SQL procedure successfully completed.
不带参数：
c:\temp>sqlplus -s scott/tiger@pdb1 @a.sqlSQL uses: no_text_givenPL/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 onset ver offset termout offcolumn 1 new_value 1select null as "1" from dual where 1 = 2;set termout ondeclarel_some_text varchar2(1000);beginselect 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 littlefootSQL uses: littlefootPL/SQL procedure successfully completed.
Without parameter:
c:\temp>sqlplus -s scott/tiger@pdb1 @a.sqlSQL uses: no_text_givenPL/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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论