CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqloracle-sqldeveloper
user2133404
user2133404

Reputation: 1857

Variable must be declared error in a stored procedure

I have a package whose body consists of a stored procedure.

The stored procedure makes a call to a stored procedure in another package. However I need to pass the result of a select statement as a parameter. so I stored the result in a variable and passed it as a parameter. However when the procedure call is performed, it says, the variable is not declared.

This is the sample code. 

create or replace 
PACKAGE BODY PKG_BODY

PROCEDURE PROC (error OUT VARCHAR2,
                  RESULT OUT BOOLEAN ) AS

-----------------------------
-----------------------------

 DECLARE
    PARAM TABLE1.COLUMN1%TYPE;
    BEGIN
    SELECT COLUMN1
    INTO PARAM
    FROM TABLE1
    WHERE COLUMN2=ID AND COLUMN3='NAME';
    END;

PUBLISH_MSG.PUBLISH_PMM_MSG('ID',PARAM,PO_ERR_MSG); --another procedure call
END PROC;

I am getting the following error : 

PLS:00201 'Identifier PARAM must be declaed'

Do I have to declare the PARAM before somewhere else? Please help !!

Upvotes: 0

Views: 2769

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

You have defined that variable in a nested PL/SQL block - it can't be accessed from an outer block (it doesn't even exist after the block is finished). You should move definition of your param variable to the outer block, for example:

PROCEDURE PROC (error OUT VARCHAR2,
                  RESULT OUT BOOLEAN )
AS
  PARAM TABLE1.COLUMN1%TYPE;
BEGIN
-----------------------------
-----------------------------


    SELECT COLUMN1
    INTO PARAM
    FROM TABLE1
    WHERE COLUMN2=ID AND COLUMN3='NAME';

PUBLISH_MSG.PUBLISH_PMM_MSG('ID',PARAM,PO_ERR_MSG); --another procedure call
END PROC;

Upvotes: 3

Related Questions