stored procedure data source in Forms 5.0
From: John Noronha <johnjn_at_bigfoot.com>
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 ?
begin
end
end Received on Tue Sep 07 1999 - 19:41:54 CEST
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?=&%B
M;&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="!M
M;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<RYF
M=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