sql*plus oracle db-executing procedure with boolean output参数

xytpbqjk  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(337)

我试图执行一个存储过程,其中包含一个输入(varchar)和一个输出(boolean)参数,来自sql*plus。
我所拥有的:

ALTER session SET nls_language='AMERICAN';
set serveroutput on;
declare bResult boolean;
exec procedureName('TEST', bResult);
/
exit;

我得到的是:

PLS-00103: Encountered the symbol 'end-of-file' when expecting one of the following:
begin function pragma procedure subtype type <an identifier>

我正在从一个批处理脚本调用sqlplus,我需要调用这个过程,并根据该过程的结果继续执行批处理脚本(bresult)。
在sql developer中,我可以成功地执行过程并使用以下命令返回输出(在sql
plus中不起作用):

ALTER session SET nls_language='AMERICAN';
set serveroutput on;
declare bResult boolean;
begin
procedureName('TEST', bResult);
dbms_output.put_line(sys.diutil.bool_to_int(bResult));
end;

我能做些什么使它与sql*plus一起工作?

yjghlzjz

yjghlzjz1#

以下在sql*plus中使用(不要忘记结尾 / ):

$ sqlplus system/oracle @tproc

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 18 16:31:55 2020
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Thu Jun 18 2020 16:31:27 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

Session altered.

SQL> --
SQL> create or replace procedure procedurename(p1 in varchar2, p2 out boolean)
  2  as
  3  begin
  4   p2 := true;
  5  end;
  6  /

Procedure created.

SQL> show errors
No errors.
SQL> 
SQL> ALTER session SET nls_language='AMERICAN';

Session altered.

SQL> set serveroutput on;
SQL> declare bResult boolean;
  2  begin
  3  procedureName('TEST', bResult);
  4  dbms_output.put_line(sys.diutil.bool_to_int(bResult));
  5  end;
  6  /
1

PL/SQL procedure successfully completed.

SQL>
hlswsv35

hlswsv352#

实际上,您是使用 declare ,您需要使用匿名块,如下所示:

SQL>
SQL> ALTER session SET nls_language='AMERICAN';

Session altered.

    SQL> set serveroutput on;
    SQL> declare bResult boolean;
      2  begin -- this is needed
      3  proc('TEST', bResult); -- remove exec
      4  end;
      5  /

    PL/SQL procedure successfully completed.

    SQL> exit;
    Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.3.0.0.0

    C:\Users\tejas.hingu>

相关问题