Solved

Oracle PL/SQL Stored Procedure

Posted on 2014-09-18
20
715 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 74

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 74

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
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: 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 74

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 74

Expert Comment

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

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
 

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 35

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 35

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
clob to char in oracle 3 64
SQL Developer 6 62
Retreiving column names in Windows but not in Unix 11 61
join actual table rows based on the column 25 22
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

820 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