creating a stored procedure for dataset

Would someone send me a sample of a stored procedure that I can use as a template for creating a report dataset?

Find more posts tagged with

Comments

Hi johnsonDMG, 
 
This post from the devShare may help in setting up a stored procedure report. 
 
<a class='bbc_url' href='http://www.birt-exchange.com/devshare/designing-birt-reports/447-using-stored-procedures-with-birt/#description'>Using Stored Procedures with BIRT - Tutorials - BIRT Exchange</a> 
 
Hope this helps.

johnsonDMG

What I needed was the specifics on how to create the Oracle stored procedure so that I could create a dataset. That part is left out of the documentation you are referring to. I'm a SQL Server developer... and I wasn't quite sure the correct structure needed to be.

mwilliams

johnsonDMG, 
 
You did get it to work though? If not, or if anyone else looks at this thread for help on this, you can find examples of Oracle stored procedures and how to create them at the following links: 
 
<a class='bbc_url' href='http://www.oracle.com/technology/sample_code/tech/java/jsp/oracle9ijsp.html'>Oracle9i Java Stored Procedure Samples</a> 
 
<a class='bbc_url' href='http://www.devshed.com/c/a/Oracle/Oracle-Stored-Procedures/'>Oracle Stored Procedures</a>

johnsonDMG

i saw both of those documents... the Oracle 9i example was a skeleton (very vague) and wasn't much help. I finally used a document that I used when I was creating sp's for crystal. I came up with the following sp, and it worked.

===============================
package example
========================

create or replace PACKAGE Test_Package
AS TYPE Test_Type IS REF CURSOR RETURN PA_CPTY%ROWTYPE;
END Test_Package;

==================
stored procedure: /* Test_Package body
===================

create or replace
PROCEDURE Test_Procedure002 (Test_Cursor IN OUT Test_Package.Test_Type,
Test_Parameter IN PA_CPTY.CPTY_ID%TYPE
)
AS
BEGIN
OPEN Test_Cursor FOR
SELECT *
FROM PA_CPTY
WHERE PA_CPTY.CPTY_ID = test_parameter;
END Test_Procedure;
/

===================
test script:
==================

SET SERVEROUTPUT ON
DECLARE
test_cursor test_package.test_type;
resultset test_cursor%rowtype;
begin
test_procedure002(test_cursor,'ADS.IDO.016');
if not test_cursor%isopen then
dbms_output.put_line('the cursor is not open');
else
dbms_output.put_line('the cursor is open');
end if;
fetch test_cursor into resultset;
while test_cursor%found loop
dbms_output.put_line(resultset.cpty_id || ', ' || resultset.cpty_desc );
-- dbms_output.put_line(resultset.cpty_desc);
fetch test_cursor into resultset;
end loop;
end;
/

this is the detail that I needed with regards to creating the refcursor;

And also for this sp I call it this way in BIRT:

{call PLATEAU58.TEST_PROCEDURE002(?,?)}

THEN I modified the parameter attributes in BIRT :

Test_Cursor as Output and "linked to.." None
CPTY_ID IN (input parm) "linked to.." CPTY_ID ( the dataset field).

...and it works

mwilliams

Thanks johnsonDMG!

Jimmy

package:
CREATE OR REPLACE PACKAGE apps.xx_bean_pk AS

TYPE test_cursor_type IS REF CURSOR RETURN schema.tableName%ROWTYPE;

PROCEDURE test1(
p_test_cursor_type OUT test_cursor_type,
p_param1 IN NUMBER);
END;
package body:
CREATE OR REPLACE PACKAGE BODY apps.xx_bean_pk AS
PROCEDURE test1(
p_test_cursor_type OUT test_cursor_type,
p_param1 IN NUMBER) IS
BEGIN
OPEN p_test_cursor_type
FOR SELECT *
FROM tableName
where fieldName=p_param1;

END;
END;
birt's Query
{call apps.xx_bean_pk.test1(?,?)}
Parameters:
p_test_cursor_type ,Direction:Output
p_param1 ,Data Type:Decimal,Default:4

database version:oracle 8i
eclipse verson:2.0.0.CR1
Birt Version:2.3.1

But I have a question, when i Run birt's preview results ,it appear:
Cannot fetch ths next row.
Cannot move sown to next row in the result set.
SQL error #1: ORA-08103: object no longer exists

what's happen ?? does Birt support oracle 8i database ?? if i use 9i db to run it, it's ok
Can anyone help me ?? Thks !!

bhanley

What driver are you using to connect? Are you using native drivers? If so I would cross check your drivers to ensure they support Oracle 8.

Jimmy

<blockquote class='ipsBlockquote' data-author="bhanley">What driver are you using to connect? Are you using native drivers? If so I would cross check your drivers to ensure they support Oracle 8.</blockquote>
 
I change the driver ,it can work now,thks, but speed is not fast, 
under is my driver and driver info: if you have another 8i driver , 
would you email to me ?? thks 
oracle.jdbc.OracleDriver(1.0) 
oracle.jdbc.driver.OracleDriver(1.0) 
 
Anyway, it can work now , I can do my work continue ,thanks again !!