PL/SQL的小常识
2024-07-21 02:34:36
供稿：网友
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.
