?
Solved

How to Return Global Temp Table results to Oracle developer from Stored Proc?

Posted on 2011-04-30
7
Medium Priority
?
1,307 Views
Last Modified: 2013-12-18
First, I can't get this stored proc to work:

CREATE OR REPLACE PROCEDURE HR.TEST_GLB AS
BEGIN
  create global temporary table TEST_TABLE
    on commit delete rows
    as select * from HR.employees where 1 = 0;
  insert into TEST_TABLE
  SELECT * FROM TEST_TABLE;
END TEST_GLB;

Second, how do I call this to display results on screen or in file from Oracle SQL Developer?
0
Comment
Question by:OnAWingNAPrayer
  • 3
  • 2
  • 2
7 Comments
 
LVL 5

Accepted Solution

by:
morgulo earned 600 total points
ID: 35498352
First of all you should move create table statement outside procedure. Global tables stays in database like normal tables, only rows are deleted after session end.
Then you should use:
create global temporary table TEST_TABLE
    on commit preserve rows...

Open in new window

Rowsets in oracle are returned by ref cursors:
create type myrefcursor refcursor
/
CREATE OR REPLACE PROCEDURE HR.TEST_GLB(p_res out myrefcursor) AS
BEGIN
  ....
  OPEN p_res FOR SELECT * FROM TEST_TABLE;
END TEST_GLB;

Open in new window

To see results in sql developer open test window (right click on storedprocedure -> Test)
0
 
LVL 42

Expert Comment

by:dqmq
ID: 35498470
Morgulo is correct; the Create Global Temporary table command creates a DB structure that persists.  You only run it once and the database object can be used indefinately, thereafter.

As for the reference cursor, I'm not sure if that is relevant.


It's not clear from your example what you are trying to accomplish.  For example, this fragment just dupllicates every row of test table:

 insert into TEST_TABLE
  SELECT * FROM TEST_TABLE;

Maybe you meant this:

insert into TEST_TABLE
  SELECT * FROM HR.employees;
SELECT * from TEST_TABLE;




 
0
 

Author Comment

by:OnAWingNAPrayer
ID: 35498662
Morgulo,

I'm trying to create a temp table that will be session-specific for a Cognos report. I want to have as much processing handled in database rather than on the reports server thru the Framework Manager as possible.

Many people will be able to run this report at one time. There is a lot of calculations to get to the final result set. I originally tried to implement this in a view, but they kept defining more requirements. The end result was a three layer view. Horrible processing time. So, I thought that the logic could be processed on a temp table and functions and return the result set thru the stored proc.

By the way, I am in no way a PL SQL person as you might have guessed. So, my deadline is yesterday with very little clue as to what I'm supposed to do.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:OnAWingNAPrayer
ID: 35498715
dqmq,

You're correct. That was my intent. Woops! Also, I'm having the most basic problems. Where do I declare the myrefcuror? The create global temporary table TEST_TABLE
    on commit preserve rows...would be run from a sql editor and would include the insert into statement? Or would the procedure include the insert into statement so that it is refreshed when the proc is called?

Please give me baby steps! Thanks sooooo much!
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 400 total points
ID: 35498888
CREATE GLOBAL TEMPORARY TABLE can be run from the SQL prompt.  However, best practice is to include it with the installation DDL scripts for your application. What I mean to say is, treat it lke any other CREATE TABLE command for your application.

Do the insert into the TEMP TABLE in your session-specific proc. Also, since you are using the ON COMMIT PRESERVE ROWS CLAUSE, it's a good practice to precede the insert with DELETE * FROM yourtable (in case you run the proc mutliple times from the same session).  

As for passing the data back, I'm not sure if COGNOS will understand a ref cursor or if you just need to use SELECT * from temptable to return the result set.


0
 

Author Comment

by:OnAWingNAPrayer
ID: 35499524
Thanks to the two of you, I got the code to work. Basically, it takes the data from a view and insert into a cursor. The pl sql block will then, insert the cursor values into a global temp table after increasing salaries by 5%.

However, I can't seem to delete the rows before inserting into the global temp table. I'm not sure that will be necessary as the proc will be called when a report is run. The issue may be that the session remains open if the user chooses to re-run the report. I also tried to truncate. Any ideas?

DECLARE
  emp_curs     HR.emp_data.EMP_SALARY_VIEW_TYPE;
  emp_row      HR.AVGEMPSALARYPERMANAGER%ROWTYPE;
  EMP_SAL_INCR   NUMBER;
  OriginalSalary Number;
  INCREASEDSALARY NUMBER;
BEGIN
   --TRUNCATE TABLE HR.TEST_GLB_EMPSALARYTABLE;
   EMP_SAL_INCR := .05;
   OriginalSalary := 0;
   INCREASEDSALARY := 0;
  -- Open cursor, using variable:
  -- Insert Values from AvgEmpSalaryPerManager into the cursor
   HR.emp_data.INSERT_EMPSAL(emp_curs, EMP_SAL_INCR);
 -- Fetch and display data:
 LOOP
   -- Retrieve each row of data one at a time
   HR.emp_data.FETCH_EMPSAL(emp_curs, emp_row);
   EXIT WHEN emp_curs%NOTFOUND;
   OriginalSalary := EMP_ROW.AVGEMPSALARY;
   INCREASEDSALARY := EMP_ROW.AVGEMPSALARY + (EMP_ROW.AVGEMPSALARY * EMP_SAL_INCR);
   INSERT INTO HR.TEST_GLB_EMPSALARYTABLE
   (AVGEMPSALARY, EMPCOUNT, MANAGER, DEPARTMENT_NAME, JOBTITLE)
   VALUES
   (INCREASEDSALARY, EMP_ROW.EMPCOUNT, EMP_ROW.MANAGER, EMP_ROW.DEPARTMENT_NAME, EMP_ROW.JOBTITLE);
   DBMS_OUTPUT.PUT_LINE(emp_row.AVGEMPSALARY || '    ' || INCREASEDSALARY);
  END LOOP;
END;

***************************************

 PROCEDURE INSERT_EMPSAL (
    EMP_CV    IN OUT EMP_SALARY_VIEW_TYPE,
    EMP_SAL_INCR IN NUMBER
  ) IS
  BEGIN
    Open emp_cv for
    select * from HR.AVGEMPSALARYPERMANAGER;
  END INSERT_EMPSAL;
 
*****************************************

  PROCEDURE FETCH_EMPSAL (
   emp_cv   IN  EMP_SALARY_VIEW_TYPE,
   emp_row  OUT AVGEMPSALARYPERMANAGER%ROWTYPE
  ) IS
  BEGIN
    FETCH EMP_CV INTO emp_row;
  END FETCH_EMPSAL;
0
 
LVL 5

Expert Comment

by:morgulo
ID: 35501708
In my opinion You should consider creating report data in normal tables by job and query results by select statement.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question