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?
3 comments:
It must not do anything important, since no one cares whether it works or not:
"exception when others then null;
means never having to say you're sorry."
Could have had more CONCAT functions if they'd converted the '||' operator as well!
I'm really not sure what that function gives you that '||' doesn't already do in a neater, shorter and easier to read manner.
It's very strange that "concat" and "||" are used in the same expression.
But it's beautyful anyway :)
Btw, I believe you'll enjoy this exception handler:
when others then
rollback;
SERRTEXT := [long string of concatenated parameters]
insert into TR_DAG_FRM_RSV1_ERR_LOG( AUTHID, MSG_TEXT ) values ( user, SERRTEXT );
update TR_DAG_FRM_RSV1_ERR_LOG set MSG_TEXT = MSG_TEXT || R.QUERY where AUTHID = user;
commit;
raise;
end;
Post a Comment