sql server equivalent procedure in Oracle

[reenaroy]

Hi I am a Coldfusion + Sql Server Programmer. Now I am working with Oracle
which is new to me.

I want to know how to create multiple recorset through oracle procedure
eqiuvalent to SQL server procedure:

create procedure test
(
@id number
)
as
select * from table1 where id=@id
select * from table2 where id=@id
select * from table3 where id=@id



CF code to call sql server procedure is

<cfstoredproc procedure = " test" dataSource = "#datasource#" username =
"#dbaccount#" password = "#dbpassword#">
<cfprocparam type = "IN" CFSQLType = CF_SQL_number dbvarname="@ID" value =
"#ID#">
<cfprocresult name = "rs1" recordset="1">
<cfprocresult name = "rs2" recordset="2">
<cfprocresult name = "rs3" recordset="3">
</cfstoredproc>

[CF_Oracle]

Use 3 stored procedures for each table and run them consequently or
create one PL/SQL procedure using IF THEN structure (one for each table) and
pass additional IN parameter from CF page on which value condition will be
based. In cf run this procedure 3 times with values of new parameter values
you need.

[CF_Oracle]

Simplified code (more elegant solution) of Oracle stored procedure:
CREATE OR REPLACE PROCEDURE test(p_ID IN NUMBER, p_table_name IN VARCHAR) AS
BEGIN
SELECT * FROM p_table_name
WHERE iID = p_id
END;

In calling page:
<cfstoredproc procedure = " test" dataSource = "#datasource#" username =
"#dbaccount#" password = "#dbpassword#">
<cfprocparam type = "IN" CFSQLType = CF_SQL_number
variable = "p_ID"
value = "#p_ID#">
<cfprocparam type = "IN" CFSQLType = CF_SQL_varchar
variable = "p_table_name"
value = "#p_table_name#">
</cfstoredproc>

[paross1]

You need to use reference cursors in your stored procedure to pass your results
back. Since you have to declare the reference cursor as a REF CURSOR, you need
to declare it in a PL/SQL package specification, and your stored procedures
need to be in a PL/SQL package. I have attached an example of the package and
package spec declaration, with the procedure declaration as well.

You can use the same ColdFusion code that you posted.

Phil


CREATE OR REPLACE PACKAGE test_pkg IS

TYPE ref_cur_type
IS REF CURSOR;

PROCEDURE test_sp (in_id IN NUMBER, out_res1 OUT ref_cur_type,
out_res2 OUT ref_cur_type,out_res3 OUT ref_cur_type);

END test_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_pkg IS

PROCEDURE test_sp (in_id IN NUMBER, out_res1 OUT ref_cur_type,
out_res2 OUT ref_cur_type,out_res3 OUT ref_cur_type)
IS

BEGIN

OPEN out_res1
FOR
SELECT *
FROM table1
WHERE id=in_id;

OPEN out_res2
FOR
SELECT *
FROM table2
WHERE id=in_id;

OPEN out_res3
FOR
SELECT *
FROM table3
WHERE id=in_id;

END test_sp;

END test_pkg;
/

[reenaroy]

Thanks Paross,

you are right. Procedure is working perfectly.

Only one thing I want to add.

It should be resultset in place of recordset in these lines
<cfprocresult name = "rs1" recordset="1">
<cfprocresult name = "rs1" recordset="1">
<cfprocresult name = "rs1" recordset="1">
I know this was my fault :(