Interview Question on Data loading in Oracle
I attened one interview and I came across some questions which still I do not have answer .
Interview put condition before stating the interview.
I need to load various file to Oracle DB
I have only access Oracle box and can write anonymus block and select query .
No unix or OS access. client file size is unknow ( even I could not view the client file size in unix box)
Question
How you load data in oracle , without tool( here tool means Oracle utility lik sql loader , db pump , external table as well as
third party utility )
My answer was we can user UTL_FILE
Interview replied it is one of the method . ( I am not sure about other methods)
Next question --> in same data loading process , data is loading thru utl_file and committed on a loop ( say commited after for every 500 record )
and suppose if error has occurred ( no excedption handler is availbale in the code ) .
Interviewer asked me
How you find out how many records remain to load .
(See I do no't know the input file size and only availabe information is no of commit made by program.)
My answer
we can not say the how many percent is remaing
.How may reords oracle try to load we can find out but if input file size is unknown then we can not find out how may records are remain
Next question --> In a table one record got deleted or updated . Customer informed after 15 days How I find out the record which updated . Table has no trigger
no audit trail is availbale , No flash back query is possible
As per my knowlwdge we lost the data we can not say