Re: Oracle temporary tables

[Roger Harris <RHarris@CSU.ORG>]

Philippe,
I have worked successfully with Oracle temp tables.  The key is that the
table structure is constant, it is just the data that is temporary.

*******************************************************************************************************************************************************************************
>From the Oracle 8i documentation:
Temporary Tables

In addition to permanent tables, Oracle can create temporary tables to hold
session-private data that exists only for the duration of a transaction or
session.

The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table which
can be transaction-specific or session-specific. For
transaction-specific temporary tables, data exists for the duration of the
transaction. For session-specific temporary tables, data exists for the
duration of the
session. Data in a temporary table is private to the session. Each session
can only see and modify its own data. DML locks are not acquired on the
data of the temporary tables. The LOCK statement has no effect on a
temporary table because each session has its own private data.
*******************************************************************************************************************************************************************************

So what I did was create the table in SQL*Plus then insert into the table
in a Begin-Sql/End-Sql section in my SQR report.  If you use ON COMMIT
PRESERVE ROWS in the create global temporary table statement the data is
preserved for the entire session and as noted above the data is private to
each session so more than one instance of the report can run simultaneously
without data conflicts.

Roger Harris




                        Philippe Godin
                        <philippe.godin     To:     SQR-USERS@list.iex.net
                        @VIDEOTRON.CA>      cc:
                        Sent by:            bcc:
                        "Discussion of      Subject:
                        SQR,
                        Brio
                        Technology's
                        database
                        reporting
                        language"
                        <SQR-USERS@list
                        .iex.net>


                        05/31/2001
                        12:26 PM
                        Please respond
                        to sqr-users






We're using SQR Workbench compiler from Scribe but as an editor we're using
Ultra-Edit32 (which I don't think the problem can be related to...) The
version of SQR is 4.0.3 and yes your quite good in french!
Hope this helps

Philippe

-----Message d'origine-----
De : Discussion of SQR, Brio Technology's database reporting language
[mailto:SQR-USERS@list.iex.net]De la part de Paul Hoyte
Envoyé : 31 mai, 2001 14:16
À : SQR-USERS@list.iex.net
Objet :


Hello Phillip:

If my French is still correct, it appears that you are having a page fault
occur.  Is this correct?  and if so, can you provide the configuration in
which you are executing your SQR programs? Thanks

Paul Hoyte
Sharidionne, Inc.
(248) 559-6868
www.sharidionne.com
phoyte@sharidionne.com

-----Original Message-----
From: Discussion of SQR, Brio Technology's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of Philippe Godin
Sent: Thursday, May 31, 2001 2:09 PM
To: SQR-USERS@list.iex.net
Subject:


Good day,

    This is my problem if anyone can help... I'm trying to create temp
tables in SQL Server and Oracle in order to make any further requests more
efficient. Those requests are quite complex and any select statement is not
enough for my needs. I've tried to use arrays but it makes the report a
whole lot longer to execute and processing more complicated. So this is it,
I've succeded with SQL Server 7.0 to acheive my goal by doing this:

Begin-Sql
Select whatever into #temptable from mytable where blablabla;
End-Sql

The problem is with Oracle (version 8.1.5 and up), since the supported
platforms have to be both SQL Server and Oracle.

I've did some research and the previous command wouldn't work. I found that
there is a way to create a temp table by doing this:

Create global temporary table mytable (
field1 varchar2(20) not null,
field2 number(2)
) on commit delete rows;

This statement works using SQL * PLUS but not from SQR by inserting the
command between Begin-Sql and End-Sql tags.

This is the result of executing this program:

SQRWT a causé une défaillance de page dans
 le module SQRWT.DLL à 0167:1003b9e0.
Registres :
EAX=00d4030c CS=0167 EIP=1003b9e0 EFLGS=00010206
EBX=00000000 SS=016f ESP=0070e468 EBP=00000000
ECX=00e91a60 DS=016f ESI=00e50530 FS=44f7
EDX=10070b8b ES=016f EDI=00000005 GS=0000
Octets à CS : EIP :
0f bf 01 c1 e0 05 8b 90 ec 1e 0b 10 85 d2 74 15
État de la pile :
0070fc04 00000000 00000000 00000054 00000001 40000000 656c6573 00007463
0054504c 00000004 00000000 00000080 00000186 000013e6 0147747f 0147747f

Anyone have any ideas or seen this before? Or is there any other way to
create a temp table in Oracle?

Thanks in advance
Philippe Godin