Hello,

I am slightly confused with the behavior of the HOLD statement.
If I have the following code:

  
SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEUSER = ON
SET XRETRIEVAL=OFF

TABLE FILE CONTACT
PRINT
     CONTACT.CONTACT.ID
ON TABLE NOTOTAL
ON TABLE HOLD AS TEST FORMAT SQLORA
END

I see the following when I run:

  
 11.29.01 BT  (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 11.29.01 BT  (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
 11.29.01 AE    SELECT
 11.29.01 AE   T1."ID"
 11.29.01 AE    FROM
 11.29.01 AE   CLIN01.CONTACT T1;
 0 NUMBER OF RECORDS IN TABLE=        0  LINES=      0
 11.29.01 AE    DROP TABLE TEST;
 11.29.01 AE    CREATE TABLE TEST( "FOCLIST" INTEGER  NOT NULL ,"ID" INTEGER
 11.29.01 AE   NOT NULL , PRIMARY KEY ("FOCLIST") );
 11.29.01 AE   SELECT T1."FOCLIST",T1."ID" FROM TEST T1 WHERE (T1."FOCLIST" =
 11.29.01 AE   :0001);

My understanding was that the code would actually produce a temporary table and not a permanent table.
However, when I remove the FORMAT SQLORA I don't see the create table and drop table commands.

Thanks,
Manish

This message has been edited. Last edited by: manish from curaspan,

Hi,

When HOLDing the data as native SQL type the tables are created in the DB permanently by default. You have to control the persistence of the tables while creating them. This again depends on the RDBMS and whether it allows to control persistence.
Here is the syntax --not sure SAME_DB is still working or you need to give specific DB name like SQLRA.

  
ON TABLE HOLD [AS filename] FORMAT SAME_DB [PERSISTENCE persistValue]

Since you are HOLDing it as SQLORA you see the appropriate create/drop commands. If you remove that, by default it is ALPHA format and so no corresponding SQL commands - also since there is no APP HOLD the ALPHA HOLD file is temporary.

If you do not want to create permanent tables in DB - FOCUS/ALPHA HOLD files are best for intermediate manipulation of data.

thanks
Sashanka

Seems Oracle doesn't support temp tables (session wise). At least in the version we have.

If two volatile tables are joined, would the joins be processed at the database level or at WF server?

This very much depends on the query.

If the FOCUS command cannot be translated into SQL, then it will be processed at WF level.

All depends on the adapter and what its capable of.

quote:

Originally posted by Waz:
Seems Oracle doesn't support temp tables (session wise). At least in the version we have.

What version of Oracle are you on? I believe Global Temporary Tables (GTT) came out in 8.1.
Temp Table and Global Temp Table are synonymous in Oracle.

Currently on 9.2

In my testing the tables are not dropped when the session is terminated.

quote:

Originally posted by Waz:
Currently on 9.2

In my testing the tables are not dropped when the session is terminated.

Correct, only the Rows in the Table are temporary - not the object itself. Because the table definition itself is not temporary, it persists between sessions until it is explicitly dropped. DDL can be performed as long as there are no sessions currently inserting into the GTT. Indexes are available on GTTs also. No recovery-related REDO is generated for DML.

 
14:23:15 ODST> create global temporary table temp (cod number) on commit preserve rows;

Table created.

14:23:31 ODST> insert into temp values (1);

1 row created.

14:23:44 ODST> commit;

Commit complete.

14:23:48 ODST> connect user/pass@orcl
Connected.
14:25:06 ODST> select * from temp;

no rows selected

14:25:17 ODST> desc temp;
 Name                       Null?    Type
 -------------------------- -------- ----------------
 COD                                 NUMBER

14:25:34 ODST> drop table temp;

Table dropped.

This message has been edited. Last edited by: globalwm,