Home » SQL & PL/SQL » SQL & PL/SQL » Starting with stored procedures (Oracle 9i, AIX)
Starting with stored procedures [message #351924] Fri, 03 October 2008 13:39 Go to next message
ajreynolds
Messages: 10
Registered: March 2005
Junior Member
Greetings all,

I have been asked to figure out how we can use stored procedures in our application (developed in Magic). Up to this point, we have just been executing SQL statements directly from the application. As a very simple example, say I have a table named WORKTBL that looks like this:

 Name                     Null?    Type
 ------------------------ -------- -------------
 WORKSTATION_NUMBER       NOT NULL NUMBER(3)
 WORKSTATION_TYPE         NOT NULL VARCHAR2(1)

Workstation_number is the unique index. Data in this table looks like:

WORKSTATION_NUMBER WORKSTATION_TYPE
------------------ ----------------
                 1 R
                 2 R
                 3 R
               200 S
               201 S
               202 S

I want to look up all of the records that match a specific type. The select statement that I would use would be:

select workstation_number,workstation_type from worktbl where workstation_type='S';


This would return the records and my program can then process them. I want to be able to pass in a parameter to specify which workstation type to look up. In MSSQL, the procedure for this is simple:

CREATE PROCEDURE dbo.sp_wstype
@wstype char
AS
select workstation_number,workstation_type from worktbl where workstation_type=@wstype
GO


How would I the same thing in Oracle?

Thanks,
Andy
Re: Starting with stored procedures [message #351938 is a reply to message #351924] Fri, 03 October 2008 15:25 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Have a look at cursors and pl/sql procedures in the oracle documentation at http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
Re: Starting with stored procedures [message #351954 is a reply to message #351938] Fri, 03 October 2008 16:48 Go to previous messageGo to next message
ajreynolds
Messages: 10
Registered: March 2005
Junior Member
Thanks. I've worked out a procedure that prints out the data when I execute it in SQL*Plus:
CREATE OR REPLACE PROCEDURE sp_wstype
(wstype varchar2)
AS
cursor wscursor is select workstation_number,workstation_type from worktbl where workstation_type=wstype;
BEGIN
for wsrec in wscursor
loop
   dbms_output.put_line(wsrec.workstation_number||'  '||wsrec.workstation_type);
end loop;
END;

It's probably not the most elegant way of doing it, but there it is...

What I need to figure out now is how to get the data back to my application in Magic. Any tips on how to stream data back from a procedure?

Thanks,
Andy
Re: Starting with stored procedures [message #351965 is a reply to message #351954] Sat, 04 October 2008 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As joy_division said it, cursor IS the way to do it.
Does Magic not handle cursor?

Regards
Michel
Re: Starting with stored procedures [message #351967 is a reply to message #351954] Sat, 04 October 2008 01:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The search term to use is REF CURSOR
You have to open a cursor in your stored procedure, and return a reference to it to your calling program. Such a reference is called a REF CURSOR.
Then you can fetch recrods from the cursor in your calling program.
Re: Starting with stored procedures [message #352114 is a reply to message #351967] Mon, 06 October 2008 04:00 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Have A Look at this:-

Cursor Expressions in PL/SQL


Regards,
Rajat Ratewal
Previous Topic: date recorded as 'dd-mon-0008' instead of 'dd-mon-2008'
Next Topic: How to pass a whole column as parameter?
Goto Forum:
  


Current Time: Fri Dec 09 03:42:15 CST 2016

Total time taken to generate the page: 0.15045 seconds