Re: Looking for examples of OCI call Oracle Stored Proc

From: Mark Tomlinson <NoBody_at_NoPlace.Org>
Date: Mon, 19 Jul 1999 08:03:27 -0400
Message-ID: <3793140E.D8392AB7@NoPlace.Org>

Philip S Zhan wrote:

> The only material (I have) describe the OCI interface with
> Oracle Stored Procedure is the OCI Programming Guide. The
> guide has a brief description and a piece of incomplete
> example code. The example is not covering my case. There
> is no example in Oracle's demo package.
>
> Can someone point out some good resources (good OCI book with
> details) or examples.
>
> I have a stored procedure with an array of objects or a table
> of objects as its parameter. I am also trying to minimize the
> number of server round-trips. After describing the stored proc,
> I got confused with so many options to deal with its parameters:
> ArrayOfStruct? COR? etc..
>
> Philip
>
> Philip

                           Name:
                                       OCI: Binding an array of structs
to PL/SQL Table using OBINDPS
                           Category:
                                       OCI
                           Port:
                                       WIN32
                           Description:
                                       A simple modification of SCR# 166

to use OBINDPS to bind an array of structs to a PL/SQL table.

#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_1_LEN 20
#define ELEMENT_2_LEN 10

struct s_string
{

        text v_str[ELEMENT_1_LEN];
        text v_junk[ELEMENT_2_LEN];

} s_str[ELEMENTS];

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 (obindps(&cda,1,(text*) ":str",-1,(text*)
s_str[0].v_str,ELEMENT_1_LEN,
                        STRING_TYPE,0,0,0,0,(ELEMENT_1_LEN +
ELEMENT_2_LEN),0,
                        0,0,(ELEMENTS * (ELEMENT_1_LEN +
ELEMENT_2_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", s_str[0].v_str);
        printf("%s\n", s_str[1].v_str);
        printf("%s\n", s_str[2].v_str);
        printf("%s\n", s_str[3].v_str);

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

} Received on Mon Jul 19 1999 - 07:03:27 CDT