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

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