Re: Using OCI with stored procedures

/* ---------------------------- PL/SQL --------------------------- */

CREATE OR REPLACE PACKAGE SomePackage AS   PROCEDURE spGetStr (str OUT varchar2); END SomePackage;

CREATE OR REPLACE PACKAGE BODY SomePackage AS

 CURSOR curAvisor IS SELECT * FROM some_table;

PROCEDURE spGetStr (str OUT varchar2) AS   recAvisor curAvisor%ROWTYPE; -- запись BEGIN     str := '';

• open cursor ( if we haven't opened it yet) if not curAvisor%ISOPEN then open curAvisor; end if;

    begin

• get next record fetch curAvisor INTO recAvisor;
  • make some string str := recAvisor.data1 || recAvisor.data2 || recAvisor.data3 || recAvisor.data4; exception
    • close cursor when no_data_found then close curAvisor; when others then close curAvisor; raise; end;

END; END SomePackage;

/* ---------------------------- C  --------------------------- */

char rec [MAX_STROUT];
text* strSQL = (text*)"BEGIN SomePackage.spGetStr(:str); END;";

. . .

if (oopen(&cda1, &lda, (text* ) 0, -1, -1, (text*) 0, -1)) { err_report(&lda,NULL); ... }

if ( oparse ( &cda1, (text*)strSQL, (sb4) -1, DEFER_PARSE,(ub4) VERSION_7)) { err_report(&cda1,NULL); ... }

obndrv ( &cda1, (text *) ":str", -1, (ub1 *) &rec, sizeof(rec),

             VARCHAR2_TYPE, -1, (sb2 *) 0, (text *) 0, -1, -1);

 while (1)
   {

         // -------------- Execution ----------
         if ( oexec (&cda1) )
           {
              if ( cda1.rc != 1405 )  // ORA1405 - fetched column value is NULL
                err_report(&cda1,NULL);
              break;
            }
        some_action (rec,MAX_STROUT);
     }

. . .

TimKit wrote:

> I'm having a little difficulty in determining how you're supposed to define and
> process select list items which are fetched via stored procedures and OCI.
> I've gone over and over the API documentation, but have found very little in
> the way of examples or good explanations.  Could someone please provide a
> simple example  or tell me where I may be going wrong.  Here are the
> programmatic steps for my C function:
>