CODEWITHSUNDEEP
sqloracle-databasestored-procedurescrystal-reports
J S
J S

Reputation: 49

Using a PL/SQL stored procedure in Crystal Report command object

I'd like to call a stored procedure from a crystal report command object using an Oracle direct connection. The stored procedure takes a refcursor and some parameters that can be passed from the report, but I'm not sure what the syntax should look like.

For simplicity, feel free to pretend that the stored procedure only takes a refcursor and nothing else. What should the syntax look like? I assume I need to declare the refcursor, call the SP, and then return the cursor.

I'm pretty unfamiliar with this stuff though, and I'm not actually sure how to return the cursor. I figure the first bit would look like:

VARIABLE Cursor refcursor

declare
begin 
MYSTOREDPROCEDURE(:Cursor);
end;
/

I'm not sure how to then return the cursor for Crystal Reports to use (2008/2011). I hope this was enough information.

Upvotes: 0

Views: 5276

Answers (1)

craig
craig

Reputation: 26262

You can't call a stored procedure from a command. You need to add it to the report in the same manner as you add a table (in the database expert). Moreover, the SP needs to be built a certain way to work with CR; specifically, it needs to return a REF CURSOR.

Example.

Upvotes: 1

Related Questions