| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Glo temp table - when to use 'On commit preserve rows'?
Ok I see, ADO doing its stuff as usual.
As for the server, this is just my home PC right now, not any production machine or anything important.
Thanks for the help,
Dean
DA Morgan wrote:
> 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
>
>
>
 |
 |