#18 Problem when binding output stored procedures

[Luiz Rafael Culik]

Hi

I found you library very good, and i´m planning to use on an new project.

this small procedure
create or replace
procedure test3
(
param1 out varchar2
) as
begin
param1:='Hi world';
end test3;

never show hi world on screen

this is the code i tested

include "ocilib.h"

int main(void)
{
OCI_Connection cn;
OCI_Statement st;
OCI_Resultset *rs ;

int code = 22;
int code2 = 2;

if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
    return EXIT_FAILURE;

cn = OCI_ConnectionCreate("culik3", "proprio", "proprio", OCI_SESSION_DEFAULT);

st = OCI_StatementCreate(cn);

printf( "%i\n",OCI_Prepare(st, "BEGIN test3(:22); END; "));
printf( "%i\n",    OCI_SetBindMode( st,OCI_BIND_BY_POS )) ;
printf( "%i\n",    OCI_RegisterString(st, ":1",30));

printf( "%i\n",    OCI_Execute(st));

rs = OCI_GetResultset(st);
/* print resultset content */
while (OCI_FetchNext(rs))

{

    printf("> String : %s", OCI_GetString(rs, 1));

}

OCI_Commit(cn);

OCI_Cleanup();

return EXIT_SUCCESS;

}

Debugging ocilib sources i found the problem here
boolean OCI_DefineIsDataNotNull
(
OCI_Define *def
)
{
boolean res = FALSE;

if ((def != NULL) && (def->rs->row_cur > 0))  <------ ***Here the problem, def->rs->row_cur is 0 for executing this procedure***
{
    OCIInd ind = OCI_IND_NULL;

    if (def->col.type == OCI_CDT_OBJECT)
    {
       ind =  * (OCIInd *) def->buf.obj_inds[def->rs->row_cur-1];
    }
    else
    {
        ind = ((OCIInd *) (def->buf.inds))[def->rs->row_cur-1];

    }

    res = (ind != OCI_IND_NULL);
}

return res;

}

Regards
Luiz Rafael Culik

[Luiz Rafael Culik]

HI

just for complement previus message.
Also the OCI_Resultset row_cur field is keeped as 0 , so the call to OCI_FetchNext will close the fetch since rs->row_abs and rs->row_count is both 0, making OCI_GetXXXX Fail

Regards
Luiz

[Vincent Rogier]

Hello,

It does not work because you're not using the intended method for achieving what you want to do.
Before deeping in source code, take a bit of time reading on the documentation.

OCi_RegisterXXX() calls must be used only for supporting Oracle SQL "Returning into" clause.
When performing regular binding, you must use OCI_BindXXX() calls and must not use resultsets...

Another thing, you declared your precedure "test" taking an out string parameter.
But in your C code, you passing a bind string ":22" and tehn declare a bind ":1"... It cannot work.

Thus here is the right (and much simplier) way to do what you want :

int main(void)
{
    OCI_Connection *cn;
    OCI_Statement *st;

    char res[250] = "";

    OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT);

    cn = OCI_ConnectionCreate("culik3", "proprio", "proprio", OCI_SESSION_DEFAULT);
    st = OCI_StatementCreate(cn);

    OCI_Prepare(st, "BEGIN test3(:1); END; ");
    OCI_BindString(st, ":1", res, 250);
    OCI_Execute(st);

    printf("> test3 outout variable content is  : %s\n", res);

    OCI_Cleanup();

    return EXIT_SUCCESS;
}

[Luiz Rafael Culik]

Hi Vincent

Thanks for answer, but as I know for positional binds the name of parametere dont matter.

So for an stored procedure with output cursor, i can use this example bellow safely, correct

OCI_Statement* st2 = OCI_StatementCreate(cn);

print_text("\n>>>>> TEST REF CURSOR \n\n");

OCI_Prepare(st, MT("begin open :c for select * from test_fetch; end;"));
OCI_BindStatement(st, MT(":c"), st2);
OCI_Execute(st);

rs = OCI_GetResultset(st2);

/* print resultset content */
while (OCI_FetchNext(rs))
{
    print_frmt("> code : %i", OCI_GetInt(rs, 1));
    print_text(", action : "); print_dstr(OCI_GetString(rs, 2));
    print_frmt(", price : %g", OCI_GetDouble(rs,3));
    print_text(", date : "); print_dstr(OCI_GetString(rs, 4));
    print_text("\n");
}

Regards
Luiz