About to COMMIT,...suicide

From: Eric Chollet <chollet_at_stna.dgac.fr>
Date: 13 Sep 1994 14:50:24 GMT
Message-ID: <354e7g$id_at_donald.cenaath.cena.dgac.fr>

I try to use the audit mecanism on tables updated by a Pro*C piece of software and after what seems to be decades I still can't find any solution to my problem. May be someone here can save my life,...

Basically, what I do is :

1. authorize the audit mechanism in the init.ora file (AUDIT_TRAIL=true) ; database being shutdown and started up afterward,
2. create a simple table under sqlplus :

                CREATE TABLE SAMPLE (KEY CHAR(2), DATA CHAR(5));          2) set up an audit by access on that table,

		AUDIT INSERT ON SAMPLE BY ACCESS;
		COMMIT;

	3) insert rows using the following Pro*C code :

/*
** file : sample.pc (nothing to be proud of about it..)

**
*/

#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;

	VARCHAR usr_pwd[20];
	VARCHAR key[3];
	VARCHAR data[5];

EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE sqlca;

#define BindVarChar(v,t) v.len=strlen(strcpy(v.arr,t))

main(argc, argv)
int argc;
char **argv;
{
 int i;         

	BindVarChar(usr_pwd,(argv[1] ? argv[1] : "myusername/mypassword"));
	BindVarChar(data,"data");

	EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
	EXEC SQL CONNECT :usr_pwd;

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

		sprintf(key.arr,"%d", i);
		key.len = strlen(key.arr);

		
		EXEC SQL INSERT INTO SAMPLE (KEY, DATA) 
				VALUES (:key, :data);

		EXEC SQL COMMIT WORK;
	};
	
	exit(0);
		
sqlerror:
	printf("\n\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK WORK RELEASE;
	exit(1);

}

/*
** end of sample.pc
*/

	4) Panic when I see that the table USER_AUDIT_TRAIL contains only
	one row when it should contain at least 10 of them one per insert.
	The first INSERT seems to tickle the audit mechanism but remaining
	INSERTs are discarted as far as the audit is concerned,...

	I've tried to set up an audit by session, on selects etc,..but I got
	nowhere.

	Oracle Hot Line (France) can't give me any other sensible answer
	than 'try with version 7.blabla'.

	The Pro*C version I used is : 1.3
	and the Oracle RDBMS version is 6.0.37
	

If someone could provide me with any clever ideas, perhaps I won't jump thru the window,..

Thanks in advance to anyone who's gonna spend some time reading this.

				Eric Chollet
				chollet_at_stna.dgac.fr

Received on Tue Sep 13 1994 - 16:50:24 CEST