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