Message-Id: <10550.111329@fatcity.com> From: "Jomsch Kilroy" Date: Thu, 6 Jul 2000 14:44:53 +0200 Subject: external procedure and connecting... 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...