Stored Procedure in Oracle SQL Developer

Question

I'm using SQL Oracle to build a stored procedure. I'm trying to build a stored procedure of the SQL-query below

SELECT "Merch"."Id", "Order"."Id", "Order"."OrderExt"
FROM "Order"
Join "Merch" ON "Order"."MerchId" = "Merch"."Id"
WHERE "Merch"."Id" = 25 AND "Order"."OrderExt" = 'TC10045604'

I want to use 2 inparameters to compare "Merch.Id" and "Order.OrderExt". Im really new at SQL-Oracle and have a real hard time figuring out how to write this procedure.

One of the problems I have is how do a return a table with "Merch"."Id", "Order"."Id", "Order"."OrderExt" ?

Do I use a cursor in some way?

Can anybody help me out with this problem?

Justin Cave · Accepted Answer · 2012-05-07 15:08:44Z

You could write a stored procedure that had an OUT parameter that was a SYS_REFCURSOR

CREATE OR REPLACE PROCEDURE return_cursor( p_merch_id  IN "Merch"."Id"%type,
                                           p_order_ext IN "Order"."OrderExt"%type,
                                           p_rc       OUT sys_refcursor )
AS
BEGIN
  OPEN p_rc 
   FOR SELECT "Merch"."Id", "Order"."Id", "Order"."OrderExt"
         FROM "Order"
              Join "Merch" ON "Order"."MerchId" = "Merch"."Id"
        WHERE "Merch"."Id" = p_merch_id
          AND "Order"."OrderExt" = p_order_ext;
END;

It would be more natural, however, to have a stored function that returned a SYS_REFCURSOR

CREATE OR REPLACE FUNCTION return_cursor( p_merch_id  IN "Merch"."Id"%type,
                                          p_order_ext IN "Order"."OrderExt"%type )
  RETURN sys_refcursor
AS
  l_rc sys_refcursor;
BEGIN
  OPEN l_rc 
   FOR SELECT "Merch"."Id", "Order"."Id", "Order"."OrderExt"
         FROM "Order"
              Join "Merch" ON "Order"."MerchId" = "Merch"."Id"
        WHERE "Merch"."Id" = p_merch_id
          AND "Order"."OrderExt" = p_order_ext;
  RETURN l_rc;
END;

As a general matter of style, having case-sensitive table and column names is very, very rarely a good idea. And having table names that match Oracle reserved words like Order is even less likely to be a good idea. Forcing every developer to always use double quotes around every identifier and to always specify them in the proper case is going to lead to substantially more mistakes than if you simply used the default case-insensitive convention and avoided reserved words.

Ulises · Accepted Answer · 2012-05-07 15:41:02Z

2

Like you said you need to use 2 parameters so you can filter your data by Merch.Id and Order.OrderExt.

You could return the results using a SYS_REFCURSOR, for example:

 PROCEDURE MY_PROC 
                ( pOrderExt "Order"."OrderExt"%type, 
                  pMerchId "Merch"."Id"%type,               
                  recordSet OUT SYS_REFCURSOR )
        AS    
        BEGIN    
       OPEN recordSet FOR 
          SELECT "Merch"."Id", "Order"."Id", "Order"."OrderExt"
          FROM "Order"
          INNER JOIN "Merch" ON "Order"."MerchId" = "Merch"."Id"
          WHERE "Merch"."Id" = pMerchId AND "Order"."OrderExt" = pOrderExt;    
   END MY_PROC;

This is how you see results (make sure you look at the Out Variables tab in SQL Developer):

DECLARE
  pOrderExt "Order"."OrderExt"%type;
  pMerchId "Merch"."Id"%type;        
  recordSet OUT SYS_REFCURSOR;
BEGIN
  pMerchId := 25 ;
  pOrderExt := 'TC10045604';  

  MY_PROC (
    pMerchId => pMerchId,
    pOrderExt => pOrderExt,    
    recordSet => recordSet
  );
  :recordSet := recordSet; --<-- Cursor
END;

Edited: Added example for execution, made improvements as Justin Cave pointed out

edited May 7, 2012 at 15:41

answered May 7, 2012 at 15:04

Ulises

13.4k5 gold badges36 silver badges52 bronze badges

5 Comments

Justin Cave Over a year ago

If the SQL statement user473104 runs, all the double quotes are required-- "Merch" would appear to be a case-sensitive identifier. Passing in pMerchId as a varchar2 if "Merch"."Id" is a number may also lead to problems-- you're better off using the "Merch"."Id"%type

Ulises Over a year ago

I completely agree on using "Merch"."Id"%type. Could you please elaborate a little more on the double quotes? I don't see why they are needed in his case.

Justin Cave Over a year ago

If you enclose an identifier in double-quotes, Oracle interprets it as a case-sensitive identifier. That means that it will only match an object that was also created with a case-sensitive identifier and only if the casing of the object name in the data dictionary matches the casing of the object name in double-quotes. If the original SQL statement works, that means that the "Merch" table was created as CREATE TABLE "Merch"... and that every reference to "Merch" must include double-quotes and must be case sensitive

Ulises Over a year ago

good to know, thanks. I was thinking that if he used CREATE TABLE "Merch"... then Select Merch would work just fine since the case on both match.

Justin Cave Over a year ago

If you don't quote the identifier, Oracle automatically converts it to upper case before looking for a matching object name in the data dictionary (which is why SELECT * FROM merch is identical to SELECT * FROM Merch or SELECT * FROM MERCH). If you define an object with a case-sensitive identifier, you have to refer to it using a case sensitive identifier forever.

Collectives™ on Stack Overflow

Stored Procedure in Oracle SQL Developer

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related