PLS-00306 .Getting Problems with OUT parameter

[nabh4u]

hi,

I am getting an error when using OUT parameter.

Error:
PLS-00306: wrong number or types of arguments in call to 'proc_name'

Where proc_name is the name of my procedure.

Following is the code i am using:

CREATE OR REPLACE PROCEDURE proc_name (val OUT NUMBER) AS

CURSOR C  IS
    SELECT C_NO, C_INFO FROM C_TABLE;

BEGIN
 FOR RS IN C
  LOOP
   BEGIN
     val := val + 1;
   END;
  END LOOP;
END proc_name;

i want the procedure to return the value of val when it executes. I dont understand why does it give me the error. I have tried different ways and went through different manuals, but didnt get it resolved.

Please help me with the error.

Thanks in advance,
nabh4u.

[amitpatel66]

hi,

I am getting an error when using OUT parameter.

Error:
PLS-00306: wrong number or types of arguments in call to 'proc_name'

Where proc_name is the name of my procedure.

Following is the code i am using:

CREATE OR REPLACE PROCEDURE proc_name (val OUT NUMBER) AS

CURSOR C  IS
    SELECT C_NO, C_INFO FROM C_TABLE;

BEGIN
 FOR RS IN C
  LOOP
   BEGIN
     val := val + 1;
   END;
  END LOOP;
END proc_name;

i want the procedure to return the value of val when it executes. I dont understand why does it give me the error. I have tried different ways and went through different manuals, but didnt get it resolved.

Please help me with the error.

Thanks in advance,
nabh4u.

Could you please post the code that you used to call this procedure??

[nabh4u]

Could you please post the code that you used to call this procedure??

Thanks for replying Amit. I am not yet calling this procedure. I just wrote the code for displaying something on the screen if it executes fine. Is it like to use an OUT parameter we should have a procedure calling another procedure?

Well i am not sure about it. Can you tell me how to use the OUT parameter?

thanks,
nabh4u.

[amitpatel66]

Thanks for replying Amit. I am not yet calling this procedure. I just wrote the code for displaying something on the screen if it executes fine. Is it like to use an OUT parameter we should have a procedure calling another procedure?

Well i am not sure about it. Can you tell me how to use the OUT parameter?

thanks,
nabh4u.

Not exactly. You need not require another procedure to call one.

You can use anonymous block to give a call to the procedure. Something like this:

[code=oracle]

CREATE OR REPLACE PROCEDURE add_num(a IN NUMBER, b OUT NUMBER) AS
BEGIN
b:= a + 20;
END;
/

-- code to call a procedure add_num:

declare
num1 number := 20;
num2 number;
BEGIN
add_num(num1,nu m2);
DBMS_OUTPUT.PUT _LINE('Out parameter value after calling procedure is:'||num2);
END;

[/code]

[nabh4u]

Not exactly. You need not require another procedure to call one.

You can use anonymous block to give a call to the procedure. Something like this:

[code=oracle]

CREATE OR REPLACE PROCEDURE add_num(a IN NUMBER, b OUT NUMBER) AS
BEGIN
b:= a + 20;
END;
/

-- code to call a procedure add_num:

declare
num1 number := 20;
num2 number;
BEGIN
add_num(num1,nu m2);
DBMS_OUTPUT.PUT _LINE('Out parameter value after calling procedure is:'||num2);
END;

[/code]

Thanks Amit. This example might help.

nabh4u.

[amitpatel66]

Thanks Amit. This example might help.

nabh4u.

You are Welcome :) .

[subashsavji]

hi,

I am getting an error when using OUT parameter.

Error:
PLS-00306: wrong number or types of arguments in call to 'proc_name'

Where proc_name is the name of my procedure.

Following is the code i am using:

CREATE OR REPLACE PROCEDURE proc_name (val OUT NUMBER) AS

CURSOR C  IS
    SELECT C_NO, C_INFO FROM C_TABLE;

BEGIN
 FOR RS IN C
  LOOP
   BEGIN
     val := val + 1;
   END;
  END LOOP;
END proc_name;

i want the procedure to return the value of val when it executes. I dont understand why does it give me the error. I have tried different ways and went through different manuals, but didnt get it resolved.

Please help me with the error.

Thanks in advance,
nabh4u.

//

this example might be helpful to you

CREATE OR REPLACE PROCEDURE query_emp
(p_id IN emp.empNO%TYPE,
p_name OUT emp.Ename%TYPE,
p_salary OUT emp.sal%TYPE,
p_comm OUT emp.comm%TYPE)
IS
BEGIN
SELECT Ename, sal, comm
INTO p_name, p_salary, p_comm
FROM emp
WHERE empNO = p_id;
DBMS_OUTPUT.PUT _LINE(P_ID);
DBMS_OUTPUT.PUT _LINE(P_COMM);
END query_emp;
/

DECLARE
A VARCHAR2(30);
B NUMBER;
C NUMBER;
BEGIN
query_emp(7369, A,B,C);
END;
/

DECLARE
A VARCHAR2(30);
B NUMBER;
C NUMBER;
BEGIN
query_emp(7369, A,B,C);
DBMS_OUTPUT.PUT _LINE(A||B||C);
END;
/


1 DECLARE
2 A VARCHAR2(30);
3 B NUMBER;
4 C NUMBER;
5 BEGIN
6 query_emp(7369, A,B,C);
7 DBMS_OUTPUT.PUT _LINE(P_ID||A|| B||C);
8* END;
9 /
DBMS_OUTPUT.PUT _LINE(P_ID||A|| B||C);
*
ERROR at line 7:
ORA-06550: line 7, column 22:
PLS-00201: identifier 'P_ID' must be declared
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored
//