I'm still not sure what this one does, but you have to be impressed by 11 nested CONCATs.
(And by the way, you also have to be impressed by the inventor of the CONCAT function who evidently considered two arguments sufficient, unlike, say LEAST, GREATEST, DECODE, COALESCE and BIN_TO_NUM. But not NVL. Who knows what goes through these people's heads.)
PROCEDURE ins_xyz ( p_xyz_id_out OUT NUMBER, p_input_array IN myarrayrectype ) IS BEGIN p_xyz_id_out := NULL; BEGIN INSERT INTO xyztab ( xyz_id, xyz_11, xyz_12, xyz_13, xyz_21, xyz_22, xyz_23, xyz_31, xyz_32, xyz_33, xyz_41, xyz_42, xyz_43, xyz_43_concatenated ) VALUES ( xyz_seq.NEXTVAL, p_input_array.xyz_11, p_input_array.xyz_12, p_input_array.xyz_13, p_input_array.xyz_21, p_input_array.xyz_22, p_input_array.xyz_23, p_input_array.xyz_31, p_input_array.xyz_32, p_input_array.xyz_33, p_input_array.xyz_41, p_input_array.xyz_42, p_input_array.xyz_43, SUBSTR( CONCAT( CONCAT( CONCAT( CONCAT( CONCAT( CONCAT( CONCAT( CONCAT( CONCAT( CONCAT( CONCAT( p_input_array.xyz_11 || ' ', p_input_array.xyz_12 || ' '), p_input_array.xyz_13 || ' ' ), p_input_array.xyz_21 || ' ' ), p_input_array.xyz_22 || ' ' ), p_input_array.xyz_23 || ' ' ), p_input_array.xyz_31 || ' ' ), p_input_array.xyz_32 || ' ' ), p_input_array.xyz_33 || ' ' ), p_input_array.xyz_41 || ' ' ), p_input_array.xyz_42 || ' ' ), p_input_array.xyz_43 ), 1, 512 ) ) RETURNING xyz_id INTO p_xyz_id_out; EXCEPTION WHEN OTHERS THEN NULL; END; END ins_xyz;
Thanks BB for this one, which she or he (I can't say more for witness protection reasons) sent me a while ago and I almost forgot about.
I didn't post it at the time because I couldn't understand what it did. Looking at it again though, that's all part of the fun. Here's part of the conversation we had about it:
Me: Thanks BB - love it. I'm slightly puzzled by p_input_array though. Is it an array?
BB: An array of records.
Me: Yikes. So what does the target table look like? I suppose each 'xyz_nn_' column must be a nested table.
BB: In the actual system they're parts of node tuples. xyz_11, xyz_12, xyz_13, all indicate "scores" for pairings of the first node with 1, 2, 3, respectively. Hard to explain without giving away too much about the system. However, they're scalars.
Me: Glad we got that cleared up. Can I say parts of node tuples without endangering your job at NASA?