Re: Binding LOB input value to stored procedure with PDO_OCI
| From: | Jessie Hernandez | Date: | Tue, 02 Jan 2007 23:44:15 +0000 |
| Subject: | Re: Binding LOB input value to stored procedure with PDO_OCI | ||
| References: | 1 | Groups: | php.pecl.dev |
| Request: | Send a blank email to pecl-dev+get-4431@lists.php.net to get a copy of this message | ||
Hello,
The current code had PARAM_LOB values hardcoded as BLOBs (hence not allowing
CLOBs to be bound) and did not allow LOBs to be passed as input to stored
procedures. The attached patch fixes these two issues (please let me know if
it should instead be attached to the bug report described below). The fix
was done by adding three new class constants, PDO_OCI_PARAM_B_BLOB,
PDO_OCI_PARAM_B_CLOB, and PDO_OCI_PARAM_CREATE_TEMP_LOB.
PDO_OCI_PARAM_B_CLOB indicates that the value that will be bound is a CLOB
instead of a BLOB. PDO_OCI_PARAM_CREATE_TEMP_LOB indicates that a temporary
LOB should be created and passed as input (instead of waiting for the LOB to
be created and returned via a RETURNING clause).
Since a new flag is needed to create a temporary LOB, there are no BC breaks
for existing Oracle code. The following is a sample of how calling a stored
procedure with an input CLOB would look like:
-- ORACLE
CREATE OR REPLACE PROCEDURE TEST_PDO(value IN CLOB) AS
BEGIN
insert into pdoclob values( dbms_lob.substr( value, 50 ) );
END;
-- PHP
<?php
$connection = new PDO( 'oci:dbname=mydb', 'user', 'pass' );
$clob = fopen( '/tmp/testpdo.txt', 'rb' );
$s = $connection->prepare( 'begin test_pdo( ? ); end;' );
$s->bindParam( 1, $clob, PDO::PARAM_LOB, 0, PDO::OCI_PARAM_B_CLOB |
PDO::OCI_PARAM_CREATE_TEMP_LOB );
$connection->beginTransaction();
$s->execute();
$connection->commit();
?>
Also, if these same flags are used, you can even insert a clob into a table
using "insert into clob_table values( ? )" syntax instead of using "insert
into clob_table values(empty_clob()) returning clob_column into ?" syntax!
Please let me know if there is anything that needs to be improved in the
attached patch (BTW, this patch would also close this bug:
http://pecl.php.net/bugs/bug.php?id=7943 ).
Thanks!
Regards,
Jessie Hernandez
""Jessie Hernandez"" <jrhernandez05@gmail.com> wrote in message
news:90.10.10210.C8E13854@pb1.pair.com...
> I'm trying to bind a LOB value to a stored procedure call in Oracle. Since
> there is no RETURNING clause for a stored procedure call, I cannot use the
> EMPTY_CLOB() trick that's in the documentation.
>
> I've tried several things (including creating a CLOB in another statement
> using the DBMS_LOB.CREATETEMPORARY method), but nothing has worked. Here's
> some sample code of what I'm trying to do. With the oic8 extension, one
> would first have to create a BLOB using the oci_new_descriptor function,
> then bind that to the statement. Since there's no "oci_new_descriptor"
> equivalent in PDO_OCI, I cannot create the CLOB beforehand. Is the
> following
> possible for PDO_OCI?
>
> --- STORED PROCEDURE ---
> create procedure test_clob(value in clob);
>
> --- PHP CODE ---
> <?php
> $connection = new PDO( 'oci:dbname=mydb' );
> $clob = fopen( 'myfile', 'r' );
> $statement = $connection->prepare( 'begin test_clob( ? ); end;' );
>
> $statement->bindParam( 1, $clob, PDO::PARAM_LOB );
> $statement->execute();
>
> /*
> above generates an error:
> SQLSTATE[HY000]: General error: 6550 OCIStmtExecute: ORA-06550: line 1,
> column 7:
> PLS-00306: wrong number or types of arguments in call to 'TEST_CLOB'
> */
> ?>
>
>
> If the above is not possible with Oracle, can a method be provided to
> create
> descriptors only for the PDO_OCI extension? I'm willing to help implement
> this, as the logic would be the same as the one in the oci8 extension.
> Please let me know. Thanks.
>
>
> Regards,
>
> Jessie Hernandez
begin 666 oci_lob.patch
M9&EF9B M=2!P9&]?;V-I7V)A:R]O8VE?<W1A=&5M96YT+F,@<&1O7V]C:2]O
M8VE?<W1A=&5M96YT+F,*+2TM('!D;U]O8VE?8F%K+V]C:5]S=&%T96UE;G0N
M8PDR,# W+3 Q+3 R(#$X.C$V.C4R+C4X.38Y.#0T." M,#4P, HK*RL@<&1O
M7V]C:2]O8VE?<W1A=&5M96YT+F,),C P-RTP,2TP,B Q.#HQ-SHR,RXP,# P
M-S4S-C@@+3 U,# *0$ @+3(S-2PV("LR,S4L,S8@0$ *( ER971U<FX@3T-)
M7T-/3E1)3E5%.PH@?2 O*B!]?7T@*B\*( HK<W1A=&EC('9O:60@;V-I7W=R
M:71E7VQO8E]F<F]M7W-T<F5A;2AP9&]?;V-I7W-T;70@*E,L('!D;U]O8VE?
M8F]U;F1?<&%R86T@*E L('!H<%]S=')E86T@*G-T;2D**WL**PES:7IE7W0@
M;CL**PEU8C0@86UT+"!O9F9S970@/2 Q.PHK"6-H87(@*F-O;G-U;64["BL*
M*PE/0TE,;V)/<&5N*%,M/D@M/G-V8RP@4RT^97)R+" H3T-)3&]B3&]C871O
M<BHI4"T^=&AI;F<L($]#25],3T)?4D5!1%=2251%*3L**PHK"61O('L**PD)
M8VAA<B!B=69;.#$Y,ET["BL)"6X@/2!P:'!?<W1R96%M7W)E860H<W1M+"!B
M=68L('-I>F5O9BAB=68I*3L**PD):68@*"AI;G0I;B \/2 P*2!["BL)"0EB
M<F5A:SL**PD)?0HK"0EC;VYS=6UE(#T@8G5F.PHK"0ED;R!["BL)"0EA;70@
M/2!N.PHK"0D)3T-)3&]B5W)I=&4H4RT^2"T^<W9C+"!3+3YE<G(L("A/0TE,
M;V),;V-A=&]R*BE0+3YT:&EN9RP**PD)"0D))F%M="P@;V9F<V5T+"!C;VYS
M=6UE+"!N+ HK"0D)"0E/0TE?3TY%7U!)14-%+ HK"0D)"0E.54Q,+"!.54Q,
M+" P+"!344Q#4U])35!,24-)5"D["BL)"0EO9F9S970@*ST@86UT.PHK"0D)
M;B M/2!A;70["BL)"0EC;VYS=6UE("L](&%M=#L**PD)?2!W:&EL92 H;BD[
M"BL)?2!W:&EL92 H,2D["BL)3T-)3&]B0VQO<V4H4RT^2"T^<W9C+"!3+3YE
M<G(L("A/0TE,;V),;V-A=&]R*BE0+3YT:&EN9RD["BL)3T-)3&]B1FQU<VA"
M=69F97(H4RT^2"T^<W9C+"!3+3YE<G(L("A/0TE,;V),;V-A=&]R*BE0+3YT
M:&EN9RP@,"D["BM]"BL*('-T871I8R!I;G0@;V-I7W-T;71?<&%R86U?:&]O
M:RAP9&]?<W1M=%]T("IS=&UT+"!S=')U8W0@<&1O7V)O=6YD7W!A<F%M7V1A
M=&$@*G!A<F%M+"!E;G5M('!D;U]P87)A;5]E=F5N="!E=F5N=%]T>7!E(%13
M4DU,4U]$0RD@+RH@>WM[("HO"B!["B )<&1O7V]C:5]S=&UT("I3(#T@*'!D
M;U]O8VE?<W1M="HI<W1M="T^9')I=F5R7V1A=&$["D! ("TR-#,L-B K,C<S
M+#<@0$ *( EI9B H<&%R86TM/FES7W!A<F%M*2!["B )"7!D;U]O8VE?8F]U
M;F1?<&%R86T@*E ["B )"7-B-"!V86QU95]S>B ]("TQ.PHK"0ES8C(@;&]B
M7V%L;&]C7W1Y<&4@/2!344Q47T),3T(["B )"0H@"0E0(#T@*'!D;U]O8VE?
M8F]U;F1?<&%R86TJ*7!A<F%M+3YD<FEV97)?9&%T83L*( I 0" M,C8T+#@@
M*S(Y-2PQ-2! 0 H@"0D)"0D)<F5T=7)N(# ["B *( D)"0D)8V%S92!01$]?
M4$%204U?3$]".@HK"0D)"0D):68@*'!A<F%M+3YD<FEV97)?<&%R86US("$]
M($Y53$PI('L**PD)"0D)"0EC;VYV97)T7W1O7VQO;F<H<&%R86TM/F1R:79E
M<E]P87)A;7,I.PHK"BL)"0D)"0D):68@*"A:7TQ604Q?4"AP87)A;2T^9')I
M=F5R7W!A<F%M<RD@)B!01$]?3T-)7U!!4D%-7T)?0TQ/0BD@/3T@4$1/7T]#
M25]005)!35]"7T-,3T(I"BL)"0D)"0D)"6QO8E]A;&QO8U]T>7!E(#T@4U%,
M5%]#3$]".PHK"0D)"0D)?0HK"B
)"0D)"0DO*B!0+3YT:&EN9R!I<R!N;W<@
M86X@3T-)3&]B3&]C871O<B J("HO"BT)"0D)"0E0+3YO8VE?='EP92 ](%-1
M3%1?0DQ/0CL**PD)"0D)"5 M/F]C:5]T>7!E(#T@;&]B7V%L;&]C7W1Y<&4[
M"B )"0D)"0EV86QU95]S>B ]('-I>F5O9BA/0TE,;V),;V-A=&]R*BD["B )
M"0D)"0EB<F5A:SL*( I 0" M,S U+#D@*S,T,RPT,R! 0 H@"0D)"5 M/G5S
M961?9F]R7V]U='!U=" ](# ["B )"0D):68@*%!$3U]005)!35]465!%*'!A
M<F%M+3YP87)A;5]T>7!E*2 ]/2!01$]?4$%204U?3$]"*2"0D)"6-H87(@8W)E871E9%]T96UP(#T@,#L**PH@
M"0D)"0E35$U47T-!3$PH3T-)1&5S8W)I<'1O<D%L;&]C+" H4RT^2"T^96YV
M+" F4"T^=&AI;F<L($]#25]$5%E015],3T(L(# L($Y53$PI*3L*+0D)"0D)
M4U1-5%]#04Q,*$]#24%T=')3970L("A0+3YT:&EN9RP@3T-)7T1465!%7TQ/
M0BP@)F5M<'1Y+" P+"!/0TE?05144E],3T)%35!462P@4RT^97)R*2D["B )
M"0D)"5,M/FAA=F5?8FQO8G,@/2 Q.PHK"BL)"0D)"6EF("A:7U194$5?4"AP
M87)A;2T^<&%R86UE=&5R*2 A/2!)4U].54Q,*2!["BL)"0D)"0EC:&%R(&-R
M96%T95]T96UP7VQO8B ](# ["BL)"0D)"0EU8C$@;&]B7W1Y<&4@/2!/0TE?
M5$5-4%]"3$]".PHK"0D)"0D)<&AP7W-T<F5A;2 J<W1M(#T@3E5,3#L**PHK
M"0D)"0D):68@*%I?5%E015]0*'!A<F%M+3YD<FEV97)?<&%R86US*2 A/2!)
M4U].54Q,*2!["BL)"0D)"0D)8V]N=F5R=%]T;U]L;VYG*'!A<F%M+3YD<FEV
M97)?<&%R86US*3L**PHK"0D)"0D)"6EF("@H6E],5D%,7U H<&%R86TM/F1R
M:79E<E]P87)A;7,I("8@4$1/7T]#25]005)!35]"7T-,3T(I(#T](%!$3U]/
M0TE?4$%204U?0E]#3$]"*0HK"0D)"0D)"0EL;V)?='EP92 ]($]#25]414U0
M7T-,3T(["BL**PD)"0D)"0EI9B H*%I?3%9!3%]0*'!A<F%M+3YD<FEV97)?
M<&%R86US*2 F(%!$3U]/0TE?4$%204U?0U)%051%7U1%35!?3$]"*2 ]/2!0
M1$]?3T-)7U!!4D%-7T-214%415]414U07TQ/0BD**PD)"0D)"0D)8W)E871E
M7W1E;7!?;&]B(#T@,3L**PD)"0D)"7T**PHK"0D)"0D):68@*&-R96%T95]T
M96UP7VQO8BD@>PHK"0D)"0D)"7!H<%]S=')E86U?9G)O;5]Z=F%L7VYO7W9E
M<FEF>2AS=&TL("9P87)A;2T^<&%R86UE=&5R*3L**PHK"0D)"0D)"6EF("AS
M=&TI('L**PD)"0D)"0D)4U1-5%]#04Q,*$]#24QO8D-R96%T951E;7!O<F%R
M>2P@*%,M/D@M/G-V8RP@4RT^97)R+ HK"0D)"0D)"0D)"0DH3T-)3&]B3&]C
M871O<BHI4"T^=&AI;F<L($]#25]$149!54Q4+"!/0TE?1$5&055,5"P**PD)
M"0D)"0D)"0D);&]B7W1Y<&4L($]#25]!5%127TY/0T%#2$4L($]#25]$55)!
M5$E/3E]315-324].*2D["BL**PD)"0D)"0D);V-I7W=R:71E7VQO8E]F<F]M
M7W-T<F5A;2A3+"!0+"!S=&TI.PHK"0D)"0D)"0EP<FEN=&8H(F%F=&5R(&]C
M:5]W<FET95]L;V)?9G)O;5]S=')E86T@-5QN(BD["BL)"0D)"0D)"6-R96%T
M961?=&5M<"
](#$["BL)"0D)"0D)?0HK"0D)"0D)?0HK"0D)"0E]"BL**PD)
M"0D):68@*"%C<F5A=&5D7W1E;7 I"BL)"0D)"0E35$U47T-!3$PH3T-)071T
M<E-E="P@*% M/G1H:6YG+"!/0TE?1%194$5?3$]"+" F96UP='DL(# L($]#
M25]!5%127TQ/0D5-4%19+"!3+3YE<G(I*3L*( D)"0E]"B )"0D)<F5T=7)N
M(#$["B *0$ @+3,U-RPR-R K-#(Y+#<@0$ *( H@"0D)"0D)<&AP7W-T<F5A
M;5]F<F]M7WIV86Q?;F]?=F5R:69Y*'-T;2P@)G!A<F%M+3YP87)A;65T97(I
M.PH@"0D)"0D):68@*'-T;2D@>PHM"0D)"0D)"4]#24QO8D]P96XH4RT^2"T^
M<W9C+"!3+3YE<G(L("A/0TE,;V),;V-A=&]R*BE0+3YT:&EN9RP@3T-)7TQ/
M0E]214%$5U))5$4I.PHM"0D)"0D)"61O('L*+0D)"0D)"0D)8VAA<B!B=69;
M.#$Y,ET["BT)"0D)"0D)"6X@/2!P:'!?<W1R96%M7W)E860H<W1M+"!B=68L
M('-I>F5O9BAB=68I*3L*+0D)"0D)"0D):68@*"AI;G0I;B \/2 P*2!["BT)
M"0D)"0D)"0EB<F5A:SL*+0D)"0D)"0D)?0HM"0D)"0D)"0EC;VYS=6UE(#T@
M8G5F.PHM"0D)"0D)"0ED;R!["BT)"0D)"0D)"0EA;70@/2!N.PHM"0D)"0D)
M"0D)3T-)3&]B5W)I=&4H4RT^2"T^<W9C+"!3+3YE<G(L("A/0TE,;V),;V-A
M=&]R*BE0+3YT:&EN9RP*+0D)"0D)"0D)"0D))F%M="P@;V9F<V5T+"!C;VYS
M=6UE+"!N+ HM"0D)"0D)"0D)"0E/0TE?3TY%7U!)14-%+
HM"0D)"0D)"0D)
M"0E.54Q,+"!.54Q,+"
P+"!344Q#4U])35!,24-)5"D["BT)"0D)"0D)"0EO
M9F9S970@*ST@86UT.PHM"0D)"0D)"0D);B M/2!A;70["BT)"0D)"0D)"0EC
M;VYS=6UE("L](&%M=#L*+0D)"0D)"0D)?2!W:&EL92 H;BD["BT)"0D)"0D)
M?2!W:&EL92 H,2D["BT)"0D)"0D)3T-)3&]B0VQO<V4H4RT^2"T^<W9C+"!3
M+3YE<G(L("A/0TE,;V),;V-A=&]R*BE0+3YT:&EN9RD["BT)"0D)"0D)3T-)
M3&]B1FQU<VA"=69F97(H4RT^2"T^<W9C+"!3+3YE<G(L("A/0TE,;V),;V-A
M=&]R*BE0+3YT:&EN9RP@,"D["BL)"0D)"0D);V-I7W=R:71E7VQO8E]F<F]M
M7W-T<F5A;2A3+"!0+"!S=&TI.PH@"0D)"0D)?2!E;'-E(&EF("A:7U194$5?
M4"AP87)A;2T^<&%R86UE=&5R*2 ]/2!)4U]35%))3D<I('L*( D)"0D)"0DO
M*B!S=&EC:R!T:&4@<W1R:6YG(&EN=&\@=&AE($Q/0B J+PH@"0D)"0D)"6-O
M;G-U;64@/2!:7U-44E9!3%]0*'!A<F%M+3YP87)A;65T97(I.PID:69F("UU
M('!D;U]O8VE?8F%K+W!D;U]O8VDN8R!P9&]?;V-I+W!D;U]O8VDN8PHM+2T@
M<&1O7V]C:5]B86LO<&1O7V]C:2YC"3(P,#<M,#$M,#(@,3@Z,3<Z,3,N.#@W
M-#8P-CDV("TP-3 P"BLK*R!P9&]?;V-I+W!D;U]O8VDN8PDR,# W+3 Q+3 R
M(#$X.C$W.C0P+C,Q-S0T,C<R." M,#4P, I 0" M.3 L-B K.3 L.2! 0 H@
M("HO"B!02%!?34E.251?1E5.0U1)3TXH<&1O7V]C:2D*('L**PE214=)4U1%
M4E]01$]?0TQ!4U-?0T].4U1?3$].1R@B3T-)7U!!4D%-7T)?0DQ/0B(L("AL
M;VYG*5!$3U]/0TE?4$%204U?0E]"3$]"*3L**PE214=)4U1%4E]01$]?0TQ!
M4U-?0T].4U1?3$].1R@B3T-)7U!!4D%-7T)?0TQ/0B(L("AL;VYG*5!$3U]/
M0TE?4$%204U?0E]#3$]"*3L**PE214=)4U1%4E]01$]?0TQ!4U-?0T].4U1?
M3$].1R@B3T-)7U!!4D%-7T-214%415]414U07TQ/0B(L("AL;VYG*5!$3U]/
M0TE?4$%204U?0U)%051%7U1%35!?3$]"*3L*( EP:'!?<&1O7W)E9VES=&5R
M7V1R:79E<B@F<&1O7V]C:5]D<FEV97(I.PH@"B C:68@2$%615]/0TE%3E9#
M4D5!5$4*9&EF9B M=2!P9&]?;V-I7V)A:R]P:'!?<&1O7V]C:5]I;G0N:"!P
M9&]?;V-I+W!H<%]P9&]?;V-I7VEN="YH"BTM+2!P9&]?;V-I7V)A:R]P:'!?
M<&1O7V]C:5]I;G0N: DR,# W+3 Q+3 R(#$X.C$W.C U+C$V-C<X-C0T," M
M,#4P, HK*RL@<&1O7V]C:2]P:'!?<&1O7V]C:5]I;G0N: DR,# W+3 Q+3 R
M(#$X.C(U.C0W+C0W-#,X,S8Q-B M,#4P, I 0" M.3(L,R K.3(L.2! 0 H@
M#0H@97AT97)N('-T<G5C="!P9&]?<W1M=%]M971H;V1S(&]C:5]S=&UT7VUE
M=&AO9',[#0H@#0HK96YU;2!P9&]?;V-I7W!A<F%M('L-"BL)4$1/7T]#25]0
M05)!35]"7T),3T(@/2 P># Q+ T**PE01$]?3T-)7U!!4D%-7T)?0TQ/0B ]
M(#!X,#(L#0HK"5!$3U]/0TE?4$%204U?0U)%051%7U1%35!?3$]"(#T@,'@P
,-"P-"BM].PT**PT*
`
end