Re: Strange behaviour calling stored procedure from Pro*C

From: william <ewilliam_at_singnet.com>
Date: 13 Feb 2006 07:31:13 -0800
Message-ID: <1139844673.912021.29670_at_z14g2000cwz.googlegroups.com>


Hi, Sorry for the confusion.

> This is one of the many many posts here where the problem description
> is so vague, that the post boils down to 'It doesn't work. HELP'. How
> can anyone help, when the actual code isn't posted and the problem
> description is full of riddles?

To illustrate the problem, this is how the pro*c program works:

  1. pro*c program opens and reads a file containing members transaction records (15000 records average)
  2. process the records and stores it in a record list (array of data structure)
  3. creates 5 threads, each thread has its own table in oracle. each thread process the record list to find records belonging to their respective table. for example, thread1 reads record in the record list and inserts it into DESTINATION table. the SQL stored procedure goes like this:

PROCEDURE summarizeDestination(PARAM_DESTINATION VARCHAR2, PARAM_ORIGIN VARCHAR2, PARAM_DEPARTURE DATE, OERRMSG out VARCHAR2) BEGIN

	UPDATE DESTINATION
	SET VISIT = VISIT  + 1
	WHERE DESTINATION = PARAM_DESTINATION
	AND ORIGIN = PARAM_ORIGIN
	and DEPARTURE_DATE = PARAM_DEPARTURE;

	IF (SQL%ROWCOUNT = 0) then
		INSERT INTO DESTINATION (VISIT, DESTINATION, ORIGIN, DEPART_DATE)
		VALUES (1, PARAM_DESTINATION, PARAM_ORIGIN, PARAM_DEPARTURE);
	END IF;

	EXCEPTION:
	  WHEN OTHERS THE OERRMSG = SQLERRM;

END The other threads has the same logic, it summarizes the records based on origin, airline..etc. except for the 5th thread which loads all records in the record list to the master table using the following SQL statement embedded in pro*c:

EXEC SQL PREPARE S_REQ FROM "insert into master_table.."

EXEC SQL EXECUTE S_REQ using :records_list

the stored procedure for summary is called inside a loop within the 4 other threads in pro*c as shown below

for (i = 0; i < record_count; i++)
{

	if (strlen(host_dest) > 0) {
		/* some codes to write the records in a log file */

		EXEC SQL
		BEGIN
	 	summarizeDestination(:host_dest, :host_origin, :host_depart,
:oerrmsg)
		END
		end-exec;

		if (strlen(oerrmsg.arr) > 0) /* SQL ERROR */ break;
	} /* else filters the record for loading */
}

4. When every thread completes their processing the work is commited if no error , otherwise it rolls back the transaction



Now this is the output:
  1. The master table has no problem as it loads all the records in the record list into the table. e.g. 15000 records
  2. the summary table loads only the valid records for summary like those with destination. in this case, there are 6000 records with valid destination but only 5000 is loaded into the table (number of VISIT is 5000 - select sum(visit) from destination).
  3. No sql errors generated for the stored procedure. This means that 6000 records is captured in the log file.

[Quoted] For small number of records, around 1000-3000 per file, summary loading has no problem. Is there some limit to for SQL transaction to do commit... for example after inserting 1000, the program must do a commit? where does my inserted records go before doing a commit? it seems I have inserted everything but when commiting it looks like not all records are made permanent in the table.

When I rerun the loading, some of the 1000 records missing is loaded in but some of the 5000 records loaded in is now missing.

> Finally, you are aware your approach is very inefficient?
> At least you should use BULK-INSERT, but better still you should set
> up an appropiate external table, and just MERGE the external table
> into the destination table.

if you think the approach is not efficient, what's the alternative? Really need help.. thanks.. hope didn't confuse you further Received on Mon Feb 13 2006 - 16:31:13 CET

Original text of this message