1

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.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Dantevip
  • 33
  • 3
  • 2
    I think you are looking for a `Global temporary table`. A `GTT` is session specific, and you could use it inside your `PL/SQL` program. However, it is not a good idea to create objects on the fly. You should create the table once, and use it per your business need. If you are looking for something different, then please elaborate your requirement. – Lalit Kumar B Jun 17 '15 at 15:01
  • 2
    You don't create temprorary tables in Oracle like in SQL Server. You use CURSORS. – tilley31 Jun 17 '15 at 15:31

1 Answers1

0

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

tom redfern
  • 30,562
  • 14
  • 91
  • 126
Dhimant Patel
  • 23
  • 1
  • 5
  • 1
    Thanks but my question was about creating a temp table into a stored procedure and return it as resultset referencing as a Crystal Report data source. – Dantevip Jun 24 '15 at 16:32