Re: Glo temp table - when to use 'On commit preserve rows'?

dean wrote:
> CREATE GLOBAL TEMPORARY TABLE "FEB06"."TRAIN_EXPAND_ROUTE_INTERM"
> ("TRAIN_EXPANDED_ROUTE_I" NUMBER, "TRAIN_I" NUMBER,
> "P_VERSION_I" NUMBER, "ROUTE_ORDER_I" NUMBER, "KEY_S"
> VARCHAR2(1 byte), "LOCATION_I" VARCHAR2(17 byte),
> "OUTBOUND_RR_I" VARCHAR2(4 byte), "ARRIV_TZ_C" VARCHAR2(3
> byte), "CALC_ARRIV_T" NUMBER, "DEPT_TZ_C" VARCHAR2(3 byte),
> "CALC_DEPT_T" NUMBER, "CALC_DWELL_T" NUMBER,
> "OUTBOUND_SPEED_M" NUMBER, "DIST_FROM_ORIG_M" NUMBER,
> "FUEL_S" VARCHAR2(1 byte), "WORK_S" VARCHAR2(1 byte),
> "CREW_S" VARCHAR2(1 byte), "INSPECTION_S" VARCHAR2(2 byte),
> "LOCO_SERVICE_S" VARCHAR2(2 byte), "CUSTOMS_S" VARCHAR2(1
> byte), "REPORT_S" VARCHAR2(1 byte), "MECHANICAL_INSPECTION_S"
> VARCHAR2(1 byte), "STATION_TRACK_I" NUMBER,
> "OUTBOUND_TRACK_I" NUMBER, "MAX_CARS_M" NUMBER, "MAX_GROSS_M"
> NUMBER, "MAX_LENGTH_M" NUMBER, "TARGET_POWER_WEIGHT_M" NUMBER,
> "CUTOFF_OUTBOUND_T" NUMBER, "CUTOFF_INBOUND_T" NUMBER,
> "CUTOFF_ORIG_T" NUMBER, "FINAL_PROCESSING_T" NUMBER,
> "BLK_SWAP_CUTOFF_T" NUMBER, "CREW_DISTRICT_I" NUMBER,
> "CREW_LEAD_T" NUMBER, "USER_GROSS_M" NUMBER, "USER_LENGTH_M"
> NUMBER, "USER_POWER_M" NUMBER, "BLOCKED_TRACKS_I"
> VARCHAR2(255 byte), "DIST_M" NUMBER, "PENALTY_DIST_M" NUMBER,
> "AVERAGE_SPEED_M" NUMBER, "SPEED_LIMIT_M" NUMBER,
> "FUEL_CONSUMPTION_M" NUMBER, "POWER_WEIGHT_M" NUMBER,
> "TRACK_EFFICIENT_WEIGHT_M" NUMBER, "ACCELERATION_M" NUMBER,
> "DECELERATION_M" NUMBER, "TRAIN_ROUTE_I" NUMBER,
> "DIST_BACK_M" NUMBER, "SUBREGION_I" NUMBER,
> "INBOUND_TRAIN_DIRECTION_I" VARCHAR2(2 byte), "TRAIN_SET_I"
> VARCHAR2(1 byte), "SUPPLEMENTAL_WORK_ORDER_S" VARCHAR2(1
> byte), "RPT_TRAIN_ARRIVAL_S" VARCHAR2(1 byte),
> "RPT_CALL_TRAIN_S" VARCHAR2(1 byte), "RPT_WORK_ORDER_S"
> VARCHAR2(1 byte), "RPT_ADVANCED_MAKE_TRAIN_I" VARCHAR2(1
> byte), "RPT_DISPATCHER_DELAY_S" VARCHAR2(1 byte),
> "OUTBOUND_TRAIN_DIRECTION_I" VARCHAR2(1 byte)) ON COMMIT PRESERVE
> ROWS
>
> Its on either 9.2i or 10g
>
> ACT9 =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = mrc)
> )
> )
>
> Here's the issue. We use ADO on a Borland environment (windows)
> connecting to a windows server (in this case its 9.2i). I can insert
> rows into that table, and then select * from that table and there are
> no rows. If I add the ON COMMIT PRESERVE ROWS statement on the end of
> the ddl, it holds the rows until the end of the session.
>
> Cheers!
> Dean
>
>
>
> DA Morgan wrote:

>> dean wrote:
>>> Hello all,
>>>
>>> I was caught once again for leaving off the  'on commit preserve rows'
>>> part of the create global temporary table statement. After an hour of
>>> headscratching I realized that I had left this part off,, and as usual
>>> as soon as I inserted rows into a table, the disappeared into thin air
>>> (that is, even in the same session).
>>>
>>> Could someone explain this to me? It appears I do not need to 'commit'
>>> to re-use the (data in my session), even if I have on commit preserve
>>> rows. And if I don't have that, my data isn't even available during my
>>> session, even after I insert some rows into the table.
>>>
>>> Thanks for any help,
>>>
>>> Dean
>> What version of Oracle?
>> Post the DDL that created the table.
>>
>> What you describe is clearly impossible unless you are making a syntax
>> error or your front-end tool is using transient connections.
>>
>> Also post the connection string from your TNSNAMES.ORA.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org

I agree with Jim but there is one problem you should deal with immediately.

HOST = 127.0.0.1 This is not a good idea. The server should have a fixed IP address and you should use it.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org