In this section, learn about using Oracle PL/SQL functions and triggers, such as how to import a trigger and how and when to use a PL/SQL function. Also learn how to use ref cursors and import triggers from a schema.
TABLE OF CONTENTS:
- Define PL/SQL and understand PL/SQL basics a>
- PL/SQL datatypes in Oracle
- PL/SQL functions and triggers in Oracle
- Stored procedures in PL/SQL
Functions are PL/SQL stored programming units, providing a way to hide implementation details in a programming unit. Functions can be used as right operands in PL/SQL and be called from SQL statements, while procedures cannot. (From Chapter 2, Oracle 11g: PL/SQL Basics, from the book Oracle Database 11g PL/SQL Programming by Michael McLaughlin
Here are some tips from SearchOracle.com on using PL/SQL functions and procedures in Oracle:
- Learn when to use functions instead of stored procedures in this expert response:
Q: Is there any guideline as to when I should use a procedure instead of a function? For example, for returning cursors, what's the advantage of using one over another?
Expert Karen Morton: I use functions to return computed values. I can call a function in a SELECT list (for example: SELECT my_function() FROM dual ;) as well as in PL/SQL.
I use procedures to return cursors or to do any type of procedural processing (step by step) where I don't necessarily care about returning a value, only that the steps happen successfully or not.
Q: I drop and create an existing table in PL/SQL proc using EXECUTE IMMEDIATE. Later I declare a ref cursor, to process data from the table created. The problem is, the procedure executes correctly, but after execution the procedure becomes invalid. How can I solve this issue?
Expert Dan Clamage: It's a really bad idea to drop and recreate a table from an application, for a number of reasons. The biggest reason is because it's a relatively expensive operation. If this is part of a data mart or data warehouse load, then it's not so bad – there are other more expensive processes going on there… (read the full response).
- Get tips on importing/exporting wrapped PL/SQL procedures.
Q: We are in the process of upgrading from Oracle 8.0.5 to Oracle 9i. We have some PL/SQL procedures that are wrapped. When these are exported from an Oracle 8 database the dump file will import fine into the Oracle 9i database. When the Oracle 9i database is subsequently exported, there is a problem re-importing it. The import gets to the point where it is importing the stored procedures, then the memory usage goes through the roof and eventually the machine runs out of resource. My DBA tells me the effect happens because the procedures are wrapped.
Expert Brian Peasland: If I ran into this problem, I'd go back to my source code that was used to create the wrapped procedures. Hopefully your company has these documented somewhere. Just drop the procedures and recreate them, wrapped of course.
Alternatively, you can run import with SHOW=Y, FULL=Y, and LOG=filename. This will dump the DDL commands to the logfile. Open this logfile up and you can see the DDL commands to create your stored procs. Save this DDL in a script file and see what happens when you run it in SQL*Plus manually.
- See why one reader is not seeing any output statements from a PL/SQL procedure:
Q: I tried a few examples given in Oracle complete reference book. Whenever I run the examples of PL/SQL procedures using DBMS_LOB or DBMS_OUTPUT package, I do not get any output statements at all. It just quits saying PL/SQL procedure successfully completed. Do I need to turn ON something to see the output?
Expert Karen Morton: DBMS_OUTPUT.PUT_LINE relies on the SQL*Plus command SET SERVEROUTPUT ON in order to display the output. The default for SERVEROUTPUT is OFF so that's why you're not seeing anything except the successfully completed message. Turn it ON, and you'll be all set. By the way, you'll need to set it ON every time you start SQL*Plus as the settings always revert default.
- Learn why you may be getting the ORA-01422 error when procedure returns more than one row:
Q: I have an Oracle procedure that returns one or more rows depending upon the PO ID input into the SQL statement. When there is greater than one row returned I get the ORA-01422 error. How do I recode this procedure to allow for one to many returned rows of data? The PO ID (input) will be passed into the proc by another program. I have hardcoded the PO ID ('0700185') for testing pursposes. The proc is supposed to return all vouchers associated with this PO. There may be one voucher, there may be five vouchers.
Expert Greg Williams: The procedure you have created is using a SQL that should only be used if you are expecting to return only a single row. If you are expecting to return more then one row, you should use a cursor… (Read the full response and sample code).
A PL/SQL trigger is a set of statements that automatically "fires off" an action when a specific operation, such as changing data in a table, occurs. Triggers are used to preserve data integrity by consistently checking on or changing data.
According to Oracle's PL/SQL Language Reference, when a trigger is fired in response to an event, that event is associated with a table, a view, a schema, or the database, and is one of the following:
- A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
- A database definition (DDL) statement (CREATE, ALTER, or DROP)
- A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)
Read the reference manual for examples and detailed instructions on how to create triggers in PL/SQL.
Here is a list of tips and tricks from SearchOracle.com to help you work with PL/SQL triggers.
- Find out whether it's possible to import a trigger from another schema.
Q: Some of the triggers are deleted from one schema, so I want to import a trigger from another schema. Is it possible in Oracle?
Expert Brian Peasland: If you are using Oracle 9i or 10g, then you can go to the source schema and reverse engineer the DDL to re-create the trigger. Simply issue the following query in SQL*Plus in the source schema:
SELECT DBMS_METADATA.GET_DDL('TRIGGER','TRIG_NAME','OWNER') FROM dual;
You will have to supply the trigger name and the trigger owner. The output from this command can be saved to a text file and used to re-create the trigger.
Can you create a trigger to track user password changes in table?
- Find out if it's possible to write a procedure or function inside a trigger.
Q: Is it possible to write a procedure or function inside a database trigger, and vice versa?
Expert Greg Williams: Yes. You can call a procedure or function from a database trigger.
And no. You cannot trigger (fire) a database trigger from a procedure or function. There are three types of triggers: 1) DML -- fired by a DML statement acted upon a table; 2) Instead-of -- acts on views and is executed instead of the DML statement that fired it; 3) System -- fired when a system event occurs, such as startup or shutdown.
Q: How do you call an Oracle stored procedure in a trigger?
Expert Brian Peasland: This is a very easy task. Let's assume I have a stored proc titled DO_WORK and I wish to call it in a trigger. In the trigger, I might have code that looks like the following:
BEGIN DO_WORK(); END; /
You can add parameters to the proc call as well.
BEGIN DO_WORK(param1,param2); END; /