Solved

Oracle PL/SQL Stored Procedure

Posted on 2014-09-18
20
688 Views
Last Modified: 2014-09-20
I am  experienced in MS SQL Server,  but new to Oracle.

I need to create a simple stored procedure that executes a SQL statement which returns rows from a table.

In pseudocode:

create or replace procedure P_DBMTEST
(EmployeeNumber) AS ;

 BEGIN
      SELECT * FROM Employee
      WHERE  Employee.EmployeeNumber = Input parameter for the employee number.    
   END;
/

Then I need to run the procedure in a SQL statement.

P_DBMTEST(employee number):

Reference to an exammple would help.

Thanks,
0
Comment
Question by:Dovberman
  • 12
  • 4
  • 3
  • +1
20 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 40330677
this is a start, but it's not correct yet.

In oracle, you normally need to "do" something with the output of the query, you can't just select without a destination.

CREATE OR REPLACE PROCEDURE p_dbmtest(employeenumber IN employee.employeenumber%TYPE)
AS
BEGIN
    SELECT *
      FROM employee
     WHERE employee.employeenumber = p_dbmtest.employeenumber;
END;
/

if you have oracle version 12c then it is possible to generate an implicit result set but that's not normal and not possible in 11g and below.

What should happen with your query results?
0
 

Author Comment

by:Dovberman
ID: 40330727
I am using Oracle 10g R2.

The query results should be saved as a Cursor.

myCursor is ....

thanks for asking the right questions to verify my request.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40330765
try this...

CREATE OR REPLACE PROCEDURE p_dbmtest(
    employeenumber   IN     employee.employeenumber%TYPE,
    p_cursor            OUT SYS_REFCURSOR
)
AS
BEGIN
    OPEN p_cursor FOR
        SELECT *
          FROM employee
         WHERE employee.employeenumber = p_dbmtest.employeenumber;
END;
/


even better than SYS_REFCURSOR would be to create a package and declare an explicit cursor type and then have your cursor be of that type.
0
 

Author Comment

by:Dovberman
ID: 40330801
Thanks,

This gives me a good start. There is a meeting that I must go to now.

I will continue in about an hour.
0
 

Author Comment

by:Dovberman
ID: 40330821
The meeting was cancelled. I will reply in 10 minutes.

Thanks
0
 

Author Comment

by:Dovberman
ID: 40330857
This must be close.  There is only one error.

CREATE OR REPLACE PROCEDURE p_dbmtest(
     employeenumber IN employee.empno%TYPE,
     p_cursor OUT SYS_REFCURSOR
 )
 
 -- employeenumber is an input parameter whose data type is the data type
 -- defined in the employee table for the referenced column name.
 -- p_cursor is the output parameter representing the output when the
 -- procedure is run
 AS
 BEGIN
     OPEN p_cursor FOR
         SELECT *
           FROM employee
          WHERE employee.empno = p_dbmtest.employeenumber;
 END;
 /

! 11:36:22  The name in the text editor differs from the specified object name

What have I missed?

Thanks,
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40330876
that's not an error message from the database, it must be from whatever editor you're using.

I'm not sure what that message means
0
 

Author Comment

by:Dovberman
ID: 40330894
I got past that by creating rows for the table.

Now I need to call the procedure from a sql window.

should I declare a cursor is p_dbmtest(5) ?? 5 is an employee number.

What is the syntax?

Thanks
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40330906
declare
  v_cursor sys_refcursor;
begin
      p_dbmtest(5,v_cursor);
end;
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 250 total points
ID: 40330941
The concept of returning a row (or rows) from a cursor in a stored procedure is apparently very common in SQL Server, but this is definitely not the default behavior for Oracle stored procedures.  Oracle PL\SQL supports returning a "ref cursor" as ststuber suggested, apparently as a concession to non-Oracle programmers who are used to this functionality in non-Oracle systems.  This functionality was not in the earliest versions of PL\SQL.

Be aware that there are many differences between SQL Server and Oracle.  A few of the biggest ones are:
1. whether temporary tables are required, or not (in Oracle, usually not)
2. whether tables can/should be created dynamically in stored procedures (in Oracle, usually not)
3. how null values are used or referred to
4. how date values are used or referred to (in Oracle these can include the "time_of-day")
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Dovberman
ID: 40330947
It looks simple.

However:

declare
   v_cursor sys_refcursor;
 begin
       p_dbmtest(5,v_cursor);
 end;

[1]: PLS-00306: wrong number or types of arguments in call to 'P_DBMTEST'

CREATE OR REPLACE PROCEDURE p_dbmtest(
     employeenumber IN employee.empno%TYPE,
     p_cursor OUT SYS_REFCURSOR
 )

EMPNO   NUMBER(4,0) NOT NULL
SYS_REFCURSOR  is this a validcursor  type in Oracle 10g?
0
 

Author Comment

by:Dovberman
ID: 40330963
I just want to output a recordset by running a stored procedure.

What is the best way to do this?

declare
    ???

  begin
         send output as if I stated "SELECT colname1, colname2 FROM Table1 "

  end;
0
 

Author Comment

by:Dovberman
ID: 40330968
I am trying to replace a three page SQL statement with a stored procedure.
The three page statement is attempting if and case logic.

My gut feel is that a SQL statement that is this complicated needs to be done with a stored procedure.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40331004
"I just want to output a recordset by running a stored procedure."

As I indicated earlier, that is just not what Oracle does by default.

Yes, Oracle10 supports "ref cursors" in PL\SQL procedures, if you get all of the syntax correct.  I've never needed that functionality from PL\SQL, since the Oracle client tools I use do not use that PL\SQL functionality.
0
 

Author Comment

by:Dovberman
ID: 40331137
Even this simple example fails to compile on the computer I am usig.

ref: http://www.oraclecity.com/plsql-tutorial/oracle-create-or-replace-stored-procedure-example-basics/

CREATE OR REPLACE PROCEDURE p_procedure1 IS
 BEGIN
 

null;
 
END p_procedure1;

error: 12:48:16 PM  The name in the text editor differs from the specified object name
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40331166
And as sdstuber indicated earlier, that is *NOT* an Oracle database error.  That is an error from (in) the client tool you are using.
0
 

Author Comment

by:Dovberman
ID: 40331277
I am not surprised.  Client is using SQL Navigator 6.6, Struts, TOAD and who know what else.  
I was confronted with a 3 page SQL statement written by someone who is no nonger available.

This statement is not documented and ouputs a recordset.  I spent 3 days figuring out what the lines do and embedding comments.  i.e there are columns named Filename and Column_Name, NVLs wrapped around columns that are never null, etc.

Thanks for all your help.
0
 

Author Closing Comment

by:Dovberman
ID: 40331280
Thanks for al your help.  The issue is internal.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40331999
Can we help with the query as an alternative approach? (as a new question of course)

If doing that add both the full query and the explain plan (as text not image)
0
 

Author Comment

by:Dovberman
ID: 40334292
I  will edit the query to replace table and column names with generic names (Table1, etc). This is work on a Dept of Defense contract.

Thanks,
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now