Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: Need OCI check for valid reference cursors returned as out parameter from stored proc

    If I have the following:
    create sp1(foo in number, rc1 out refcursor)
    as
    begin
    select bar from t_table where val=1;
    if (bar < 0) then
    return; -- if we return here, the open rc1 code below doesn't get run.
    end;
    open rc1 for select baz, bit, bam from t_other_table; --etc...
    end;


    In my c code:
    retval = OCIStmtExecute(sp1h...)
    retval is 0;
    ...later...
    if (retval = OCIParamGet( rc1stmt_Handle, OCI_HTYPE_STMT, errh,
    (void**)&param, idx )) == OCI_SUCCESS)
    retval does return 0 (ie. OCI_SUCCESS), but the value in param is
    garbage (on my machine it is 0xcccccccc), so my next statement,
    OCIAttrGet(...), fails with a bad handle.

    In sqlplus, the exec also returns fine, but when I print out rc1, I get:
    SQL> print rc1;
    ERROR:
    ORA-24338: statement handle not executed
    SP2-0625: Error printing variable "rc1"

    This is because it never get's to the open rc1 part of the SP if it returns prematurely.

    My question is:
    Without changing the stored procedure, how can I write a check in my c code (ie. OCI) to see if the rc1 is 'valid'; ie. I can proceed to access its column names, and other parameter items?

  2. #2
    Join Date
    Feb 2004
    Posts
    5

    Re: Need OCI check for valid reference cursors returned as out parameter from stored proc

    Originally posted by wjmcdonald
    If I have the following:
    create sp1(foo in number, rc1 out refcursor)
    as
    begin
    select bar from t_table where val=1;
    if (bar < 0) then
    return; -- if we return here, the open rc1 code below doesn't get run.
    end;
    open rc1 for select baz, bit, bam from t_other_table; --etc...
    end;


    In my c code:
    retval = OCIStmtExecute(sp1h...)
    retval is 0;
    ...later...
    if (retval = OCIParamGet( rc1stmt_Handle, OCI_HTYPE_STMT, errh,
    (void**) & param, idx )) == OCI_SUCCESS)
    retval does return 0 (ie. OCI_SUCCESS), but the value in param is
    garbage (on my machine it is 0xcccccccc), so my next statement,
    OCIAttrGet(...), fails with a bad handle.

    In sqlplus, the exec also returns fine, but when I print out rc1, I get:
    SQL> print rc1;
    ERROR:
    ORA-24338: statement handle not executed
    SP2-0625: Error printing variable "rc1"

    This is because it never get's to the open rc1 part of the SP if it returns prematurely.

    My question is:
    Without changing the stored procedure, how can I write a check in my c code (ie. OCI) to see if the rc1 is 'valid'; ie. I can proceed to access its column names, and other parameter items?
    The ampersand 'm' is really a & with 'param' after it.

  3. #3
    Join Date
    Oct 2010
    Posts
    1
    Search At Valid Reference, That would be a help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •