Thanks for your prompt response. It is of great help to me. You said that you prefer to using Pro*C instead of OCI if the job can be done with Proc*C. But do you agree that OCI is a bit more convenient for multithreaded application?

I am reading your book now, which is very well written. As for Pro*C and OCI, I have not found any books other than the manuals. The Oracle University does not provide training on these two topics, which makes me wondering that Pro*C and OCI is out-of-date.
Thanks again.

Tom,

A follow up question: I have a number of threads which have dedicated connections. How can I do a commit across all of the threads (sessions). Thanks again.

Every time i come here I learn alot of things. Just one more question on the subject please: Does the Oracle parallel server has to be runing to use multithreading? I am runing an application and I am using threads.h but every ime i try to spawn more than one thread, the process dies.

Thanks,
Firas Adel

Tom, how do you program in OCI, when you say OCI are you talking about a programming languagae or programming languagae (s).

Can you give an example or some examples of OCI programming.

I have a demon written in pro*c .I have MTS parameter
in a init.ora.Recently i commented those parameter.
(because i have only 50-100 connetions)
After commenting ,i noticed my one demon fails once in a while.I asked the person who wrote that,whether it uses any
mutlithread feature,and the reply was no.
After that i had no option other then enabling those feature
again.After enabling it works fine .What could be the problem ??
Your comment please

dear tom,
You gave a code to use two different context. What i understand from that code is that if i use two different contexts there are two live connections and i have a choice of using any one of the connection. But when i tried to do the same i.e. I COUNTED THE NUMBER OF SESSIONS AT EACH STEP AND FOUND OUT THAT THE LIVE CONNECTION FORMED IS JUST ONE EVEN IF I CONNECT TO THE SECOND DB USER. i again used the first context saying EXEC SQL CONTEXT USE :CTX[0] and tried to fetch the user name and found out that it has maintained the last connection i.e. "TKYTE".

I'm attaching a code. When I connect I have 24 connection i one user while 21 in other. Now I expect it to remain same for a context depending on the context i use. I want two live connections , one each for a thread.

The code is tested on solaris 5.6

#include <stdio.h>
#include <pthread.h>

EXEC SQL INCLUDE SQLCA;

void logon(sql_context ctx, char * uid);
void *t1(sql_context *);
void *t2(sql_context *);
main()
{
sql_context ctx[2], ctx2;
pthread_t t_id[2];
int i;
char uid[2][100];
strcpy(uid[0],"rstest/xxxxxx@rstest");
strcpy(uid[1],"iiss_test/yyyyyy@esstest");
printf("%s %s\n",uid[0], uid[1]);

EXEC SQL ENABLE THREADS;

for (i=0; i<2; i++)
{
EXEC SQL CONTEXT ALLOCATE :ctx[i];
logon(ctx[i], uid[i]);
}
if (pthread_create(&t_id[0], NULL, t1, (void *)&ctx[0]))
printf("cant create thread 111\n");
if (pthread_create(&t_id[1], NULL, t2, &ctx[1]))
printf("cant create thread 222\n");
for (i=0; i<2; i++)
pthread_join(t_id[i],NULL);
}
void *t1(sql_context *ctx)
{
int l_conn,i=0;
printf("Thread 11\n");
exec sql context use :ctx;
while (i!=10)
{
exec sql select count(*) into l_conn from v$session;
printf("T111 Connections = %d ERROR = %d i= %d \n", l_conn,
sqlca.sqlcode, i);
i++;
}
return ;
}
void *t2(sql_context *ctx)
{
int l_conn, i=0;
printf("Thread 22\n");
exec sql context use :ctx;
while (i!=10)
{
exec sql select count(*) into l_conn from v$session;
printf("T222 Connections = %d ERROR = %d i = %d\n", l_conn,
sqlca.sqlcode, i);
i++;
}
}
void logon(sql_context ctx, char *uid)
{
int l_conn;
exec sql context use :ctx;
exec sql connect :uid;
exec sql select count(*) into l_conn from v$session;
printf("Connections = %d ERROR=%d\n", l_conn, sqlca.sqlcode);
}

Output:
rstest/rstest@rstest iiss_test/testdata@esstest
Connections = 24 ERROR=0
Connections = 21 ERROR=0
Thread 11
Thread 22
T111 Connections = 0 ERROR = 0 i= 0
T222 Connections = 21 ERROR = 0 i = 0
T222 Connections = 21 ERROR = 0 i = 1
T111 Connections = 0 ERROR = 0 i= 1
T222 Connections = 21 ERROR = 0 i = 2
T222 Connections = 21 ERROR = 0 i = 3
T111 Connections = 0 ERROR = 0 i= 2
T222 Connections = 21 ERROR = 0 i = 4
T222 Connections = 21 ERROR = 0 i = 5
T111 Connections = 0 ERROR = 0 i= 3
T222 Connections = 21 ERROR = 0 i = 6
T222 Connections = 21 ERROR = 0 i = 7
T111 Connections = 0 ERROR = 0 i= 4
T222 Connections = 21 ERROR = 0 i = 8
T222 Connections = 21 ERROR = 0 i = 9
T111 Connections = 0 ERROR = 0 i= 5
T111 Connections = 21 ERROR = 0 i= 6
T111 Connections = 21 ERROR = 0 i= 7
T111 Connections = 21 ERROR = 0 i= 8
T111 Connections = 21 ERROR = 0 i= 9

I'm sorry for not being very clear about my problem. Yeah I realize that there was a bug in the code. But I'll try to explain the poblem again.
My problem is that when I run the code:

#include <stdio.h>
EXEC SQL INCLUDE SQLCA;

sql_context do_connect(char * uid);
main()
{
sql_context ctx[2];
sql_context curr_ctx;
char user[31];


/* EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();*/
ctx[0] = do_connect( "rstest/rstest@rstest" );
ctx[1] = do_connect( "iiss_test/testdata@esstest" );

curr_ctx = ctx[0];
EXEC SQL CONTEXT USE :curr_ctx;
EXEC SQL select user into :user from dual;
printf( "I am %s\n", user );


curr_ctx = ctx[1];
EXEC SQL CONTEXT USE :curr_ctx;
EXEC SQL select user into :user from dual;
printf( "I am %s\n", user );
}
sql_context do_connect(char * uid)
{
char user[31];
sql_context ctx;
exec sql context use :ctx;
exec sql connect :uid;

strcpy(user,"");
EXEC SQL select user into :user from dual;
printf( "connected to %s\n", user );
return ctx;
}

The output is:

connected to RSTEST
connected to IISS_TEST
I am IISS_TEST
I am IISS_TEST

The problem is that the program maintains the last connection it had made, whereas I want two live connections.
I expect the output to be :

connected to RSTEST
connected to IISS_TEST
I am RSTEST
I am IISS_TEST

Sorry again from wasting your valuable time by not putting the problem clearly. I hope this time the problem is understandable.

Thanks and Regards

Amit Tiwari

ADD ERROR HANDLING.  YOU have none. 

Your program, as coded above, cannot be producing that output at all.  Just can't.

You are missing the exec sql context allocate :ctx;


Your output is not from the program you posted (arg!  that's a waste of time, the only conclusion I can come to is that the code posted is not the code executed).

Please -- only post that which you actually used (without that statement, your program either:

o seg faults immediately
o prints out garbage

As soon as I put the statement in:

connected to  SCOTT                         
connected to  OPS$TKYTE                     
I am SCOTT                         
I am OPS$TKYTE  

was my output)......
 

Tom,

I have compiled and executed your example pro*c program
for Multi-thread programming.
Is it possible to achieve the multi-thread concept in a
PL/SQL programming. ?

Thanks
Pushparaj

Tom,

I remember you mentioning which gives better performance with reasons but cannot find the message.

Will I get better performance with a pl/sql procedure/package over a pro*c script? How to decide which one to pick, if given a choice?

Thanks in advance!

Thanks Tom!

Since I am reading from a big monster table and checking for certain values in various other tables and finally updating a target table, I guess my best bet would be to use pl/sql.

Anyway, as you said, I will first try it in pl/sql.

Thanks again.

Hi Tom,

You say "If the work is all related -- it should be a SINGLE transaction, not different ones. ". Does it mean that we cannot have a transaction spaning multiple threads? Say I want to update a partition table, each partition update being done in it's own thread, and commit once in the end? If there is a error in one thread rollback all, and try later, may be? This is to avoid adding restart features/ code in the program?

I know in Pl/SQL parallel DML this is achieved through implicit 2PC accross parallel threads (running in their own sessions?). How can this be done when programming in Pro*C multi-threading? And, also if there is an error in one thread, how can it make all threads to abort.

Thanks

Thanks Tom,
Please confirm my understanding
1. A transaction can span multiple threads, this can happen only when the threads do not use their own separate sql contexts. OR sql context has to be shared between the threads.
2. Otherwise, if a transaction has to span multiple threads, XA interface and external resource manager has to be used. This external resource manager can be a TP monitor like BEA tuxedo. And, this kind of programming is conplex and should be avoided.

Tom

I have following code sort off

main() {

fetch from XYZ;

insert into ABC from the fetched records of XYZ;

thread(1);
thread(2);
thread(3);

COMMIT; --commit transactions

}

thread(x int) {


insert records into tab1 for x;

}

The above program inserts the records into ABC but not in Tab1. Is it because it is saperate thread and after successful completion threads data is rollbacked. I have tried putting COMMIT in tread function as well but it did not work.

Can I request you to suggest on this please.

Thanks

Dear Tom,

Your answer is very clear.

We are accessing Db in multiple threads, we are following "way 1" (from above said)
After along struggle we found this way.

1. Will you please tell me what exception will be thrown by Oracle if we access a session in multiple threads.
2. And if we are doing read only form DB (select statement), why can't we use multiple threads with out mutex for a session.

Best regards
Ramakrishnan.A

From your reply,

1) Second threads accessing a secession will Crash in client side it self, means this will not reached DB ?

2) Sorry I could not find reason why read only should done in threads.


Best regards
Ramakrishnan.A

Hi Tom,
You have often suggested using single connection against opening multiple connections to database. In j2ee environment it is very common to use Message Driver Beans(MDB) . Put some thing in queue and MDB will pick it for processing. This way you are multi threading in j2ee environment and it is the "way 2" approach you explained above.Each MDB will have its own connection. In the MDB you can open/get a connection(via application server provided connection pool) and use it, and later close/put the connection (retured to connection pool). Now MDB can select/insert or update a record in oracle database. In this case it is not possible to work with single connection. Is this a bad design? What are the possible caveates.
We are having 12 cpu solaris box running oracle, and we are hitting it with appox 200 MDB's.
Thanks

Hi Tom,
Yes the architecture does take care of it. Let me explain what we are doing. We run program-1 which opens database connection and reads the list of unique id's needed to be processed. Then program-1 will connect to queue and in a loop put one by one all the id's in queue. Now each id is a message. After this, program-1 is done. The transaction associated with program-1 is complete. The messages which were put in queue will be picked by MDB. Note that if we have put 100 messages and we configured only 10 MDB's then the First 10 id's will be picked by MDB for processing and 90 are sitting in queue. Each MDB will get one message. Now MDB will start a new transaction for doing its processing. It will open connection to database and read tables to find the other attributes of id. For doing this it has to read approx 4 other tables. once all the attributes are read it will do the required calculations. After that it will save the calculated results. The transaction associate with the MDB is over. The connection is closed.

Hi Tom,

You write that it's a good thing to do the job in one sql statement, if possible. If pl/sql is involved that gives a context switch penalty.

1) Is this penalty much lower in 10.x where pl/sql are compiled to machine code? (Ie can the same thread in the server execute that code when no vm has to be involved)

2) Compared to a call (context switch) from outside (e.g a pro*c program on the same machine as the db), this (sql-plsql) context switch is much smaller, right?

3) Is there different penalty on the Windows platform where it's a big process with threads compared to the Unix version where there are a bunch of daemons?

4) And to change the subject:
What does the
EXEC SQL ENABLE THREADS;
really do? Tell Pro*c to use reentrant functions? If I use one thread only, will it be slower with that on? If no, why isn't it on by default?

Thanks!
Marcus

Hello Tom,
I request you to read the below mentioned problem carefully.
for which I am getting group of results but I want to compare most recent one with the above transaction and see the number of days to eliminate the customer.
Please, show me the result in single row function, not in group function.

****************PROBLEM**************************
I have a problem, related to dates.
Here is the example
cust_Number Date_of_trans
(MM/DD/YYYY)
0000 0012 3454 07/10/2004
0000 0012 3454 07/31/2004
0000 0012 3454 08/15/2004
I have above three transactions with same customer but different dates. Now I want to calculate number of days between transaction 3 (i.e., 8/15/2004) and transaction 2( i.e, 7/31/2004) and same with transaction 2 with transaction 1. Will you please provide me the solution in oracle sql or Pl/SQL.

I used LAG and LEAD function
select date_of_trans,
LAG(date_of_trans,1,null)
OVER (PARTITION BY cust_number
ORDER BY date_of_trans) last_trans,
date_of_trans - LAG(date_of_trans,1,null)
OVER (PARTITION BY cust_number
ORDER BY date_of_trans) A_No_of_days,
LEAD(date_of_trans,1,null)
OVER (PARTITION BY cust_number
ORDER BY date_of_trans) nxt_trans,
LEAD(date_of_trans,1,null)
OVER (PARTITION BY cust_number
ORDER BY date_of_trans)- date_of_trans
B_No_of_days from Day_book Order by cust_number, date_of_trans
/
result:
21 days
15 days

I can not use the above LAG and LEAD in Where clause of oracle to filter the records those are < 21 days.
HOW CAN I FILTER RECORDS IN ORACLE?

Thanks

hi Tom.
I had developed a testing project that is multithread application bulit by pro*c and Visual C++ 6.0.
When i execute the .exe,an error occurs,the second thread have not logon Oracle.
The following is my code, I can't find what mistake I made in my code.
Would you like to show it to me? Thanks.


EXEC SQL BEGIN DECLARE SECTION;
sql_context global_ctx[THREAD_COUNT];
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE dbcm DATABASE;

/*
* Function: err_report()
*
* Description: This routine handle Oracle error.
*/
int err_report(struct sqlca sqlca)
{
if (sqlca.sqlcode < 0)
{
cout<<endl<<sqlca.sqlerrm.sqlerrml<<"."<<sqlca.sqlerrm.sqlerrmc<<endl<<endl;
return -1;
}
else
return 0;
}


/*
* Function: logon()
*
* Description: This routine logs on to Oracle.
*/
void logon(sql_context ctx,char *conninfo){
EXEC SQL BEGIN DECLARE SECTION;
char connstr[20];
EXEC SQL END DECLARE SECTION;

EXEC SQL CONTEXT USE :ctx;
strcpy(&connstr[0],(char*)conninfo);

EXEC SQL CONNECT :connstr;
}

/*
* Function: logoff()
*
* Description: This routine logs off from Oracle.
*/
void logoff(sql_context ctx)
{
EXEC SQL CONTEXT USE :ctx;
EXEC SQL COMMIT WORK RELEASE;
}

/*
* Function: connect_db()
* Description: This routine allocate runtime context
* and create a connection to oracle.
*/
void connect_db()
{
EXEC SQL BEGIN DECLARE SECTION;
int index = 0;
EXEC SQL END DECLARE SECTION;

EXEC SQL WHENEVER SQLERROR DO err_report(sqlca);

for(index = 0;index < THREAD_COUNT;++index)
{
EXEC SQL CONTEXT ALLOCATE :global_ctx[index];
logon(global_ctx[index],CONNECT_STRING);
cout<<"start session:"<<index<<" ctx:"<<global_ctx[index]<<endl;
}
}

/*
* Function: disconnect_db()
* Description: This routine disconnect from oracle.
*/
void disconnect_db()
{
int index = 0;
for(index = 0;index < THREAD_COUNT;++index)
{
cout<<"close session:"<<index<<endl;
logoff(global_ctx[index]);
EXEC SQL CONTEXT FREE :global_ctx[index];
}
}

void *get_context(int context_no)
{
if(context_no<THREAD_COUNT && context_no>=0)
{
EXEC SQL CONTEXT USE :global_ctx[context_no];
return global_ctx[context_no];
}
else
{
return 0;
}
}

int execute_sp()
{
struct sqlca sqlca;
sql_context ctx;

EXEC SQL WHENEVER SQLERROR DO err_report(sqlca);
ctx = (sql_context)get_context(1);
cout<<ctx<<endl;
EXEC SQL CONTEXT USE :ctx;
EXEC SQL AT dbcm EXECUTE
BEGIN
sp_multhrd();
END;
END-EXEC;

//cout<<"OK!"<<endl;
return 0;
}

int select_data()
{
struct sqlca sqlca;
sql_context ctx;

EXEC SQL BEGIN DECLARE SECTION;
int emp_count;
EXEC SQL END DECLARE SECTION;

EXEC SQL WHENEVER SQLERROR DO err_report(sqlca);
ctx = (sql_context)get_context(0);
EXEC SQL CONTEXT USE :ctx;
EXEC SQL select count(EMPNO)
into :emp_count
from EMP
where DEPTNO = 20;
//cout<<emp_count<<endl;
return 0;

}

void __stdcall thread_1(void * para)
{
int count = 0;
assert(get_context(0));
cout<<get_context(0)<<endl;
while(1)
{
select_data();
Sleep(1);
++count;
if(count>50)
break;
}
}

void __stdcall thread_2(void *para)
{
int count = 0;
assert(get_context(1));
cout<<get_context(1)<<endl;
while(1)
{
execute_sp();
Sleep(2);
count++;
if(count>10)
break;
}
}

void __stdcall thread_3(void *para)
{
assert(get_context(2));
cout<<get_context(2)<<endl;
}

void main()
{
int index = 0;
HANDLE hThread[THREAD_COUNT];

connect_db();
hThread[0] = CreateThread(NULL,0,(LPTHREAD_START_ROUTINE)thread_1,NULL,NULL,NULL);
if(!hThread[0])
return;
hThread[1] = CreateThread(NULL,0,(LPTHREAD_START_ROUTINE)thread_2,NULL,NULL,NULL);
if(!hThread[1])
return;
hThread[2] = CreateThread(NULL,0,(LPTHREAD_START_ROUTINE)thread_3,NULL,NULL,NULL);
if(!hThread[2])
return;

/* Logoff sessions. */
for(index=0;index<THREAD_COUNT;index++)
{
cout<<"Waiting for Thread "<<index<<" to stop...."<<endl;
if(WaitForSingleObject(
hThread[index],
INFINITE) != WAIT_OBJECT_0)
cout<<"Error waiting for thread "<<index<<" to terminate"<<endl;
else
cout<<"Thread "<<index<<" stopped"<<endl;

cout<<"Stop Session "<<index<<"...."<<endl;
}
disconnect_db();
}

I added the exec sql enable threads,and set the project is
multi_threaded project,built,executed.But the error already existed.

Hi Tom,perhaps you have seen that I use some embedded PL/SQL
in second thread.I changed these codes,directly use embedded sql,like this:
EXEC SQL update accounts
set balance = 1003.2 where acount = 10004;
No error occured.
But when I coded like this:
EXEC SQL EXECUTE
BEGIN
update accounts set balance = 1003.0 where account = 10004;
END;
END-EXEC;
It said that no logon.
So I think I made some mistake in using embedded PL/SQL,
then how use embedded pl/sql in multi_threaded project?

hi,

We have a program writen in C++ multithread which has a function that uses a embedded PL/SQL:

declare PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN INSERT INTO ERRORS
( ERROR_ORDER,INSTANCE_ID,PARTITION_ID , ERROR_TIME , MODULE_ID,ERROR_ID,SEVERITY_ID,ACTION_ID ,
ERROR_TEXT )
VALUES ( :iErrorOrder_sql , :iIstanceId_sql , :iPartitionId_sql , TO_DATE ( :tErrorTime_sql , 'yyyy-mm-dd hh24:mi:ss' ) , :iModuleId_sql , :iErrorId_sql ,
:iSeverity_sql , :iActionId_sql ,
:tShortText_sql );
COMMIT;
EXCEPTION WHEN OTHERS THEN ROLLBACK;
END ;

The problem with this PL/SQL is that when two of the many threads ( we run inside a UNIX server with 2 CPU - Tru64 - Oracle 9.2.0.6.0 ) use this piece of code simultaneous the process core dumps.

Can we use embedded PL/SQL in multi thread programs?
Or maybe there are other directives to use?

We noticed from the output of Trace Analyzer that we have two different cursors doing the same insert:

CURSOR_ID:4 LENGTH:446 ADDRESS:9989c60 HASH_VALUE:3139834983 OPTIMIZER_GOAL:CHOOSE USER_ID:34 (INTERNAL_TEAM)

declare PRAGMA AUTONOMOUS_TRANSACTION ; BEGIN INSERT INTO ERRORS ( ERROR_ORDER , INSTANCE_ID ,
PARTITION_ID , ERROR_TIME , MODULE_ID , ERROR_ID , SEVERITY_ID , ACTION_ID ,
ERROR_TEXT ) VALUES ( :iErrorOrder_sql , :iIstanceId_sql , :iPartitionId_sql ,
TO_DATE ( :tErrorTime_sql , 'yyyy-mm-dd hh24:mi:ss' ) , :iModuleId_sql , :iErrorId_sql ,
:iSeverity_sql , :iActionId_sql ,
:tShortText_sql ) ; COMMIT ; EXCEPTION WHEN OTHERS THEN ROLLBACK ; END ;

call count cpu elapsed disk query current rows misses
------- --------- --------- --------- ------------ ------------ ------------ ------------ ---------
Parse 1 0.00 0.00 0 0 0 0 0
Execute 1 0.01 0.01 0 6 10 1 0
------- --------- --------- --------- ------------ ------------ ------------ ------------ ---------
total 2 0.01 0.01 0 6 10 1 0

Event Times Count Max. Total Blocks
waited on Waited Zero Time Wait Waited Accessed
----------------------------------------------------------------- --------- --------- --------- --------- ---------
log file sync.................................................... 1 1 0.00 0.00
SQL*Net message to client (idle)................................. 1 1 0.00 0.00
----------------------------------------------------------------- --------- --------- --------- --------- ---------
total............................................................ 2 2 0.00 0.00 0

non-idle waits................................................... 1 1 0.00 0.00 0
idle waits....................................................... 1 1 0.00 0.00

| binds parsing bind variable values according to position within SQL: <position>:<value> ...
| trc_line trc_line NSBBE means: No separate bind buffer exists (i.e. find value under a prior pos)
| -------- -------- ---------------------------------------------------------------------------------
| 69 65 0:0 1:0 2:0 3:"2005-08-25 14:55:02" 4:70000 5:-1000101 6:101 7:1 8:"Process Started"

CURSOR_ID:6 LENGTH:240 ADDRESS:9973f18 HASH_VALUE:3170032622 OPTIMIZER_GOAL:CHOOSE USER_ID:34 (INTERNAL_TEAM)

INSERT INTO ERRORS ( ERROR_ORDER , INSTANCE_ID , PARTITION_ID , ERROR_TIME , MODULE_ID , ERROR_ID ,
SEVERITY_ID , ACTION_ID , ERROR_TEXT ) VALUES ( :B1 , :B2 , :B3 , TO_DATE ( :B4 ,
'yyyy-mm-dd hh24:mi:ss' ) , :B5 , :B6 , :B7 , :B8 , :B9 )

call count cpu elapsed disk query current rows misses
------- --------- --------- --------- ------------ ------------ ------------ ------------ ---------
Parse 1 0.00 0.00 0 2 0 0 0
Execute 1 0.00 0.00 0 4 9 1 0
------- --------- --------- --------- ------------ ------------ ------------ ------------ ---------
total 2 0.01 0.01 0 6 9 1 0

| binds parsing bind variable values according to position within SQL: <position>:<value> ...
| trc_line trc_line NSBBE means: No separate bind buffer exists (i.e. find value under a prior pos)
| -------- -------- ---------------------------------------------------------------------------------
| 112 108 0:0 1:0 2:0 3:"2005-08-25 14:55:02" 4:70000 5:-1000101 6:101 7:1 8:"Process Started"

Does this make sense to you?

let us know

Robert Hanrahan

I have a requirment to multithread [run many instance of same program for different parameters simultaneously] a stored procedure. Is there any way to achieve that in Oracle?

Hello.

I have been reported a problem in a production environment. A ProC/C++ dll (which I haven't done) is returning the SQL-02134 error. These are my first steps with threads in Pro*C/C++ and I am quite lost.

I see that there is a function for connecting to the db in which EXEC SQL CONTEXT ALLOCATE and EXEC SQL CONTEXT USE are written. EXEC SQL CONTEXT USE is also written before every sql statement or pl/sql block.

I think that the idea for the dll to work is making one connection for each thread which would call a function in the dll so that there would be a EXEC SQL CONTEXT ALLOCATE at the beginning and then would be a EXEC SQL CONTEXT USE for each sql statement or pl/sql block.

I have not been able to cause the error in my development environment (I have only been able to cause a SQL-02131 error if I use the same connection for different calls to the dll functions).

Oracle release is 9.2.0

Some advice?

Thanks in advance.

Tom, I need ur adivse on the below query. In Oracle 9i, the code precompiles and produces test.c file whereas in new
migrated Oracle 10g environment the same code failes with the below message

test.pc:
---------
#include <signal.h>
#include <pthread.h>
#include <unistd.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <malloc.h>
#include <time.h>
#include <sqlca.h>

sql_context ctx;

int main(int argc, char* argv[]) {
signal(SIGINT, trapSigInt);

EXEC SQL ENABLE THREADS;
EXEC SQL CONTEXT ALLOCATE :ctx;
EXEC SQL CONTEXT USE :ctx;

return 0;

}


In Oracle 9i, the code precompiles and produces test.c file whereas in new migrated ORacle 10g environment the same code failes with the below message

"Pro*C/C++: Release 10.2.0.4.0 - Production on Thu Jan 29 17:33:56 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

System default option values taken from:
/b122/usr/oracle/product/102/precomp/admin/pcscfg.cfg

Syntax error at line 135, column 2, file /usr/include/standards.h:
Error at line 135, column 2 in file /usr/include/standards.h
#warning The -qdfp option is required to process DFP code in headers.
1
PCC-S-02014, Encountered the symbol "warning" when expecting one of the
followin
g:

a numeric constant, newline, define, elif, else, endif,
error, if, ifdef, ifndef, include, line, pragma, undef,
an immediate preprocessor command, a C token,
The symbol "newline," was substituted for "warning" to continue.

Syntax error at line 77, column 39, file /usr/include/sys/sched.h:
Error at line 77, column 39 in file /usr/include/sys/sched.h
int sched_getparam(pid_t, struct sched_param *);
.....................................1
PCC-S-02201, Encountered the symbol "sched_param" when expecting one of the
foll
owing:

, )

Syntax error at line 0, column 0, file test.pc:
Error at line 0, column 0 in file test.pc
PCC-S-02201, Encountered the symbol "<eof>" when expecting one of the
following:

; : an identifier, end-exec, random_terminal

Error at line 0, column 0 in file test.pc
PCC-F-02102, Fatal error while doing C preprocessing"

[tkyte@dellpe t]$ cat t.pc
#include <signal.h>
#include <pthread.h>
#include <unistd.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <malloc.h>
#include <time.h>
#include <sqlca.h>

sql_context ctx;

int main(int argc, char* argv[]) {
signal(SIGINT, trapSigInt);

 EXEC SQL ENABLE THREADS;
 EXEC SQL CONTEXT ALLOCATE :ctx;
 EXEC SQL CONTEXT USE  :ctx;

return 0;

}

[tkyte@dellpe t]$ proc t.pc

Pro*C/C++: Release 10.2.0.4.0 - Production on Fri Jan 30 14:27:10 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

System default option values taken from: /home/ora10gr2/precomp/admin/pcscfg.cfg

[tkyte@dellpe t]$ head t.c

/* Result Sets Interface */
#ifndef SQL_CRSR
#  define SQL_CRSR
  struct sql_cursor
  {
    unsigned int curocn;
    void *ptr1;
    void *ptr2;
    unsigned int magic;

In the above error message, the standards.h is a warning message so i can avoid that but the error with sched.h is the one which i am interested in. If you could throw some views that would be helpful for me. Many thanks again.