Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
Title: RE: [HACKERS] Anybody have an Oracle PL/SQL reference at hand? > Tom Lane wrote: > > Hmm. Not only is that a pretty short list, but many of them don't > correspond very closely to the errors that Postgres would raise. I think these where like predefined 'shortcuts' for most common exceptions/errors. You can declare exceptions for any oracle error number smth. like PRAGMA EXCEPTION_INIT(rollback_too_small, -1555) >> PL/SQL allows users to define and raise their own exceptions too. > >Yeah. Our RAISE statement really needs a lot of work, but I think it >will have to be left for a future release cycle ... Sorry, if the following doesn't make sense, I started exploring just a week ago... >From reading the docs I could't determine what would be the exception to catch in the outer block for e.g. RAISE NOTICE 'caught division_by_zero'; and how to raise the same exeption again, for example if I determine that the exception still cannot be handled correcly in WHEN OTHERS section. Does RAISE raise the same exception or can I specify the type somehow? Andre
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
On Wed, Aug 04, 2004 at 09:46:22AM +0800, Christopher Kings-Lynne wrote: > >Depending on how tense you want to be about Oracle compatibility, we > >could make people actually write their blocks as above --- that is, > >the SAVEPOINT and ROLLBACK commands would be a required part of the > >exception-block syntax. They wouldn't actually *do* anything, but > >they would make the code look more like its Oracle equivalent. I'm not > >for this, but maybe someone wants to make the case for it? > > So long as I can emulate SQL MERGE with it, I'm happy. ie. I need a > solution to the 'try update, if no rows changed then insert (unique > index)' common race condition issue. If I cannot keep looping that > until it succeeds, then exceptions don't help me... Honestly, I'd *love* to see a merge command built in, assuming it's not very difficult. I would think that having the database handle this internally would be much more performant than using pgsql for it. > Chris > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command >(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
On Tue, 3 Aug 2004, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > On Tue, Aug 03, 2004 at 10:17:14AM -0400, Tom Lane wrote: > >> Right. Essentially, our implementation is supplying the SAVEPOINT and > >> ROLLBACK TO commands implicitly as part of any block with an EXCEPTION > >> clause. When we get around to updating the "Oracle porting" guide in > >> the plpgsql docs, this will need to be clearly explained. > > > If it's not difficult it would probably be good to allow for handling > > the rollback yourself. > > We're not doing that. This is a server-side function we're talking > about: it is executing *inside* the transaction that you want it to fool > with the status of. If you want logic that can issue SAVEPOINT and > ROLLBACK at arbitrary points, code it on the client side. Just another take on this: a lot of PL/SQL I've seen uses the EXCEPTIONs block simply to output strings describing the exception. That is: EXCEPTION WHEN foo THEN RAISE NOTICE 'You cannot foo'; WHEN bar THEN RAISE NOTICE 'You cannot bar'; END; In this case, no exception handler is accessing or modifying SQL data. Would it be worth trying to identify these situations so that we can avoid subtransaction overhead? Gavin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
BEGIN; SAVEPOINT start; INSERT INTO users VALUES(user || suffix); EXIT; EXCEPTION WHEN UNIQUE_VIOLATION THEN ROLLBACK TO start; suffix := suffix + 1; END; By the way, while I know Oracle won't abort the transaction, they might rollback whatever work the command that failed had done; I'm not really sure how that's handled. How about a new EXCEPTION clause: EXCEPTION NO ROLLBACK WHEN UNIQUE... Chirs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
Depending on how tense you want to be about Oracle compatibility, we could make people actually write their blocks as above --- that is, the SAVEPOINT and ROLLBACK commands would be a required part of the exception-block syntax. They wouldn't actually *do* anything, but they would make the code look more like its Oracle equivalent. I'm not for this, but maybe someone wants to make the case for it? So long as I can emulate SQL MERGE with it, I'm happy. ie. I need a solution to the 'try update, if no rows changed then insert (unique index)' common race condition issue. If I cannot keep looping that until it succeeds, then exceptions don't help me... Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > On Tue, Aug 03, 2004 at 10:17:14AM -0400, Tom Lane wrote: >> Right. Essentially, our implementation is supplying the SAVEPOINT and >> ROLLBACK TO commands implicitly as part of any block with an EXCEPTION >> clause. When we get around to updating the "Oracle porting" guide in >> the plpgsql docs, this will need to be clearly explained. > If it's not difficult it would probably be good to allow for handling > the rollback yourself. We're not doing that. This is a server-side function we're talking about: it is executing *inside* the transaction that you want it to fool with the status of. If you want logic that can issue SAVEPOINT and ROLLBACK at arbitrary points, code it on the client side. Oracle seems to have defined PL/SQL as though the function code executes outside the database engine, which is kind of an odd way to look at it IMHO. But even if you think it's just the right thing, we can't support it with anything approximating our current design for PLs. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
On Tue, Aug 03, 2004 at 10:17:14AM -0400, Tom Lane wrote: > Gavin Sherry <[EMAIL PROTECTED]> writes: > > > BEGIN; > > SAVEPOINT start; > > INSERT INTO users VALUES(user || suffix); > > EXIT; > > EXCEPTION > > WHEN UNIQUE_VIOLATION THEN > > ROLLBACK TO start; > > suffix := suffix + 1; > > END; > > Right. Essentially, our implementation is supplying the SAVEPOINT and > ROLLBACK TO commands implicitly as part of any block with an EXCEPTION > clause. When we get around to updating the "Oracle porting" guide in > the plpgsql docs, this will need to be clearly explained. > > Depending on how tense you want to be about Oracle compatibility, we > could make people actually write their blocks as above --- that is, > the SAVEPOINT and ROLLBACK commands would be a required part of the > exception-block syntax. They wouldn't actually *do* anything, but > they would make the code look more like its Oracle equivalent. I'm not > for this, but maybe someone wants to make the case for it? > > regards, tom lane If it's not difficult it would probably be good to allow for handling the rollback yourself. In this example it wouldn't matter; the row triggering an error won't be inserted. But if you were inserting data from a multi-row source such as a temporary table it would make a difference. By the way, while I know Oracle won't abort the transaction, they might rollback whatever work the command that failed had done; I'm not really sure how that's handled. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
The upsides, as I see them: They use one system for handling all exceptions, user generated or not. They didn't come up with their own arbitrary names for all the error codes they have. Naming an exception follows all the namespace rules; for example, the exception code example I gave was in the 'symer' package, so you would reference all those execptions as: EXCEPTION WHEN symer.some_error_name_I_came_up_with THEN As for waiting for the code to fail in the field, I'm not sure that really applies... I only trap exceptions that I know might happen and have some specific way to deal with, otherwise I let them percolate up the call stack. Looking at the PGSQL side, I think using one system for both system and user defined errors is a good thing. Right now I don't know that PGSQL has any concept of different user defined error codes, but that's something that might be usefull. It allows an application to trap your errors based on an error code, instead of depending on parsing error text. Oracle has defined probably 10,000 or more error codes, which is why it would be impractical for them to come up with a definative name for each one. PostgreSQL isn't at that stage, so it's not as big an issue. But if one error handling system is created for both internal and 'user-defined' errors then it would be possible to set aside chunks of error codes for gborg projects, for example. This would allow them to document the errors that might occure specific to their code. Oracle kind of does this in two ways.. they segment the error numbers, and they also prefix each error with a three leter acronym indicating what general system the error is from. As anxious as I am to have error trapping capability in plpgsql, it might be good to consider possible future uses before deciding on a system to handle it. On Mon, Aug 02, 2004 at 09:24:29PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > Oracle defines very few named exceptions. Instead, the intention is that > > you define a name for a numeric exception and use it yourself. > > Yeah, I noticed that. It seems a spectacularly bad idea :-(. What > redeeming social value has it got? AFAICS there are no upsides, only > downsides: you might get the numeric code wrong, and never know it > until your code fails in the field; and even if you always get it > right, having every bit of code invent its own random name for the > same exception doesn't seem like it does anything for readability or > maintainability. > > In any case we use SQLSTATEs not SQLCODEs, so we have no hope of being > compatible with Oracle at that level. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
Gavin Sherry <[EMAIL PROTECTED]> writes: > BEGIN; > SAVEPOINT start; > INSERT INTO users VALUES(user || suffix); > EXIT; > EXCEPTION > WHEN UNIQUE_VIOLATION THEN > ROLLBACK TO start; > suffix := suffix + 1; > END; Right. Essentially, our implementation is supplying the SAVEPOINT and ROLLBACK TO commands implicitly as part of any block with an EXCEPTION clause. When we get around to updating the "Oracle porting" guide in the plpgsql docs, this will need to be clearly explained. Depending on how tense you want to be about Oracle compatibility, we could make people actually write their blocks as above --- that is, the SAVEPOINT and ROLLBACK commands would be a required part of the exception-block syntax. They wouldn't actually *do* anything, but they would make the code look more like its Oracle equivalent. I'm not for this, but maybe someone wants to make the case for it? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
Gavin Sherry <[EMAIL PROTECTED]> writes: > I agree with you that forcing users to declare names for SQLCODEs is not > such a great idea. What I do like, however, is the ability to declare your > own exceptions. For example: Agreed, that would be a good thing to have, but I think it's something we'll have to leave for the next release cycle. > I also took a look at the Oracle PL/SQL exceptions in 10g. There are only > 21 of them people have much finer granularity with PL/PgSQL. The problem > is that I'd imagine that I'd a lot of PL/SQL code captures the exception > VALUE_ERROR (which seems to cover all of SQLSTATE Class 22 it seems). Isn't this covered by our provision for catching whole classes? WHEN DATA_EXCEPTION THEN catches all of class 22, for example. > Anyway, I've attached a patch which adds a few more labels for existing > SQLSTATE error codes where there is a one-to-one mapping from PostgreSQL > to Oracle. I had originally gone into this with the idea of duplicating their labels, but I'm now of the opinion that it's a bad idea and we shouldn't have just partial agreement. That would just lull porters of Oracle code into thinking they didn't need to review their exception labels. So, yeah, it would be easy to offer ZERO_DIVIDE as an accepted spelling, but will that really improve anyone's life? This leads into another thing I wanted to have some discussion about. The way the plpgsql exception code is currently written, it doesn't notice or complain about bad labels. For instance you could write WHEN FOOBAR THEN and you'd never get a complaint; the corresponding handler code would simply never be entered. This is probably bad. How tense do we need to be about detecting bad condition labels? Any opinions out there about what to do about these points? > Having now added these new exception labels, and given that there are some > errors not supported as exceptions from within PL/PgSQL (success, > warnings, etc), perhaps should generate our own list of error codes within > the PL/PgSQL documentation by looking at plerrcodes.h ? I think referencing Appendix A is the way to do it. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
One other difference when compared with Oracle is that Oracle does not abort the transaction which raised the exception. Although I generally do not think this is a great idea, it does allow for things like retry loops. Assuming we have savepoints, consider the following function which creates a user account DECLARE suffix int; BEGIN suffix := 1; LOOP BEGIN; SAVEPOINT start; INSERT INTO users VALUES(user || suffix); EXIT; EXCEPTION WHEN UNIQUE_VIOLATION THEN ROLLBACK TO start; suffix := suffix + 1; END; END LOOP; END; Again, it might not be great to leave the database in an inconsistent state when we get to the exception handler and I'd be all for generating another exception if the (sub) transaction was not rolled back and the exception handler tried to access data. Just some ideas. Gavin ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
On Mon, 2 Aug 2004, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > Oracle defines very few named exceptions. Instead, the intention is that > > you define a name for a numeric exception and use it yourself. > > Yeah, I noticed that. It seems a spectacularly bad idea :-(. What > redeeming social value has it got? AFAICS there are no upsides, only > downsides: you might get the numeric code wrong, and never know it > until your code fails in the field; and even if you always get it > right, having every bit of code invent its own random name for the > same exception doesn't seem like it does anything for readability or > maintainability. I agree with you that forcing users to declare names for SQLCODEs is not such a great idea. What I do like, however, is the ability to declare your own exceptions. For example: DECLARE invalid_sale EXCEPTION; BEGIN ... IF saleid < 0 THEN RAISE EXCEPTION invalid_sale; END IF; ... IF price < '0.00' THEN RAISE EXCEPTION invalid_sale; END IF; ... EXCEPTION WHEN invalid_sale THEN ... END; This is essentially using the exception system for as a goto mechanism, which usually I wouldn't like except for the problems created when you have large PL/PgSQL blocks which may encounter the same conditions in different parts of the block. This will also be useful because people will want to emulate Oracle PL/SQL behaviour of generating an exception if is generated when a SELECT INTO returns no rows. So, they could do: SELECT INTO myvar ... IF NOT FOUND THEN RAISE EXCEPTION NO_DATA_FOUND; END IF I also took a look at the Oracle PL/SQL exceptions in 10g. There are only 21 of them people have much finer granularity with PL/PgSQL. The problem is that I'd imagine that I'd a lot of PL/SQL code captures the exception VALUE_ERROR (which seems to cover all of SQLSTATE Class 22 it seems). This would be a special case to the excecption label map. There is also the STORAGE_ERROR exception which covers ERRCODE_OUT_OF_MEMORY, ERRCODE_DISK_FULL, ERRCODE_INSUFFICIENT_RESOURCES, ERRCODE_IO_ERROR and ERRCODE_DATA_CORRUPTED (!!). There is also INVALID_CURSOR, which basically covers all the cursor errors. I have no evidence that these exceptions are in wide use so, maybe its not a problem at all. Anyway, I've attached a patch which adds a few more labels for existing SQLSTATE error codes where there is a one-to-one mapping from PostgreSQL to Oracle. Having now added these new exception labels, and given that there are some errors not supported as exceptions from within PL/PgSQL (success, warnings, etc), perhaps should generate our own list of error codes within the PL/PgSQL documentation by looking at plerrcodes.h ? Just some thoughts... GavinIndex: src/pl/plpgsql/src/plerrcodes.h === RCS file: /usr/local/cvsroot/pgsql-server/src/pl/plpgsql/src/plerrcodes.h,v retrieving revision 1.3 diff -2 -c -r1.3 plerrcodes.h *** src/pl/plpgsql/src/plerrcodes.h 2 Aug 2004 17:03:48 - 1.3 --- src/pl/plpgsql/src/plerrcodes.h 3 Aug 2004 10:28:44 - *** *** 40,43 --- 40,44 { "datetime_value_out_of_range", ERRCODE_DATETIME_VALUE_OUT_OF_RANGE }, { "division_by_zero", ERRCODE_DIVISION_BY_ZERO }, + { "zero_divide", ERRCODE_DIVISION_BY_ZERO }, { "error_in_assignment", ERRCODE_ERROR_IN_ASSIGNMENT }, { "escape_character_conflict", ERRCODE_ESCAPE_CHARACTER_CONFLICT }, *** *** 188,191 --- 189,193 { "lock_file_exists", ERRCODE_LOCK_FILE_EXISTS }, { "plpgsql_error", ERRCODE_PLPGSQL_ERROR }, + { "program_error", ERRCODE_PLPGSQL_ERROR }, { "raise_exception", ERRCODE_RAISE_EXCEPTION }, { "internal_error", ERRCODE_INTERNAL_ERROR }, ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Oracle defines very few named exceptions. Instead, the intention is that > you define a name for a numeric exception and use it yourself. Yeah, I noticed that. It seems a spectacularly bad idea :-(. What redeeming social value has it got? AFAICS there are no upsides, only downsides: you might get the numeric code wrong, and never know it until your code fails in the field; and even if you always get it right, having every bit of code invent its own random name for the same exception doesn't seem like it does anything for readability or maintainability. In any case we use SQLSTATEs not SQLCODEs, so we have no hope of being compatible with Oracle at that level. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
On Sat, Jul 31, 2004 at 01:43:25PM -0400, Tom Lane wrote: > Andrew Dunstan <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> Can anyone check how well the syntax of plpgsql EXCEPTION, as described > >> at > >> http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > >> agrees with what Oracle does? > > > It appears you can have multiple exceptions in a single handler, but > > that the separator is 'OR' rather than ','. > > See > > http://www.cise.ufl.edu/help/database/oracle-docs/appdev.920/a96624/13_elems17.htm > > Got it. We can do that. > > > The page also gives a link to a list of the predefined exceptions. > > Hmm. Not only is that a pretty short list, but many of them don't > correspond very closely to the errors that Postgres would raise. > Maybe we should just forget about trying to be compatible with Oracle's > condition names. That still leaves us with needing to decide what our > condition names should be, though. > > One thing that just occurred to me is that we should accept the category > names (in errcodes.h, the exceptions with '000' as the last three digits > of the code) as matching any exception in their category. > > > PL/SQL allows users to define and raise their own exceptions too. > > Yeah. Our RAISE statement really needs a lot of work, but I think it > will have to be left for a future release cycle ... > Oracle defines very few named exceptions. Instead, the intention is that you define a name for a numeric exception and use it yourself. CREATE OR REPLACE PACKAGE symer AS en_missing_data CONSTANT NUMBER := -20999; exc_missing_data EXCEPTION; PRAGMA EXCEPTION_INIT (exc_missing_data, -20999); END; -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
Hi Tom, I have sent you and the list the HTML page from the oracle tech network describing all of this. However, it seems to have disappeared in to the void since you don't seem to have received it and it hasn't hit the list yet. You can get a free login to access all the oracle docs and manuals, here: http://otn.oracle.com/pls/db10g/portal.portal_demo3?selected=1 If you like, however, I can just zip and email you the full PL/SQL PDF manual that I just downloaded from the above URL. Let me know if you want the PDF emailed to you. Can anyone check how well the syntax of plpgsql EXCEPTION, as described at http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING agrees with what Oracle does? I did some googling but couldn't find anything that seemed authoritative. I'm wondering in particular if Oracle allows multiple condition names per WHEN, along the lines of WHEN condition [ , condition ... ] THEN handler_statements It does, but with a different syntax: --- If you want two or more exceptions to execute the same sequence of statements, list the exception names in the WHEN clause, separating them by the keyword OR, as follows: EXCEPTION WHEN over_limit OR under_limit OR VALUE_ERROR THEN -- handle the error If any of the exceptions in the list is raised, the associated sequence of statements is executed. The keyword OTHERS cannot appear in the list of exception names; it must appear by itself. You can have any number of exception handlers, and each handler can associate a list of exceptions with a sequence of statements. However, an exception name can appear only once in the exception-handling part of a PL/SQL block or subprogram. --- Also it would be nice to see a complete list of the "condition" names that they accept. I whipped up a quick table based on our ERRCODE macro names, see http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/pl/plpgsql/src/plerrcodes.h but I'm certain that's not what we really want to expose to users in the long run. -- ACCESS_INTO_NULL A program attempts to assign values to the attributes of an uninitialized object. CASE_NOT_FOUND None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause. COLLECTION_IS_NULL A program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray. CURSOR_ALREADY_OPEN A program attempts to open an already open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers, so your program cannot open that cursor inside the loop. DUP_VAL_ON_INDEX A program attempts to store duplicate values in a database column that is constrained by a unique index. INVALID_CURSOR A program attempts a cursor operation that is not allowed, such as closing an unopened cursor. INVALID_NUMBER In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number. LOGIN_DENIED A program attempts to log on to Oracle with an invalid username or password. NO_DATA_FOUND A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. Because this exception is used internally by some SQL functions to signal that they are finished, you should not rely on this exception being propagated if you raise it within a function that is called as part of a query. NOT_LOGGED_ON A program issues a database call without being connected to Oracle. PROGRAM_ERROR PL/SQL has an internal problem. ROWTYPE_MISMATCH The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. For example, when an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible. SELF_IS_NULL A program attempts to call a MEMBER method, but the instance of the object type has not been initialized. The built-in parameter SELF points to the object, and is always the first parameter passed to a MEMBER method. STORAGE_ERROR PL/SQL runs out of memory or memory has been corrupted. SUBSCRIPT_BEYOND_COUNT A program references a nested table or varray element using an index number larger than the number of elements in the collection. SUBSCRIPT_OUTSIDE_LIMIT A program references a nested table or varray element using an index number (-1 for example) that is outside the legal range. SYS_INVALID_ROWID The conversion of a character string into a universal rowid fail
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Can anyone check how well the syntax of plpgsql EXCEPTION, as described >> at >> http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING >> agrees with what Oracle does? > It appears you can have multiple exceptions in a single handler, but > that the separator is 'OR' rather than ','. > See > http://www.cise.ufl.edu/help/database/oracle-docs/appdev.920/a96624/13_elems17.htm Got it. We can do that. > The page also gives a link to a list of the predefined exceptions. Hmm. Not only is that a pretty short list, but many of them don't correspond very closely to the errors that Postgres would raise. Maybe we should just forget about trying to be compatible with Oracle's condition names. That still leaves us with needing to decide what our condition names should be, though. One thing that just occurred to me is that we should accept the category names (in errcodes.h, the exceptions with '000' as the last three digits of the code) as matching any exception in their category. > PL/SQL allows users to define and raise their own exceptions too. Yeah. Our RAISE statement really needs a lot of work, but I think it will have to be left for a future release cycle ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
Hello, From I can tell from Oracle pl/SQL programming page 130 ;) it is identical. However Oracle does have thinkgs like EXCEPTION_INIT. Here are the name of the Oracle predefined exceptions: CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX INVALID_CURSOR INVALID_NUMBER LOGIN_DENIED NO_DATA_FOUND NOT_LOGGED_IN PROGRAM_ERROR STORAGE_ERROR TIMEOUT_ON_RESOURCE TOO_MANY_ROWS TRANSACTION_BACKED_OUT VALUE_ERROR Sincerely, Joshua D. Drake Tom Lane wrote: Can anyone check how well the syntax of plpgsql EXCEPTION, as described at http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING agrees with what Oracle does? I did some googling but couldn't find anything that seemed authoritative. I'm wondering in particular if Oracle allows multiple condition names per WHEN, along the lines of WHEN condition [ , condition ... ] THEN handler_statements Also it would be nice to see a complete list of the "condition" names that they accept. I whipped up a quick table based on our ERRCODE macro names, see http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/pl/plpgsql/src/plerrcodes.h but I'm certain that's not what we really want to expose to users in the long run. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0034 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Anybody have an Oracle PL/SQL reference at hand?
Tom Lane wrote: Can anyone check how well the syntax of plpgsql EXCEPTION, as described at http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING agrees with what Oracle does? I did some googling but couldn't find anything that seemed authoritative. I'm wondering in particular if Oracle allows multiple condition names per WHEN, along the lines of WHEN condition [ , condition ... ] THEN handler_statements Also it would be nice to see a complete list of the "condition" names that they accept. I whipped up a quick table based on our ERRCODE macro names, see http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/pl/plpgsql/src/plerrcodes.h but I'm certain that's not what we really want to expose to users in the long run. It appears you can have multiple exceptions in a single handler, but that the separator is 'OR' rather than ','. See http://www.cise.ufl.edu/help/database/oracle-docs/appdev.920/a96624/13_elems17.htm The page also gives a link to a list of the predefined exceptions. PL/SQL allows users to define and raise their own exceptions too. cheers andrew ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Anybody have an Oracle PL/SQL reference at hand?
Can anyone check how well the syntax of plpgsql EXCEPTION, as described at http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING agrees with what Oracle does? I did some googling but couldn't find anything that seemed authoritative. I'm wondering in particular if Oracle allows multiple condition names per WHEN, along the lines of WHEN condition [ , condition ... ] THEN handler_statements Also it would be nice to see a complete list of the "condition" names that they accept. I whipped up a quick table based on our ERRCODE macro names, see http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/pl/plpgsql/src/plerrcodes.h but I'm certain that's not what we really want to expose to users in the long run. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend