ocinewdescriptor

(PHP 4, PHP 5, PECL OCI8 >= 1.0.0)

ocinewdescriptor — Alias von oci_new_descriptor()

Beschreibung

Warnung

Dieser Alias wurde mit PHP 5.4.0 als DEPRECATED (veraltet) markiert. Sich auf diesen Alias zu verlassen ist in keiner Weise empfehlenswert.

add a note User Contributed Notes
ocinewdescriptor

kirt at diagonalsoftware dot com
27-Jul-2005 04:43


Here is an example of retrieving a CLOB as an output parameter from a stored procedure. This is a bit hack-y and maybe there's a cleaner way to do this, but I couldn't find one. The following definitely works with Oracle 9:



// the query to call the procedure, which includes declaring the

// output parameter and assigning the result to a variable to be bound.

$qry = '

declare clob_out clob;

begin

  myprocedure(someparam_in, clob_out); 

  :myclob := clob_out;

end;

';



// parse the query and bind the 'myclob' variable

$sth = OCIParse($conn,$qry);

$myclob = OCINewDescriptor($conn,OCI_D_LOB);

OCIBindByName($sth,":myclob",$myclob,-1,OCI_B_CLOB);



OCIExecute($sth);



// display the results

echo $myclob->load();

<Serg Petrenko> pserg at inkfrog dot com
09-Jun-2005 06:22


How to insert big XML data as CLOB into table with XMLType field.



<?php



//CREATE TABLE sometable(

//id number(8) not null,

//record XMLType

//) XMLTYPE COLUMN record STORE AS OBJECT RELATIONAL

//XMLSCHEMA "someschema" ELEMENT "some_element";

//



$sql = "INSERT INTO sometable(id, record) VALUES(some_sequqnce.nextval, sys.xmltype.createxml(:rec)) RETURNING ID INTO :rid";

$stmt = OCIParse($ora_conn,$sql);

$clob = OCINewDescriptor($ora_conn, OCI_D_LOB);

$rowid = OCINewDescriptor($ora_conn,OCI_D_ROWID);

OCIBindByName($stmt, ':rec', &$clob, -1,OCI_B_CLOB);

OCIBindByName($stmt, ':rid', $rowid, -1);

$clob->WriteTemporary($xml,OCI_TEMP_CLOB);

$success = OCIExecute($stmt,OCI_DEFAULT);

if(!$success) {

OCICommit($ora_conn);

}

OCIFreeStatement($stmt);

OCIFreeDesc($lob);



?>



I hope it will help :)

cjbj at hotmail dot com
17-Mar-2004 07:55


In PHP5 the way Example 2 passes a CLOB bind variable as an input

parameter to a PL/SQL procedure can be extended to BLOBs.



The critical change is:



    OCIBindByName($stmt, ':data', $blob, -1, OCI_B_BLOB);

    $blob->WriteTemporary($data, OCI_B_BLOB);



This doesn't work for me in PHP4.  I believe it is because the

implementation of OCIWriteTemporaryLob() always binds as a CLOB.

(This is true as of php4-STABLE-200403170230).  In PHP5 the interface

has changed and a type parameter is permitted.

Nathan Rogers
06-Nov-2003 12:35


I found another method of inserting/updating lob data.  It works the same was as passing lob parameters to a stored procedure and avoids the need for a RETURNING clause.

    $lob = OCINewDescriptor($conn, OCI_D_LOB);

    $stmt = OCIParse($conn,"insert into $table (id, the_blob) 

               values(my_seq.NEXTVAL, :the_blob)");

    OCIBindByName($stmt, ':the_blob', &$lob, -1, OCI_B_BLOB);

    $lob->WriteTemporary($data);

    OCIExecute($stmt, OCI_DEFAULT);

    $lob->close();

    $lob->free();

    OCICommit($conn);



There are some cases involving triggers where you can't use a RETURNING clause, so this method can come in handy.  The case where I needed it was updating a view that had an instead-of update trigger.

Maxwell_Smart (at) ThePentagon (dot) com
23-Sep-2003 11:13


Just a note. When INSERTing a CLOB, if a VALUES clause is used, Oracle notes: You cannot initialize an internal LOB attribute in an object with a value other than empty or null. That is, you cannot use a literal.



That's why all the examples here INSERT an EMPTY_CLOB(), and use RETURNING to grab the pointer.



However, a CLOB can also be INSERTed via a SELECT statement, and that won't require any descriptors.



Example:



$Clob = Str_Replace("'", "''", $Clob);



OCIParse($DB, "INSERT INTO My_Table (My_Clob) SELECT '$Clob' FROM Dual");



This, of course, allows the use of a WHERE clause as well.

tca at engineer dot com
25-Oct-2002 10:33


Two examples of retrieving CLOBs from the database.  They are almost identical.  The first is using a package(and cursor) which is how I interface to Oracle at work, and the second is using straight SQL, which most people post examples in.



I also convert the case from upper to lower, since that is how I prefer to work with assoc arrays...



Instead of using the get_class() function you could use the OCIColumnType() function which (in this case) would return 'CLOB' as a result...



/**

* Example 1

*

* Using a PL/SQL package and cursor

*

*/

$cursor=':p_cur';

$sql2="begin clobPackage.getClob($cursor); end;";

$curs=OCINewCursor($conn);

$stmt=OCIParse($conn,$sql2);

OCIBindByName($stmt,$cursor,&$curs,-1,OCI_B_CURSOR);

OCIExecute($stmt,OCI_DEFAULT);

OCIExecute($curs,OCI_DEFAULT);

$x=0;

while(OCIFetch($curs)){ 

  $cols=OCINumCols($curs);

  for($i=1;$i<=$cols;$i++){

    $column_name=OCIColumnName($curs,$i);

    if(is_object($tmp=OCIResult($curs,$i))&&get_class($tmp)=='OCI-Lob'){

      $column_value=$tmp->load();

    }else{

      $column_value=$tmp;

    }

    $result[$x][strtolower($column_name)]=trim($column_value);

  }

  $x++;

}

OCICommit($conn);



/**

* Example 2

*

* Using a SELECT

*

*/

$query="SELECT a_num, a_clob FROM clob_test";

$stmt=OCIParse($conn,$query);

OCIExecute($stmt,OCI_DEFAULT); 

$x=0;

while(OCIFetch($stmt)){ 

  $ncols=OCINumCols($stmt);

  for($i=1;$i<=$ncols;$i++){

    $column_name=OCIColumnName($stmt,$i);

    if(is_object($tmp=OCIResult($curs,$i))&&get_class($tmp)=='OCI-Lob'){

      $column_value=$tmp->load();

    }else{

      $column_value=$tmp;

    }

    $result[$x][strtolower($column_name)]=trim($column_value);

  }

  $x++;

}

OCICommit($conn);



I hope someone finds this useful.



Cheers,

Keith.

sozturk at emediamillworks dot com
10-Apr-2002 12:30


I had the same problem with updating the lobs with shorter content as in one of the notes above. The addition of "\0" at the end of the replacement text didn't help either. But the following worked perfectly:



$sql = "UPDATE sometable SET lob_col = EMPTY_LOB() WHERE key_col = $key RETURNING lob_col INTO :lob";

$stmt = OCIParse($conn,$sql);

$lob = OCINewDescriptor($conn,OCI_D_LOB);

OCIBindByName($stmt,':lob',&$lob,-1,OCI_B_BLOB);

OCIExecute($stmt,OCI_DEFAULT);

$lob->save($sometext);

$lob->free();

aidanpeiser at yahoo dot com
27-Mar-2002 08:12


another way to display your clob details !



    $query = "select * from Your_clob_table";

    $stmt = OCIParse($conn, $query);

    ociexecute($stmt); 

        

        while ( OCIFetch($stmt)) 

        { 

         $lob = OCIResult($stmt,"CLOB_MESSAGE");

         $CLOB_MESSAGE = $lob->load();

         echo $CLOB_MESSAGE;

        }



this works,

moom_mong at yahoo dot com
19-Dec-2001 07:54


To read a lob, other way: 





$sql = OCIParse("select * from table_with_lob_field"); 


OCIExecute($sql, OCI_DEFAULT); 


while ( OCIFetch($sql)) { 


 $o = ociresult($sql,"loc_field_name");


 $loc_field_name = $o->load();


 print $loc_field_name;


};

dave dot dunkin at vista dot com
16-Aug-2000 12:16


PHP3 has ocifreedescriptor($desc) and PHP4 has ocifreedesc($desc), but both support $desc->free().

jcd at iddg dot com
03-Mar-2000 11:42


The code up above is somewhat correct... here's an example of how I got a CLOB to work:<br>


<pre>


function insert_adinfo($AdInfoID, $MagazineType, $Publish, $DatePost, $BodyText) 


{


   global $db;





    // Insert record into database


    $clob = OCINewDescriptor($db, OCI_D_LOB);


    $stmt = OCIParse($db,"insert into tblAdInfo values ($AdInfoID,                  $MagazineType, '$Publish', to_date('$DatePost', 'YYYY-MM-DD'),                  EMPTY_CLOB()) returning BodyText into :the_blob");


    OCIBindByName($stmt, ':the_blob', &$clob, -1, OCI_B_CLOB);


    OCIExecute($stmt, OCI_DEFAULT);


    if($clob->save($BodyText)){


        OCICommit($db);


    }else{


        echo "Problems: Couldn't upload Clob\n";


    }


   


   OCIFreeDescriptor($clob);


   OCIFreeStatement($stmt);


}


</pre>

add a note

ocinlogon

ocinewcursor

Last updated: Fri, 18 May 2012