Stored procedures in PL/SQL

Learn about Oracle PL/SQL stored procedures, such as the stored procedure definition and stored procedure examples in this PL/SQL tutorial for beginners and experts.

In this section, learn about Oracle PL/SQL stored procedures, such as the stored procedure definition and stored

procedure examples. Also, learn whether you can create database objects in stored procedures and invoke a stored procedure in a SELECT statement. 

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 
 

 

  Stored procedures in PL/SQL  

More on using Oracle PL-SQL

Get an expert's explanation of PL-SQL to_date and to_char functions

Learn to solve a PL-SQL error and create dynamic tables

Find out how Oracle DBAs can benefit from Dell Toad

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:

  1. I want a stored procedure in Oracle, which will return multiple rows. What should be the code?
  2. I also want to call this stored procedure from VB.NET and display this record in this platform.
  3. In the DBMS_SQL package, what does NATIVE stand for?

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.

 

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.

 

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;
/

 

Q: I have a situation to call the same stored procedure from inside the same procedure. For example, let's say I have a procedure called ConverSubPlans with some parameters. I am doing some validations inside that procedure and based on those, I need to call ConverSubPlans once again within ConverSubPlans. Is it advisable? 

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close