9 Replies Latest reply: Feb 18, 2013 5:00 AM by user_sneha RSS

    Bind Variable  is NOT DECLARED" error with SQL Developer

    user_sneha
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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