Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Pawan.

Asked: September 28, 2002 - 10:19 am UTC

Last updated: October 24, 2008 - 1:03 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

What is the best way for someone to learn using Pro*c if one does not know C very well. Why would one use Pro*c and not PL/SQL?
Thanks

and Tom said...

You *need* to know C well before using Pro*C.

Pro*c simply lets you embedded SQL in C code. If you do not know how to code in C, you will not get very far at all with Pro*C.

I use pro*c only when I cannot accomplish the task efficiently in PLSQL or SQL. In 9i, with external tables, merge, pipelined functions -- I'm very very hard pressed to find a reason to use C.

I've use pro*c to dump data to flat files and sometimes to load it.

Rating

  (55 ratings)

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

Comments

sample program

Khalid, September 28, 2002 - 11:32 am UTC

Hi Tom
1.Could you show us a very simple program in Pro C.
2.What is the requirements to run it complier...etc.


Tom Kyte
September 28, 2002 - 12:14 pm UTC

see
</code> http://asktom.oracle.com/~tkyte/proc_makefile/

for a simple makefile for Unixland that works with pretty much every release of Oracle since v7.

You need

a) proc
b) the C compiler supported on your operating system

An example could be:

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5408706816128 <code>

near the bottom - i just did a little proc to benchmark something recently.

Pro-c

Rose, February 13, 2003 - 3:10 pm UTC

Hi,Tom

I have doubts about ProC and I need your help!

We have 2 Server machines and 2 Client machines:
a)Server A with Oracle8i and Server B with Oracle9i
b)Machine C with Client Oracle8i and machine D with Client Oracle9i

I create an application using C and ProC.
I am confused. My application must access Database 8i and 9i too. My doubts:
1)What version of ProC I must use: 8i or 9i?
It depends what Database my application access or in what client it executes?

2)If I create 2 aplications: App8i and App9i ,using ProC 8i and Proc 9i respectively , I can execute both without problem in any client machine?

Your response is very important! Thanks!


Tom Kyte
February 13, 2003 - 5:28 pm UTC

1) depends on the client software installed where the pro*c is to run.

8i pro*c can access 8i and 9i
9i pro*c can access 8i and 9i

2) yes, as the clients can support more then one client installation of the oracle software. You would just make sure the oracle_home was set properly for each.

Pro*C

Rose, February 14, 2003 - 6:58 am UTC

My application app8i.exe (C and ProC 8i) access DB Oracle9i.
I executed this apllication in Client 9i, but it was unsuccessful. The application required Oracle8i .dll files (ex: oraclient8.dll, oranls8.dll,...).

To execute app8i.exe in Client 9i, I must have Oracle8i dll files?

Thanks!!



Tom Kyte
February 14, 2003 - 7:53 am UTC

No -- this statement:

To execute app8i.exe in Client 9i, I must have Oracle8i dll files?

is false (actually, it is a trick question). YOU CANNOT execute an 8i client application using 9i client software.


In order to execute app8i.exe, you NEED to run with the 8i client -- the 9i client may well be installed on that same machine but that is not relevant. You need the 8i client for -- well -- 8i clients.

Pro*C to write C Program

robert, February 14, 2003 - 1:57 pm UTC

Can you use Pro*C to write just any program you can write in C ?

Tom Kyte
February 14, 2003 - 8:39 pm UTC

Pro*c is "greater than" C as pro*c is in fact C with embedded SQL in it.

pro*c is a precompiler that takes c that looks like:

{
...
exec sql open c;
exec sql fetch c into :n;
....
printf( "n = %d\n", n );
}

and turn it into 100% C

Connecting from C program to Oracle9i DB Server

Moiz, February 17, 2003 - 3:12 am UTC

I have a C program and through this program I have to insert some (heavy) mathematically processed data (say, variable x in column x of table xx) into the Oracle 9i database. I do not know Oracle (unfortunately) and I have to submit my program for testing to my Oracle DBA very soon.

Please tell me what connection string or driver I have to use to connect to the server and issue the insert SQL command.

Thanks

Tom Kyte
February 17, 2003 - 10:53 am UTC

You need to learn just a little proc. This will be enough pro*c actually:

main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
varchar oracleid[31];
int x;
EXEC SQL END DECLARE SECTION;

strcpy( oracleid.arr, "scott/tiger@foo" );
oracleid.len = strlen(oracleid.arr);

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL CONNECT :oracleid;
printf("\nConnected to ORACLE as user: %s\n\n", oracleid.arr);

x = really_complex_calculation();

exec sql insert into xx ( x ) values ( :x );

/* Disconnect from ORACLE. */
/* EXEC SQL COMMIT WORK RELEASE; */
exit(0);
}

But...

A reader, April 29, 2003 - 12:00 pm UTC

Hi Tom,

Once you've written the above code, how do you call it? As far as I understand, it needs to be pre-compiled using the pre-compiler provided by Oracle, and then compiled again using a supported C compiler. But how do you call this executable now? Specifically, can it be called from inside pl/sql or sql code (external proc perhaps)? Or is the idea of this pro*c code to be called stand-alone/independently from the OS command line or from within an application? I guess I'm not clear on what purpose pro*c serves. Thanks in advance.

Kashif

Tom Kyte
April 29, 2003 - 12:06 pm UTC

this makes a command line program suitable to be used to unload large amounts of data fairly quickly from the command line.

It is not to be called from plsql (although there is no reason why you could not make it into an external procedure and call it. if you have my book "Expert one on one Oracle" -- i cover that in some detail)

Can you make code more secure

David Gibbs, April 29, 2003 - 12:32 pm UTC

Tom

You gave a Pro*C code snippet that is very similar to the way we run our C app on an 8.1.7 database. However I don't like the way the connection string is hard coded and echoed out. I want to have our developers change to running the app with an OS authentication instead. The developers here don't really want to spend the time researching how to do this so I will ask you straight out. How can this be acheived?

Tom Kyte
April 29, 2003 - 1:17 pm UTC

strcpy( oracleid.arr, "/" );


that is it...

linker error

abdul wahab, June 22, 2003 - 1:56 am UTC

Dear TOM,
I am trying to learn using C language with Oracle.I wrote one simple program which is as follows:
#include <stdio.h>
#include <ctype.h>
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR uid[20];
VARCHAR pwd[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
main()
{
printf("ENTER USER-NAME:");
scanf("%s",uid.arr);
printf("ENTER PASSWORD:");
scanf("%s",pwd.arr);
uid.len=strlen(uid.arr);
pwd.len=strlen(pwd.arr);
EXEC SQL WHENEVER SQLERROR GOTO errproc;
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
printf("CONNECTED TO ORACLE.\n");
exit(0);
errproc:
printf("erroro occured.\n");
exit(1);
}

then i compiled this with pro C.
when i loaded this compiled file(corc1) in C compiler and compiled it..it went fine with out warnings.
But when i try to run this i get this disgusting error:
linking C:\turboC2\CORC1.exe
linker error: undefined symbol '_sqlcxt' in module corc1.c
Can you please help me resolve this.
Thanks


Tom Kyte
June 22, 2003 - 8:21 am UTC

"disgusting", that is an interesting adjective.

link in the libraries needed for pro*c. check out the sample makefiles. Last I used windoze was in the 816 timeframe -- the library back then was

$ORACLE_HOME\precomp\lib\msvc\orasql8.lib

the 8 is probably different, poke around.

Pro*c debuging

atul, July 02, 2003 - 4:52 am UTC

Hi Tom,
In pro*c program, i have a insert statement.At runtime,values coming as parameters are invalid. For ex, a
char value comes as parameter for a column of type number.Is there any way to find out the exact position in the insert statement which has the wrong value or where the error starts in the insert statement.


Tom Kyte
July 02, 2003 - 8:15 am UTC

A reader, July 02, 2003 - 1:45 pm UTC

Tom,

I am totally new to pro*c..I copied your sample source code
as saved it as source1.pc, now do i have to relink first by giving make command or i have to give proc comamnd

thanks..



Tom Kyte
July 02, 2003 - 2:10 pm UTC

see the first follow above.

c or c++

umesh, July 04, 2003 - 12:31 am UTC

Tom
I have started learning c++ for my application and for pro*c++ i have the prerequisite of learning c++ . My question is is it necessaary to go purely for C to do Pro*c or everything in C is doable with C++ The relevance of this question is related only from Pro*c/c++ point of view
Thanks

Tom Kyte
July 04, 2003 - 7:54 am UTC

Most things are doable in C++, there are some pro*c restrictions, such as the setting of PARSE= on the command line which mandate the use of certain constructs (like you need the exec sql begin/end declare section tags and such)

Bulk Insert, Bulck Collect/Fetch , Bulk Update/Delete in Pro*c

Tony, July 18, 2003 - 1:01 am UTC

1. Is Bulk Insert, Bulck Collect/Fetch and Bulk Update/Delete possible in Pro*C? If yes, could you give an example please?

2. Will there be any performance gain if I put my business logic in stored procedure and call it in my Pro*c instead of doing it in Pro*C.

Tom Kyte
July 18, 2003 - 1:49 am UTC

1) proc .... prefetch=100

is one way,

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a97269/pc_07pls.htm#1365 <code>

another

2) yes, no, maybe, it depends. (mostly yes in my experience but common sense must reign supreme here. if your business logic includes computing a eigen vector for a set of values -- C might be faster. If you are just making sure that A > B+5, plsql would be)

If you can use a stored procedure to avoid pulling the data out of the database -- over the network -- just to send it back to the database over the network to insert again -- plsql would be good.

Example for bulk update

Tony, July 18, 2003 - 6:45 am UTC

Can you give an example for bulk update in Pro*C?

Tom Kyte
July 18, 2003 - 8:48 am UTC

look in $ORACLE_HOME/precomp/demo/proc, there are examples.

it is fully - fully - documented as well.

Try Perl

Vin, July 18, 2003 - 9:54 am UTC

If you don't know C/Pro*C and don't want to work directly in PL/SQL, then take a look at Perl.



a question about sys_refcursor with PRO*C

Fernando, November 24, 2003 - 6:22 am UTC

Inside a PRO*C program I need to call a PL/SQL stored procedure that has an output parameter of the type sys_refcursor. How can I do that?

In case it wasn't posible, what could I do?

(The reason why the procedure uses that parameter is because it is used to keep several rows of a query).


Tom Kyte
November 24, 2003 - 8:28 am UTC


create or replace procedure proc( p_cursor in out sys_refcursor )
as
begin
open p_cursor for select ename, empno from emp;
end;
/




static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
char ename_data[31] ;
int empno_data ;
SQL_CURSOR dyn_cursor;
EXEC SQL END DECLARE SECTION;

EXEC SQL ALLOCATE :dyn_cursor;

EXEC SQL EXECUTE
BEGIN
proc( p_cursor=>:dyn_cursor );
END;
END-EXEC;

EXEC SQL WHENEVER NOT FOUND DO BREAK ;

while (SQLCODE == 0)
{
EXEC SQL FETCH :dyn_cursor INTO :ename_data, :empno_data;
printf("Ename = %s Empno = %d\n", ename_data, empno_data) ;
}
EXEC SQL WHENEVER NOT FOUND CONTINUE;
EXEC SQL CLOSE :dyn_cursor;
}



A reader

alex, January 02, 2004 - 3:29 pm UTC

Tom

I have a query having joins with four tables each having around 100Millions of data and result of this query is around 600,000 records. But when I am opening a cursor using Pro C then I get following error message

ORA-12805: parallel query server died unexpectedly

I am using the following statment to open and fetch the records...

EXEC SQL CONTEXT USE DEFAULT;
EXEC SQL WHENEVER SQLERROR DO err_report(sqlca);
EXEC SQL OPEN OpnCursor USING :x:indx;
Error is coming from the above statement
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;) {
memset(rec, '\0', sizeof(rec));
EXEC SQL FETCH OpnCursor INTO :rec INDICATOR :indRec;
.
.

Can you please advise me why this error is coming and how it can be sorted out.

Thanks



Tom Kyte
January 02, 2004 - 4:25 pm UTC

contact support for things like this

pcscfg.cfg file

Fernando Sánchez, January 08, 2004 - 12:33 pm UTC

I've recently installed Oracle 8.1.7 on Mandrake 9.1. I want to
precompile my first Pro*C program (just doing "proc my_program.pc",
not using any makefile).

I've written this pcscfg.cfg file (it was empty after installation):


sys_include=($ORACLE_HOME/precomp/public,/usr/lib/
gcc-lib/i586-mandrake-linux-gnu/3.2.2/include,/usr/include)
include=($ORACLE_HOME/precomp/public)
include=($ORACLE_HOME/rdbms/demo)


When trying to precompile, I get the following:

.................

Syntax error at line 43, column 9, file /usr/lib/g
cc-lib/i586-mandrake-linux-gnu/3.2.2/include/stdarg.h:
Error at line 43, column 9 in file /usr/lib/gcc-li
b/i586-mandrake-linux-gnu/3.2.2/include/stdarg.h
typedef __builtin_va_list __gnuc_va_list;
........1
PCC-S-02201, Encountered the symbol "__builtin_va_list" when
expecting one of the following:

auto, char, const, double, enum, float, int, long,
ulong_varchar, OCIBFileLocator OCIBlobLocator,
OCIClobLocator, OCIDateTime, OCIExtProcContext, OCIInterval,
OCIRowid, OCIDate, OCINumber, OCIRaw, OCIString, register,
short, signed, sql_context, sql_cursor, static, struct,
union, unsigned, utext, uvarchar, varchar, void, volatile,
a typedef name,
The symbol "enum," was substituted for "__builtin_va_list" to
continue.

.......................

and a lot of more errors of the same kind.


Any idea? Thanks in advance.



Tom Kyte
January 08, 2004 - 3:23 pm UTC

we don't really do mandrake as a supported linux distro.

redhat and united (Conectiva Linux Enterprise Edition SuSE Linux Enterprise Server 8 (SLES 8) Turbolinux Enterprise Server 8 Advanced and Basic)

A reader, January 08, 2004 - 5:03 pm UTC

Do you use windows??

Tom Kyte
January 08, 2004 - 8:26 pm UTC

not a chance.

well, I look out them alot (have a bird feeder outside my home office, lots of nice birds) but as software on a computer, no.

questions about pro*C performance

Fernando Sánchez, February 15, 2004 - 8:58 am UTC

I have a pair of doubts:

1. Is embedding PL/SQL code, instead of SQL, bad for the performance of a pro*C program? (not being expert at C, I find easier to write PL/SQL code)

2. Is there any important difference in terms of performance between embedding PL/SQL anonymous blocks and embedding stored subprograms (without nested calls)?

Thanks in advance.


Tom Kyte
February 15, 2004 - 11:59 am UTC

1) i'd rather embedd a call to a PLSQL stored procedure :)

exec sql execute
begin
foo( :x, :y );
end;


but you can embedd anonymous blocks easily. They'll be sent to the server, execute entirely on the server and the results will be sent back. In many cases, you'll find this to be much faster than sending row after row after row back to the C program for processing just to send the rows back to the server again.

But -- in the end, the answer is "it depends". If what you have to do is computationally expensive, C might win in the speed race. Otherwise, PLSQL will probably be "more than fast enough"

I would skip the pro*c alltogether here if my skills lie in PLSQL. Why complicate it at all?

2) yes, the first hard parse must compile the entire block -- where as stored procedures would already be compiled. It is safer with stored procedures as well as the dependency mechanism is in place (you can answer the question "so, who is using this stuff" easily with stored procedures -- no so with anonymous blocks).

I would encourage the liberal use of stored procedures over embeding monsterous blocks of anonymous plsql in an application.

basic question about pro*C when records are blocked

Fernando Sanchez, March 28, 2004 - 6:16 am UTC

Hello Tom.

What can happen when a pro*C function tries to update or delete a record that is blocked by another transaction. What kinds of sqlcode can I get? Or can the function wait until the end of the other transaction?

Thanks in advance.

Tom Kyte
March 28, 2004 - 9:14 am UTC

it'll wait.

and you will of course have suffered a lost update, classic RDBMS problem introduced by code....


what I mean is -- if your pro*c code is trying to delete a record

that someone else is simultaneously updating....

why are they even bothering to do the update, since you are going to erase it without even looking at it.....

(eg: you might have some transactional integrity issues to work out in your application if this is a common occurence)

a couple of basic questions more

Fernando Sanchez, April 11, 2004 - 10:54 am UTC

Thanks again Tom.

In connection with the problem of an application making transactions meanwhile another user could make others I don't think is going to happen us normally (I'm afraid none of us has thought enough about it). But, could you give me some references about how Oracle manages typical transactional integrity issues (beginner level)?

Another question. If in a Pro*C code I use anonymous blocks with dynamyc sql sentences, how are they compiled and processed?



Tom Kyte
April 11, 2004 - 11:21 am UTC

There is no such thing as "beginner level" in transaction processing. It is the crux of database management. You either know it or you are a danger to the data. It is black and white.

You need to read the concepts guide, understand the concepts guide (especially the concurrency control and data protection sections). Then, if you want it in a more conversational tone - you might consider "Expert one on one Oracle" where I go over transactions, locking, concurrency control in my fashion. (takes more than 32k which I limit myself to here)


The anonymous block is sent to the server like any other statement and processed exactly like any other statement would be. It is parsed, optimized if needed, executed. If you are interested in the long story (all about parsing, hard, soft, softer soft) either "Beginning Oracle Programming", or "Effective Oracle by Design" covers this in detail (as does the Oracle Performance Guide)

all oracle doc is available to anyone on otn.oracle.com


Help for comile and run a Proc* Program

Manoj Kumar, April 14, 2004 - 1:40 am UTC

Hi,
I am a new for Pro*c . I am using Oracle 9i on window NT environment. How we can compile and run Pro*C program from this environment. please give information in detail with command

Thanks in Advance



Tom Kyte
April 14, 2004 - 7:41 am UTC

the command:

c:\> proc

IF you installed it (in the client developer tools, you won't get it with just a db install)


that'll turn a .pc file into a .c file which you compile using a c compiler. you then link with whatever libraries are needed on windows (see the samples in $ORACLE_HOME/precomp/demo/proc)

if you don't have the demos or proc.exe, you'll have to install it.

Manoj Kumar, April 16, 2004 - 2:21 am UTC

Thank for reply,
now i have a .c file which i got from .pc. now which library file are neede to attached and how to attached it and how to run and get .exe file

Thanks in advance

Tom Kyte
April 16, 2004 - 7:30 am UTC

look up one review...

How to run a Proc*C file

Manoj Kumar, April 16, 2004 - 2:21 am UTC

Thank for reply,
now i have a .c file which i got from .pc. now which library file are neede to attached and how to attached it and how to run and get .exe file

Thanks in advance

problem understanding pro*C cursor assignment

Fernando Sanchez, April 28, 2004 - 10:34 am UTC

Hello.

I have a problem (8.1.7 release). I've made a pro*C application that calls a PL/SQL function that returns a ref cursor. I think that my problem is that my assigment is not OK, so that I have 2 cursors opened (the PL/SQL ref cursor and the pro*C cursor).

My pro*C code is like this:

......
......

EXEC SQL BEGIN DECLARE SECTION;
SQL_CURSOR cur_SMS;
EXEC SQL END DECLARE SECTION;

......
......

EXEC SQL ALLOCATE :cur_SMS;

......
......

:cur_SMS := PA_MNG_EVENTS.FN_GET_SMS_Vector(v_CO_CFG_N,:var_out_length,v_ErrCode,v_ErrMsg);

......
......

EXEC SQL FETCH :cur_SMS INTO :var_CO_SMS_N, :var_DS_SMS_V,
:var_CO_REMITE_V:indicator_CO_REMITE_V, :var_CO_MODO_V, :var_CO_TEXT_V,
:var_CO_PER_ENVIO_N, :var_CO_NUM_REIN_N:indicator_CO_NUM_REIN_N, :var_CO_TIPO_MSJ_N;

......
......

EXEC SQL CLOSE :cur_SMS;

......
......

The problem is that the instruction EXEC SQL CLOSE :cur_SMS is being executed but I think that my PL/SQL ref cursor is not closed, so
I'm getting the ORA-01000 error.


Is the following assigment correct ?

:cur_SMS :=
PA_MNG_EVENTS.FN_GET_SMS_Vector(v_CO_CFG_N,:var_out_length,v_ErrCode,v_ErrMsg);



Thanks in advance.

strange error with pro*C

Fernando Sánchez, May 03, 2004 - 10:18 am UTC

Thank you Tom.

Now I'm having a different problem, and it semms to be because a code problem, but I don't know whai is wrong.

My pro*C function is like this:

int CSRIRDB_checkGT(char* in_CO_GT_V,
char* out_SQ_GZL_01)
{

int var_Error = CSRIRDB_OK;
char var_Sqlerrmc[2000];
char var_msg[512]; /* 512 es la longitud máxima del mensaje oracle incluyendo saltos de línea */
size_t var_buf_len, var_msg_len;

..................

/* and the error is beacuse the following assignment */

..................

:out_SQ_GZL_01 := to_char(v_SQ_GZL_01);

.................
/* where v_SQ_GZL_01 is a NUMBER(5) */

I don't know what can be happening.



problem solved using auxiliary varibles

Fernando Sánchez, May 03, 2004 - 11:11 am UTC

Finally, the problem was solved by not assigning directly to the parameter by to a varibale that was declared as an array.

I'm not sure what the problem was, though. The error description made me think about a very different problem.


problem with performance

Fernando Sanchez, June 28, 2004 - 8:24 am UTC

I have a pro*C function that needs around 0.059 seconds to insert a record in a table. I have to improve that.

This is my function:

int CSRIRDB_putEvent(char* in_CO_MSDN_V,
char* in_CO_IMSI_V,
char* in_CO_GT_V,
unsigned int in_CO_TIP_EVT_N,
char* in_DS_EVENT_V,
unsigned int in_CO_ERROR_N,
int in_CO_CAUSA_N,
char* in_CO_MAP_VERS_V,
int in_CO_PER_CLT_N,
char* in_CO_MCC_N,
char* in_CO_MNC_N,
char* in_FX_CLT_D)
{
int var_Error = CSRIRDB_OK;
/*char var_CadenaSecuencia[15+1];*/
/*char var_CadenaFecha[50+1];*/
short int indicator_SQ_REV_01;
short int indicator_FX_RCP_EVT_D;
char var_Sqlerrmc[5000];
char var_msg[512]; /* 512 es la longitud máxima del mensaje oracle incluyendo saltos de línea */
size_t var_buf_len, var_msg_len;

trace_write(TRACE_LEVEL_LOW,"INICIO CSRIRDB_putEvent");

indicator_SQ_REV_01 = -1; indicator_FX_RCP_EVT_D = -1;

EXEC SQL EXECUTE

DECLARE

CSRIRDB_OK Constant NUMBER(1) := 0;
CSRIRDB_NON_EXPECTED_ERROR Constant NUMBER(4) := 4000;
CSRIRDB_EVT_OK Constant NUMBER(2) := 14;
CSRIRDB_EVT_KO Constant NUMBER(2) := 15;
CSRIRDB_NEW_CLIENT_EVENT Constant NUMBER(2) := 16;
CSRIRDB_SMS_SENT_EVENT Constant NUMBER(2) := 17;

/*v_reg_IR_TB_CLTS IR_TB_CLTS%ROWTYPE; */
/*v_Timestamp DATE; */
v_SQ_REV_01 NUMBER;
v_FX_RCP_EVT_D IR_TB_RCP_EVTS.FX_RCP_EVT_D%TYPE;
v_CO_MAP_VERS_V IR_TB_RCP_EVTS.CO_MAP_VERS_V%TYPE;
v_DS_EVENT_V IR_TB_RCP_EVTS.DS_EVENT_V%TYPE;
v_CO_PER_CLT_N IR_TB_CLTS.CO_PER_CLT_N%TYPE;
v_CO_MCC_N IR_TB_CLTS.CO_MCC_N%TYPE;
v_CO_MNC_N IR_TB_CLTS.CO_MNC_N%TYPE;
v_FX_RCP_IAS_D IR_TB_RCP_EVTS.FX_RCP_IAS_D%TYPE;

v_ErrCode NUMBER;
v_ErrMsg VARCHAR2(5000);

NON_EXPECTED_ERROR EXCEPTION;

BEGIN

v_CO_PER_CLT_N := :in_CO_PER_CLT_N;

if (length(:in_CO_MCC_N)=0) THEN
v_CO_MCC_N := NULL;
else
v_CO_MCC_N := to_number(:in_CO_MCC_N);
END if;

if (length(:in_CO_MNC_N)=0) THEN
v_CO_MNC_N := NULL;
else
v_CO_MNC_N := to_number(:in_CO_MNC_N);
END if;

if (length(:in_FX_CLT_D)=0) THEN
v_FX_RCP_IAS_D := NULL;
else
v_FX_RCP_IAS_D := to_date(:in_FX_CLT_D,'dd/mm/yyyy hh24:mi:ss');
END if;

select decode(:in_CO_MAP_VERS_V,'',NULL,:in_CO_MAP_VERS_V), decode(:in_DS_EVENT_V,'',NULL,:in_DS_EVENT_V)
into v_CO_MAP_VERS_V, v_DS_EVENT_V
from dual;

PA_INSERT.PR_INS_IR_TB_RCP_EVTS(:in_CO_IMSI_V,:in_CO_MSDN_V,v_CO_PER_CLT_N,
:in_CO_GT_V,v_CO_MAP_VERS_V,v_CO_MCC_N,v_CO_MNC_N,:in_CO_TIP_EVT_N,:in_CO_ERROR_N,
v_DS_EVENT_V,v_SQ_REV_01,v_FX_RCP_EVT_D,v_FX_RCP_IAS_D,:in_CO_CAUSA_N,v_ErrCode,v_ErrMsg);

if (v_ErrCode<0) THEN
RAISE NON_EXPECTED_ERROR;
END if;


:var_Error := sqlcode;
:var_Sqlerrmc := sqlerrm;

EXCEPTION
WHEN NON_EXPECTED_ERROR THEN
rollback;
:var_Error := CSRIRDB_NON_EXPECTED_ERROR;
:var_Sqlerrmc := v_ErrMsg;
WHEN OTHERS THEN
rollback;
:var_Error := CSRIRDB_NON_EXPECTED_ERROR;
:var_Sqlerrmc := sqlerrm;
END;

END-EXEC;

trace_write(TRACE_LEVEL_LOW,"FINAL CSRIRDB_putEvent");

if ((sqlca.sqlcode == 0) && (var_Error == CSRIRDB_OK))
{
trace_write(TRACE_LEVEL_HIGH,"Insertado evento OK");

return CSRIRDB_OK;
}
else if (sqlca.sqlcode != 0)
{
var_buf_len = sizeof (var_msg);
sqlglm(var_msg, &var_buf_len, &var_msg_len);

trace_write(TRACE_LEVEL_ALWAYS, "(%s) (%d)",var_msg,sqlca.sqlcode);

return CSRIRDB_FATAL_ERROR;
}
else /* var_Error != CSRIRDB_OK */
{
trace_write(TRACE_LEVEL_HIGH, "(%s)",var_Sqlerrmc);

return CSRIRDB_NON_EXPECTED_ERROR;
}


}


This is my table:
CREATE TABLE IR_TB_RCP_EVTS (
SQ_REV_01 NUMBER (15) NOT NULL,
CO_IMSI_V VARCHAR2 (15),
CO_MSDN_V VARCHAR2 (18),
CO_PER_CLT_N NUMBER (10),
CO_GT_V VARCHAR2 (15),
FX_RCP_EVT_D DATE NOT NULL,
CO_MAP_VERS_V NUMBER (5),
CO_MCC_N NUMBER (5),
CO_MNC_N NUMBER (5),
CO_TIP_EVT_N NUMBER (5) NOT NULL,
FX_RCP_IAS_D DATE,
CO_ERROR_N NUMBER (5) NOT NULL,
CO_CAUSA_N NUMBER (5),
DS_EVENT_V VARCHAR2 (100) )
PARTITION BY RANGE (FX_RCP_EVT_D)
...........

And the procedure PR_INS_IR_TB_RCP_EVTS just inserts.


What things could/should I do?


Tom Kyte
June 28, 2004 - 9:17 am UTC

I don't understand why the big block. I would call

PA_INSERT.PR_INS_IR_TB_RCP_EVTS(:in_CO_IMSI_V,:in_CO_MSDN_V,v_CO_PER_CLT_N,
:in_CO_GT_V,v_CO_MAP_VERS_V,v_CO_MCC_N,v_CO_MNC_N,:in_CO_TIP_EVT_N,:in_CO_ERROR_N
,
v_DS_EVENT_V,v_SQ_REV_01,v_FX_RCP_EVT_D,v_FX_RCP_IAS_D,:in_CO_CAUSA_N,v_ErrCode,v
_ErrMsg);

directly and just pass it the strings ('' is null, to_number('') will be null, the if then elses are not necessary)


Also, never:

select decode(:in_CO_MAP_VERS_V,'',NULL,:in_CO_MAP_VERS_V),
decode(:in_DS_EVENT_V,'',NULL,:in_DS_EVENT_V)
into v_CO_MAP_VERS_V, v_DS_EVENT_V
from dual;


that would be better as "if then else" at the very least. but it is not necessary since:


void process()
{
EXEC SQL BEGIN DECLARE SECTION;
char * instr;
varchar ostr[255];
short ostr_i;
EXEC SQL END DECLARE SECTION;

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

instr = "";
ostr.len = 255;
ostr_i = 1;

exec sql execute
begin
if :instr = ''
then
:ostr:ostr_i := 'it was blank';
elsif :instr is null
then
:ostr:ostr_i := 'it was NULL';
else
:ostr:ostr_i := 'it was neither blank, nor null "' || :instr || '"';
end if;
end;
end-exec;
printf( "%.*s\n", ostr.len, ostr.arr );
}

[tkyte@tkyte-pc t]$ ./t
it was NULL




it is null already.....


So, I would

a) have nothing more than begin procedure(:binds); if (v_error) then raise_application_error(-20001,'whatever'); end;

b) get rid of those exception handlers.
o the rollback would happen automagically.
o the error code and message are easily available in pro*c already.
sqlca.sqlcode and the full error text via:


static void sqlerror_hard()
{
char errmsg[513];
int length;
int real_length = sizeof(errmsg);

EXEC SQL WHENEVER SQLERROR CONTINUE;

printf("\nORACLE error detected:");
sqlglm( errmsg, &real_length, &length );
printf("\n% .*s \n", length, errmsg );
}





Some questions more

Fernando Sanchez, June 30, 2004 - 8:51 am UTC

Thanks Tom.

I've followed your advices and I've won around 10%, but it is still taking too long. I've tried not doing commit after the insert but I it doesn't improve (in fact it worsens!).

I've been suggested to use locally managed tablespaces instead of dictionary managed tablespaces. Can that have something to do with inserts taking so long?

Can you guess what kind of problems can be happening?


Tom Kyte
June 30, 2004 - 10:27 am UTC

can you bulk it up? remove the round trips?

You don't have any inserts, you are calling a stored procedure. We can definitely speed that up if you bulk up the data.

the caller of your function would have to save up arrays of data to be processed, we can pass that in a single shot to the server and have say 100 inputs processed in one call. it would very likely have a measurable benefit.

also -- look at the stored procedure you are calling, you'll only go as fast as it goes.



indicator variable value

Alex Smith, July 15, 2004 - 2:39 pm UTC

Hello Tom

In Pro C, If Indicator variable has value = -1 means NULL value , = 0 means Variable has some value returned.

Can I request you to tell what are other possible values for an indicator variable in Pro C.

Thanks

Tom Kyte
July 15, 2004 - 6:24 pm UTC

+1 = truncated
-2 = another kind of truncated



fortunately we document these factoids
</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a97269/pc_04dat.htm#2239 <code>

The same question, over again...

Mariano, July 16, 2004 - 10:40 am UTC

Tom, hi.

Just in order to clarify (at least for me because I can see you've already answered it)

if I precompile a file with a proc++9i, the final program (after compiling and making it with the c++ compiler):

can be execute on a machine with a 8.0.5 client (connected to a 8.0.5 server)?

can be execute on a machine with a 8.0.5 server?

if the answer is no, is there anyway to make it work (e.g.: install some extra libraries -dynamic or static-)?


If you don't mind another question:

the following
void sqlglm(char *message_buffer,
size_t *buffer_size,
size_t *message_length);
is used to printf the error code of any action: is there a function that can be use to only output the error number?

As always, thanks a lot.
Mariano


Tom Kyte
July 16, 2004 - 1:44 pm UTC

you can run a 9i client against 805 using sqlnet.

it can be executed on the machine with the 805 server as long as the 9i client software is installed.

not sure what you mean by the last question -- but if all you want is the sqlcode, sqlca.sqlcode has that.

I'm afraid I was not clear enough

Mariano, July 16, 2004 - 1:53 pm UTC

Tom,
I think I didn't explain myself clear enough, sorry.
Let me try again:
In an 9i server, with proc++9i I precompile a small command line tool that connects to datase. After succesfull proc precompilation, I create an .exe with my c++ compiler.

I need this .exe to run, connect and execute in a machine that has an 8.0.5 client (connecting to an 8.0.5 database).
Will it work there? For your last answers, I'm afraid it won't.
If the answer is no, is anything that can be done to make it work? (extra code, anything)

Tom Kyte
July 16, 2004 - 2:10 pm UTC

you need to have the 9i client installed. that is the thing that needs be done to make it work.

you'd probably be much better off using java and thin jdbc -- no install needed outside of the required jar files. more portable than a "exe" file as well -- everyone would be able to use it, not just the windows users.

Madhulika

Madhulika Karan, July 19, 2004 - 8:15 am UTC

Hi Tom,
I hit upon this site accidently and found it too helpful.
Since status shows not to ask anymore questions, I waited for a week, but getting same status.
Tom, it would be helpful if you could tell me a way to call one pro*c executable from xyz.pc file.
To be more precise :
I have a file xyz.pc in Server A.
Have another file abc.pc on server B.
Want to call xyz executable from abc passing input as server/userid/passwd/input string.
Is it possible?
It would be a great if anybody can help me on this.

Thanks.


Tom Kyte
July 19, 2004 - 8:53 am UTC

if you turn your "executable" into an external procedure -- sure, then it would just be a stored procedure call over a database link.

Otherwise, you are asking an operating system question -- nothing to do with Oracle really. You'd have to look at what features your OS of choice offers as far as that goes.

If you have access to my book Expert One on One Oracle -- I demonstrate in there how to make a Pro*C External Procedure, or see:

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg11rtn.htm#1656 <code>

Java.-

Mariano, July 19, 2004 - 7:11 pm UTC

Tom
Hi.

I followed your advice and translate my needs from ProC to Java.
Never touched java in my life. However, I wrote down some .java files, I compile and run it using JDeveloper.

My beginner doubt is the following: If I want that the guy with the 8.0.5 database I mention a few posts before, tests my little application: what files do I need to send him? (e.g.: In Proc, after compiling and make, I get a .exe)
Thanks in advance

Tom Kyte
July 19, 2004 - 7:19 pm UTC

you need to send him your jar file and make sure he has the thin jdbc jar file as well (classes12.jar i think it is)

ORA-12805: parallel query server died unexpectedly

Steve, August 24, 2004 - 9:27 am UTC

Tom
What could be the reason for this error?..The process was running fine for 1 year.
Thanks

Tom Kyte
August 24, 2004 - 10:15 am UTC

[tkyte@xtkyte-pc tkyte]$ oerr ora 12805
12805, 00000, "parallel query server died unexpectedly"
// *Cause: A parallel query server died unexpectedly, PMON cleaning
// up the process.
// *Action: Check your system for anomalies and reissue the statement.
// If this error persists, contact Oracle Support Services.
// See trace file for more details.

[tkyte@xtkyte-pc tkyte]$


Pro*C

reader, May 16, 2005 - 10:30 am UTC

Tom,
I just need to have a vendor Application execute in our 9.2.0.6 enviornment on Unix 11i. No compiles will be done just executions.

What is that I need to install from Oracle perspective.

thanks.

Tom Kyte
May 16, 2005 - 1:10 pm UTC

just the client software.

Performance comparison between Pro*C & Perl DBI for Oracle Insert, Update, Fetch and Delete

Deepak Samant, June 15, 2005 - 9:50 am UTC

Hi Tom,
COuld you please give me your valuable suggestion on the performance aspect of oracle 9.0.2, if using perl DBI & Pro*C as per my findings below.

The result what I found for update, fetch & delete is very surprising. Could you please help me in this regard.

I'm sorry for putting the question in this section, as I couldn't able to post the question from regular forum & waited for last 15 days.

Thanks in advance for your support.

Please find below my findings in both PERL & PRO*C:

Perl Performance:

Operation Time Taken


1) INSERT 34 seconds
2) UPDATE 26 seconds
3) FETCH 25 seconds
4) DELETE 27 seconds


ProC Performance:

Operation Time Taken


1) INSERT 48 seconds
2) UPDATE 8 minutes 56 seconds
3) FETCH 9 minutes 19 seconds
4) DELETE 4 minutes 7 seconds

Perl snippet
sub update
{
ldf($log_file, "INFO"," At start of SQL Update Block - Prepare Once(CTEST3) ");

$sql = "UPDATE CTEST3 SET ROLL = :roll+1, ACCOUNT_ID = :acc_id+1 WHERE SL_NO = :sl_no1";
$sth = $dbh->prepare($sql);

while ($j <= 10000)
{
$sth->bind_param_inout(':sl_no1',\$v1,1000) ;
$sth->bind_param_inout(':roll',\$v2,1000) ;
$sth->bind_param_inout(':acc_id',\$v3,1000) ;
$sth->execute();
$j++;
$v1++;
$v2++;
$v3++;
}

ldf($log_file, "INFO"," At end of SQL Update Block - Prepare once(CTEST3)");
$sth->finish();
}

ProC snippet
for(v_count1=0;v_count1 <= 10000;v_count1++)
{

EXEC SQL UPDATE CTEST3
SET ROLL = :v_roll + 1, ACCOUNT_ID = :v_account_id + 1 WHERE SL_NO = :v_sl_no;
v_sl_no++;
v_roll++;
v_account_id++;
}
logToDateFile(db_error_file,INFO,"End of SQL Block for UPDATE (CTEST) \n");


Regards,
Deepak Samant(India)

Tom Kyte
June 15, 2005 - 10:15 am UTC


I can only guess you did not do remotely even close to the same work.

use sql_trace=true and verify you are first. make sure you are parsing the same and everything else (hold cursor and so on-- look at the pro*c command line options)

Performance comparison between Pro*C & Perl DBI for Oracle Insert, Update, Fetch and Delete

Deepak Samant, June 16, 2005 - 12:35 am UTC

Hi Tom,
Thanks a lot for your responce.

I'm running this query remotely, as the server is situated in U.K and we are working from Offshore.

Could you please elaborate the statement you have used "verify you are first", is not clear to me.

Could you please explain in more detail, why this kind of abrupt outcome is coming.

I had tested this more than thrice & always getting such kind of difference in the responce time.

Thanks again for your help.

Regards,
Deepak Samant,(India)

Tom Kyte
June 16, 2005 - 3:43 am UTC

verify you are doing the same work first.

tkprof it.

different ways to connect to oracle

A reader, July 22, 2005 - 12:34 am UTC

Tom,
Other than PRO*C and OCI, are there any ways to connect to oracle from C?

Thanks.


Tom Kyte
July 22, 2005 - 8:50 am UTC

there are lots of "api" wrappers people have written, such as ODBC for example, they all pretty much ride on top of OCI at the end of the day.

so yes, there are, odbc would be an example of yet another api.

VARCHAR variable

Giridhar, October 18, 2005 - 8:17 am UTC

Hi Tom,
It is given as follows in pro*c manual in "Embedded PL/SQL Chapter".
-----------------------------------------------------------
You can use the VARCHAR datatype to declare variable-length character strings. If the VARCHAR is an input host variable, you must tell Oracle what length to expect. Therefore, set the length component to the actual length of the value stored in the string component.

If the VARCHAR is an output host variable, Oracle automatically sets the length component. However, to use a VARCHAR (as well as CHARZ and STRING) output host variable in your PL/SQL block, you must initialize the length component before entering the block. So, set the length component to the declared (maximum) length of the VARCHAR, as shown here:

-----------------------------------------------------------

My doubt is why do we need to set the length of the VARCHAR variable?

Thanks.
Giridhar

Tom Kyte
October 18, 2005 - 9:16 am UTC

because the documentation says to:

you must tell Oracle what length to
expect. Therefore, set the length component to the actual length of the value
stored in the string component.



(i don't know WHY everything is the way it is, I just know WHAT is expected - that is important....)

Pro*c in Oracle10g

rose, February 07, 2006 - 7:58 am UTC

Hi Tom,
I have not found Pro*C in Installer Oracle10g Enterprise Edition for Windows.
Is it available ?

Thank you very much



Tom Kyte
February 08, 2006 - 12:59 am UTC

yes, I don't have the CD set with me, but it was an option on the main install screen as I recall (part of the client install I believe)

Pro*C GUI in Oracle10g

rose, February 08, 2006 - 11:37 am UTC

Tom,

I installed and reinstalled Oracle Development Kit (10gR2)- Oracle Programmer Pro*C/C++.
Proc.exe was installed in /oraclehome/bin, but Pro*C GUI isn't available.

After this, I read in "Oracle Database Installation Guide 10g R1 for Microsoft Windows" that the component Pro*C GUI is not available (is a deprecated component) for installation with Oracle Database 10gR1 (I supose R2 too).

Now I have this problem and I would like your help: I always used Pro*C GUI to generate my application (Oracle 7, 8i and 9i). How can I use Pro*C in 10g?

thank you very much

Tom Kyte
February 09, 2006 - 4:08 am UTC

you just run it on the command line ....
</code> http://docs.oracle.com/docs/cd/B19306_01/win.102/b14321/use.htm#sthref35 <code>
The documentation explains the parameters.

You can easily put it into your makefile for your project to automate the entire process.

Proc Tuning & embedded PL/SQL

Thierry Nestelhut, March 09, 2006 - 3:19 pm UTC

Hello Tom,

We have a new batch to create for which performance is very important.

For this kind of treatment, we used to create ProC program.
Usually we have a cursor on huge tables fetching into host arrays (100 rows per fetch), some individuals actions then, in parallel, we insert into other tables (host arrays again).

This new program is a little bit different because we have a lot of individual controls to perform and theses controls are elementary select to do on several tables.
Theses controls have to be done for each row fetched by the main cursor.

(1) My first question is the following : Do you thing we should group all theses controls/selects in one PL/SQL block to reduce the number of calls (as adviced in the Oracle Documentation) ?

For your information, binaries are stored and executed in the server where this Oracle 9i database is managed, then the network has no influence (I hope) on the performance.

In fact, I ask you this question because I believed that :

EXEC SQL SELECT ...;

was better (direct access to the SQL engine ?) than (access via the PL/SQL engine ?) :

EXEC SQL EXECUTE
BEGIN
...
END;
END-EXEC;

(2) Am I wrong ?

Thank you.
Best regards.


Tom Kyte
March 09, 2006 - 4:00 pm UTC

I would be doing this in plsql frankly. Using as LITTLE procedural code as possible and as many BIG JUICY SQL statements as possible.



An error:ORA--1405:Message -1405 not found...

steve, March 26, 2006 - 8:39 pm UTC

Hi, Tom.
Now I'm in trouble on pro*c++ program.
We developed a pro*c++ project on Win2K to access Oracle 8i DB.After successful precompiling and building, we run the program, but we reteived an error:
"ORA--1405: Message -1405 not found; product=RDBMS; facility=ORA".
I must say that this error didn't usually occur, sometimes it appear, and prehaps in next time it disappear.
After reading the Oracle document, we have no idea to resoleve this problem.
Tom, can you tell me something about this error, and what we can do to resolve this problem.
Thx a lot.

Tom Kyte
March 26, 2006 - 8:55 pm UTC

Looks like your ORACLE_HOME environment variable is not set.

if it cannot find the message file, that would be what I would expect - that oracle_home was not set or was set wrong.

I think my oracle_home was right set.

A reader, March 27, 2006 - 8:17 pm UTC

I think my oracle_home was right set,perhaps it isn't the cause. Because the other errors occured, the message file was found and the message was showed. If the oracle_home was not set, may i say that when all errors occured, the all message would not be found, and the system would return the same message such as:"Ora--1405: message was not found, product=..."?
But in fact it did't.

Tom Kyte
March 27, 2006 - 8:26 pm UTC

then I have a feeling your pro*c program you wrote stomped massively hard on some memory.

Eg: You have a memory overwrite in your code and stomped on something big time. C is notorious for that if you are not careful in your programming. Might be time for a memory bounds checker.

for the environment got wiped out somehow - else that message would not appear.

Oh,memory prolem

Steve, March 29, 2006 - 10:33 pm UTC

Thank you, Tom.
Perhaps you are right. Our program is a multi-thread program, I will check the memory bound.


proc executable and other pro*C libraries not installed after an EE installation

A reader, May 24, 2006 - 6:55 pm UTC

Hi Tom,

I did a initial EE installation of 9201 and then upgraded the binaries to 9207. After that I tried to compile a pro*c program and it is erroring out because the proc executable is not in $ORACLE_HOME/bin. After checking the proc configuration I found out that there are a bunch of proc libraries that are missed on $ORACLE_HOME/precomp/lib as well. I ran the universal installer and I checked the installed products and the Oracle programmer 9201 and the upgrade to 9207 were installed. I also checked the log file for the binaries installation and I did not see any problems or error during the binaries installation and the upgrade.
1-How can I am missing libraries and the proc executable if the Pro*C installations is being shown as done?
2-Should I remove the Oracle programmer installation for 9207 and do a manually installation of the Oracle Programmer 9201 again and then upgrade that to 9207?
Or what should I do to get the libraries I need to get pro*C work?
3-Can I reisntall the pro*C binaries having databases up and using the 9207 binaries? or should I shut the DBs down and then reinstall the binaries?
I would really appreciate any help you can give me on this.
Thanks

Tom Kyte
May 25, 2006 - 1:29 pm UTC

please utilize SUPPORT for installation and configuration issues. I don't even know what platform you are on or anything.



ProC and External Procedure

rose, August 28, 2006 - 11:23 am UTC

Hi Tom,
My application is in ProC.
My question is: I need to apply .DLL function in each row from table X (this function is a search).
Is it possible use .DLL functions in PL/SQL using ProC instead OCI?
How?

thanks,


rose

Tom Kyte
August 28, 2006 - 12:21 pm UTC

if you are building a "search" that will be called for each row, and itself will be doing SQL back to the database, you will have a slow search. You might rethink your architecture here.


</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14407/pc_07pls.htm#i8054 <code>
but yes, pro*c can be used.

ProC and External Procedure

rose, August 28, 2006 - 2:19 pm UTC

Tom,
the function is not "Search", sorry.
it is a "comparison" between 2 values:
comp(char *a, char *b).
obs: It is not a comparison using '>' , '<' or '=' .

I my application, I use cursor to bring (to C language) all rows from table X , then I apply the function comp() (.dll) in each "row" until it returns find_OK.
But this way is not functional because contents can modify in DB.

rose


Tom Kyte
August 28, 2006 - 4:40 pm UTC

i hope a "cartesian join" is not part of the picture here either then.....



Which one is better??

A reader, August 29, 2006 - 9:41 am UTC

Hi Tom,
I'm using Proc to write up a report. However, the SQL I'm using is not supported in the version of Proc I'm using. Basically, what I'm trying to do is something like
select a, b, c,
(select sum(d) from tab2
where tab2.col1 = tab1.col1 ) sum_d
from tab1, tab3, tab4
where tab1.col1 = tab3.col1
and tab1.col2 = tab4.col2 (+)
and tab1.col3 = tab4.col3 (+)

The table tab2 may or may not contain records.

I have two options (as far as I can see)

Either I do
select a, b, c, sum(d) sum_d
from tab1, tab3, tab4, tab2
where tab1.col1 = tab3.col1
and tab1.col2 = tab4.col2 (+)
and tab1.col3 = tab4.col3 (+)
and tab1.col1 = tab2.col1 (+)
group by a,b,c;


or
for each row in the outer query

select a, b, c
from tab1, tab3, tab4
where tab1.col1 = tab3.col1
and tab1.col2 = tab4.col2 (+)
and tab1.col3 = tab4.col3 (+);

I read from the tab2 table

select sum(d) from tab2
where col1 = :val_1;

Which option would you go for and why? Is there any other way to get to my results?

Thank you




Tom Kyte
August 29, 2006 - 3:31 pm UTC

i would always do it in a single query.

scalar subqueries are most useful in an interactive environment to produce a 'better response time for the first rows'. Since you want them all, it is doubtful that you want your query to use anything other than just joins.


select a, b, c, sum_d
from tab1, tab3, tab4, (select sum(d) sum_d, col1 from tab2 ) tab2
where ....

would be it.



For the query above

A reader, August 29, 2006 - 10:33 am UTC

Hi Tom,
I've just added a new packaged function to return the information I need and I'm just calling this function from within the PROC SQL. Sorry wasn't using my brain as I should have!

If you do think of any other way that is more efficient please do let me know.

Ta


Tom Kyte
August 29, 2006 - 3:32 pm UTC

"PROC SQL" is a sort of made up term, the use of PROC in the question above I found confusing.

If you mean to use the term procedure, or stored procedure - that would make things more clear in the future....

single sql statement. don't do it in pl/sql functions called from sql.

Variable Value Display

Arindam Mukherjee, September 13, 2006 - 6:53 am UTC

Respected Sir,

I know the answer is “NO” but still I would like to ask you if any option is left out of my knowledge. In a Pro*C program, one database procedure or package is invoked. Can I display the value of any variable defined in that Database procedure or package? My understanding is “dbms_output” is available in SQL*Plus environment where I am executing Pro*C in UNIX environment.
Only one option exists if I put one “out” parameter to display the value of that variable in UNIX environment but I don’t think of changing the signature of database object just for testing.

So before saying positively “NO” to my colleagues, I would like to ask you just to double check how much correct I am.




Tom Kyte
September 13, 2006 - 2:38 pm UTC

what is the goal - you can run a debugger on plsql code
</code> http://www.oracle.com/technology/obe/sqldev/plsql_debug/plsql_debug.htm <code>

if you want to get information from plsql to a client:

a) out variables (of course)
b) anything that writes to package globals (that is after all all that dbms_output.put_line really does, fills in an array the client retrieves and displays)
c) using dbms_application to set session information
d) using an application context to do something very similar to c
e) using utl_file to write a trace file

and so on...

PRO*C, Unix, XML Validation using XSD

Bipin, July 21, 2008 - 6:29 am UTC

Hi,

Is it possible to validate the XML using XSD in PRO*C or Unix shell script?

If yes how can we use that?

Using Pro C 8i Compiler to run Oracle 9i

NG PING LOONG, October 23, 2008 - 11:02 pm UTC

Dear master TOM,
What is the changes need to be done it , if existing source code was written in oracle 8i and compile it into Pro C/C++ version 8 to run through into Oracle 9i?

Tom Kyte
October 24, 2008 - 1:03 pm UTC

you can either use your existing binaries as is

or you can precompile them with the 9i pro*c and run them


it is up to you - there shouldn't be anything to modify.

PRO*C Link error

Nantha, May 14, 2009 - 12:25 pm UTC

Tom,
I am trying to compile and run a very basic Pro*c code with oracle 10g, I don't see any Precompiling error. But when I compile my code with Microsoft Visual C++ compiler, I am getting the following error:
C:\Program Files\Microsoft Visual Studio 8\VC\INCLUDE\sqlcpr.h(66) : error C2732
: linkage specification contradicts earlier specification for 'sqliem'
C:\Program Files\Microsoft Visual Studio 8\VC\INCLUDE\sqlcpr.h(66) : see
declaration of 'sqliem'

This is my code:

#include <stdio.h>
#include <string.h>
#include <sqlda.h>
#include <sqlcpr.h>

EXEC SQL INCLUDE SQLCA.H;

void main()
{

EXEC SQL BEGIN DECLARE SECTION;
char * uid ="TEST/TEST@WORLDDB";
EXEC SQL END DECLARE SECTION;

EXEC SQL WHENEVER SQLERROR GOTO errexit;
EXEC SQL CONNECT :uid ;

printf("Connected to Oracle\n");

EXEC SQL COMMIT WORK RELEASE;
return;

errexit:
printf("Connection failed");
return;
}
Could you please let me know where exacly I am doing wrong?