Table of contents:
Requires Free Membership to View
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
| Stored procedures in PL/SQL |
A PL/SQL program that is stored in a database in compiled form and can be called by name is referred to as a stored procedure. A stored procedure can be shared by a number of programs. Stored procedures are helpful in controlling access to data, preserving data integrity ad improving productivity. Browse through these tips to learn more about using PL/SQL stored procedures in Oracle, including stored procedure errors, PL/SQL stored procedure examples and more:
Get an introduction to stored procedures for Oracle and learn how to create a PL/SQL stored procedure that uses REF CURSORS in this step-by-step example from Oracle's Database 2 Day + .NET Developer's Guide.
Read an example of an Oracle stored procedure that can be used to return multiple rows.
Q:
Expert Greg Williams:
1) There are many types of procedures that you can use. Here is an example of a REF CURSOR:
create or replace procedure employee_sel(
cv_results in out sys_refcursor)
is
begin
open cv_results for
select first_name, last_name
from employee;
end;
where sys_refcursor is a type of REF CURSOR type 2) Try calling this procedure in a select statement in VB.NET. Since VB.NET is not my strong suit, this is all of the help I can give you on VB.NET.
3) Dbms_sql.native is a mandatory parameter for DBMS_SQL.PARSE.
Oracle8i introduced native dynamic SQL, an alternative to DBMS_SQL. Using native dynamic SQL, you can place dynamic SQL statements directly into PL/SQL blocks. In most situations, native dynamic SQL can replace DBMS_SQL. Native dynamic SQL is easier to use. For more info on dynamic SQL statements see Note:198306.1 at metalink.oracle.com.
- Learn how to find out which stored procedure is currently running for a session.
Q: How can I find out which stored procedure is currently running for a session?
Expert Brian Peasland: The answer is to really find out which SQL statement a session is currently running. Stored procedures will appear as an SQL statement. To do this, we'll use the following query:
SELECT a.sqltext FROM v$session s, v$sqlarea a WHERE s.sql_address = a.addr AND s.sql_hash_value = a.hash_value AND s.username='MYUSER';
In my example, I limited this to just queries issued by MYUSER. But you can do other limitations on the V$SESSION view as well.
- Find out how to make a stored procedure to autotransfer data between Oracle servers.
Q: I want to ask you how to make a stored procedure to autotransfer updated data from a remote server to a central server. Can I autotransfer using stored procedures?
Expert Brian Peasland: Basically, you need to set up a database link and have your stored procedure insert data through the database link. Chapter 29 of the Oracle 10g Administrator's Guide discusses this topic.
Under the section titled "Schema Object Name Resolution," the documentation shows an example of using an INSERT statement in the db link. Your stored procedure can contain this sort of INSERT statement.
- Is it possible to invoke a stored procedure in a SELECT statement?
Q: How do you invoke a stored procedure in a SELECT statement?
Expert Brian Peasland:
You cannot invoke a stored procedure in a SELECT statement. However, you can call a function in a SELECT statement. The function can be a wrapper for the stored procedure. For instance, assume that I have a stored procedure called FOO. Let's create a wrapper function BAR that calls the stored proc:
CREATE FUNCTION bar () RETURN number AS BEGIN foo(); RETURN 1; END; /
Now I can use the function above to execute the stored proc in my SELECT statement:
SELECT bar() FROM dual;
Q: How do you create a link between two databases inside a stored procedure?
Expert Brian Peasland: Normally, one does not create database objects inside stored procedures. But on the rare occasion that you do have this requirement, you can use the EXECUTE IMMEDIATE command to run the CREATE DATABASE LINK command similar to the following:
BEGIN EXEC IMMED 'create database link db_link connect to foo identified by bar using ''tns_entry'''; END; /
- Read this sample code for calling a stored procedure from the same stored procedure in Oracle.
Expert Brian Peasland: You sure can call a procedure from within that same procedure. Such an operation is called "recursion". One procedure recursively calls itself. The big thing to keep in mind is that you need an ending point otherwise the recursive calls will be infinite and never stop. A common recursive procedure or function is to compute the factorial of a number. N factorial (written N!) is defined as N*(N-1)*(N-2)*...*2*1. This also means that N! = N*(N-1)!. So a recursive function is born. Such a recursive function would appear as follows:
CREATE FUNCTION fact (n NUMBER)
RETURN NUMBER IS
returnVal NUMBER;
BEGIN
IF (n = 1) THEN
returnVal := 1;
ELSE
returnVal := n*fact(n-1);
END IF;
RETURN returnVal;
END;
/
Notice that in the above example, I have a terminating condition (n=1). If this condition is not met, then the function is called again. The same concept applies to procedures. But ensure that you have a terminating condition.
This was first published in August 2009
Join the conversationComment
Share
Comments
Results
Contribute to the conversation