PL/SQL的小常识

　　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 oclearcase/" target="_blank" >ccurs 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.

原文转自：http://www.ltesting.net