Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: execute proceduers from oci

Re: execute proceduers from oci

From: DriftWood <drift_wood_at_my-deja.com>
Date: Tue, 07 Mar 2000 18:37:36 GMT
Message-ID: <8a3i9f$1te$1@nnrp1.deja.com>

/*************************************************/
/*   SQL to create table and Stored Procedures   */
/*************************************************/
/*
Create table OCI8StoredProcedureSampleTable

                (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;

*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>

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;

OCIBind *p_Bind2 = (OCIBind *) 0;
OCIBind *p_Bind3 = (OCIBind *) 0;
OCIDefine *p_define1 = (OCIDefine *) 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");
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, (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");

checkerr(p_err, OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0, (OCISnapshot *)

                          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;
strcpy(field2, "Entry 4");
printf("\n\n*************************************************\n");
printf("Example 2 - Using OUT Parameters\n");
printf("*************************************************\n");
printf("	OCIStmtPrepare\n");

strcpy(mySql,(text *) "Begin OCI8StoredProcedureSample4 (:field1, :field2, :field3); END;");
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");
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");

checkerr(p_err, OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0, (OCISnapshot *)

                          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;
}
static void checkerr(errhp, status)
OCIError *errhp;sword status;
{
  text errbuf[512];
  ub4 errcode;
  switch (status)
		{
		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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US