Re: Looking for examples of OCI call Oracle Stored Proc

Hi, Mark, Thank you very much for your help. Your example demonstrated OCI7 stored procedure interface. As you indicated that "it is not possible to bind PL/SQL tables of records, only PL/SQL tables of simple types like numbers or varchars." This is probably the limitation of Oracle 7.

I am trying to bind a table of records (user defined objects) as the parameter of the stored procedure. Can Oracle8 do that?

Philip

>
> Name:
> OCI Basic Array Binding to PL/SQL Table Sample Code
> Category:
> OCI
> Port:
> WIN32
> Description:
> A great simplification of the Oracle supplied CDEMO4.
Uses
> obndra to bind an array of chars to a PL/SQL table. Note that it is
not
> possible to bind PL/SQL
> tables of records, only PL/SQL tables of simple types
like
> numbers or varchars.
>
> /*
>
> create or replace package test_pkg as
> type array is table of varchar2(20) index by binary_integer;
> procedure test_proc(v_array out array);
> end;
> /
>
> create or replace package body test_pkg as
> procedure test_proc(v_array out array) is
> cursor c1 is select loc from dept;
> begin
> open c1;
> for i in 1..4 loop
> fetch c1 into v_array(i);
> end loop;
> end;
> end;
> /
>
> */
>
> #include <stdio.h>
> #include <memory.h>
> #include <ociapr.h>
>
> #pragma comment(lib, "d:\\orant\\oci73\\lib\\old\\ora73.lib")
>
> #define VARCHAR2_TYPE 1
> #define NUMBER_TYPE 2
> #define INT_TYPE 3
> #define FLOAT_TYPE 4
> #define STRING_TYPE 5
> #define ROWID_TYPE 11
> #define DATE_TYPE 12
>
> Lda_Def lda;
> Cda_Def cda;
> ub1 hda[256];
>
> #define ELEMENTS 4
> #define ELEMENT_LEN 20
>
> text v_str[ELEMENTS][ELEMENT_LEN];
> int a_size = ELEMENTS;
>
> void main()
> {
> memset(hda,0,255);
>
> if (olog(&lda,hda,(text*)
> "scott/tiger_at_exampledb",-1,0,-1,0,-1,OCI_LM_DEF))
> printf("Logon failed: %i\n", lda.rc);
>
> if (oopen(&cda,&lda,0,-1,-1,0,-1))
> printf("Open failed: %i\n", lda.rc);
>
> if (oparse(&cda,(text*) "begin test_pkg.test_proc(:str);
> end;",-1,1,2))
> printf("Parse failed: %i\n", cda.rc);
>
> if (obndra(&cda,(text*) ":str",-1,(text*)
> v_str,20,STRING_TYPE,-1,
> 0,0,0,(ELEMENTS * ELEMENT_LEN),&a_size,
> 0,-1,-1))
> printf("Binding failed: %i\n", cda.rc);
>
> if (oexec(&cda))
> printf("Execute failed: %i\n", cda.rc);
>
> printf("%s\n", v_str[0]);
> printf("%s\n", v_str[1]);
> printf("%s\n", v_str[2]);
> printf("%s\n", v_str[3]);
>
> if (ocan(&cda))
> printf("Cancel failed: %i\n", cda.rc);
>
> if (oclose(&cda))
> printf("Close failed: %i\n", lda.rc);
>
> if (ologof(&lda))
> printf("Logoff failed: %i\n", lda.rc);
> }
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jul 20 1999 - 14:09:59 CDT