This discussion is archived
9 Replies Latest reply: Feb 18, 2013 3:00 AM by user_sneha RSS

Bind Variable  is NOT DECLARED" error with SQL Developer

user_sneha Newbie
Currently Being Moderated
Hi All,

While executing the below block in SQL Developer, I get "Bind Variable "OUT_RSELL" is NOT DECLARED" error.
RSELL_REC_TYPE is a package local record type.

SET SERVEROUTPUT ON;
SET FEEDBACK OFF;
SET TIMING OFF;
--VARIABLE DECLARATION
var IN_REPLY_TYPE NUMBER ;
var IN_TEST_ID VARCHAR2(5) ;
var IN_START_DATE DATE ;
var IN_END_DATE DATE ;
var OUT_RSELL PKG_GET_DATA.RSELL_REC_TYPE ;
--VARIABLE INITIALIZATION

exec :IN_REPLY_TYPE:= 'R';
exec :IN_TEST_ID:= '0018511';

BEGIN
--CALL THE PROCEDURE
PKG_GET_DATA.PROC_GET_RSELL(
:IN_REPLY_TYPE,
:IN_TEST_ID,
TRUNC(SYSDATE),--start date
TRUNC(SYSDATE+2),--end date
:OUT_RSELL);
END;
/
print OUT_RSELL ;



But the equivalent Declare-Begin block with "OUT_RSELL PKG_GET_DATA.RSELL_REC_TYPE" in the "Declaration" sections works well.

Can anyone please suggest where am I going wrong?
  • 1. Re: Bind Variable  is NOT DECLARED" error with SQL Developer
    Purvesh K Guru
    Currently Being Moderated
    You have mixed SQL Plus syntax and commands with SQL Developers.

    SQL Developer does not have a command line interface. Hence you will have to adapt to the syntax supported by SQL Developer.
    SET SERVEROUTPUT ON;
    SET FEEDBACK OFF;
    SET TIMING OFF;
    --VARIABLE DECLARATION
    v_IN_REPLY_TYPE NUMBER ;               ---> Removed VAR; Precede with v_ to general convention
    v_IN_TEST_ID VARCHAR2(5) ; 
    v_IN_START_DATE DATE ; 
    v_IN_END_DATE DATE ; 
    v_OUT_RSELL PKG_GET_DATA.RSELL_REC_TYPE ; 
    --VARIABLE INITIALIZATION
    
    BEGIN
    
    v_IN_REPLY_TYPE:= 'R';         --> Variable assignment can be done during declaration itself.
    v_IN_TEST_ID:= '0018511';
    
    --CALL THE PROCEDURE
    PKG_GET_DATA.PROC_GET_RSELL(
    v_IN_REPLY_TYPE,
    v_IN_TEST_ID,
    TRUNC(SYSDATE),--start date
    TRUNC(SYSDATE+2),--end date
    v_OUT_RSELL); 
    END; 
    / 
    print OUT_RSELL ;        ---> Print is not valid in SQL Developer; Use DBMS_OUTPUT to print the data.
    Since you did not provide how the PKG_GET_DATA.RSELL_REC_TYPE is, I cannot suggest how to process it. I assume it to be a Collection, so you will have to write a Loop to traverse it and print using the DBMS_OUTPUT to the buffer. And with Serveroutput on, you can view it in your editor's output window.

    I hope this gives you a way to move forward.
  • 2. Re: Bind Variable  is NOT DECLARED" error with SQL Developer
    user_sneha Newbie
    Currently Being Moderated
    Hi Purvesh,

    Actually I am not using a DECLARE BEGIN block. Also it works just fine when I write the same code within a DECLARE-BEGIN-END section. I wanted to know how to declare it with "VAR" keyword.
  • 3. Re: Bind Variable  is NOT DECLARED" error with SQL Developer
    Sg049 Explorer
    Currently Being Moderated
    you can declare a variable by using "VARIABLE" key word,not with "VAR" i think. Try with "VARIABLE".




    Thanks,
    Sg
  • 4. Re: Bind Variable  is NOT DECLARED" error with SQL Developer
    Purvesh K Guru
    Currently Being Moderated
    user_sneha wrote:
    Hi Purvesh,

    Actually I am not using a DECLARE BEGIN block. Also it works just fine when I write the same code within a DECLARE-BEGIN-END section. I wanted to know how to declare it with "VAR" keyword.
    My Bad!!!!

    I missed that you have not used the Declare section. See it updated:
    SET SERVEROUTPUT ON;
    SET FEEDBACK OFF;
    SET TIMING OFF;
    --VARIABLE DECLARATION
    DECLARE                                      ---> Added Declare
    v_IN_REPLY_TYPE NUMBER ;               ---> Removed VAR; Precede with v_ to general convention
    v_IN_TEST_ID VARCHAR2(5) ; 
    v_IN_START_DATE DATE ; 
    v_IN_END_DATE DATE ; 
    v_OUT_RSELL PKG_GET_DATA.RSELL_REC_TYPE ; 
    --VARIABLE INITIALIZATION
     
    BEGIN
     
    v_IN_REPLY_TYPE:= 'R';         --> Variable assignment can be done during declaration itself.
    v_IN_TEST_ID:= '0018511';
     
    --CALL THE PROCEDURE
    PKG_GET_DATA.PROC_GET_RSELL(
    v_IN_REPLY_TYPE,
    v_IN_TEST_ID,
    TRUNC(SYSDATE),--start date
    TRUNC(SYSDATE+2),--end date
    v_OUT_RSELL); 
    END; 
    / 
    print OUT_RSELL ;        ---> Print is not valid in SQL Developer; Use DBMS_OUTPUT to print the data.
    You need to use VARIABLE syntax for it.

    See an explanation by Justin Cave Variable in SQL Developer.
  • 5. Re: Bind Variable  is NOT DECLARED" error with SQL Developer
    user_sneha Newbie
    Currently Being Moderated
    Hi SG,

    Both the keyword: "VARIABLE" and "VAR" are correct. Tried out both...still no clue:-(
  • 6. Re: Bind Variable  is NOT DECLARED" error with SQL Developer
    Purvesh K Guru
    Currently Being Moderated
    user_sneha wrote:
    Hi SG,

    Both the keyword: "VARIABLE" and "VAR" are correct. Tried out both...still no clue:-(
    It won't, unless you make it a Bind variable by using a Prefix of Colon where the variables are used. See the link in my previous post where Justin has demonstrated it.
  • 7. Re: Bind Variable  is NOT DECLARED" error with SQL Developer
    user_sneha Newbie
    Currently Being Moderated
    Hi Purvesh,

    So far i know, there are two approaches to execute a block in SQL developer. One is the usual DECLARE-BEGIN-END block, wherein you use dbms_output to display the data and other is the "variable" binding method, wherein you can use the "print" statement.

    First approach is working in the given case, but when i try and use the second approach, the declaration "var OUT_RSELL PKG_GET_DATA.RSELL_REC_TYPE ; " leads to "BIND VARIABLE" error. I want to know if there is some other way of declaring a package local record type in SQL statement "VAR". Also, similar declaration for "OBJECT TYPE" is working. Hope I am able to put forth my concern clearly.
  • 8. Re: Bind Variable  is NOT DECLARED" error with SQL Developer
    Purvesh K Guru
    Currently Being Moderated
    See this:
    variable v_var number;
    exec :v_var := 1111;
    
    print v_var;
    
    --Output
    anonymous block completed
    V_VAR
    ----
    1111
  • 9. Re: Bind Variable  is NOT DECLARED" error with SQL Developer
    user_sneha Newbie
    Currently Being Moderated
    Purvesh,

    This declaration is working for all data types other than a package local record or table type. I would like to know how to work around this for package local record and table type only. Rest is fine

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points