11 Replies Latest reply: Oct 9, 2013 6:43 PM by davidp 2 RSS

    error

    natpidgeon

      when running this query in sql *plus

       

        • 1. Re: error
          Frank Kulash

          Hi,

           

           

           

          natpidgeon wrote:

           

           

          ...

          i keep getting error

           

           

          SP2-0552: BIND VARIABLE "PVUSAGEDATE" NOT DECLARED.

          im confuse on why it only gives me a error on the usage date but not the other ones, or maybe there is a better way to run it.

          thanks

          It could be because the other bind variables are declared correctly, but pvusagedate isn't.

          Or it could be that they are all invalid, but the parser happens to look at pvusagedate first, sees that it's not defined, raises the error and stops work on that query.

           

          No kidding; you need to start posing complete test scripts so that the people who want to help you can re-create the problem and test their ideas.  The error is probably not in the code you posted. Don't repeat the same mistakes over and over.

          See the forum FAQ:

          https://forums.oracle.com/message/9362002

           

          and your previous messages

          https://forums.oracle.com/thread/2589996

          https://forums.oracle.com/thread/2589517

          https://forums.oracle.com/thread/2588455

          https://forums.oracle.com/thread/2580574

          • 2. Re: error
            Anar Godjaev

            Hi

             

            it is very interesting error

             

            Please change :PvProperty to &PvProperty

             

            Thank you

             


            • 3. Re: error
              Warren Tolentino


              it is either you are using oracle forms or reports and that is why you have a bind variable. if it is a forms check your block items and it could be you mispelled the item in question or if you are using oracle report check the parameters.

              • 4. Re: error
                SomeoneElse

                > Please change :PvProperty to &PvProperty

                 

                Eh?  What's your basis for that change?

                • 5. Re: error
                  Anar Godjaev

                  if you do this replacement you will not get that error...

                  • 6. Re: error
                    Pablolee

                    Show us where / how you are (or at least, how you think you are) declaring your bind variables.

                    • 7. Re: error
                      SomeoneElse

                      You need to declare and populate your bind variables.  (you can't have a date bind variable type in sql plus, so you need to do a to_date for the character string).

                       

                      SQL> var PvProperty number

                      SQL> var pvUnitTypeID number

                      SQL> var pvUsageDate varchar2(30)

                      SQL>

                      SQL> begin

                        2     :PvProperty := 123;

                        3     :pvUnitTypeID := 456;

                        4     :pvUsageDate := '08-OCT-2013';

                        5

                        6     dbms_output.put_line (:PvProperty);

                        7     dbms_output.put_line (:pvUnitTypeID);

                        8     dbms_output.put_line (:pvUsageDate);

                        9  end;

                      10  /

                       

                      123

                      456

                      08-OCT-2013

                       

                      PL/SQL procedure successfully completed.

                       

                       

                      • 8. Re: error
                        natpidgeon

                        OK thanks,

                         

                        • 9. Re: error
                          SomeoneElse

                          > how can i add DBMS_OUTPUT.PUT_LINE to this query to i can see a output

                           

                          What output are you trying to see?

                          • 10. Re: error
                            davidp 2

                            First you need a SQL*Plus command, SET SERVEROUT ON

                            See SET System Variable Summary

                             

                            Then put the DBMS_OUTPUT as a sqlPlus statement of its own in the procedure. Since most of your procedure is a single statement, in what you posted, it can only go immediately after the BEGIN or immediately before the END.

                             

                            e.g.

                            BEGIN

                               dbms_output.put_line (:PvProperty);

                               dbms_output.put_line (:pvUnitTypeID);

                               dbms_output.put_line (:pvUsageDate);

                               SELECT Main.SEGMENT_CODE, ...

                               ...

                               dbms_output.put_line ('Rows selected: ' || SQL%ROWCOUNT);

                            END;

                            • 11. Re: error
                              davidp 2

                              Re: the original question, "im confuse on why it only gives me a error on the usage date but not the other ones, or maybe there is a better way to run it."

                               

                              SQL will only give you one error per statement. It stops processing once it finds an error. Which error it reports is not officially defined. Often it complains about the problems at the end of the statement when there are similar problems earlier in the statement. This is quite different from the behaviours of many programming language compilers.

                               

                              So if a SQL statement reports a syntax error, all you can say is "the statement has syntax errors, including the reported problem." You cannot say "It's OK up to the point the error is reported."

                               

                              I hope this helps clarify things.