I'll try to explain it in details:
I have a table in Oracle 8i composed of only three columns:
COL1 - number(10), COL2 - varchar2(3600), COL3 - long
In my app, I have to read a text file and insert the data in the table
described above.
The insert command (through a TQuery.ExecSQL) is inside a try...except
and at the except clause (where I capture a eDatabaseError expection) I
save the error message in a log file. After this app is run I take a
look at the log file and I find out that from the +180000 lines
included, about 200 lines couldn't be inserted because of the 'Missing
right quote' exception (I save in the log the INSERT command that should
be executed from the TQuery var and I confirm that all the queries have
all the necessary quotes, none is missing), and only one couldn't be
inserted because of the 'String literal is too long' Oracle error, but
it's not the case.
My question is: is there any limitation about the size of the
TQuery.SQL.Text property? There are some with the size of 500, 600, 700
bytes, but this size is normal AFAIK and some lines which are bigger
than these were inserted without any problem.
Maybe is there any character which can't be inserted? The single quote
(') I changed for the acute (´) to avoid some problems, and the (:) I
doubled so Delphi won't take it as a parameter.
My setup is: Delphi IDE 5, Oracle 8i and Win98.
If some information is missing, please inform me.
TIA,
Marcos Rogério Cardoso
John Pierce
I'm still looking for an answer,
Marcos Rogério Cardoso
John Pierce
If you have any more questions, keep asking,
Marcos Rogério Cardoso
Marcos Rogério Cardoso wrote:
> Phew! Lotsa questions, huh? ;^)
> Let's try to answer it:
> The 'Missing right quote' message is being returned from the BDE.
> The RequestLive property is False (default) because I don't set it when I
> create the TQuery var.
We want to prevent the BDE from massaging the Sql statements, so set RequestLive
:= True, which will pass the SQL directly to Oracle. At that point, it will
either be accepted, or we'll get an Oracle error message, which should be more
informative.
>
> The SQL Passthrough mode at the BDE is set to 'SHARED AUTOCOMMIT'.
> The BDE version is 5.00 (you didn't ask but I guess this information might
> be necessary).
> I don't know SQL Monitor.
Sql Monitor comes with the Enterprise version. How are you connecting to
Oracle? If it's via ODBC, then we may hit a brick wall on this one.
>
> If I keep on trying running only one of those rejected INSERT statements
> through Delphi it keeps returning the same message, but if I try to run it
> through SQL*Plus it truncates the statement because it's too long so it
> doesn't work there too.
Try in SQL worksheet. There's no length limit there.
>
> Unfortunately I couldn't find a way to make it work through SQL*Loader,
> because the first column of the table is a sequencer, the second column is
> extracted from the 26th position to the first ocurrence of the '#' character
> in the line of the text file read (it has a variable length), and the third
> column is from the first occurrence of the '#' character to the end of the
> line (variable length here too).
I couldn't follow that, but I'll take your word for it. You may end up having
to massage the file bit so that you can use Sql*LOader. (See remark on ODBC
above).
>
>
> If you have any more questions, keep asking,
> Marcos Rogério Cardoso
That's about it. See what happens.
John Pierce
See the missing right quote?
Another problem could be an embedded quote char:
insert into table1 (Field1,Field2)
values ('Something','something not so long, let's say 1k text');
Quoted text is 'something not so long, let'.
--
----------------------
Regards
Robert Cerny
Remove both qwe when replying
email: robert.q...@neosys.xrs.qwe.si
No questions via email, unless explicitly invited.
Marcos Rogério Cardoso wrote in message <3975A963...@furb.rct-sc.br>...
>Phew! Lotsa questions, huh? ;^)
>Let's try to answer it:
>The 'Missing right quote' message is being returned from the BDE.
>The RequestLive property is False (default) because I don't set it when I
>create the TQuery var.
>The SQL Passthrough mode at the BDE is set to 'SHARED AUTOCOMMIT'.
>The BDE version is 5.00 (you didn't ask but I guess this information might
>be necessary).
>I don't know SQL Monitor.
>If I keep on trying running only one of those rejected INSERT statements
>through Delphi it keeps returning the same message, but if I try to run it
>through SQL*Plus it truncates the statement because it's too long so it
>doesn't work there too.
>Unfortunately I couldn't find a way to make it work through SQL*Loader,
>because the first column of the table is a sequencer, the second column is
>extracted from the 26th position to the first ocurrence of the '#'
character
>in the line of the text file read (it has a variable length), and the third
>column is from the first occurrence of the '#' character to the end of the
>line (variable length here too).
>
>
Thank you very much for your help, John, it really helped me a lot,
Marcos Rogério Cardoso
It looks like you have already solved your problem, but here is another
solution. Try setting the ParamCheck property on the TQuery to False. I
had the same problem with MS SQL 7. I picked this up from a post back in
October 1999. It worked for me.
Al
"Marcos Rogério Cardoso" <mcar...@furb.rct-sc.br> wrote in message
news:3975F8C3...@furb.rct-sc.br...