[OCI] [ORA-00600] stored procedures with index-by table parameters

From: <sam.gwo_at_gmail.com>
Date: 15 Nov 2006 01:14:59 -0800
Message-ID: <1163582099.762021.33360@k70g2000cwa.googlegroups.com>

Hi all,

Everytime I recompile the stored procedure, my program could run correctly only once.

Any further running got ORA-00600 error.

Your replay is appreciated.

$ ./oci_test
Bind1
Bind2
Bind3
exec
val:test

                 World 11
                 World 22

$ ./oci_test
Bind1
Bind2
Bind3
Error -

                                                          ORA-00600:

internal error code, arguments: [17112], [0x9FFFFFFFEF55F750], [], [], [], [], [], []
OR

Below is the code:

/* HP-UX B.11.23 ia64 oracle 9.2.0.7 */
/******************************************************************/
/* A simple array binding exercise to a PL/SQL table. */
/* Demonstrates the use of the OciBindByPos array functionality */
/* in the context of an OciBindArrayOfStruct context */
/******************************************************************/
/*

• This program is from the Internet;And I do some modifictions. */
  /*
  create or replace package test as type buf_arr is table of varchar2(20) index by binary_integer; procedure arr (p1 out varchar2, p2 out buf_arr, p3 out varchar2); end; /

create or replace package body test as
  procedure arr (p1 out varchar2, p2 out buf_arr, p3 out varchar2) is   begin
    p2(1) := 'World 11';
    p2(2) := 'World 22';
  end;
end;
/
*/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#include <oci.h>

OCIEnv    *p_env;
OCIError  *p_err;
OCISvcCtx *p_svc;
OCIStmt   *p_sql;

OCIBind   *p_bnd1;

OCIBind *p_bnd2;
OCIBind *p_bnd3;

int rc = 0;

char v_val1[21] = {"test"};
char v_val2[6][21];
char v_val3[21] = {"test"};

int error(int rc);

int main()
{

        // Step 1: Initialize OCI
        rc = OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,
                (dvoid * (*)(dvoid *, size_t)) 0,
                (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                (void (*)(dvoid *, dvoid *)) 0 );

        // Step 2: Initialize the OCI evironment
        rc = OCIEnvInit( (OCIEnv **) &p_env, OCI_DEFAULT, (size_t) 0,

(dvoid **) 0 );

        // Step 3: Initialize the OCI handles
        rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_err,
OCI_HTYPE_ERROR,
                (size_t) 0, (dvoid **) 0);
        rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_svc,
OCI_HTYPE_SVCCTX,
                (size_t) 0, (dvoid **) 0);

        char* v_usr = "scott";
        char* v_pwd = "tiger";
        char* v_tns = "local";

        // Step 4: Connect using a single session connect
        rc = OCILogon(p_env, p_err, &p_svc, (OraText *) v_usr, strlen
(v_usr),
                (OraText *) v_pwd, strlen(v_pwd), (OraText *) v_tns,

strlen(v_tns));

        error(rc);

        // Step 5: Allocate and prepare a SQL statement
        rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_sql,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);

        char* v_sql = "begin test.arr(:p1,:p2,:p3); end;";

        rc = OCIStmtPrepare(p_sql, p_err, (OraText *) v_sql, (ub4)
strlen(v_sql),
                (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);

        error(rc);


        ub4 v_val_cnt1 = 0;
        ub4 v_val_cnt2 = 0;
        ub4 v_val_cnt3 = 0;

        // Step 6: Bind the values for the bind variable

        rc = OCIBindByPos(p_sql, &p_bnd1, p_err, 1,
                (dvoid *) &v_val1[0],
                (sb4) 21,
                SQLT_STR,
                (dvoid *) 0,
                (ub2 *) 0,
                (ub2 *) 0,
                (ub4) 0,                // indicates the maximum array
size
                (ub4 *) &v_val_cnt1, // recieves the number of elements
used
                OCI_DEFAULT);
				error(rc);

        printf("Bind1\n");

        rc = OCIBindByPos(p_sql, &p_bnd2, p_err, 2,
                (dvoid *) &v_val2[0][0],
                (sb4) 21,
                SQLT_STR,
                (dvoid *) 0,
                (ub2 *) 0,
                (ub2 *) 0,
                (ub4) 6,                // indicates the maximum array
size
                (ub4 *) &v_val_cnt2, // recieves the number of elements
used
                OCI_DEFAULT);

        error(rc);

        rc = OCIBindArrayOfStruct(p_bnd2, p_err, (sb4)21, 0, 0, 0);
        error(rc);
        printf("Bind2\n");

        rc = OCIBindByPos(p_sql, &p_bnd3, p_err, 3,
                (dvoid *) &v_val3[0],
                (sb4) 21,
                SQLT_STR,
                (dvoid *) 0,
                (ub2 *) 0,
                (ub2 *) 0,
                (ub4) 0,                // indicates the maximum array
size
                (ub4 *) &v_val_cnt3, // recieves the number of elements
used
                OCI_DEFAULT);
        error(rc);
        printf("Bind3\n");

				rc = OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0, (CONST
										OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);
        error(rc);
        printf("exec\n");

        // Print out the result
				printf("val:%s\n",v_val3);
        for (unsigned int i=0; i < v_val_cnt2; i++)
                printf("%25s\n",&v_val2[i][0]);

        // Disconnect from the server and free the
        rc = OCILogoff(p_svc, p_err);
        rc = OCIHandleFree((dvoid *) p_sql, OCI_HTYPE_STMT);
        rc = OCIHandleFree((dvoid *) p_svc, OCI_HTYPE_SVCCTX);
        rc = OCIHandleFree((dvoid *) p_err, OCI_HTYPE_ERROR);

        return 0;

}

int error(int rc)
{

        // This is a generic error checking routine
        char errbuf[100];
        int      errcode;

        if (rc != 0)
        {
          OCIErrorGet((dvoid *)p_err, (ub4) 1, (OraText *) NULL,(sb4
*)&errcode,
                        (OraText *) errbuf, (ub4)
sizeof(errbuf),OCI_HTYPE_ERROR);
          printf("Error - %2525.*s\n", 512, errbuf);
          exit(0);
        }
        return 0;

} Received on Wed Nov 15 2006 - 03:14:59 CST