Some problems in writing Pl/sql program

Source: Internet
Author: User
Tags commit expression modify sql return
Program | questions
Remember the last time you wrote the Pl/sql program or just graduated soon, but also with Oracle 7 for Novell
Then only occasionally use Oralce, pl/sql some of the grammar has been completely forgotten,
This is not, encountered a number of low-level problems, I would like to write down, I hope that will not forget.


1. Cursor with a for update type
When cursor is defined, a for update is added because the data needs to be modified and deleted after the cursor is opened.
But after you modify and delete the data and commit before you close cursor, the following error occurs:
ORA-01002: Read violation order
ORA-06512: In "JWGL." Pckgstudsltcourse ", line 62
ORA-06512: On line 2

error:ora-06550: Line 2nd, column No. 0:
PLS-00103: the symbol "End-of-file" appears when the following is required:
Begin Case Declare
Exit for Goto if Loop mod null pragma raise return Select
Update while with "an identifier"
"A double-quoted delimited-identifier" "A Bind variable"
Close current Delete fetch lock insert open rollback
SavePoint set SQL Execute commit forall merge
"A single-quoted SQL string" pipe

At first I thought it was because I nested the SELECT clause, not the for update.
Later discovered is because I have deleted the data immediately after the commit, and immediately fetch data from the cursor.
In fact, when you open the cursor with for update, the system adds exclusive locks (exclusive) to the extracted data,
This allows other users to update, delete, and lock these records before the lock is released.
And once I execute a commit, the lock is released, the cursor becomes invalid, and then there is an error when I fetch the data.
Therefore, the commit is to be placed outside the loop, until all data is processed and then commit, then close cursor.

2. How to assign an object's properties in a method of an object type
In general, when a method of an object is invoked, the object itself self is passed in the parameter of type in, and therefore cannot be
Modify the properties of an object  otherwise the following error will occur.

error:pls-00363: expression ' SELF ' cannot be used as an assignment target
Line:26
Text:setroundno (Vsltcoursecalendar.getroundno ());

Error:pl/sql:statement ignored
Line:26
Text:setroundno (Vsltcoursecalendar.getroundno ());
Trying to compile a object in Oracle 9i.
Why is this throwing a exception?
Does anyone have a clue? Or all as puzzled as me? Any experts?

Throws Error:
Pls-00363:expression ' SELF. Attrib_number ' cannot be used as a assignment target

But you can do this by passing in the self in and then assigning the object properties within the method.
Member FUNCTION Test_function (SELF-out test_object) return number



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.