|
Subject:
|
Create and executing an oracle stored procedure
|
|
Posted By:
|
Venstar
|
Post Date:
|
1/8/2006 4:07:37 PM
|
Hi All,
I am new to Oracle. I am facing problem in executing the procedure. My requirement is to create a procedure which passes one input parameter and gets the stats data for that input value and should return an array or ADO.
Can somebody help me pls...
CREATE OR REPLACE procedure DB_Popup1 ( c_Val IN Schema1.Table1_stat_data.id%TYPE, cur_val OUT Schema1.Table1_stat_data.number%TYPE)is
begin
select distinct count(*) into cur_val from Schema1.Table1_stat_data
where TO_CHAR(Table1_stat_data.datetime,'dd-mon-yyyy') = TO_CHAR(SYSDATE -2,'dd-mon-yyyy')
AND id_cre = '1609' group by id having count(*) < 244;
end;
/
Thanks in advance,
Ven
|
|
Reply By:
|
prabodh_mishra
|
Reply Date:
|
1/10/2006 4:10:57 AM
|
Try using ref cursors..
Define a package with procedure and ref. cursor declaration.
Write the procedure in the package body.
It's the most convenient way to return a multidimensional
array like data...
PROCEDURE <proc name>
( var1 IN NUMBER,
CUR_1 IN OUT refCur_1
)
IS
BEGIN
OPEN refCur_1 FOR
<SELECT statement>
END;
|
