Skip to main content

Oracle Stored Procedure invocation raises "ORA-01007: variable not in select list"

Author: m.koessldorfer@tme.co.at (mkoss)

Hi Team, I am using a Windows XP Client and a Uniface 9.4 server on Linux, DB is Oracle 11g. Usecase is to call an Oracle Stored Procedure in order to fill up an entity with data. Stored Procedure (Package P_TEST) is defined like this PROCEDURE HUGO( PID IN VARCHAR2, OUTPARAM OUT SYS_REFCURSOR) AS BEGIN OPEN OUTPARAM FOR SELECT PARENTCONTAINERID, CONTAINERID, TYPENAME, DATA FROM TABLE(HMSVIEW.FN_HMSVIEW_GETSTEPS(PID)); END HUGO; Function FN_HMSVIEW_GETSTEPS called within stored procedure returns pipelined. FUNCTION FN_HMSVIEW_GETSTEPS( PID IN VARCHAR2 ) RETURN HMSVIEWTABLEEX PIPELINED IS .... The entity (STEPS2.PPU) I want to fill I defined with exactly the field as returend by Stored Procedure: ...SELECT PARENTCONTAINERID, CONTAINERID, TYPENAME, DATA FROM TABLE(HMSVIEW.FN_HMSVIEW_GETSTEPS(PID)); Invocation from form is implemented as: activate "P_TEST".HUGO("179","STEPS2.PPU") At clientside I alway receive a $procerror -150. Looking at server log, I see the following: From Client:chn=38;len=98: ACTREQ; typ=A;av=I;op=s;mod=5;iop=0;ign=0; hop=0;dbg=1;pid=4356;tid=2124;qid=0;ins=0; ICCDRV-ORA-ERR Call ICC component failed, ORA-01007: variable not in select list To Client:chn=38;len=32: ANSWER; typ=Z;av=L;op=Z;ret=-150,0; hop=0;dbg=0;pid=4356;tid=2124;qid=0;ins=0; When I switch entity parameter in Signature definition from OUT to INOUT I receive also a $procerror -150, but with slightly different error text in server log: From Client:chn=38;len=98: ACTREQ; typ=A;av=I;op=s;mod=5;iop=0;ign=0; hop=0;dbg=1;pid=4356;tid=2124;qid=0;ins=0; ICCDRV-ORA-ERR Error with ICC system occurred, Error - Entity parameter may only be define as an output To Client:chn=38;len=32: ANSWER; typ=Z;av=I;op=Z;ret=-150,0; hop=0;dbg=0;pid=4356;tid=2124;qid=0;ins=0; Has anybody got any idea or hint for me? Thanks a lot in advance, Max

Oracle Stored Procedure invocation raises "ORA-01007: variable not in select list"

Author: m.koessldorfer@tme.co.at (mkoss)

Hi Team, I am using a Windows XP Client and a Uniface 9.4 server on Linux, DB is Oracle 11g. Usecase is to call an Oracle Stored Procedure in order to fill up an entity with data. Stored Procedure (Package P_TEST) is defined like this PROCEDURE HUGO( PID IN VARCHAR2, OUTPARAM OUT SYS_REFCURSOR) AS BEGIN OPEN OUTPARAM FOR SELECT PARENTCONTAINERID, CONTAINERID, TYPENAME, DATA FROM TABLE(HMSVIEW.FN_HMSVIEW_GETSTEPS(PID)); END HUGO; Function FN_HMSVIEW_GETSTEPS called within stored procedure returns pipelined. FUNCTION FN_HMSVIEW_GETSTEPS( PID IN VARCHAR2 ) RETURN HMSVIEWTABLEEX PIPELINED IS .... The entity (STEPS2.PPU) I want to fill I defined with exactly the field as returend by Stored Procedure: ...SELECT PARENTCONTAINERID, CONTAINERID, TYPENAME, DATA FROM TABLE(HMSVIEW.FN_HMSVIEW_GETSTEPS(PID)); Invocation from form is implemented as: activate "P_TEST".HUGO("179","STEPS2.PPU") At clientside I alway receive a $procerror -150. Looking at server log, I see the following: From Client:chn=38;len=98: ACTREQ; typ=A;av=I;op=s;mod=5;iop=0;ign=0; hop=0;dbg=1;pid=4356;tid=2124;qid=0;ins=0; ICCDRV-ORA-ERR Call ICC component failed, ORA-01007: variable not in select list To Client:chn=38;len=32: ANSWER; typ=Z;av=L;op=Z;ret=-150,0; hop=0;dbg=0;pid=4356;tid=2124;qid=0;ins=0; When I switch entity parameter in Signature definition from OUT to INOUT I receive also a $procerror -150, but with slightly different error text in server log: From Client:chn=38;len=98: ACTREQ; typ=A;av=I;op=s;mod=5;iop=0;ign=0; hop=0;dbg=1;pid=4356;tid=2124;qid=0;ins=0; ICCDRV-ORA-ERR Error with ICC system occurred, Error - Entity parameter may only be define as an output To Client:chn=38;len=32: ANSWER; typ=Z;av=I;op=Z;ret=-150,0; hop=0;dbg=0;pid=4356;tid=2124;qid=0;ins=0; Has anybody got any idea or hint for me? Thanks a lot in advance, Max

Hi Team, one more information to this topic: I am routing the "remote service" via [services_exec] section from the client to the server, and the server in turn routes it again via entry in [services_exec] to the RDBMS. Greetings, Max


Author: mkoss (m.koessldorfer@tme.co.at)

Oracle Stored Procedure invocation raises "ORA-01007: variable not in select list"

Author: m.koessldorfer@tme.co.at (mkoss)

Hi Team, I am using a Windows XP Client and a Uniface 9.4 server on Linux, DB is Oracle 11g. Usecase is to call an Oracle Stored Procedure in order to fill up an entity with data. Stored Procedure (Package P_TEST) is defined like this PROCEDURE HUGO( PID IN VARCHAR2, OUTPARAM OUT SYS_REFCURSOR) AS BEGIN OPEN OUTPARAM FOR SELECT PARENTCONTAINERID, CONTAINERID, TYPENAME, DATA FROM TABLE(HMSVIEW.FN_HMSVIEW_GETSTEPS(PID)); END HUGO; Function FN_HMSVIEW_GETSTEPS called within stored procedure returns pipelined. FUNCTION FN_HMSVIEW_GETSTEPS( PID IN VARCHAR2 ) RETURN HMSVIEWTABLEEX PIPELINED IS .... The entity (STEPS2.PPU) I want to fill I defined with exactly the field as returend by Stored Procedure: ...SELECT PARENTCONTAINERID, CONTAINERID, TYPENAME, DATA FROM TABLE(HMSVIEW.FN_HMSVIEW_GETSTEPS(PID)); Invocation from form is implemented as: activate "P_TEST".HUGO("179","STEPS2.PPU") At clientside I alway receive a $procerror -150. Looking at server log, I see the following: From Client:chn=38;len=98: ACTREQ; typ=A;av=I;op=s;mod=5;iop=0;ign=0; hop=0;dbg=1;pid=4356;tid=2124;qid=0;ins=0; ICCDRV-ORA-ERR Call ICC component failed, ORA-01007: variable not in select list To Client:chn=38;len=32: ANSWER; typ=Z;av=L;op=Z;ret=-150,0; hop=0;dbg=0;pid=4356;tid=2124;qid=0;ins=0; When I switch entity parameter in Signature definition from OUT to INOUT I receive also a $procerror -150, but with slightly different error text in server log: From Client:chn=38;len=98: ACTREQ; typ=A;av=I;op=s;mod=5;iop=0;ign=0; hop=0;dbg=1;pid=4356;tid=2124;qid=0;ins=0; ICCDRV-ORA-ERR Error with ICC system occurred, Error - Entity parameter may only be define as an output To Client:chn=38;len=32: ANSWER; typ=Z;av=I;op=Z;ret=-150,0; hop=0;dbg=0;pid=4356;tid=2124;qid=0;ins=0; Has anybody got any idea or hint for me? Thanks a lot in advance, Max

Hi Max, I'm not an Oracle expert, but to me "ORA-01007: variable not in select list" seems that you must be selecting or fetching INTO something (usually a record type). Your INTO target, whatever it is, doesn't match column for column your SELECT list. Either you change the table you're fetching from or you change the INTO target. Translated into Uniface I'm suspecting that the number of parameters in the signature somehow stil doesn't match up with the output list of your stored procedure. Uniface $procerror -150 ('A hardware or software error occurred. Contact your Uniface representative.') doesn't ring a bell for me. I think your heading towards the right solution though. Is it possible for you to change the sequence of the parameters for instance and do some more 'trial and error' ? Cheers, Arjen


Author: Arjen van Vliet (arjen.van.vliet@uniface.com)
Terms & ConditionsAccessibility statement