Oracle cursor problem - Printable Version

Oracle cursor problem - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Oracle cursor problem (/showthread.php?tid=8755)

Oracle cursor problem - El Forum - 05-30-2008

[eluser]Unknown[/eluser]
Hello. I`m a new user of the codeignitor. Trying to get data from the oracle base using a CodeIgniter functioanality. My user function (which is listed below) returning a refcursor. And I can not rich the data from it. CodeIgniter always return an error like this:

Message: ocibindbyname() [function.ocibindbyname]: Error while trying to retrieve text for error ORA-01036

here is the code:

Code:
public function getAll() {

        $curs = $this->db->get_cursor();

        $params=array(

                    array('name'=>'data', 'value'=>&$curs, 'type'=>OCI_B_CURSOR, 'length'=>-1)

            );

        return $this->db->stored_procedure("TEST","GETDATA" ,$params);

    }

Here is an oracle function:

Code:
RETURN SYS_REFCURSOR IS

     RESULT SYS_REFCURSOR;

BEGIN

     OPEN RESULT FOR

          SELECT NAME

          FROM TABLE;

     RETURN RESULT;

END;

And here is the example of the php code (without a codeignitor framework) which returns me a normal data.

Code:
$conn = oci_connect("login", "pass", $db, 'UTF-8');

$curs = OCINewCursor($conn); 

$stmt = OCIParse($conn,"begin :data := package.procedure(); end;"); 

ocibindbyname($stmt,"data",&$curs,-1,OCI_B_CURSOR); 

oci_execute($stmt); 

oci_execute($curs); 

while (OCIFetchInto($curs,&$data)) { 

   var_dump($data); 

} 

OCIFreeStatement($stmt); 

OCIFreeCursor($curs); 

OCILogoff($conn);

Please help me to understand how to use it with cursors.

Oracle cursor problem - El Forum - 05-30-2008

[eluser]Weblizard[/eluser]
it is just a guess !
You may need to put ':' before variable's name , change this

Code:
array('name'=>'data', 'value'=>&$curs, 'type'=>OCI_B_CURSOR, 'length'=>-1)

to this

Code:
array('name'=>':data', 'value'=>&$curs, 'type'=>OCI_B_CURSOR, 'length'=>-1)

Oracle cursor problem - El Forum - 06-02-2008

[eluser]Unknown[/eluser]
I tried this. It is also not working.
Any way i wrote my own part of code for this situation.
Thanks.
It was just a surprise for me that such functionality is not present.

Oracle cursor problem - El Forum - 06-02-2008

[eluser]Weblizard[/eluser]
I've some problem with stored_procedure method too and also I change it on my way to works correctly with mine
Can you explain what did you get after adding ':' to the code?

Oracle cursor problem - El Forum - 06-02-2008

[eluser]unficyp[/eluser]
hello,

"Error while trying to retrieve text for error ORA-01036" means that you are
missing some environment variables (eg ORACLE_HOME,NLS_LANG,etc) because the oracle client
cant find any error TEXt for the error CODE.

ORA-01036 means illegal variable name/number.
http://ora-01036.ora-code.com/

illegal variables are often reserved keywords in oracle..like number,type,etc.

btw..there is a patch for the oci8 driver:
http://ellislab.com/forums/viewthread/80004/

maybe this will fix your problem ?

i never used the CI db layer,so i don't know how to handle stored procs....

good look Smile

Oracle cursor problem - El Forum - 10-28-2009

[eluser]Jbeetle[/eluser]
hello,

howto get data from $cursor?

Thanks.