"Chris" <cs123._no_spam__at_telstra.com> wrote in message news:<kXBDb.54895$aT.20485_at_news-server.bigpond.net.au>...
> "Arif" <khanbkup_at_yahoo.co.uk> wrote in message
> news:651adcd8.0312160314.38c54c8a_at_posting.google.com...
> > We are updating our database using flat files. SQL Loader loads
> > records in temp table from a flat file. Our shell script then calls a
> > stored procedure that reads the records in the temp table, validates
> > them and then updates another table with the data.
> > We encountered a peculiar problem in our production environment, where
> > a file was processed correctly upto storing in the temp table stage.
> > Then the log shows 'ORA-01756: quoted string not properly terminated'
> > error. After that all the changes got rolled back.
> > We processed the same file on our test environment which is an exact
> > replica of the production environment and the file was processed
> > successfully without any errors.
> > Any idea as to what must have caused the error.
>
> You may need to post your control file and the line of data that was
> rejected
Well found the error. The process was creating a temporary file with
the name as 'unix process id'.'db generated request id' , in /tmp. The
unix shell script would then list the /tmp to get the file and read
the 'request_id' to pass to the stored procedure. Problem occurs when
more than 1 files are returned by by the command "request_id = 'ls
*.$process_id | cut -f1 -d.'. The $request_id variable is now an array
and when this array is passed to the stored procedure, ORA 1756
occurs.
Received on Thu Dec 18 2003 - 02:34:22 CST
