Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 764
  • Last Modified:

Oracle PL/SQL Stored Procedure

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
Dovberman
Asked:
Dovberman
  • 12
  • 4
  • 3
  • +1
2 Solutions
 
sdstuberCommented:
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
 
DovbermanAuthor Commented:
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
 
sdstuberCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
DovbermanAuthor Commented:
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
 
DovbermanAuthor Commented:
The meeting was cancelled. I will reply in 10 minutes.

Thanks
0
 
DovbermanAuthor Commented:
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
 
sdstuberCommented:
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
 
DovbermanAuthor Commented:
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
 
sdstuberCommented:
declare
  v_cursor sys_refcursor;
begin
      p_dbmtest(5,v_cursor);
end;
0
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
DovbermanAuthor Commented:
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
 
DovbermanAuthor Commented:
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
 
DovbermanAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
"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
 
DovbermanAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
DovbermanAuthor Commented:
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
 
DovbermanAuthor Commented:
Thanks for al your help.  The issue is internal.
0
 
PortletPaulCommented:
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
 
DovbermanAuthor Commented:
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 12
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now