How to call a procedure in a package which has an OUT value?

96 Views Asked by At

I have a sql procedure in a package which has a OUT parameter. Package body is given bellow

create or replace
PACKAGE BODY TEST_PACKAGE AS  -- body
   Procedure            TEST_PROCEDURE (
       data_1       IN        VARCHAR2
,      data_2       IN        VARCHAR2
,      data_3       IN        DATE
,      out_data     OUT       VARCHAR2 )
IS
   BEGIN
       INSERT INTO TEST_TABLE(
  COLUMN_1,
  COLUMN_2,
  COLUMN_3) VALUES(
  data_1,
  data_2,
  data_3
  );
  out_data:='SUCCESS';
End TEST_PROCEDURE;
END TEST_PACKAGE;

I tried to invoke this procedure with the bellow SQL command.

DECLARE
sample_out_data VARCHAR2(100);
Begin
TEST_PACKAGE.TEST_PROCEDURE ('test_data_1','test_data_2',2012-02-23,sample_out_data);
END;

But it gives me errors. Can anyone please tell me how to call this procedure?

The error is given bellow

Error starting at line 13 in command:
Declare
sample_out_data VARCHAR2(100);
Begin
TEST_PACKAGE.TEST_PROCEDURE ('test_1','test_2',2012-02-23,sample_out_data);
End;
Error report:
ORA-06550: line 4, column 1:
PLS-00306: wrong number or types of arguments in call to 'TEST_PROCEDURE'
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
1

There are 1 best solutions below

0
On
create or replace
   Procedure            TEST_PROCEDURE (
       id           IN        number
,      price        IN        long
,      date_req     IN      timestamp
,      out_status   OUT     varchar2
 )
IS
   BEGIN
       INSERT INTO orders(
  id,
  price,
  date_req) VALUES(
  id,
  price,
  date_req  );
out_status:='SUCESS';
 End TEST_PROCEDURE;

OUTPUT

DECLARE
SUCCESS VARCHAR2(100);
begin
TEST_PROCEDURE(10,1456,'11-NOV-13 12.42.53.658439 PM',SUCCESS);
DBMS_OUTPUT.PUT_LINE('THE STATUS IS'||' '||SUCCESS);
end;