Home » SQL & PL/SQL » SQL & PL/SQL » to make entry in log table
to make entry in log table [message #242254] Fri, 01 June 2007 05:59 Go to next message
rajalakshmi
Messages: 5
Registered: May 2007
Junior Member
I have transfer procedure which will insert its parameters into fields of sam table.If any exception raised then it should be entered into log_sam table.If exception raised in inserting into one column i want to insert that column name also in log_sam table.This is the sample i tried.Actually i have to insert data from one text file to table.Data in text file will be like this "a","v","l".

Sam table is
Column Name Data Type Nullable Default Primary Key
NO NUMBER No - -
NAME VARCHAR2(10) Yes - -
AGE VARCHAR2(10) Yes - -

log_sam
Column Name Data Type Nullable Default Primary Key
ERRLINE VARCHAR2(250) Yes - -
PROCNAME VARCHAR2(250) Yes - -
TABNAME VARCHAR2(250) Yes - -
ERRMSG VARCHAR2(250) Yes - -
COLNAME VARCHAR2(4000) Yes - -
OCURRED TIMESTAMP(6) Yes - -


Transfer procedure
create or replace procedure transfer
(buf VARCHAR2)
is
a char(10);
b char(10);
c char(10);
temp char(50);
bf varchar2(300);
i integer;
ecode varchar2(200);
emsg varchar2(200);
colna varchar2(200);
begin
bf:=buf;
for i in 1..3
loop
if i=1 then
SELECT REGEXP_substr(bf,'[^"][^"]*') into a FROM DUAL;
temp:=a;
insert into sam(no) values(a);
elsif i=2 then
SELECT REGEXP_substr(bf,'[^"][^"]*') into b FROM DUAL;
temp:=b;
update sam set name=b;
else
SELECT REGEXP_substr(bf,'[^"][^"]*') into c FROM DUAL;
temp:=c;
update sam set age=c;
end if;
select ltrim(bf,'"')into bf from dual;
select ltrim(bf,temp)into bf from dual;
select ltrim(bf,'",')into bf from dual;
end loop;
insert into sam values(a,b,c);
Exception
when others then
emsg:=SQLERRM;
insert into log_sam values(bf,'transfer','sam',emsg,colname,systimestamp);
end;

if i execute
exec transfer('"yy","kk","pp");
it will raise exception because first column of table is number and we r trying to insert character data.the exception will be catched .I want to insert the columnname whose insertion raised the exception.What code i need to add in handler?
plz reply.

Re: to make entry in log table [message #242267 is a reply to message #242254] Fri, 01 June 2007 07:41 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post your Oracle version (4 decimals).
Please read and apply How to format your posts.
Edit your post to fit this (and indent the code).
Post the execution with the error message.

Regards
Michel
Previous Topic: returning multiple records as a parameter
Next Topic: Join or Subquery? Is there another more efficient way of doing this
Goto Forum:
  


Current Time: Tue Dec 06 04:18:51 CST 2016

Total time taken to generate the page: 0.22229 seconds