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

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

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.

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.

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.

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.

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

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?

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?

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.

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

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:

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

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.

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