CODEWITHSUNDEEP
sqloracle-databasestored-procedures
user3117500
user3117500

Reputation: 29

Oracle Stored Procedure inout parameter issue - Got the solution - Typo error

I have successfully created the below stored procedure:

CREATE OR REPLACE PROCEDURE FIRSTPROC (ID1 IN VARHAR2, ID2 IN NUMBER )
AS 
CURSOR FIRST_CUR AS
 SELECT id2val
 FROM 
 WHERE id1val = ID1;

BEGIN
  DBMS_OUTPUT.PUT_LINE(ID1);
  OPEN FIRST_CUR;
  FETCH FIRST_CUR INTO ID2;
  IF FIRST_CUR%NOTFOUND THEN
     ID2 := 0;   
  END IF;

END

While executing, I am passing value as 23 in ID1 variable, and the value is displayed in the DBMS_OUTPUT.PUT_LINE statement. And the value is present in the table, but I am not getting the result

But if I hardcode the 23 in the stored procedure I am getting the value. Got confused. Something I am missing above.

CREATE OR REPLACE PROCEDURE FIRSTPROC (ID1 IN VARHAR2, ID2 IN NUMBER )
AS 
CURSOR FIRST_CUR AS
 SELECT id2val
 FROM 
 WHERE id1val = 23;

BEGIN
  DBMS_OUTPUT.PUT_LINE(ID1);
  OPEN FIRST_CUR;
  FETCH FIRST_CUR INTO ID2;
  IF FIRST_CUR%NOTFOUND THEN
     ID2 := 0;   
  END IF;

END

Upvotes: 0

Views: 107

Answers (2)

MT0
MT0

Reputation: 167774

Some issues:

  1. VARHAR2 us a typo and should be VARCHAR2.
    • You could instead specify the datatypes to reference the corresponding table columns using table_name.column_name%TYPE.
  2. The procedure does not output anything - you need to change one of the IN parameters to an OUT (or an IN OUT parameter).
  3. Your query does not specify a table name.
  4. You can use SELECT ... INTO ... rather than a CURSOR.

Something like this:

CREATE OR REPLACE PROCEDURE FIRSTPROC (
  ID1 IN  table_name.id1val%TYPE,
  ID2 OUT table_name.id2val%TYPE
)
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE(ID1);
  SELECT id2val
  INTO   ID2
  FROM   table_name
  WHERE  id1val = ID1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    ID2 := 0; -- Or you could use NULL
  WHEN TOO_MANY_ROWS THEN
    ID2 := 0; -- Or you could use NULL
END;
/

Upvotes: 1

OldProgrammer
OldProgrammer

Reputation: 12159

First, you have declared ID2 as an IN parameter, so the result is not getting returned. You need to declare ID2 as an IN OUT or an OUT parameter. (BTW, your code supplied won't even compile, as "VARHAR2" is a typo). The second procedure does the same, so I don't see how that is possible, so you must have something else going on.

Upvotes: 0

Related Questions