external procedure and connecting...

From: Jomsch Kilroy <jedi99_at_gmx.net>
Date: Thu, 6 Jul 2000 14:44:53 +0200
Message-Id: <10550.111329@fatcity.com>

hi cracks,

just another question (with code fragments) about oci-programming for oracle 8.0.5 on linux:

my proc.definition in oracle is:
(it's my first external proc ever...)

1.) create or replace library voss_ext_procs as '/oracle/app/oracle/product/8.0.5/vossen_tools.so';

2.) PROCEDURE do_pack(packing_seq IN varchar2)

    IS EXTERNAL
    NAME "verpack"
    LIBRARY voss_ext_procs
    language c
    WITH CONTEXT
    PARAMETERS (CONTEXT,

                packing_seq string);

it's a c-prg compiled with gcc and linked with ld, i don't want to paste all the code in here 'cos it's more than 2500 lines, first i want to fetch some stuff into a structure with int's and chars, process it and write it back to another table.

i've managed to do this now, but i've to connect back to oracle out of the external proc again using this fragment:

   rc = OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0,

          (dvoid * (*)(dvoid *, dvoid *, size_t))0,
          (void (*)(dvoid *, dvoid *)) 0 );

   rc = OCIEnvInit( (OCIEnv **) &p_env, OCI_DEFAULT, (size_t) 0, (dvoid **) 0 );

   rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_err, OCI_HTYPE_ERROR,

           (size_t) 0, (dvoid **) 0);

   rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &with_context, OCI_HTYPE_SVCCTX,
           (size_t) 0, (dvoid **) 0);

// external logon !!!!!!!!
// oracle does not recomend that!!!

   rc = OCILogon(p_env, p_err, &p_svc, "uid", 6, "pw", 6, "instance", 3);

    rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_sql,

           OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);    rc = OCIStmtPrepare(p_sql, p_err, (text *)command_string,

           (ub4) strlen(command_string), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
// all the variables go like this...

   rc = OCIDefineByPos(p_sql, &p_dfn, p_err, 1, (dvoid *) &p_obj_id,

           (sword) 22, SQLT_STR, (dvoid *) 0, (ub2 *)0,
           (ub2 *)0, OCI_DEFAULT);

....

// the exec the statement

   rc = OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0,

      (CONST OCISnapshot *) NULL,
      (OCISnapshot *) NULL, OCI_DEFAULT);

// and then fetch data until !OCI_SUCCESS

   if (rc==OCI_SUCCESS)
    {
    max_packings=0;
    while (rc != OCI_NO_DATA)

     {
        p_store  = atoi(p_apg_obj_id);
        p_index  = packings[p_store][0].max;
        p_index++;

            #ifdef DEBUG_ME_FULL
             fprintf(file_handle,"Storing Packing :
%i/%i\n",p_store,p_index);
            #endif

        packings[p_store][0].max++;

        packings[p_store][p_index].obj_id      =  atoi(p_obj_id);
        packings[p_store][p_index].name        =  atoi(p_name);
        packings[p_store][p_index].max_volume  =  atof(p_volume);
        packings[p_store][p_index].prod_gruppe =  p_store;
        rc = OCIStmtFetch(p_sql, p_err, 1, 0, 0);

        max_packings++;

     }

 }
...

// and finally free all handles...

   rc = OCIHandleFree((dvoid *) p_sql, OCI_HTYPE_STMT); /* Free handles */

   rc = OCIHandleFree((dvoid *) with_context, OCI_HTYPE_SVCCTX);

   rc = OCIHandleFree((dvoid *) p_err, OCI_HTYPE_ERROR);
// end

---

my probles is

1. oracle does not recomend to connect again from an external procedure
2. i cannot use context which is a parameter of the calling function
3. due to the external connect the connection i establish stays until i
disconnect the main app     which calls do_pack() from the clients

4. managing it this way i have to hardcode username and pw for the database...

please help me out of this, i've been mangling my brain for weeks now... Received on Thu Jul 06 2000 - 07:44:53 CDT