Passing an oracle variable from a SQL statement to pipeline pilot

JB 2015-02-04

Hi,

Does the Pipeline Pilot Oracle ODBCs support the INSERT INTO and RETURNING commands, in this case returning results written to a variable in oracle

I’m inserting records into a table and need to return the primary key which is generated by the data base using auto increment. I’m using the following syntax

DECLARE primarykey NUMBER(38);

BEGIN

INSERT INTO CSCS_DEV.SYS_LEG2SUB

(CSCS_DEV.SYS_LEG2SUB.PAGE,

CSCS_DEV.SYS_LEG2SUB.SECTION,

CSCS_DEV.SYS_LEG2SUB.RETIRED,

CSCS_DEV.SYS_LEG2SUB.RETIREDCOMMENT,

CSCS_DEV.SYS_LEG2SUB.RECORD_COMMENT,

CSCS_DEV.SYS_LEG2SUB.IDLEGISLATION,

CSCS_DEV.SYS_LEG2SUB.IDSYS_SUBSTANCE,

CSCS_DEV.SYS_LEG2SUB.IDSYS_MARKUSH_REG,

CSCS_DEV.SYS_LEG2SUB.CREATEDBY,

CSCS_DEV.SYS_LEG2SUB.MODIFIEDBY,

CSCS_DEV.SYS_LEG2SUB.DATEMODIFIED)

VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT TO_CHAR(SYSDATE,'yyyy/mon/dd') FROM dual)

)

RETURNING CSCS_DEV.SYS_LEG2SUB.IDLEG2SUB INTO primarykey;

--dbms_output.put_line (primarykey);

END;

This is working in oracle directly, but when I run it in the pipeline pilot Insert component I cannot get it to output the variable primarykey. This is my preferred way of doing the query it means I always get the correct key. Is there a way to do this?

I’ve also tried a slightly different approach. In this case I use an Insert followed by a Select statement to pull back the last sequence for the primary key (this is a slightly less robust way as it feasible 2 closely timed updates could run in between the Insert and select)