Hi Folks.
I'm trying to do a seemingly simple insert of records into an Oracle Table and I just can't seem to figure it out.
I have a table component that is filtered by the user and contains multiple fields. I want to take a specific column from that table and use that to bulk insert the results into a new Oracle Table.
I'm using the GUI Resource with the Action type "Bulk insert records"
My array of records to insert is defined by
{{PLANNING_TABLE.data.PROJ_ID}}
When I run the preview I get a table with the header "recordsToInsert" and all of the PROJ_ID's to enter. Looks perfect.
When I go to run this I get the following.
- statusCode:422
- error:"Unprocessable Entity"
- message:"begin execute immediate 'insert into "XATORPC"."PROJ_PLAN" ("0", "1", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "2", "20", "21", "3", "4", "5", "6", "7", "8", "9") values (:1, :2, :3, :4, :5, :6, :7, :8, :9, DEFAULT, DEFAULT, DEFAULT, :10, DEFAULT, DEFAULT, :11, :12, :13, :14, :15, :16, :17)' using :1, :2, :3, :4, :5, :6, :7, :8, :9, DEFAULT, :10, DEFAULT, DEFAULT, :11, :12, :13, :14, :15, :16, :17; execute immediate 'insert into "XATORPC"."PROJ_PLAN" ("0", "1", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "2", "20", "21", "3", "4", "5", "6", "7", "8", "9") values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22)' using :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39; execute immediate 'insert into "XATORPC"."PROJ_PLAN" ("0", "1", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "2", "20", "21", "3", "4", "5", "6", "7", "8", "9") values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22)' using :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58, :59, :60, :61; execute immediate 'insert into "XATORPC"."PROJ_PLAN" ("0", "1", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "2", "20", "21", "3", "4", "5", "6", "7", "8", "9") values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22)' using :62, :63, :64, :65, :66, :67, :68, :69, :70, :71, :72, :73, :74, :75, :76, :77, :78, :79, :80, :81, :82, :83; execute immediate 'insert into "XATORPC"."PROJ_PLAN" ("0", "1", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "2", "20", "21", "3", "4", "5", "6", "7", "8", "9") values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22)' using :84, :85, :86, :87, :88, :89, :90, :91, :92, :93, :94, :95, :96, :97, :98, :99, :100, :101, :102, :103, :104, :105;end; - ORA-06550: line 1, column 355: PLS-00103: Encountered the symbol "DEFAULT" when expecting one of the following: ( - + case in mod new not null out continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe <an"
- data:null
queryExecutionMetadata:{} 5 keys
- estimatedResponseSizeBytes:2908
- resourceTimeTakenMs:107
- isPreview:false
- resourceType:"oracledb"
- lastReceivedFromResourceAt:1673032585947
- source:"resource"
Obviously it's an issue with the formatting.
I can make it work fine if I format the array as follows:
{{
[
{PROJ_ID:PLANNING_TABLE.data.PROJ_ID['0']},
{PROJ_ID:PLANNING_TABLE.data.PROJ_ID['1']},
{PROJ_ID:PLANNING_TABLE.data.PROJ_ID['2']},
{PROJ_ID:PLANNING_TABLE.data.PROJ_ID['3']},
{PROJ_ID:PLANNING_TABLE.data.PROJ_ID['4']},
]}}
Works like a charm, however it's manual and won't work because the number of items in the array is a constantly changing length.
I know I probably need to do something with the Transformer but I'm struggling.
Help?!
Thanks in advance.