12 Replies Latest reply: Mar 8, 2013 8:44 AM by Billy~Verreynne RSS

    what is wrong in this syntax

    953733
      declare newDate DATE;
      BEGIN
      newDate := &EnterNewDate;
      update SUBJECT_VISIT
      SET eventdatelocal = newDate
      ,calldate = newDate
      ,calldatelocal = newDate
      where
      visit_id = '17'
      and subject_id = &Enter_Subject_ID;
      END
        • 1. Re: what is wrong in this syntax
          BCV
          Include SQL Terminator (;).
          • 2. Re: what is wrong in this syntax
            Hoek
            Well, as far I can see, you miss a semi-colon ( the ';' ) after your END statement.
            But why not post as much information as you can?
            Are you getting an error? What is the code and message?
            Be as clear as you can.
            See: {message:id=9360002}
            • 3. Re: what is wrong in this syntax
              Chanchal Wankhade
              Hi,

              you can try below code
              declare newDate DATE :=&EnterNewDate;
              BEGIN
              update SUBJECT_VISIT
              SET eventdatelocal = newDate
              ,calldate = newDate
              ,calldatelocal = newDate
              where 
              visit_id = 17 --should not be in cotes.
              and subject_id = &Enter_Subject_ID;
              Its very bad idea to provide visit_id varchar2 as you providing the value in single cote.
              • 4. Re: what is wrong in this syntax
                BluShadow
                Apart from missing a semicolon off the END... You need to treat dates as dates, so something like:
                declare
                  newDate DATE;
                BEGIN
                  newDate := to_date('&EnterNewDate','DD/MM/YYYY');
                  update SUBJECT_VISIT
                  SET eventdatelocal = newDate
                     ,calldate = newDate
                     ,calldatelocal = newDate
                  where visit_id = '17'
                  and   subject_id = &Enter_Subject_ID;
                END;
                and then you have to ensure the person inputting the date at the command line, does so in DD/MM/YYYY format (or whatever format you specify in the code).
                When you use SQL*Plus substitution variables, you need to ensure they are properly encapsulated. If you don't specify quotes in the code, then the input will either need to be something like a number, or will need to include quotes in the input if a string is expected. If quotes are included in the code around the substitution variable, then the input should be without quotes. If a Date is expected, then you need to ensure the input is correctly converted to a DATE datatype.

                This is messy, and one of the reasons why substitution variables are not a good user interface and should only be used for ad-hoc things that are run by technical people who understand the necessary inputs. For a proper user interface you would use a proper application layer such as APEX, Java, .NET etc. and pass values in as parameters to a procedure rather than an anonymous block of PL/SQL.
                • 5. Re: what is wrong in this syntax
                  BluShadow
                  Chanchal Wankhade wrote:
                  Its very bad idea to provide visit_id varchar2 as you providing the value in single cote.
                  Unless visit_id is a varchar2 column and can contain values that are non-numeric. We cannot assume as we don't have the database of the OP.
                  • 6. Re: what is wrong in this syntax
                    mtefft
                    What makes you think something is wrong with it? Did you get error messages? Would you care to share them?

                    Some possibilities:
                    - Your final END needs a semicolon
                    - To run this, add a / on the line after the END;
                    - Your use of & makes this look like a SQL Plus script. Whatever you provide as values for &EnterNewDate and &Enter_Subject_ID will just be slapped into your code as is. No quotation marks will be added (unless the text you enter includes them).
                    - Read up on the badness of implicit date conversions.
                    • 7. Re: what is wrong in this syntax
                      953733
                      Thanks much. It is working now.
                      • 8. Re: what is wrong in this syntax
                        Chanchal Wankhade
                        Handle:  950730

                        Status Level:  Newbie

                        Registered:  Aug 3, 2012

                        Total Posts:  8

                        Total Questions:  5 (5 unresolved)

                        if you got answer then mark this thread as answered you have not make any question as answered.
                        • 9. Re: what is wrong in this syntax
                          Billy~Verreynne
                          Dislike the approach used - using SQL*Plus substitution variables directly in a PL/SQL anonymous block.

                          Primarily, because you have NO IDEA what exactly will be send to the database server - as the substitution means that SQL*Plus CHANGES the source code block prior to sending it to the server for compiling and executing.

                          Secondarily, this is not the correct and robust and secure client-server approach to Oracle - the approach that says, use bind variables.

                          So if you do want to use (ugly and clunky) SQL*Plus substitution variables, I suggest the following approach:
                          set define on
                          accept newDate date format yyyy/mm/dd prompt "Enter New Date (yyyy/mm/dd): "
                          accept subjectID number prompt "Enter Subject ID: "
                          
                          
                          --// create proper SQL*Plus (host) variables to use as bind variables
                          var newDate varchar2(20)
                          var subjectID number
                          
                          --// SQL*Plus is primitive - and forces us to use PL/SQL to assign
                          --// values to its own host variables
                          exec :newDate := '&newDate';
                          exec :subjectID := '&subjectID';
                          
                          --// done with ugly substitution variables
                          set define off  
                          
                          begin
                                  update testtab
                                  set     new_date = to_date( :newDate, 'yyyy/mm/dd' )
                                  where   subject_id = :subjectID;
                          
                                  dbms_output.put_line( SQL%RowCount||' row(s) updated.' );
                          end;
                          /
                          Edited by: Billy Verreynne on Mar 8, 2013 2:44 PM - added explicit string-to-date conversion as recommended by Ben
                          • 10. Re: what is wrong in this syntax
                            953733
                            I corrected the syntax as per the suggestions. It is working now. Thanks
                            • 11. Re: what is wrong in this syntax
                              bencol
                              Billy,

                              Given that :newDate is a string, should you not:
                              set     new_date = TO_DATE(:newDate,'yyyy/mm/dd')
                              ?

                              Agree with you on the ugliness of using substitution variables.
                              • 12. Re: what is wrong in this syntax
                                Billy~Verreynne
                                Yes. Indeed.

                                Am so used to binding variables as date, I forget that SQL*Plus does not support that option. My bad.

                                Thanks, will correct the example.