This discussion is archived
1 2 3 Previous Next 35 Replies Latest reply: Mar 4, 2013 4:22 AM by javaMan2012 RSS

Stored Procedure

javaMan2012 Newbie
Currently Being Moderated
Hi Folks,

I am learning Oracle PL/SQL and was trying out some Stored Procedure queries. I was trying out a book example where a stored procecure is being called. The code in question is as follow:


CREATE OR REPLACE PROCEDURE update_invoices_credit_total
(
     invoice_number_param VARCHAR2,
     credit_total_param NUMBER
)

AS
BEGIN
     UPDATE invoices
     SET credit_total = credit_total_param
     WHERE invoice_number = invoice_number_param;


COMMIT;
EXCEPTION
     WHEN OTHERS THEN
     ROLLBACK;
END;

CALL update_invoices_credit_total('367447', 300);

I am getting a 'Syntax error, expected:' message when I hover the mouse over the word update in the call statement. I am not sure why I am getting this, hope someone can advise. Thanks.

(I am running Oracle 11g express edition on my Windows 7 machine. Oracle SQL Developer 3.1.07 is the 'editor' through which I run the above commands.)

Edited by: javaMan2012 on Feb 28, 2013 3:34 AM
  • 1. Re: Stored Procedure
    BluShadow Guru Moderator
    Currently Being Moderated
    javaMan2012 wrote:
    Hi Folks,

    I am learning Oracle PL/SQL and was trying out some Stored Procedure queries. I was trying out a book example where a stored procecure is being called. The code in question is as follow:


    CREATE OR REPLACE PROCEDURE update_invoices_credit_total
    (
         invoice_number_param VARCHAR2,
         credit_total_param NUMBER
    )

    AS
    BEGIN
         UPDATE invoices
         SET credit_total = credit_total_param
         WHERE invoice_number = invoice_number_param;


    COMMIT;
    EXCEPTION
         WHEN OTHERS THEN
         ROLLBACK;
    END;

    CALL update_invoices_credit_total('367447', 300);

    I am getting a 'Syntax error, expected:' message when I hover the mouse over the word update in the call statement. I am not sure why I am getting this, hope someone can advise. Thanks.
    Which line is it indicating the error is on? and what's the full error message (it should tell you what it expected).

    Looking at your code, I can't see anything syntactically wrong with it, except perhaps you may need to remove the blank line between the closing bracket of the parameters and the AS keyword (it depends on the interface you are using to send the code to the SQL engine e.g. SQL*Plus may complain about such blank lines and treat what you're providing as two statements).
  • 2. Re: Stored Procedure
    BluShadow Guru Moderator
    Currently Being Moderated
    BluShadow wrote:
    Which line is it indicating the error is on? and what's the full error message (it should tell you what it expected).

    Looking at your code, I can't see anything syntactically wrong with it, except perhaps you may need to remove the blank line between the closing bracket of the parameters and the AS keyword (it depends on the interface you are using to send the code to the SQL engine e.g. SQL*Plus may complain about such blank lines and treat what you're providing as two statements).
    To add to my previous... what interface are you using?
  • 3. Re: Stored Procedure
    naviNkumArG Newbie
    Currently Being Moderated
    hi,

    mention which tool you are using, find the following pl/sql block I tried with Toad 9.5

    CREATE OR REPLACE PROCEDURE update_emp (emp_id NUMBER)
    AS
    BEGIN
    UPDATE emp
    SET sal = 2000
    WHERE empno = emp_id;

    COMMIT;
    EXCEPTION
    WHEN OTHERS
    THEN
    ROLLBACK;
    END;

    call update_emp('7839');
  • 4. Re: Stored Procedure
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Do not like the exception handler.

    The error that was raised is not "fixed" by rolling back. If anything, the rollback is a resource protection issue - and not relevant ito how the exception needs to be handled. In which case the raise statement must follow in order to re-raise the exception as the rollback did not address the error.
  • 5. Re: Stored Procedure
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Oh yeah - consider using standard sensible and de-facto programming and naming standards. As Java guidelines and naming conventions state.

    Coding reserved words in upper case is a horribly silly approach, devoid of any logical reason and based on mainframe coding standards dating back to the immediate post-punchcard period, over 30 years ago.

    In today's developer environment where even old text editors like Unix vi supports syntax highlighting, the use of upper casing of reserved words is, bluntly put IMO, an idiotic approach.
  • 6. Re: Stored Procedure
    JonWat Explorer
    Currently Being Moderated
    SQL Developer handles SQL statements very well: if you tell it to send just the current SQl statement it will happily figure out (assuming your have used your semi-colons correctly) where the beginning and end of the statements are that your cursor is sitting on, and send that. When you have some PL/SQL script on the page SQL Developer is not so good at figuring out what to send as a single "statement". You will need to highlight exactly what you want to send, whether it is a PL/SQL block (like your procedure definition, or some SQL or PL/SQL that is somewhere else on the worksheet.

    Submitting the whole worksheet as a script should still work for you in this case, though.

    Jon
  • 7. Re: Stored Procedure
    Another_user Explorer
    Currently Being Moderated
    Coding reserved words in upper case is a horribly silly approach, devoid of any logical reason and based on mainframe coding standards dating back to the immediate post-punchcard period, over 30 years ago.
    Uppercase reserved words in PLSQL still seems pretty standard as far as I can tell. This is the default when using either TOAD or SQL Developer to format your code.
  • 8. Re: Stored Procedure
    rp0428 Guru
    Currently Being Moderated
    >
    I am getting a 'Syntax error, expected:' message when I hover the mouse over the word update in the call statement. I am not sure why I am getting this, hope someone can advise. Thanks.
    >
    You are missing the '/' character that indicates the end of your PL/SQL procedure.
    WHEN OTHERS THEN
    ROLLBACK;
    END;
    
    CALL update_invoices_credit_total('367447', 300);
    The 'CALL' statement is being considered part of the PL/SQL. Add a forward slash and a blank line
    WHEN OTHERS THEN
    ROLLBACK;
    END;
    /
    
    CALL update_invoices_credit_total('367447', 300);
  • 9. Re: Stored Procedure
    javaMan2012 Newbie
    Currently Being Moderated
    Hi Folks,

    Thank you so much for the response. I think there is something new for me to learn from all of you. Initially when I did not make the forward slash, as mentioned by rp0428, I was getting both a red crooked line indicator under the word update, which when mouse hovered said:
    Syntax error, expected*
    +.+
    +;+
    +)+

    and also when I run the statement I got the following error:
    PROCEDURE update_invoices_credit_total compiled*
    Warning: execution completed with warning*

    After I added the forward slash, the red crooked line error is still there, but when I run the statement now I am not getting any error, instead I get a:
    PROCEDURE update_invoices_credit_total compiled* message. Thinking all is well, I went to the Invoices table to check and see if the 'credit total' column's row value has changed to 300 in the line where invoice number is 367447, I see no change. Where am I going wrong now?

    Edited by: javaMan2012 on Feb 28, 2013 8:32 PM
  • 10. Re: Stored Procedure
    rp0428 Guru
    Currently Being Moderated
    >
    Where am I going wrong now?
    >
    You were told earlier where you were going wrong.
    EXCEPTION
       WHEN OTHERS
    That just tells Oracle you really don't care wre you are 'going wrong now'.

    Get rid of the exception handler completely so you can see what exception you are getting.
  • 11. Re: Stored Procedure
    sb92075 Guru
    Currently Being Moderated
    javaMan2012 wrote:
    Hi Folks,

    Thank you so much for the response. I think there is something new for me to learn from all of you. Initially when I did not make the forward slash, as mentioned by rp0428, I was getting both a red crooked line indicator under the word update, which when mouse hovered said:
    Syntax error, expected*
    +.+
    +;+
    +)+

    and also when I run the statement I got the following error:
    PROCEDURE update_invoices_credit_total compiled*
    Warning: execution completed with warning*

    After I added the forward slash, the red crooked line error is still there, but when I run the statement now I am not getting any error, instead I get a:
    PROCEDURE update_invoices_credit_total compiled* message. Thinking all is well, I went to the Invoices table to check and see if the 'credit total' column's row value has changed to 300 in the line where invoice number is 367447, I see no change. Where am I going wrong now?

    Edited by: javaMan2012 on Feb 28, 2013 8:32 PM
    >
    Hi Folks,

    Thank you so much for the response. I think there is something new for me to learn from all of you. Initially when I did not make the forward slash, as mentioned by rp0428, I was getting both a red crooked line indicator under the word update, which when mouse hovered said:
    Syntax error, expected*
    +.+
    +;+
    +)+

    and also when I run the statement I got the following error:
    PROCEDURE update_invoices_credit_total compiled*
    Warning: execution completed with warning*

    After I added the forward slash, the red crooked line error is still there, but when I run the statement now I am not getting any error, instead I get a:
    PROCEDURE update_invoices_credit_total compiled* message. Thinking all is well, I went to the Invoices table to check and see if the 'credit total' column's row value has changed to 300 in the line where invoice number is 367447, I see no change. Where am I going wrong now?

    Edited by: javaMan2012 on Feb 28, 2013 8:32 PM

    after the compile was successful did you do as below again?

    CALL update_invoices_credit_total('367447', 300);
  • 12. Will the real PL/SQL language standards please stand up?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Another_user wrote:
    Coding reserved words in upper case is a horribly silly approach, devoid of any logical reason and based on mainframe coding standards dating back to the immediate post-punchcard period, over 30 years ago.
    Uppercase reserved words in PLSQL still seems pretty standard as far as I can tell. This is the default when using either TOAD or SQL Developer to format your code.
    So why is it not a standard in Java as per Code Conventions for the Java Programming Language?

    Why is not a standard across all Microsoft technologies and languages as per Guidelines for Names?

    Why is it not a standard for naming conventions and code style in PHP?

    Why is it not a standard in Python as per the Style Guide for Python Code?

    Is PL/SQL different from all these other languages? So different that it needs standards that are a 180 degree different from these languages?

    What is PL/SQL? It is based on the Ada language. Part of the Pascal family of languages designed by prof. Niklaus Wirth. I used Pascal back when many here was still in diapers or not even born yet. There are no standard in Pascal that states reserved words need to be upper case. But what about PL/SQL's direct parent, the Ada language?

    So what does the Ada 95 Quality and Style Guide, chapter 3, say about naming conventions? If any standard should be followed in PL/SQL, it should be adherence to the language that PL/SQL calls parent. ADA DOES NOT SUPPORT RESERVE WORDS IN UPPER CASE AS A STANDARD.

    So why then is this then used as if it is a standard in PL/SQL?

    I tell you why. Because of monkey see, monkey do. Because of not understanding the fundamentals of WHY standards are needed in programming languages, and IGNORANCE as to why there are a large set of common standards across programming languages. Topped off by STUPIDITY in applying "standards" in PL/SQL that DOES NOT EXIST IN ANY PROGRAMMING LANGUAGE TODAY.

    As for TOAD and SQL-Developer? As I said. Monkey see and monkey do.

    And yes, this posting is intended to abrasive and aggressive - as that is what is needed to challenge what many think are actually standards for PL/SQL. Attack what they think are/should be standards, forcing them to evaluate that and defend that. And maybe then, when forced to think about standards, and evaluate common standards used in other languages, will lead them to some insight regarding standards. Or then, maybe not....
  • 13. Re: Stored Procedure
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    javaMan2012 wrote:

    After I added the forward slash, the red crooked line error is still there, but when I run the statement now I am not getting any error, instead I get a:
    PROCEDURE update_invoices_credit_total compiled* message.
    Some basics.

    SQL language does not have a statement terminator - because the SQL language is about submitting a single statement at a time for execution. So:
    // valid SQL statement
    select * from emp order by empno
    
    // invalid SQL statement
    select * from emp order by empno;
    The PL/SQL language is a procedural language. It has packages, procedures and functions, and multiple statements. It is also based on Ada (which is based on Pascal), and uses the standard statement termination character, the semicolon (<i>;</i>).

    SQL*Plus is a CLI - it needs to know when the user has completed typing an instruction that needs to be send to the server. It uses the (no surprise) semi-colon character. E.g.
    // you type in SQL*Plus
    SQL> select * from emp order by empno;
    
    // it sends the following to the server
    select * from emp order by empno
    But what about PL/SQL code? It uses semicolons too. SQL*Plus (as you type) does basic parsing of what you type - in order to detect whether you are typing SQL code or PL/SQL code. When it detects that you are typing PL/SQL code, it ignores semicolons as "send-to-server" instructions - and it considers these semicolons as part of the code it needs to send to the server.

    So how does SQL*Plus now know when to send that PL/SQL code (with semicolons) to the server? It has a second character that is a "send-to-server" instruction. The forward slash (<i>/</i>).

    E.g.
    // you type ..
    SQL> begin
      2>   MyFunkyProc( param => 123 );
      3> end;
      4> /
    
    // .. and SQL*Plus sends the following to the server
    begin
      MyFunkyProc( param => 123 );
    end;
    Tools like TOAD and SQL-Developer follow the same convention. You can however also simply highlight the instruction (excluding "send-to-server" character), and execute it (execute button click or hot keystroke).

    It is important that you understand the basic syntax of the language - as that is what needs to be correctly coded, irrespective of the language or client used to send those server (SQL or PL/SQL) instructions to the database.
  • 14. Re: Will the real PL/SQL language standards please stand up?
    Dave Rabone Journeyer
    Currently Being Moderated
    stop beating around the bush :-)


    Agree completely ... looking at uppercased stuff gives me nasty flashbacks to COBOL - and in fact that's probably where the convention came from - a lot a COBOL programmers will have migrated into PL/SQL
1 2 3 Previous Next

Legend

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