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

x
?
Solved

Oracle stored procedure - Select statement problem

Posted on 2006-07-01
2
Medium Priority
?
1,339 Views
Last Modified: 2008-03-10
hi

I'm new to the oracle. I'm created the following SP in oracle but it gives error

CREATE OR REPLACE  PROCEDURE "SYSTEM"."SMS_SELECTEMPLOYEE"  is  
 BEGIN
   SELECT * from EMPTable
END;

plz  guide me how to return the all the records using oracle sp

thanks and regards
mk_mur

0
Comment
Question by:mk_mur
  • 2
2 Comments
 
LVL 54

Expert Comment

by:Ryan Chong
ID: 17023516
I think you need to create a package with a Cursor, try:

CREATE OR REPLACE PACKAGE MYPACKAGE AS
    TYPE my_cur IS REF CURSOR;
    FUNCTION getListFund RETURN my_cur;
    FUNCTION getListMain RETURN my_cur;
    FUNCTION getListGeo RETURN my_cur;
    FUNCTION getListSpecial RETURN my_cur;
    FUNCTION getLastUpdatedDate RETURN my_cur;    
END;
/


CREATE OR REPLACE  PROCEDURE "SYSTEM"."SMS_SELECTEMPLOYEE"
(p_cursor OUT MYPACKAGE.my_cur)
AS
BEGIN
    OPEN p_cursor FOR
    SELECT * from EMPTable;
END;
/
0
 
LVL 54

Accepted Solution

by:
Ryan Chong earned 150 total points
ID: 17023518
oops, should be simple as this....

CREATE OR REPLACE PACKAGE MYPACKAGE AS
    TYPE my_cur IS REF CURSOR;
END;
/


CREATE OR REPLACE  PROCEDURE "SYSTEM"."SMS_SELECTEMPLOYEE"
(p_cursor OUT MYPACKAGE.my_cur)
AS
BEGIN
    OPEN p_cursor FOR
    SELECT * from EMPTable;
END;
/
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

885 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