Visit Our UNIX and Linux User Community


how to obtain a persistent oracle connection in proc*c pthread


 
Thread Tools Search this Thread
Top Forums Programming how to obtain a persistent oracle connection in proc*c pthread
# 1  
Old 08-10-2009
how to obtain a persistent oracle connection in proc*c pthread

Hi all,
I have problem to set up a persistent oracle connection in child thread.
We have a massive batch process dealing with large data. Application was written and impossible to be rewritten. I am trying to extract the most time consuming data fetching and put it into a dedicated child thread.
So main thread will only process(including quick fetch from DB) and child can constantly fetch. Data will be flushed from child to main by global variables.
However I can't see any way to obtain a dedicated DB connection in pthread child.
I got core when main and child both try to access DB.
I tried using context, allocate different context to main and child, but not working, still same core. Smilie
If I stop main accessing DB, then child is fetching continuously.

Can some one help me out or give some code I can refer to?

Thanks in advance!
# 2  
Old 08-10-2009
One easy way - Use IPC - create separate processes. Oracle uses coprocesses in Pro*C. That means your child thread and the main program are actually trying to get the coprocess to run fetches at the same time.

Map a big chunk of shared memory. Start your fetcher as one process, start main as another.
Have main feed fetch requests to the fetcher process , fetcher returns the data. All via shared memory.

Also consider running your old main code as a group of processes. Suppose you are processing items 1000 - 9000.
Start old main with parms 1000-1999, run it in the background. Start old main in the background again with parms 2000-2999, and so on. Call wait for all the processes to complete. This is 'sort of' threading.
# 3  
Old 08-10-2009
Thanks for prompt reply, we can't use the second option due to many existing facilities in this batch will require the batch to be finished in 1 process.
I am very interested in using IPC, where can I see any sample code of IPC? Do you mean fork?
Thanks!
# 4  
Old 12-03-2009
Thanks Jim, the implementation was very successful, I did it by separate the fetcher out as a new process that you mentioned as the first option.
Fetcher always fetches before main needs, then main flush fetched data from IPC.
Performance is improved by 40%.
Appreciate your help!

Previous Thread | Next Thread
Test Your Knowledge in Computers #473
Difficulty: Easy
A single 0 or 1 is referred to as a bit.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Parallel execution of Oracle PROC in UNIX

Hi, Trying to run the Oracle Proc in parallel, but not working.. below code runs each call of the procedure and its coming out of the loop before its completion and printing the last statement! not waiting for the sqlplus to complete. for name in `cat abc.txt` do (sqlplus -s ... (3 Replies)
Discussion started by: ATWC
3 Replies

2. Programming

Help in pro*c for Oracle connection

hi, am making oracle connection through pro*c using the following command sample: exec sql connect username identified by password. but m getting the following errors frequently even though listener and db is running fine but some times code is working fine and not giving errors. ... (2 Replies)
Discussion started by: senkerth
2 Replies

3. Shell Programming and Scripting

Perl Oracle connection error

I've a Centralized Server which can connect to all Oracle Databases. When I was trying the below code it doesn't work, But tnsping was working fine. #!D:/perl5/bin/perl.exe use DBI; my $dbh = DBI->connect('dbi:Oracle:QBDIWCE', 'IDUSER', 'SECRET123#' ) || die( $DBI::errstr . "\n" ) ; ... (1 Reply)
Discussion started by: ilugopal
1 Replies

4. UNIX and Linux Applications

How to capture the value returned by a stored proc while executing it from SQSH connection

I have a very simple set up I am connecting to a MS SQL db using SQSH statement from a shell script In this sqsh connection i am trying to execute a stored proc However I want to capture the value returned by the stored proc. I haven't really come across anything useful so far which would... (0 Replies)
Discussion started by: shishirkotkar
0 Replies

5. Shell Programming and Scripting

Oracle connection with UBUNTU

Hi all, Will I be able to connect to oracle using UBUNTU. If so, can any one provide me the steps. I want to try table archival and sql loader using control files. Please do help. Thanks, Raaga (1 Reply)
Discussion started by: Raaga
1 Replies

6. Shell Programming and Scripting

SSH Connection To Oracle

My problem is this....:eek: Platform=Sun Solaris UNIX / Oracle 10g 1) I'm trying to SSH from my local system A to remote system B 2) Once connected, I need to sudo to ID that has the ability to connect to the Oracle database 3) Then run a script that connects via sqlplus and... (2 Replies)
Discussion started by: khand67
2 Replies

7. UNIX for Dummies Questions & Answers

Reurning Oracle Proc out parameter

Hi. I need sime help with a unix script. The script runs a Oracle Procedure which then runs some child procedures which are autonomous. When the child procedures fail, the sql error is not passed back to the parent procedure - i.e all the othert child procedures run. I want to execute the... (0 Replies)
Discussion started by: Zdnas_79
0 Replies

8. Windows & DOS: Issues & Discussions

Oracle 9i ODBC connection

I am attempting to access an Oracle 9i database located on a Solaris server from an XP client. The server is running Oracle 9i9.2.0.1.0 and the XP is Oracle client 9i 9.2.0.1.0. When I start a listener on the server, the listener starts but I receive a message “The listener supports no services”. I... (3 Replies)
Discussion started by: jkuchar747
3 Replies

9. Shell Programming and Scripting

Unix call to Oracle PL/SQL pkg/store.proc

HI, I'm trying to get this right, please can you help. In my unix korn shell script, I call an oracle stored proc within a package and I specify 3 parameters, 2 of which are IN OUT parameters (i.e. I expect the stored proc to change them and return them back to me). Does the unix code... (7 Replies)
Discussion started by: csong2
7 Replies

10. UNIX for Advanced & Expert Users

Unix + oracle connection

Requirements: Using a shell script I have to check the 4 values from a table in Oracle and then store the result as "Y" in a Unix variable if the values are desired and "N" if the values are not desired. Explanation: There is a table abc in oracle which has 4 fields. if all the four values... (2 Replies)
Discussion started by: decci_7
2 Replies

Featured Tech Videos