fact:

　　PL/SQL 9 ，PL/SQL 8 ，PL/SQL 7

　　symptom:

　　1> Compiling PL/SQL fails

　　2> ORA-06550: line %s, column %s:\n%s

　　3> PLS-00801: internal error [%s]

　　4> PLS-00801: internal Error [74901]

　　change:

　　NOTE ROLE: To reproduce: declare col number(2.0); begin null; end;

　　cause:

　　Incorrectly using a '.' instead of a ',' as a DECIMAL separator in the TYPE

　　declaration of the PL/SQL code leads to this error. For example: as var1

　　NUMBER (10.0); instead of var1 NUMBER (10,0);

　　Bug:

　　#637990

　　PLS-801[74901] OCCURRED WHEN USED NUMBER(2.0)

　　INSTEAD OF NUMBER (2,0)

　　fix:

　　This is still not fixed in PL/SQL Workaround: Change '.' to ',' and recompile

　　the package. Example: From: var1 NUMBER (10.0); To: var1 NUMBER (10,0);

　　fact:

　　PL/SQL 9 ，PL/SQL 8 ，PL/SQL 7

　　EXECUTE IMMEDIATE, DBMS_SQL

　　symptom:

　　1> Calling PROCEDURE in EXECUTE IMMEDIATE fails

　　2> ORA-00900: invalid SQL statement

　　cause:

　　When using EXECUTE IMMEDIATE (native dynamic SQL), the SQL server expects

　　a valid SQL statement. A PROCEDURE/FUNCTION call on its own is not a valid

　　SQL statement (because it is PL/SQL), so the SQL server does not understand

　　the statement. The SQL server can process a PL/SQL block, however.

　　fix:

　　Embed the call to the PROCEDURE in an anonymous block - i.e. put BEGIN ...

　　END around it - and submit the anonymous block instead

　　e.g.

　　CREATE OR REPLACE procedure exec_proc1 (arg1 number, arg2 varchar2)

　　IS

　　BEGIN

　　EXECUTE IMMEDIATE 'BEGIN proc1('||arg1||', '||arg2||'); END;';

　　END;

　　or

　　CREATE OR REPLACE procedure exec_proc1 (arg1 number, arg2 varchar2)

　　IS

　　plsql_block := 'BEGIN proc1('||arg1||', '||arg2||'); END;';

　　BEGIN

　　EXECUTE IMMEDIATE plsql_block USING 7788, 500;

　　END;

　　Reference:

　　PL/SQL User's Guide and Reference

　　goal:

　　How to verify the enabled roles for a session within a trigger or

　　PL/SQL routine

　　fact:

　　Oracle Server - Enterprise Edition

　　PL/SQL

　　DBMS_SESSION

　　fix:

　　The view 'session_roles' lists the roles that are currently enabled to the user.

　　When a PL/SQL routine, like a trigger, is executed, it is executed as the owner

　　of the routine. Therefor querying the 'session_roles' view within a PL/SQL

　　routine will not give the information of the executing user session.

　　It is not possible to retrieve a list of enabled roles for a session within a

　　PL/SQL routine. By using the DBMS_SESSION.IS_ROLE_ENABLED it is possible to

　　check whether a role is enabled or not.

　　Example:

　　create or replace procedure ptst is

　　begin

　　if dbms_session.is_role_enabled('TEST') then

　　dbms_output.put_line ('Role is enabled');

　　else

　　dbms_output.put_line ('Role is NOT enabled');

　　end if;

　　end;

　　/

　　Reference:

　　: Function

　　DBMS_SESSION.IS_ROLE_ENABLED

　　fact:

　　Oracle Server - Enterprise Edition 9.0.1

　　Oracle Server - Enterprise Edition 8.1.6.2

　　HP Alpha OpenVMS 7.3

　　HP Tru64 UNIX 5.1

　　compatible = 8.0.5

　　symptom:

　　1> Executing remote packaged PROCEDURE fails

　　2> PLS-00801: internal error [%s]

　　3> internal error [1407]

　　4> No local or remote trace files generated

　　cause:

　　The cause of this problem is still undetermined.

　　fix:

　　Verify the version of the remote database and set the COMPATIBLE parameter of

　　the remote database as close as possible to that of the local database.

　　In this case setting compatible initSID.ora parameter to 8.1.6 from 8.0.5 on

　　remote site solved the problem.

　　fact:

　　PL/SQL 9 ，PL/SQL 8 ，PL/SQL 7

　　symptom:

　　The following error occurs while inserting empty blob to return blob locators.

　　ORA-22990: lob locator cannot span transaction

　　cause:

　　The above sql statements were executed within a for loop.

　　After each fetch a call to dbms_lob.copy is made followed by commit within the loop.

　　Fetching accross commits is not allowed since it results to cursor invalidation

　　because the a COMMIT release any locks held by the session.

　　From Oracle 8 Server Application Developer's Guide, Chapter 6, Large Objects:

　　The insert statement automatically starts a transaction and locks the row.

　　Once it has occurred, the locator may not be used outside the current

　　transaction, since a COMMIT release any locks.

　　Therefore any fetch after the lock will result in ORA-22990: LOB locators

　　cannot span transactions.

　　fix:

　　It is not advisable to use a COMMIT inside a loop. Use commit after the

　　loop ends.

　　reference:

　　Oracle 8 Server Application Developer's Guide

　　fact:

　　PL/SQL 9 ，PL/SQL 8 ，PL/SQL 7

　　ORA-01001 results

　　symptom:

　　Preparing a cursor in a function, but executing it in another

　　1>. Prepare cursor in a function

　　2>. A function is called that opens the cursor and does a fetch.

　　3>. The function in step 2 is called again to do another fetch

　　and ORA-01001 results

　　cause:

　　What happened is that between calls to the function that does

　　the fetch the cursor cache is overwritten and the prepared

　　statement is being lost.

　　Workaround:

　　put the prepare, open, and fetch statements in one function.

　　fact:

　　JDBC OCI , pre-compiler

　　ORA-01001: maximum open cursors exceeded

　　symptom:

　　The error message “ORA-01001: maximum open cursors exceeded”

　　returned in a JDBC connection, even though the OPEN_CURSOR

　　(default = 50) has been increased.

　　cause:

　　The number of cursors one client can open at a time on a connection

　　is limited (50 is the default value).

　　If you don't close these cursors explicitly, you will get this error

　　eventually. Simply increasing the "OPEN_CURSORS" limit can help

　　you avoid the problem for a while, but that just hides the problem, not

　　solve it.

　　Workaround:

　　You do need to explicitly close the statement, by using the method

　　stmt.close() in order to close and freeup the cursors. It is your responsibility

　　to explicitly close out cursors that you no longer need.