creating stored procs from MS SQL to ORACLE 8i

[jermine]

i honestly do not know how to create procedures in ORACLE.
There's the IN and OUT... i cant seem to understand it.

in my mind i need a stored procedure in an MS SQL frame of mind.
============================
Create procedure GET_DATA

@Ctry_id varchar(50),
@Subj_id varchar(50),
@Agency_id varchar(50),
@Unit_measure_id varchar(50)

AS

BEGIN

******** HERE I DO THE FOLLOWING : *******
CREATE A TEMP TABLE
CREATE A CURSOR THAT LOOPS TO GET ALL THE DATA I NEED, INSERTS DATA TO A TEMP TABLE

*******************************************

close my_cursor
deallocate my_cursor
END

Select * from mytable
=============================
please pardon me for asking help on this. i really dont understand how its done in oracle.

 

[spenglerr]

First for returning a record set and not only one row you will have to use a (so called) cursor variable. You can then compile PL/SQL-procedures that return result sets into arrays instead of calling the procedure n-times which would be quite nasty and performance degrading. So here is the definition for a cursor variable (I do not know if you need it in your special case, but it's never bad to know it):

CREATE OR REPLACE PACKAGE pk_cursor
AS
TYPE allgCursor IS REF CURSOR;
END pk_cursor;
/


Now I will rewrite your routine a little bit. I will use a cursor variable for returning a record set (this is OUTPUT of the procedure => OUT) and I use three of your variable as pure INPUT (=> IN) for your procedure and one of your variables will get modified in the procedure and then given back to the program (so it is needed as an INPUT and will be given backk as OUTPUT => IN OUT). You will not have to specify length in the parameters, just in local declarations this is needed. I changed your comment to a real comment. And then there is a little bit how-to:


Create or Replace procedure GET_DATA
(
sel_table OUT pk_cursor.allgCursor
Ctry_id IN varchar2,
Subj_id IN varchar2,
Agency_id IN varchar2,
Unit_measure_id IN OUT varchar2
)
AS
i NUMBER;
c VARCHAR2(200); -- local declarations need the length
.....
BEGIN

/******** HERE I DO THE FOLLOWING : *******
I do not need to CREATE A TEMP TABLE
I just need to use A CURSOR THAT LOOPS TO GET ALL THE DATA I NEED,
I do not need to INSERTS DATA TO A TEMP TABLE
*******************************************/

-- now a cursor to get the data
OPEN sel_table FOR
select a.col1,a.col2,b.col1,b.col2,c.col1
from tableA a, tableB b, tableC c
where a.id=b.id AND b.no=c.no
and a.col3=Ctry_id
and b.col3=Subj_id
and c.col2=Agency_id
and c.col3=Unit_measure_id
order by a.col1,b.col1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
Unit_measure_id := 'no data found';
END get_data;
/

 

[jermine]

Hi spenglerr!

thanks for replying...
ill try what you've suggest...
cross you fingers that i get it right!
thanks again!