I want to create a summary report table with a stored procedures. I cannot use views since there are too many records and I only want one record from a bunch of tables, it would be too slow. Oh I will have to pass a param to the stored procedure, the key actually.

Is there anyway for me to achieve this?

Thanks in advance!

-Mongo

You could do like this

CREATE GLOBAL TEMPORARY TABLE <table_name>
(
<column_name> <column_type>,
....
....
....
) ON COMMIT DELETE ROWS;
/

Only thing is this table remains in the DB unlike the SQL server.
Read more here:
http://download-west.oracle.com/docs...73.htm#2061433

Or try out using PL/SQL Collections..
Here is a sample
--------------------------------
/************************************************** ******* A PL/SQL table is a type of COLLECTION in Oracle.

A COLLECTION is an ordered group of elements, all of the same type
(for example, the grades for a class of students). Each element has a unique subscript that
determines its position in the collection. PL/SQL offers two COLLECTION types. Items of type TABLE
are either index-by tables (Version 2 PL/SQL tables) or nested tables (which extend the functionality of
index-by tables). Items of type VARRAY are varrays (short for variable-size arrays).

This sample gives an idea of working with COLLECTIONs of type TABLE.
You will grasp the following from the sample
1. Declare a COLLECTION of type TABLE containing OBJECTS
2. Populate(INSERT values) the COLLECTION from a table
3. UPDATE the contents of the COLLECTION
4. SELECT all the values from the COLLECTION and display them

I have used PL/SQL COLLECTIONs to convert the temp-tables in sybase to Oracle version
************************************************/

SET SERVEROUTPUT ON

-- a PLSQL table sample

-- baseball table
DROP TABLE tab_baseball
/

CREATE TABLE tab_baseball
(
ID INT,
DESCRIPTION VARCHAR2(20)
)
/

INSERT INTO tab_baseball VALUES(1,'YANKEES')
/

INSERT INTO tab_baseball VALUES(2,'METS')
/

INSERT INTO tab_baseball VALUES(3,'MARINERS')
/

INSERT INTO tab_baseball VALUES(4,'GIANTS')
/

DROP TYPE to_baseball
/

DROP TYPE obj_baseball
/

-- baseball object that holds one record of "baseball"
CREATE OR REPLACE TYPE obj_baseball IS OBJECT
(
ID INT,
DESCRIPTION VARCHAR2(20)
)
/

-- a PL/SQL table that contains more than one "baseball" objects
CREATE OR REPLACE TYPE to_baseball IS TABLE OF obj_baseball
/

DECLARE
-- a local variable of type "table of objects"
lto_baseball to_baseball := to_baseball();
BEGIN

/* insert the first 3 records from the "baseball" table into the PL/SQL table "to_baseball" */
FOR baseball IN (SELECT ID,DESCRIPTION FROM tab_baseball WHERE ID <= 3)
LOOP
lto_baseball.extend;
lto_baseball(lto_baseball.count) := obj_baseball(baseball.ID,baseball.DESCRIPTION);
END LOOP;

/* verify the number of records in the PL/SQL table "to_baseball" */
DBMS_OUTPUT.PUT_LINE('After First Insert : lto_baseball.COUNT = ' || lto_baseball.COUNT);

/* insert the remaining records from the "baseball" table into the PL/SQL table "to_baseball" */
FOR baseball IN (SELECT ID,DESCRIPTION FROM tab_baseball WHERE ID > 3)
LOOP
lto_baseball.extend;
lto_baseball(lto_baseball.count) := obj_baseball(baseball.ID,baseball.DESCRIPTION);
END LOOP;

/* verify again the number of records in the PL/SQL table "to_baseball" */
DBMS_OUTPUT.PUT_LINE('After Second Insert : lto_baseball.COUNT = ' || lto_baseball.COUNT);

/* Update the PL/SQL table "to_baseball" */
FOR li_loopidx IN lto_baseball.FIRST..lto_baseball.LAST
LOOP
lto_baseball(li_loopidx).DESCRIPTION := lto_baseball(li_loopidx).DESCRIPTION || ' team';
END LOOP;

/*
SELECTing from the PL/SQL collection is slightly different from the standard SELECT syntax.

SELECT <list of columns> FROM TABLE( CAST(<local_collection_variable> AS <collection_variable>) )

*/
/* print the whole PL/SQL table */
DBMS_OUTPUT.PUT_LINE('Print the whole PL/SQL table "to_baseball"');
FOR baseball IN (SELECT ID,DESCRIPTION FROM TABLE(CAST(lto_baseball AS to_baseball)))
LOOP
DBMS_OUTPUT.PUT_LINE(baseball.ID || '-----' ||baseball.DESCRIPTION);
END LOOP;

END;
/

--------------------------------
Read more here:
http://download-west.oracle.com/docs...colls.htm#1059

Hi matheshjp,

I haven't got a chance to try out your code, but just one quick question. Can I do:

create or replace GLOBAL TEMPORARY TABLE ();

I will be running the summary for each record as per user request. Like I don't need to delete the table, but I need to empty the rows in the table.

Thanks for your reply!

-Mongo

Sorry, I need to be more specific with my requirements. I don't think PL/SQL collection is suitable in my case.

Pre-Condition:
Table A: key_a, column_a1, column_a2, date_a3, etc..
Table B: key_b, key_a, column_b1, column_b2, date_b3, etc...
Table C: key_c, key_a, column_c1, column_b2, date_c3, etc...
Table D: key_d, key_a, column_d1, column_d2, date_d3, etc...

[pre]
Summary Report:

Activity Date column_a1 column_b2 ....
-------- ----- ---------- ----------
Table A date_a3 column_a1 null
Table B date_b3 null column_b2
Table C date_c3 null column_b2
Table D date_d3 null null
[/pre]

key_a is the link between tables. I will have to save the above summary in a table somehow so that Crystal Reports would be able to retrieve it. After generated the report, I will have to clean the temp table.

Interesting problme, no?

Thanks!

Global temp tables only hold data for the current transaction or session (up to you as to which) so only the current transaction/session can see its own data, any other users of the table will only see their data not yours. Of course this assumes you are generating and reading the data through a single session. If this is not the case then I would suggest using a normal table and putting some sort of session key so you know which data belongs to your session.

You are right, Alan. I just realized that. In fact, I don't think I would be able to pass the session from my Webapp to Crystal Reports and then to Oracle easily. So what I am going to do is to create a temporary table myself, and populate it before passing the key_a to Crystal Reports. Three steps to populate the temp table:

1).
in my webapp, I try a dummy update that pass the 'key_a' to the temp table

2).
then in my after update trigger, I empty the table first.

3).
then i call a insert procedure that populate the data in the temp table based on the key_a pass in (old:key_a).

will I get a mutating error if I do this. I don't have to empty the table per-say. But I definitely need to delete all the old records with the same key_a.

Thanks!

-Mongo