Create temporary tables in Oracle stored procedure to show in Crystal Reports

Question

In SQL Server I can create stored procedures which creates a temp table, insert values into it, and then return a select from that temp table to be the result set for a composite Crystal Report.

I have no idea how to perform it in Oracle stored procedures.

I know I can create a string variable and then execute immediate. But then I don't know how to insert values, and that the result set will be the Crystal Report source.

Answer 1

You may try it using plsql procedure as follows.

CREATE PROCEDURE testRS (lcout OUT sys_refcursor) AS
  BEGIN
    OPEN lcout 
    FOR 
    SELECT object_name, object_type 
    FROM user_objects;
  END testRS;

sys_refcursor is a weak cursor, meaning it can point to any query, and no type is enforced.

To execute under sqlplus (similar API should be available under crystal report), you will need to define a sqlplus variable, which holds resultset from cursor inside the procedure.

-- Define sqlplus variable
SQL> var ncc refcursor;

-- Call to procedure.
SQL> exec TESTPKG.testRS( :ncc );

PL/SQL procedure successfully completed.

-- Display the resultset.
SQL> print :ncc;

Hope it helps,

Dhimant