Home » SQL & PL/SQL » SQL & PL/SQL » External Table Error Trapping (ORACLE 10g)
External Table Error Trapping [message #282601] Thu, 22 November 2007 08:32 Go to next message
ukupatil
Messages: 5
Registered: January 2007
Location: MUMBAI
Junior Member

Hi All,

I have created a procedure to upload data from text file. it traps error and writes in text file at defined folders.
Now i want to trap the error at procedure level and find out the records or related columns and keep a log in data base.

Please advise me on this issue.

The procedure, table data and error logs are as follows:

table data in text file as :
SRNO|NAME|SURNAME|ADDRESS|PIN|PHONE|
01|VIKRANT|PATIL|CHIPLUN|400035|9833406777|
02|VINAY|PATIL|CHIPLUN|400035|9833406777|
03|ABHIJIT|PATIL|39|2,CHIPLUN, DIST RATNAGIRI|400035|9833406777|
04|SURENDRA|PATIL|CHIPLUN|400035|9833406777|

----========================================================
Record Log in BAD_DIR folder :

BAD_DIR:: myADDRESS_1060_44712.bad

03|ABHIJIT|PATIL|39|2,CHIPLUN, DIST RATNAGIRI|400035|9833406777|

----========================================================
Record Log in LOG_DIR folder :

LOG file opened at 11/22/07 18:58:57

Field Definitions for table MYADDRESS
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted

Fields in Data Source:

SRNO CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
NAME CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
SURNAME CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
ADDRESS CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
PIN CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
PHONE CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
error processing column PIN in row 4 for datafile C:\UPLOAD\myADDRESS.TXT
ORA-12899: value too large for column PIN (actual: 25, maximum: 20)



These errors i want to trap in exception block if possible. My procedure is as follows:

create or replace
PROCEDURE A_TEST_EXT_TABLE AS
v_TABNAME VARCHAR2(10):='myADDRESS';
v_CREATE_STRING VARCHAR2(500);
v_TAB_Counter INTEGER;
BEGIN

BEGIN
SELECT COUNT(1) INTO v_TAB_Counter FROM user_objects WHERE Object_Type = 'TABLE' AND Object_Name = 'myADDRESS_EXT';
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_TAB_Counter:= 0;
END;

IF v_TAB_Counter > 0 THEN
EXECUTE IMMEDIATE 'DROP TABLE myADDRESS_EXT';
END IF;

v_CREATE_STRING:= 'CREATE TABLE ' || v_TABNAME ||' (
SRNO VARCHAR2(20),NAME VARCHAR2(20),SURNAME VARCHAR2(20),ADDRESS VARCHAR2(20),PIN VARCHAR2(20),PHONE VARCHAR2(20)
)';
v_CREATE_STRING := v_CREATE_STRING || ' ORGANIZATION EXTERNAL (DEFAULT DIRECTORY UPLOAD ACCESS PARAMETERS';
v_CREATE_STRING := v_CREATE_STRING || ' (RECORDS DELIMITED BY NEWLINE SKIP 1 BADFILE BAD_DIR:'''|| v_TABNAME ||'%a_%p.bad'' LOGFILE LOG_DIR:'''|| v_TABNAME ||'%a_%p.log'' FIELDS TERMINATED BY ''|'') LOCATION ('''|| v_TABNAME ||'.TXT''))';
v_CREATE_STRING := v_CREATE_STRING || ' REJECT LIMIT UNLIMITED';
DBMS_OUTPUT.PUT_LINE(v_CREATE_STRING);
EXCEPTION
WHEN others THEN
raise_application_error(-20000, 'Unknown Exception Raised: ' || SQLCODE || ' ' || sqlerrm);
END A_TEST_EXT_TABLE;



Please reply,

ukupatil

Mumbai, India


Re: External Table Error Trapping [message #282605 is a reply to message #282601] Thu, 22 November 2007 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Laughing
WHEN others THEN
raise_application_error(-20000, 'Unknown Exception Raised: ' || SQLCODE || ' ' || sqlerrm);

Really useless.

Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: External Table Error Trapping [message #282753 is a reply to message #282605] Fri, 23 November 2007 05:43 Go to previous message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

search for DBMS_ERRLOG or EXCEPTIONS table.

regards,
Previous Topic: Procedure to return resultset
Next Topic: Create several procedures
Goto Forum:
  


Current Time: Wed Dec 07 22:15:13 CST 2016

Total time taken to generate the page: 0.04959 seconds