Title: External Procedures "C"
Example of external procedures using "C"Source/Text/Comments
/* Copyright (c) Oracle Corporation 1997, 1998. All Rights Reserved. */
/*
NAME
extproc.c - C function to illustrate the External Procedures.
DESCRIPTION
This file contains 'C' functions which are called from extproc.sql
*/
/*************************************************************************
*
* This file has 'C' functions for external procedures in demopack procedure
* in extproc.sql.
*
*
***************************************************************************/
#include
#include
#include
#include
#include
#ifndef OCI_ORACLE
# include
#endif
#define BUFLEN 256
struct ocictx
{
OCIEnv *envhp; /* For OCI Environment Handle */
OCISvcCtx *svchp; /* For OCI Service Handle */
OCIError *errhp; /* For OCI Error Handle */
OCIStmt *stmtp; /* For OCI Statement Handle */
OCIStmt *stm1p; /* For OCI Statement Handle */
OCIBind *bnd1p; /* For OCI Bind Handle */
OCIBind *bnd2p; /* For OCI Bind Handle */
OCIBind *bnd3p; /* For OCI Bind Handle */
OCIDefine *dfn1p; /* For OCI Define Handle */
OCIDefine *dfn2p; /* For OCI Define Handle */
OCIDefine *dfn3p; /* For OCI Define Handle */
};
typedef struct ocictx ocictx;
void check_err( str, err, errbuf, oci_ctxp )
char *str;
int err;
char *errbuf;
ocictx *oci_ctxp;
{
if ( err )
{
OCIErrorGet( (dvoid *)oci_ctxp->errhp, (ub4) 1, (text *)0, (sb4 *)&err,
(text *)errbuf, (ub4)sizeof( errbuf ), (ub4)OCI_HTYPE_ERROR );
fprintf(stdout,"%s returned errno: %d\nerrstr: %s\n", str, err, errbuf);
return;
}
else
fprintf(stdout,"%s returned: 0\n", str);
}
/* ----------------------------- UpdateSalary ----------------------------- */
/* UpdateSalary - This function updates the sal column of emp table
* corresponding to the given rowid.
*
*/
#ifdef WIN32COMMON
__declspec(dllexport)
#endif
void UpdateSalary(with_context, r_id, r_len)
OCIExtProcContext *with_context; /* With Context ptr */
char *r_id; /* rowid */
sb4 r_len; /* length of rowid r_id */
{
char *sql_stmt =
"UPDATE emp SET sal = sal+20 WHERE rowid = chartorowid(:1)";
int err;
ocictx oci_ctx;
ocictx *oci_ctxp = &oci_ctx;
char errbuf[512];
/* Obtain OCI handle for SQL statement using the context passed. */
err = OCIExtProcGetEnv(with_context, /* With context */
&oci_ctxp->envhp,
&oci_ctxp->svchp,
&oci_ctxp->errhp);
check_err("OCIExtProcGetEnv", err, errbuf, oci_ctxp);
/* Obtain the Statement handle. */
err = OCIHandleAlloc(oci_ctxp->envhp,
(dvoid **)&oci_ctxp->stmtp,
OCI_HTYPE_STMT,
0,
0);
check_err("OCIHandleAlloc(stmth)", err, errbuf, oci_ctxp);
/* Obtain the Bind handle. */
err = OCIHandleAlloc(oci_ctxp->stmtp,
(dvoid **)&oci_ctxp->bnd1p,
OCI_HTYPE_BIND,
0,
0);
check_err("OCIHandleAlloc(bnd1h)", err, errbuf, oci_ctxp);
/* Prepare the Statement for execution. */
err = OCIStmtPrepare(oci_ctxp->stmtp,
oci_ctxp->errhp,
(text *) sql_stmt,
strlen(sql_stmt),
OCI_NTV_SYNTAX,
OCI_DEFAULT);
check_err("OCIStmtPrepare", err, errbuf, oci_ctxp);
/* Bind the variable by Position. */
err = OCIBindByPos(oci_ctxp->stmtp,
&oci_ctxp->bnd1p,
oci_ctxp->errhp,
1,
r_id,
r_len,
SQLT_CHR,
0,
0,
0,
0,
0,
OCI_DEFAULT);
check_err("ocibndp(rowid)", err, errbuf, oci_ctxp);
/* Execute the Statement. */
err = OCIStmtExecute(oci_ctxp->svchp,
oci_ctxp->stmtp,
oci_ctxp->errhp,
1,
0,
NULL,
NULL,
OCI_DEFAULT);
check_err("OCIStmtExecute", err, errbuf, oci_ctxp);
/* Free the SQL statement handle. */
err = OCIHandleFree(oci_ctxp->stmtp,
OCI_HTYPE_STMT);
check_err("OCIHandleFree", err, errbuf, oci_ctxp);
} /* UpdateSalary */
/* ------------------------------ PercentComm ------------------------------ */
/* PercentComm - This function calculates the percentage of commission with
* respect to salary and return the commission. If commission indicator is
* OCI_IND_NULL it returns (float)0.0.
*
*/
#ifdef WIN32COMMON
__declspec(dllexport)
#endif
float PercentComm(salary, salary_ind, comm, comm_ind, return_ind)
float salary; /* salary passed by value */
short salary_ind; /* INDICATOR for salary */
float comm; /* commission passed by value */
short comm_ind; /* INDICATOR for commission */
short *return_ind; /* INDICATOR to return NULL */
{
float tmpcomm;
/* Check if salary or commission is NULL
*/
if ((salary_ind == OCI_IND_NULL) || (comm_ind == OCI_IND_NULL))
{
tmpcomm = (float)0.0;
*return_ind = (short)OCI_IND_NULL;
}
else
{
tmpcomm = (float)(comm * 100 / salary);
*return_ind = (short)OCI_IND_NOTNULL;
}
return(tmpcomm);
} /* PercentComm */
/* --------------------------- PercentComm_ByRef --------------------------- */
/* PercentComm_ByRef - This function is the same as function PercentComm
* but uses doubles instead of floats and the parameters are passed
* By Reference.
*
*/
#ifdef WIN32COMMON
__declspec(dllexport)
#endif
double *PercentComm_ByRef(with_context, salary, salary_ind,
comm, comm_ind, return_indicator)
OCIExtProcContext *with_context; /* With Context */
double *salary; /* salary passed by ref */
short salary_ind; /* INDICATOR for salary */
double *comm; /* commission passed by ref */
short *comm_ind; /* INDICATOR for commission passed by ref */
short *return_indicator; /* RETURN indicator */
{
double *tmpdoubleptr;
/* Allocate a double for the return. PL/SQL will free this
* memory upon return from this function.
*
* CAUTION : You should not allocate an automatic variable ( on the
* C stack and return its address, since that memory location
* is not guranteed to exist with valid data once this function
* returnns to PL/SQL
*/
tmpdoubleptr = OCIExtProcAllocCallMemory(with_context, sizeof(double));
if ((salary_ind == (short)OCI_IND_NULL) ||
(*comm_ind == (short)OCI_IND_NULL) )
{
*tmpdoubleptr = (double)0.0;
*return_indicator = (short)OCI_IND_NULL; /* Indicator NULL for return */
}
else
{
*tmpdoubleptr = (double)(*comm) * 100.0 / (double)(*salary);
*return_indicator = (short)OCI_IND_NOTNULL; /* Indicate not null return */
}
return(tmpdoubleptr);
} /* PercentComm_ByRef */
/* -------------------------------- EmpExp -------------------------------- */
/* EmpExp - This function finds the difference between hiredate and
* current date and returns the number of years an employee worked
* with the company.
*
*/
#ifdef WIN32COMMON
__declspec(dllexport)
#endif
void EmpExp(hiredate, hiredate_len, hiredate_ind,
num_experience, num_experience_ind)
char *hiredate;
int hiredate_len; /* length of hiredate */
short hiredate_ind; /* indicator for hiredate */
long *num_experience; /* out : # years of experience */
int *num_experience_ind; /* out : indicator for num_experience */
{
struct tm *hire_date;
double diff; /* variable to store the difference in time in seconds */
double diff_day;
int dd;
int mm;
int yy;
time_t curr_time; /* store current time */
char *tok;
/* Allocate some memory for hire_date. NOTE, since calloc is being
* used to allocate the memory, PL/SQL has no knowledge of its
* allocation and hence the meory thus allocated will not be freed
* by PL/SQL. It is this functions responsibility to free such memory.
*
* After an alloc, typically, the program should check for errors !!
*/
hire_date = (struct tm *)calloc(1, sizeof(struct tm ));
/* Parse the input date string which is in DD-MM-YY format
*/
tok = strtok(hiredate, "-"); /* Get day string */
dd = atoi(tok); /* Convert to integer */
tok = strtok(NULL, "-"); /* Get month string */
mm = atoi(tok); /* Convert to integer */
tok = strtok(NULL, "\0"); /* Get Year String */
yy = atoi(tok); /* Convert to integer */
hire_date->tm_mday = dd;
hire_date->tm_mon = mm;
hire_date->tm_year = yy;
/* Get the difference in seconds. */
diff = (double)difftime( time(&curr_time), mktime(hire_date) );
/* Free the memory previously allocated */
free(hire_date);
/* Convert seconds to years. */
*num_experience = (long)(diff/(double)(60.0 * 60.0 * 24.0 * 365.0));
*num_experience_ind = (int)OCI_IND_NOTNULL; /* indicate not null value */
} /* EmpExp */
/* ----------------------------- CheckEmpName ----------------------------- */
/* CheckEmpName - This function checks if the source (src_ename)
* employee's name can be copied over to the destination (dst_ename).
* If the copy can be made, it copies the src_ename to the dst_ename
* else it raises the Numeric or value error.
*
*/
#ifdef WIN32COMMON
__declspec(dllexport)
#endif
int CheckEmpName(dst_ename, dst_ename_maxlen,
src_ename, src_ename_length, with_context)
char *dst_ename;
sb4 *dst_ename_maxlen; /* Maxlen */
char *src_ename;
sb4 src_ename_length; /* Length */
OCIExtProcContext *with_context; /* With context ptr */
{
#define NUMERIC_OR_VALUE_ERROR (int)(6502)
if (src_ename_length < *dst_ename_maxlen)
{
/* Since the src_ename string length is less than the maxlength of
* the dst_ename string buffer, copy the src_ename into the dst_ename
*/
strcpy(dst_ename, src_ename);
return((int)0);
}
else
{
/* Raise the numeric or value error */
OCIExtProcRaiseExcp(with_context, NUMERIC_OR_VALUE_ERROR);
/* immediately return back to PL/SQL */
return((int)-1);
}
return((int)-1); /* This code will never be executed, unless someone
changes the above logic */
#undef NUMERIC_OR_VALUE_ERROR
} /* CheckEmpName */
/* ----------------------------- LobDemo -------------------------------- */
/* LobDemo - This function demonstrates the usage of various OCI functions
* on lob locators passed as IN, INOUT & OUT and returns a locator.
* It executes following OCI Lob functions:
* 1. OCIDescriptorAlloc.
* 2. OCILobAssign.
* 3. OCILobCopy.
* 4. OCILobGetLength.
* 5. OCILobTrim.
* 6. OCILobRead.
* 7. OCILobWrite.
*/
#ifdef WIN32COMMON
__declspec(dllexport)
#endif
OCILobLocator *LobDemo ( with_context, lob_in, lob_inout, lob_out, ind_in,
ind_inout, ind_out, ind_ret )
OCIExtProcContext *with_context; /* With Context */
OCILobLocator *lob_in; /* Lob Locator passed as IN */
OCILobLocator **lob_inout; /* Lob Locator passed as IN OUT */
OCILobLocator **lob_out; /* Lob Locator passed as OUT */
sb4 ind_in; /* Indicator for lob_in*/
sb4 *ind_inout; /* Indicator for lob_inout */
sb4 *ind_out; /* Indicator for lob_out */
sb4 *ind_ret; /* Indicator for RETURN locator */
{
ocictx oci_ctx;
ocictx *oci_ctxp = &oci_ctx;
OCILobLocator *lob_ret;
int err;
char errbuf[512];
ub1 local_buf[BUFLEN];
ub4 len_in;
ub4 len_inout;
ub4 len_out;
ub4 offset;
ub4 amount;
ub4 local_buflen;
/* Obtain OCI handle for SQL statement using the context passed. */
err = OCIExtProcGetEnv(with_context, /* With context */
&oci_ctxp->envhp,
&oci_ctxp->svchp,
&oci_ctxp->errhp);
check_err("OCIExtProcGetEnv", err, errbuf, oci_ctxp);
/***** Test Assign lob_in to lob_inout *****/
err = (int) OCILobAssign(oci_ctxp->envhp,
oci_ctxp->errhp,
lob_in,
lob_inout);
check_err("OCILobAssign", err, errbuf, oci_ctxp);
/* Set indicator of lob_inout */
*ind_inout = ind_in;
/***** Test Read from lob_in and Write to lob_out *****/
/* Allocate a new lob descriptor for lob_out */
err = OCIDescriptorAlloc(oci_ctxp->envhp,
(dvoid **) lob_out,
(ub4) OCI_DTYPE_LOB, /* Type of Descriptor */
(size_t) 0,
(dvoid **) 0);
check_err("OCIDescriptorAlloc", err, errbuf, oci_ctxp);
/* Intialize the locator lob_ret by assigning lob_in to it */
/* We can also initialize it by selecting a valid locator from the database */
err = (int) OCILobAssign(oci_ctxp->envhp,
oci_ctxp->errhp,
lob_in,
lob_out);
check_err("OCILobAssign", err, errbuf, oci_ctxp);
/* Trim lob_out to have length = 1 */
err = (int) OCILobTrim(oci_ctxp->svchp,
oci_ctxp->errhp,
*lob_out,
(ub4)1);
check_err("OCILobTrim", err, errbuf, oci_ctxp);
/* Read the first 15 bytes of lob_in to local_buf. */
amount = 15;
offset = 1;
local_buflen = BUFLEN;
err = OCILobRead(oci_ctxp->svchp,
oci_ctxp->errhp,
lob_in,
&amount,
offset,
(dvoid *) local_buf,
local_buflen,
(dvoid *) 0,
(sb4 (*)(dvoid *, CONST dvoid *, ub4, ub1 )) 0,
(ub2) 0,
(ub1) SQLCS_IMPLICIT);
check_err("OCILobRead", err, errbuf, oci_ctxp);
/* Write to lob_out first 15 bytes from local_buf. */
err = OCILobWrite(oci_ctxp->svchp,
oci_ctxp->errhp,
*lob_out,
&amount,
offset,
(dvoid *) local_buf,
(ub4) local_buflen,
OCI_ONE_PIECE,
(dvoid *)0,
(sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 * )) 0,
(ub2)0, (ub1)SQLCS_IMPLICIT);
check_err("OCILobWrite", err, errbuf, oci_ctxp);
/* Set indicator of lob_out */
*ind_out = ind_in;
/***** Test Copy lob_inout to lob_ret *****/
/* Allocate a new lob descriptor for lob_ret */
err = OCIDescriptorAlloc(oci_ctxp->envhp,
(dvoid **) &lob_ret,
(ub4) OCI_DTYPE_LOB, /* Type of Descriptor */
(size_t) 0,
(dvoid **) 0);
check_err("OCIDescriptorAlloc", err, errbuf, oci_ctxp);
/* Intialize the locator lob_ret by assigning lob_in to it */
err = (int) OCILobAssign(oci_ctxp->envhp,
oci_ctxp->errhp,
lob_in,
&lob_ret);
check_err("OCILobAssign", err, errbuf, oci_ctxp);
/* Copy first 25 bytes from lob_inout to lob_ret with source and destination */
/* offsets set to 1 */
amount = 25;
err = OCILobCopy(oci_ctxp->svchp,
oci_ctxp->errhp,
lob_ret,
*lob_inout,
amount,
(ub4) 1,
(ub4) 1);
check_err("OCILobCopy", err, errbuf, oci_ctxp);
/* Set indicator of lob_ret */
*ind_ret = *ind_inout;
/* Return lob_ret locator back to PL/SQL */
return(lob_ret);
}
/* end of file extproc.c */