Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: execute proceduers from oci
/*************************************************/ /* SQL to create table and Stored Procedures */ /*************************************************//*
(field1 number(5), field2 varchar2(30));
CREATE OR REPLACE PROCEDURE OCI8StoredProcedureSample3
(field1 number, field2 IN OUT varchar2) is
begin
insert into OCI8StoredProcedureSampleTable values (field1, field2);
Commit;
field2 := 'Successful';
end;
CREATE OR REPLACE PROCEDURE OCI8StoredProcedureSample4
(field1 number, field2 char, field3 OUT varchar2) is
begin insert into OCI8StoredProcedureSampleTable values (field1, field2); Commit; field3 := 'Successful'; end; CREATE OR REPLACE FUNCTION OCI8StoredProcedureSample5 RETURN VARCHAR2 is v_Sysdate DATE; v_charSysdate VARCHAR2(20); begin SELECT TO_CHAR(SYSDATE, 'dd-mon-yyyy') into v_charSysdate FROM DUAL; return(v_charSysdate); end;
static void checkerr (OCIError *p_err, sword status);
#pragma comment(lib, "d:\\orant\\oci80\\lib\\msvc\\oci.lib")
void main()
{
OCIEnv *p_env; OCIError *p_err; OCISvcCtx *p_svc; OCIStmt *p_sql; OCIBind *p_Bind1 = (OCIBind *) 0;
char field2[20] = "Entry 3";
char *field3;
//char field3[20];
sword field1 = 3;
text *mySql = (text *) "Begin OCI8StoredProcedureSample3
(:field1, :field2); END;";
printf("OCIInitialize\n");
checkerr(p_err, OCIInitialize((ub4) OCI_OBJECT, (dvoid *) 0, (dvoid * (*) ()) 0, (dvoid * (*) ()) 0, (void (*) ()) 0));
printf("OCIEnvInit\n");
checkerr(p_err, OCIEnvInit(&p_env, (ub4) OCI_DEFAULT,
(size_t) 0, (dvoid **)0));
printf("OCIHandleAlloc\n");
checkerr(p_err, OCIHandleAlloc(p_env, &p_err, OCI_HTYPE_ERROR,
(size_t) 0, (dvoid **) 0));
printf("OCIHandleAlloc\n");
checkerr(p_err, OCIHandleAlloc(p_env, &p_svc, OCI_HTYPE_SVCCTX,
(size_t) 0, (dvoid **) 0));
printf("OCIHandleAlloc\n");
checkerr(p_err, OCIHandleAlloc(p_env, &p_sql, OCI_HTYPE_STMT, (size_t)
0, (dvoid **) 0));
printf("OCILogon\n\n");
checkerr(p_err, OCILogon(p_env, p_err, &p_svc, "SCOTT", 5, "TIGER",
5, "V8", 2));
/********************************************************/ /* Example 1 - Using an IN OUT Parameters */ /********************************************************/ printf("*************************************************\n"); printf("Example 1 - Using an IN OUT Parameters\n"); printf("*************************************************\n"); printf(" OCIStmtPrepare\n"); printf(" %25s\n",mySql); checkerr(p_err, OCIStmtPrepare(p_sql, p_err, mySql, (ub4) strlen(mySql), OCI_NTV_SYNTAX,OCI_DEFAULT)); printf(" OCIBindByPos 1\n");
SQLT_INT, 0, 0, 0, 0, 0,
OCI_DEFAULT));
printf(" OCIBindByPos 2\n");
checkerr(p_err, OCIBindByPos(p_sql, &p_Bind2, p_err, 2, field2, (sizeof
(field2) - 1),
SQLT_CHR, 0, 0, 0, 0, 0,
OCI_DEFAULT));
printf(" Field2 Before:\n"); printf(" size ---> %25d\n", sizeof(field2)); printf(" length ---> %25d\n", strlen(field2)); printf(" value ---> %25s\n", field2); printf(" OCIStmtExecute\n");
NULL, (OCISnapshot *) NULL, (ub4)
OCI_COMMIT_ON_SUCCESS));
printf(" Field2 After:\n"); printf(" size ---> %25d\n", sizeof(field2)); printf(" length ---> %25d\n", strlen(field2)); printf(" value ---> %25s\n", field2); /********************************************************/ /* Example 2 - Using OUT Parameters */ /********************************************************/field1 = 4;
printf("\n\n*************************************************\n"); printf("Example 2 - Using OUT Parameters\n"); printf("*************************************************\n"); printf(" OCIStmtPrepare\n");
(ub4) strlen(mySql), OCI_NTV_SYNTAX,
OCI_DEFAULT));
printf(" OCIBindByPos 1\n");
checkerr(p_err, OCIBindByPos(p_sql, &p_Bind1, p_err, 1, (dvoid *)
&field1, sizeof(sword),
SQLT_INT, 0, 0, 0, 0, 0,
OCI_DEFAULT));
printf(" OCIBindByPos 2\n");
checkerr(p_err, OCIBindByPos(p_sql, &p_Bind2, p_err, 2, field2, strlen
(field2),
SQLT_CHR, 0, 0, 0, 0, 0,
OCI_DEFAULT));
printf(" OCIBindByPos 3\n");
checkerr(p_err, OCIBindByPos(p_sql, &p_Bind3, p_err, 3, field3, 19,
SQLT_CHR, 0, 0, 0, 0, 0,
OCI_DEFAULT));
printf(" OCIStmtExecute\n");
checkerr(p_err, OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0,
(OCISnapshot *)
NULL, (OCISnapshot *) NULL, (ub4)
OCI_COMMIT_ON_SUCCESS));
printf(" Field3 After:\n"); printf(" size ---> %25d\n", sizeof(field3)); printf(" length ---> %25d\n", strlen(field3)); printf(" value ---> %25s\n", field3); /********************************************************/ /* Example 3 - Using a Function to Return a Value */ /********************************************************/ printf("\n\n*************************************************\n"); printf("Example 3 - Using a Function to Return a Value \n"); printf("*************************************************\n"); printf(" OCIStmtPrepare\n"); strcpy(mySql,(text *) "SELECT OCI8StoredProcedureSample5 from DUAL"); printf(" %25s\n",mySql); checkerr(p_err, OCIStmtPrepare(p_sql, p_err, mySql, (ub4) strlen(mySql), OCI_NTV_SYNTAX,OCI_DEFAULT)); checkerr(p_err, OCIDefineByPos(p_sql, &p_define1, p_err, 1, (dvoid *) field3,
(sword) 20, SQLT_STR, (dvoid *) 0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); printf(" OCIStmtExecute\n");
NULL, (OCISnapshot *) NULL, (ub4)
OCI_COMMIT_ON_SUCCESS));
printf(" The return value:\n"); printf(" size ---> %25d\n", sizeof(field3)); printf(" length ---> %25d\n", strlen(field3)); printf(" value ---> %25s\n", field3);return;
{ case OCI_SUCCESS: break; case OCI_SUCCESS_WITH_INFO: printf("Error - OCI_SUCCESS_WITH_INFO\n"); break; case OCI_NEED_DATA: printf("Error - OCI_NEED_DATA\n"); break; case OCI_NO_DATA: printf("Error - OCI_NO_DATA\n"); break; case OCI_ERROR: OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR); printf("Error - %25s\n", errbuf); break; case OCI_INVALID_HANDLE: printf("Error - OCI_INVALID_HANDLE\n"); break; case OCI_STILL_EXECUTING: printf("Error - OCI_STILL_EXECUTE\n"); break; case OCI_CONTINUE: printf("Error - OCI_CONTINUE\n"); break; default: break; }
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Mar 07 2000 - 12:37:36 CST