Question

如何在sqlplus中运行PACKAGE.FUNCTION

Answer 1

可以使用select命令执行，注意packge里面不要有关于数据状态的语句，如果所引用的函数是一个独立的PL / SQL函数，请不要使用函数。
执行示例：
定义：
create or replace PACKAGE ztest_package IS
function ztest_f(
staff_no varchar2,
start_date date ,
remarks varchar2
)
return varchar2;
END;
执行：
select ztest_package.ztest_f('1',null,'2') from dual;
或者将上面的语句保存为a.sql
SQL> @ c:\shp\a.sql

Answer 2

ORA-06571 function name does not guarantee not to update
database

Cause There are two possible causes for this message:

?A SQL statement references a packaged PL/SQL function that does not
contain a pragma that prevents the database from being updated.
?A SQL
statement references a stand-alone, PL/SQL function that contains an instruction
to update the database.

Action If the referenced function is a
packaged PL/SQL function: Recreate the PL/SQL function with the required pragma;
be certain to include the "Write No Database State" (WNDS) argument in the
argument list of the pragma.

If the referenced function is a stand-alone
PL/SQL function: Do not use the function.

Answer 3

在sqlplus中运行PACKAGE.FUNCTION,这3种方法应该如何去改.

create or replace PACKAGE ztest_package IS
function ztest_f(
staff_no varchar2,
start_date date ,
remarks varchar2
)
return varchar2;
END;
/

create or replace PACKAGE BODY ztest_package IS
function ztest_f(
staff_no IN varchar2,
start_date IN date ,
remarks IN varchar2
)
return varchar2
AS
rtn_varchar2 varchar2(2);
begin
rtn_varchar2 := '00';
return (rtn_varchar2);
end;
END;
/
方法一:
select ztest_package.ztest_f('1',to_date('20020202','yyyymmdd'),'2') from dual;

方法二:
declare
vv varchar2;
begin
:vv := ztest_package.ztest_f('1',null,'2');
end;
/
方法三:
SET SERVEROUTPUT ON
exec :vv1 :=ztest_package.ztest_f('1',null,'2');