Thanks for the question, Sateesh.

Asked: November 01, 2000 - 2:03 pm UTC

Last updated: April 10, 2008 - 3:11 pm UTC

Version: 8.1.5

Viewed 1000+ times

You Asked

Hi Tom,
I was just going through the OCI manual to know what exactly is OCI. What i understood after going through the first chapter is now we can use OCI instead of using Precompilers. If i have a Pro*C program, would it be better to change to OCI. What are the advantages of doing so?

Thanks
Sateesh

and Tom said...

There is, in my opinion, nothing to be gained from going to OCI over Pro*C - especially if you already know pro*c and do not know OCI.

I find pro*c to be infinitely more productive then OCI. I can do in one or two statements in Pro*c what takes many lines of code in OCI.

There are very very few things you can do in OCI that you cannot do in pro*c. The only thing I've ever encountered that you can do in OCI but not Pro*C is dynamically bind to a prepared stored procedure call that takes PLSQL table types. That is a rare thing to need (and even then -- you can connect with pro*c and use OCI when and if you need to rather then for everything)

There are some object features available in OCI that are not as accessible in Pro*c but in general -- I use pro*c until I cannot and then use OCI.

It depends on what you are comfortable with. As of 8i, pro*c is a layer on top of OCI. The performance of the two is extremely comparable.

Rating

(8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

OCI or Precompiler

roseh.y., June 19, 2001 - 4:16 pm UTC

OCIStmtPrepare actions

Manish, May 23, 2003 - 8:10 am UTC

Hi Tom,

We are an ardent user of OCI for our database application development. Till now we were under the impression that the syntax and semantic checking of any statement is done in OCIStmtPrepare call. But if there is a syntax error in the statement for example:

SELECT t.name, t.sal
FROM1 emp t;

note that we have written FROM1 instead of FROM in the above example, OCIStmtPrepare does not give any error. We also observed that the entry of this statement does not go to the v$sql. That means that the syntax parsing is not done in the OCIStmtPrepare call.

Our questions are :

1. When does the syntax and semantics check happen in OCI context.

2. What exactly is done in the OCIStmtPrepare call.

Thanks
Manish

May 23, 2003 - 9:10 am UTC

it (oci) is bundling as much stuff as it can to reduce (remove) roundtrips to and from the server.

from chapter 4 of the OCI guide:

Preparing Statements

SQL and PL/SQL statements need to be prepared for execution by using the statement prepare call and bind calls (if necessary). In this phase, the application specifies a SQL or PL/SQL statement and binds associated placeholders in the statement to data for execution. The client-side library allocates storage to maintain the statement prepared for execution. An application requests a SQL or PL/SQL statement to be prepared for execution using the OCIStmtPrepare() call and passing it a previously allocated statement handle. This is a completely local call, requiring no round trip to the server. No association is made at this point between the statement and a particular server.

Help with OCI

A reader, November 19, 2004 - 12:44 pm UTC

Tom:

I am writing an OCI program and having this question. please help me out!!

I am using OCIStmtPrepare(stmhp, dbc->oerrh, sqlstmt, strlen(sqlstmt),(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT); and it works fine as long as sqlstmt is shorter than 65535. However, some of my sqlstmt might be longer than 65535. it seems OCI can only take up to 65535 even though the datatype of the fourth parameter us UB4.

November 19, 2004 - 2:11 pm UTC

what happens if you do longer than that?

more info

A reader, November 19, 2004 - 12:49 pm UTC

so for example, if strlen(sqlstmt) is 70000 bytes. after the execution , it appears to me that only the first 4465 bytes was get executed(70000-65535). so instead of having a complete sql statmeent, it only have part of it when i query user_source.

November 19, 2004 - 2:12 pm UTC

that sounds like improper behaviour of OCI if true -- please contact support with a tiny test case for that.

A reader, March 02, 2005 - 12:16 pm UTC

Tom,

We recently upgraded to 9206 on our test environment and we are clients getting this error 

ORA-03127: no new operations 
allowed until the active operation ends

Are the are known issues with OCI for 9206 acessing long columns.

This is the table structure and query.
SQL> desc t1 
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PK_t1                              NOT NULL NUMBER(10)
 M                                             VARCHAR2(10)
 SQ_ID                                             VARCHAR2(10)
 SPARTNER                                         VARCHAR2(15)
 CONTENTS                                           LONG
 P_SA                             VARCHAR2(30)
 P_FPOST                                     CHAR(1)
 ACTION                                             VARCHAR2(10)
 MID                                                CHAR(16)
 STATUS                                             VARCHAR2(10)
 TC                                       NUMBER(10)
 TD                                        NUMBER(10)
 TDA                                     NUMBER(25,4)
 TCA                                    NUMBER(25,4)
 DTSTAMP                                    VARCHAR2(23)
 REFERENCE                                          VARCHAR2(10)
 RFLAG                                         CHAR(2)
 OFFICE                                             CHAR(3)
 BDATE                                          DATE
 HIF_INSTANCE                                       NUMBER(10)
 RM                                       VARCHAR2(512)
 PDATE                                           DATE

select * 
FROM t1 WHERE status is null and hif_instance = 100 AND ROWNUM <= 1

March 02, 2005 - 12:38 pm UTC

not that I'm aware of.

Pro*C a layer on top of OCI?

Richard, March 02, 2005 - 3:50 pm UTC

In your original response, you said:

> It depends on what you are comfortable with. As of 8i,
> pro*c is a layer on top of OCI. The performance of the two
> is extremely comparable.

I was always under the impression that the Pro*C libraries are completely independent of the OCI libraries. Your statement seems to imply that Pro*C code uses the OCI libraries. Is that true? Or, are the libraries independent?

March 02, 2005 - 5:25 pm UTC

in 8.0 and before pro*c was a layer on top of sqllib, in 8i, proc*c became a layer on top of OCI.

they were different historically

thank you

Eric Grancher, March 03, 2005 - 1:07 am UTC

How to keep cursors open

Sai, April 10, 2008 - 3:03 pm UTC

Just like HOLD_CURSOR parameter in Pro*C to keep cursors(statement handles) open across multiple executions, how can we do it using OCI?

Thanks

April 10, 2008 - 3:11 pm UTC

just don't close them?

you open them
don't close them.

Just like if you wanted to keep a file open, you would use a static variable, put the file handle into that and just refer to it call after call after call.

Breadcrumb

Question and Answer