stored procedure data source in Forms 5.0
Date: Tue, 7 Sep 1999 21:41:54 +0400
Message-ID: <7r3k42$fo22_at_news.emirates.net.ae>
Has anyone used a stored procedure as a data source for a Forms 5.0 block ?
I am using a Forms 5.0 block with stored procedure data source. The
'select', 'update', 'delete' and 'lock' procedures are defined within a
package (module_functions_pkg) The package compiled okay within Oracle (ver
7.3) and I also verified (through SQL*PLUS) that the 'select' works in the
expected manner.
On doing an 'execute query' in the form however, I get a 1403 error (i.e. no
rows selected). However, if I change the 'select' procedure logic to use a
'simple' cursor, i.e. a cursor without parameters and a harcoded where
clause (i.e. where ..... like '%'), the 'execute query' returns all the rows
correctly.
This has me stumped. Would appreciate advice. Package source attached as well as enclosed below in case the attachment is rejected.
Regards,
John J Noronha
ADNOC FOD
Abu Dhabi, UAE
PS. This uses some ideas from Paul Dorsey's book 'Developer 2000 Handbook' chapter on 'Procedural Encapsulation'.
/******************** package specification: module_functions_pkg*************/
create or replace package module_functions_pkg as
- Types
type tfunction is record ( rfunction_id jn_adm_functions.function_id%type, rfunction_name jn_adm_functions.function_name%type, rmodule_id jn_adm_functions.module_id%type ); type tfunction_table is table of tfunction index by binary_integer; type tfunction_key is record ( rfunction_id jn_adm_functions.function_id%type ); type tfunction_key_table is table of tfunction_key index by binary_integer; ------------------------------------- Procedures procedure select_rows ( pfunction_table in out tfunction_table ); procedure update_rows ( pfunction_table in tfunction_table ); procedure delete_rows ( pfunction_key_table in tfunction_key_table ); procedure lock_rows ( pkey_table in tfunction_key_table );
end;
/******************* package body :: module_functions_pkg *************/create or replace package body module_functions_pkg as
PROCEDURE select_rows ( pfunction_table in out tfunction_table ) is
vindex number := 1;
vfunction_id jn_adm_functions.function_id%type;
cursor cfunctions (pfunction_id varchar2) is select function_id, function_name, module_id from jn_adm_functions a where a.function_id like pfunction_id order by 1;
begin
if pfunction_table(1).rfunction_id is null then vfunction_id := '%'; else vfunction_id := pfunction_table(1).rfunction_id; end if; for rfunctions in cfunctions( vfunction_id ) loop pfunction_table(vindex).rfunction_id := rfunctions.function_id; pfunction_table(vindex).rfunction_name := rfunctions.function_name; pfunction_table(vindex).rmodule_id := rfunctions.module_id; vindex := vindex + 1; end loop;
end select_rows;
PROCEDURE update_rows ( pfunction_table in tfunction_table ) is
vindex number := 1; vcount number := pfunction_table.count;
begin
for vinvex in 1 .. vcount loop if ( not pfunction_table(vindex).rmodule_id is null ) then update jn_adm_functions set module_id = pfunction_table(vindex).rmodule_id where function_id = pfunction_table(vindex).rfunction_id; end if; end loop;
end update_rows;
PROCEDURE delete_rows ( pfunction_key_table in tfunction_key_table ) is
vindex number := 1; vcount number := pfunction_key_table.count;
begin
for vindex in 1 .. vcount loop
update jn_adm_functions set module_id = null where function_id = pfunction_key_table(vindex).rfunction_id; end loop; end delete_rows; PROCEDURE lock_rows ( pkey_table in tfunction_key_table ) is vindex number := 1; vcount number := pkey_table.count; vdummy varchar2(1); begin for vindex in 1 .. vcount loop select 'x' into vdummy from jn_adm_functions a where a.function_id = pkey_table(vindex).rfunction_id for update nowait; end loop; end lock_rows;
end module_functions_pkg;
/********************** end package body ******************/
begin 666 module_functions_pkg_body.sql
M8W)E871E(&]R(')E<&QA8V4_at_<&%C:V%G92!B;V1Y(&UO9'5L95]F=6YC=&EO M;G-?<&MG(&%S#0H-"B _at_("!04D]#14154D4@<V5L96-T7W)O=W,@*"!P9G5N M8W1I;VY?=&%B;&4_at_:6X@;W5T('1F=6YC=&EO;E]T86)L92 I(&ES#0H-"B @M(" _at_(" @=FEN9&5X(&YU;6)E<B Z/2 Q.PT*#0H@(" @(" @('9F=6YC=&EO M;E]I9"!J;E]A9&U?9G5N8W1I;VYS+F9U;F-T:6]N7VED)71Y<&4[#0H-"B _at_ M(" _at_(" @8W5R<V]R(&-F=6YC=&EO;G,@*'!F=6YC=&EO;E]I9"!V87)C:&%R
M,BD_at_:7,-"B @(" @(" @(" @("!S96QE8W0_at_9G5N8W1I;VY?:60L(&9U;F-T M:6]N7VYA;64L(&UO9'5L95]I9 T*(" _at_(" @(" @(" @("!F<F]M(&IN7V%D M;5]F=6YC=&EO;G,_at_80T*(" @(" @(" @(" @('=H97)E(&$N9G5N8W1I;VY? M:60_at_;&EK92!P9G5N8W1I;VY?:60-"B @(" @(" @(" @("!O<F1E<B!B>2 Q M.PT*(" _at_(&)E9VEN#0H@(" @(" -"B @(" @("!I9B!P9G5N8W1I;VY?=&%BM;&4H,2DN<F9U;F-T:6]N7VED(&ES(&YU;&P_at_=&AE;@T*(" @(" @(" @(" @ M('9F=6YC=&EO;E]I9" Z/2 G)2<[#0H_at_(" @(" @96QS90T*(" @(" @(" @ M(" _at_('9F=6YC=&EO;E]I9" Z/2!P9G5N8W1I;VY?=&%B;&4H,2DN<F9U;F-T M:6]N7VED.PT*(" _at_(" @(&5N9"!I9CL-"@T*(" @(" @(&9O<B!R9G5N8W1I M;VYS(&EN(&-F=6YC=&EO;G,H('9F=6YC=&EO;E]I9" I(&QO;W -"_at_T*(" @ M(" _at_(" @<&9U;F-T:6]N7W1A8FQE*'9I;F1E>"DN<F9U;F-T:6]N7VED(#H] M(')F=6YC=&EO;G,N9G5N8W1I;VY?:60[#0H_at_(" @(" @("!P9G5N8W1I;VY? M=&%B;&4H=FEN9&5X*2YR9G5N8W1I;VY?;F%M92 Z/2!R9G5N8W1I;VYS+F9U M;F-T:6]N7VYA;64[( T*(" _at_(" @(" @<&9U;F-T:6]N7W1A8FQE*'9I;F1E M>"DN<FUO9'5L95]I9" Z/2!R9G5N8W1I;VYS+FUO9'5L95]I9#L_at_#0H-"B @ M(" _at_(" @('9I;F1E>" Z/2!V:6YD97@@*R Q.PT*#0H@(" @(" @96YD(&QO M;W [#0H-"B _at_("!E;F0@<V5L96-T7W)O=W,[#0H-"B @(" @(" @( T*(" @ M(%!23T-%1%5212!U<&1A=&5?<F]W<R H('!F=6YC=&EO;E]T86)L92!I;B!T M9G5N8W1I;VY?=&%B;&4_at_*2!I<PT*#0H@(" @(" @('9I;F1E>"!N=6UB97(@ M.CT_at_,3L-"B @(" @(" @=F-O=6YT(&YU;6)E<B Z/2!P9G5N8W1I;VY?=&%B
M;&4N8V]U;G0[#0H-"B _at_("!B96=I;@T*#0H@(" @(" @(&9O<B!V:6YV97@@ M:6X_at_,2 N+B!V8V]U;G0@;&]O< T*(" @(" @(" @(&EF("@@;F]T('!F=6YC M=&EO;E]T86)L92AV:6YD97_at_I+G)M;V1U;&5?:60@:7,@;G5L;" I('1H96X-M"_at_T*(" @(" @(" @(" @=7!D871E(&IN7V%D;5]F=6YC=&EO;G,-"B @(" @ M(" _at_(" @(" @("!S970@;6]D=6QE7VED(#T@<&9U;F-T:6]N7W1A8FQE*'9I M;F1E>"DN<FUO9'5L95]I9 T*(" _at_(" @(" @(" @("!W:&5R92!F=6YC=&EO M;E]I9" ]('!F=6YC=&EO;E]T86)L92AV:6YD97_at_I+G)F=6YC=&EO;E]I9#LM" _at_T*(" @(" @(" @(&5N9"!I9CL-"@T*(" @(" @("!E;F0@;&]O<#L-"@T* M(" _at_(&5N9"!U<&1A=&5?<F]W<SL-"@T*#0H@(" @4%)/0T5$55)%(&1E;&5T M95]R;W=S("_at_@<&9U;F-T:6]N7VME>5]T86)L92!I;B!T9G5N8W1I;VY?:V5Y M7W1A8FQE("D_at_:7,-"@T*(" @(" @(" @("!V:6YD97@@;G5M8F5R(#H](#$[ M#0H_at_(" @(" @(" @('9C;W5N="!N=6UB97(@.CT@<&9U;F-T:6]N7VME>5]T
M86)L92YC;W5N=#L-"_at_T*(" @(&)E9VEN#0H-"B @(" @(" @(" @9F]R('9I M;F1E>"!I;B Q("XN('9C;W5N="!L;V]P#0H-"B _at_(" @(" @(" @(" @('5P M9&%T92!J;E]A9&U?9G5N8W1I;VYS#0H_at_(" @(" @(" @(" @(" @('-E="!MM;V1U;&5?:60_at_/2!N=6QL#0H@(" @(" @(" @(" @("!W:&5R92!F=6YC=&EO M;E]I9" ]('!F=6YC=&EO;E]K97E?=&%B;&4H=FEN9&5X*2YR9G5N8W1I;VY?
M:60[#0H-"B _at_(" @(" @(" @96YD(&QO;W [#0H-"B @(" @96YD(&1E;&5T M95]R;W=S.PT*#0H-"B _at_(" @4%)/0T5$55)%(&QO8VM?<F]W<R H('!K97E? M=&%B;&4_at_:6X@=&9U;F-T:6]N7VME>5]T86)L92 I(&ES#0H-"B @(" @(" @ M('9I;F1E>"!N=6UB97(_at_.CT@,3L-"B @(" @(" @('9C;W5N="!N=6UB97(@ M.CT_at_<&ME>5]T86)L92YC;W5N=#L-"B @(" @(" @('9D=6UM>2!V87)C:&%R M,B_at_Q*3L-"@T*(" @("!B96=I;@T*(" @(" @(" @9F]R('9I;F1E>"!I;B Q M("XN('9C;W5N="!L;V]P#0H-"B _at_(" @(" @(" @("!S96QE8W0@)W_at_G( T*M(" _at_(" @(" @(" @(" @:6YT;R!V9'5M;7D-"B @(" @(" @(" @(" @(&9R M;VT_at_:FY?861M7V9U;F-T:6]N<R!A#0H@(" @(" @(" @(" @("!W:&5R92!A M+F9U;F-T:6]N7VED(#T_at_<&ME>5]T86)L92AV:6YD97_at_I+G)F=6YC=&EO;E]I M9 T*(" _at_(" @(" @(" @(" @("!F;W(@=7!D871E(&YO=V%I=#L-"B @(" @ M(" _at_(" -"B @(" @(" @(&5N9"!L;V]P.PT*#0H@(" @(&5N9"!L;V-K7W)O M=W,[#0H-"F5N9"!M;V1U;&5?9G5N8W1I;VYS7W!K9SL-"B _at_(" @(" @( T* `
end
begin 666 module_functions_pkg.sql
M8W)E871E(&]R(')E<&QA8V4_at_<&%C:V%G92!M;V1U;&5?9G5N8W1I;VYS7W!K
M9R!A<PT*#0HM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2T_at_
M(%1Y<&5S#0H-"B _at_(" @='EP92!T9G5N8W1I;VX@:7,@<F5C;W)D("@-"B @ M(" _at_(" @(" @("!R9G5N8W1I;VY?:60@(" @(" @(&IN7V%D;5]F=6YC=&EO M;G,N9G5N8W1I;VY?:60E='EP92P-"B _at_(" @(" @(" @("!R9G5N8W1I;VY?M;F%M92 _at_(" @(&IN7V%D;5]F=6YC=&EO;G,N9G5N8W1I;VY?;F%M925T>7!E M+ T*(" _at_(" @(" @(" @(')M;V1U;&5?:60@(" @(" @(" @:FY?861M7V9U M;F-T:6]N<RYM;V1U;&5?:60E='EP90T*(" _at_(" I.PT*#0H@(" @('1Y<&4@ M=&9U;F-T:6]N7W1A8FQE(&ES('1A8FQE(&]F('1F=6YC=&EO;B!I;F1E>"!B
M>0T*(" _at_(" @(" @(" @(" @(" @(" @(" @(&)I;F%R>5]I;G1E9V5R.PT* M#0H_at_(" @('1Y<&4@=&9U;F-T:6]N7VME>2!I<R!R96-O<F0@* T*(" @(" @ M(" _at_(" @(')F=6YC=&EO;E]I9" @(" @(" @:FY?861M7V9U;F-T:6]N<RYFM=6YC=&EO;E]I9"5T>7!E#0H_at_(" @("D[#0H-"B @(" @='EP92!T9G5N8W1I M;VY?:V5Y7W1A8FQE(&ES('1A8FQE(&]F('1F=6YC=&EO;E]K97D_at_:6YD97@@ M8GD-"B _at_(" @(" @(" @(" @(" @(" @(" @("!B:6YA<GE?:6YT96=E<CLM" _at_T*(" @(" O*G1Y<&4@=&9U;F-T:6]N7V-U<G-O<B!I<R!R968_at_8W5R<V]R M('1F=6YC=&EO;CLJ+PT*#0HM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM M+2TM+2TM+2TM(%!R;V-E9'5R97,-"_at_T*+RH@("!P<F]C961U<F4@<V5L96-T M7V-U<G-O<B H('!F=6YC=&EO;E]C=7)S;W(_at_:6X@;W5T('1F=6YC=&EO;E]C M=7)S;W(_at_*3LJ+PT*#0H@(" @('!R;V-E9'5R92!S96QE8W1?<F]W<R H('!F M=6YC=&EO;E]T86)L92!I;B!O=70_at_=&9U;F-T:6]N7W1A8FQE("D[#0H-"B @ M(" _at_<')O8V5D=7)E('5P9&%T95]R;W=S("@@<&9U;F-T:6]N7W1A8FQE(&EN M('1F=6YC=&EO;E]T86)L92 I.PT*#0H_at_(" @('!R;V-E9'5R92!D96QE=&5? M<F]W<R H('!F=6YC=&EO;E]K97E?=&%B;&4_at_:6X@=&9U;F-T:6]N7VME>5]T M86)L92 I.R -"_at_T*(" @("!P<F]C961U<F4@;&]C:U]R;W=S("@@<&ME>5]T F86)L92!I;B!T9G5N8W1I;VY?:V5Y7W1A8FQE("D[#0H-"F5N9#L` `
end Received on Tue Sep 07 1999 - 19:41:54 CEST