Hi,
I am trying to insert data into a partitioned table which has a partition name based on one of the primary key columns of the table. e.g if the value of the key column is PN GBRTEST the partition name is PN_GBRTEST. So my insert statement should look like
insert into <table_name> partition (<partition_name_from_key>) values (<key>,'DATA1') etc
I am trying to generate the partition name dynamically while inserting using the Oracle Enterprise Stage. Any thoughts on how I will be able to achieve this? So far I've tried using the transformer but I get Insert Prepare Failed ORA-00928: missing SELECT keyword
I would be grateful for any pointers in this issue.
Thanks,
Hema
Inserting Data into Partitioned Tables
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi Ray,
Thanks for the response. I am not sure what you mean. The table on the Database is a partitioned table, and i am trying to insert values in a particular partition by deriving the partition name from a key column. I am able to insert data via Oracle enterprise stage if I hard code the partition name:
INSERT
INTO
#GP_SCHEMA#PS_GP_RSLT_ACUM PARTITION (PN_GBRTEST)
VALUES
(ORCHESTRATE.EMPLID, ORCHESTRATE.CAL_RUN_ID, ORCHESTRATE.EMPL_RCD, ORCHESTRATE.GP_PAYGROUP, ORCHESTRATE.CAL_ID, ORCHESTRATE.ORIG_CAL_RUN_ID, ORCHESTRATE.RSLT_SEG_NUM, ORCHESTRATE.PIN_NUM, ORCHESTRATE.EMPL_RCD_ACUM, ORCHESTRATE.ACM_FROM_DT, ORCHESTRATE.ACM_THRU_DT, ORCHESTRATE.SLICE_BGN_DT, ORCHESTRATE.SEQ_NUM8, ORCHESTRATE.SRC_SYS_ID, ORCHESTRATE.USER_KEY1, ORCHESTRATE.USER_KEY2, ORCHESTRATE.USER_KEY3, ORCHESTRATE.USER_KEY4, ORCHESTRATE.USER_KEY5, ORCHESTRATE.USER_KEY6, ORCHESTRATE.COUNTRY, ORCHESTRATE.ACM_TYPE, ORCHESTRATE.ACM_PRD_OPTN, ORCHESTRATE.CALC_RSLT_VAL, ORCHESTRATE.CALC_VAL, ORCHESTRATE.USER_ADJ_VAL, ORCHESTRATE.PIN_PARENT_NUM, ORCHESTRATE.CORR_RTO_IND, ORCHESTRATE.VALID_IN_SEG_IND, ORCHESTRATE.CALLED_IN_SEG_IND, ORCHESTRATE.SLICE_END_DT, ORCHESTRATE.LOAD_ERROR, ORCHESTRATE.DATA_ORIGIN, ORCHESTRATE.CREATED_EW_DTTM, ORCHESTRATE.LASTUPD_EW_DTTM, ORCHESTRATE.BATCH_SID)
I need to be able to parameterise the partition name based on CAL_RUN_ID so that data can be inserted into different partitions dynamically at runtime.
Hope this clarifies. Sorry if I haven't been able to explain this very well.
Regards,
Thanks for the response. I am not sure what you mean. The table on the Database is a partitioned table, and i am trying to insert values in a particular partition by deriving the partition name from a key column. I am able to insert data via Oracle enterprise stage if I hard code the partition name:
INSERT
INTO
#GP_SCHEMA#PS_GP_RSLT_ACUM PARTITION (PN_GBRTEST)
VALUES
(ORCHESTRATE.EMPLID, ORCHESTRATE.CAL_RUN_ID, ORCHESTRATE.EMPL_RCD, ORCHESTRATE.GP_PAYGROUP, ORCHESTRATE.CAL_ID, ORCHESTRATE.ORIG_CAL_RUN_ID, ORCHESTRATE.RSLT_SEG_NUM, ORCHESTRATE.PIN_NUM, ORCHESTRATE.EMPL_RCD_ACUM, ORCHESTRATE.ACM_FROM_DT, ORCHESTRATE.ACM_THRU_DT, ORCHESTRATE.SLICE_BGN_DT, ORCHESTRATE.SEQ_NUM8, ORCHESTRATE.SRC_SYS_ID, ORCHESTRATE.USER_KEY1, ORCHESTRATE.USER_KEY2, ORCHESTRATE.USER_KEY3, ORCHESTRATE.USER_KEY4, ORCHESTRATE.USER_KEY5, ORCHESTRATE.USER_KEY6, ORCHESTRATE.COUNTRY, ORCHESTRATE.ACM_TYPE, ORCHESTRATE.ACM_PRD_OPTN, ORCHESTRATE.CALC_RSLT_VAL, ORCHESTRATE.CALC_VAL, ORCHESTRATE.USER_ADJ_VAL, ORCHESTRATE.PIN_PARENT_NUM, ORCHESTRATE.CORR_RTO_IND, ORCHESTRATE.VALID_IN_SEG_IND, ORCHESTRATE.CALLED_IN_SEG_IND, ORCHESTRATE.SLICE_END_DT, ORCHESTRATE.LOAD_ERROR, ORCHESTRATE.DATA_ORIGIN, ORCHESTRATE.CREATED_EW_DTTM, ORCHESTRATE.LASTUPD_EW_DTTM, ORCHESTRATE.BATCH_SID)
I need to be able to parameterise the partition name based on CAL_RUN_ID so that data can be inserted into different partitions dynamically at runtime.
Hope this clarifies. Sorry if I haven't been able to explain this very well.
Regards,