PL/SQL Oracle Frequently asked questions

Should one use PL/SQL or Java to code procedures and triggers?

Internally the Oracle database supports two procedural languages, namely PL/SQL and Java. This leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?".

Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9iDB supports native compilation of Pl/SQL code to binaries, and also you can find new PL/SQL features in Oracle 10g and 11g.

The following section briefly describes the difference between these two language environments:


PL/SQL:
• Data centric and tightly integrated into the database
• Proprietary to Oracle and difficult to port to other database systems
• Data manipulation is slightly faster in PL/SQL than in Java
• Easier to use than Java (depending on your background)

Java:
• Open standard, not proprietary to Oracle
• Incurs some data conversion overhead between the Database and Java type systems
• Java is more difficult to use (depending on your background)

How can one see if somebody modified any code?

Code for stored procedures, functions and packages is stored in the Oracle Data Dictionary. One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:

SQL> SELECT substr(OBJECT_NAME,1,40),
TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
STATUS
FROM USER_OBJECTS
WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';

It will as for date value put your spacific required date and check the result.

Enter value for check_from_date: 17-oct-2007

ObjectName CREATE_TIME MOD_TIME STATUS
------------------ --------------- --------------- -------
PROFITC 17-Oct-07 10:22 18-Oct-07 16:06 VALID
SAPCOSTCENTER3 12-Oct-07 11:40 18-Oct-07 15:16 VALID

How can one search PL/SQL code for a string/ key value?

The following query is handy if you want to know where a certain table, field or expression is referenced in your PL/SQL source code.

SQL> SELECT TYPE, NAME, LINE
FROM USER_SOURCE
WHERE UPPER(TEXT) LIKE '%&KEYWORD%';

It will as for KEYWORD value put your spacific required VALUE and check the result.

Enter value for keyword: DATAPOSTING

TYPE NAME LINE
------------ ------------------------------ ---------
PROCEDURE DATAPOSTING 7
PROCEDURE DATAPOSTING_DDO 6

* Further Questions will be available soon

How can I protect my PL/SQL source code?

PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.

This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available.


I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus, but will give an error in PL/SQL. Choose one of the following solutions:
• Grant direct access on the tables to your user. Do not use roles!
• GRANT select ON ddo.emp TO my_user;
• Define your procedures with invoker rights (Oracle 8i and higher);
• Move all the tables to one user/schema.

What is a mutating and constraining table?

"Mutating" means "changing". A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.

Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.

• A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger) .
• A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.