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



      when running this query in sql *plus


        • 1. Re: error
          Frank Kulash





          natpidgeon wrote:




          i keep getting error




          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.


          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:



          and your previous messages





          • 2. Re: error
            Anar Godjaev



            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

                > 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

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

                    • 7. Re: error

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

                        2     :PvProperty := 123;

                        3     :pvUnitTypeID := 456;

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


                        6     dbms_output.put_line (:PvProperty);

                        7     dbms_output.put_line (:pvUnitTypeID);

                        8     dbms_output.put_line (:pvUsageDate);

                        9  end;

                      10  /






                      PL/SQL procedure successfully completed.



                      • 8. Re: error

                        OK thanks,


                        • 9. Re: error

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




                               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);


                            • 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.