In the latest version of TOAD, when I try to compile that procedure:
CREATE OR REPLACE PROCEDURE BROUILLE.admw009lb IS
t_titi varchar2(1) default ‘é’;
BEGIN
null;
END;
/
I get that error:
[Error] Syntax check (2: 8): ERROR line 2, col 8, ending_line 2,
ending_col 18: The string initializer has a value which is too long
It does that when I use a character with an accent. If I change the ‘é’
for an ‘e’, it works.
It works well in TOAD 10.1. We speak french here so accents are important.
–
Louis Brouillette
Chargé de projet informatique et technologique (DBA)
Université du Québec à Trois-Rivières
Tel: (819) 376-5011 ext. 2435
Email: Louis.Brouillette@uqtr.ca
You probably have BYTE SEMANTICS as the default instead of CHAR SEMANTICS and are on a UTF8 database. In UTF8 all accents take up 2 bytes. The first 128 code points are the same but anything above 128 uses the UTF8 code points which means that you need to specify character semantics.
Change it to
CREATE OR REPLACE PROCEDURE BROUILLE.admw009lb IS
t_titi varchar2(1 CHAR) default ‘é’;
BEGIN
null;
END;
/
If you have CHAR SEMANTICS then everything will work. However the limit to a table VARCHAR2 is still 4,000 bytes. Which means that if you have a variable defined as varchar2(2000) then you are OK because even if every character is an accent it will only be 4000 bytes.
Are you in a UTF8 database? Did you see my reply regarding BYTE vs CHAR
semantics? What you see is exactly what is expected in a UTF8 database. From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Louis
BROUILLETTE Sent: May 11, 2010 10:45 AM To: toad@yahoogroups.com Cc: Jeff Smith
Subject: Re: [toad] Problem in sql editor in 10.5.0.41 Thanks Jeff for the F5
workaround. It works event if I still see the error. About the change of length,
I don’t want to do it because the length is a validation. Will this be corrected
in the next version ? Le 2010-05-11 10:06, Jeff Smith a écrit : I guess the
better workaround would be to do your ‘compiles’ via Toad’s
Execute via SQL*Plus F5 dropdown…
The database character set is WE8ISO8859P1 and I’m sure it’s a TOAD bug
(introduced in the latest version) because it works well in TOAD 10.1.
Thanks for your help.
Le 2010-05-11 10:22, Erwin Rollauer a écrit :
You probably have BYTE SEMANTICS as the default instead of CHAR SEMANTICS
and are on a UTF8 database. In UTF8 all accents take up 2 bytes. The first
128 code points are the same but anything above 128 uses the UTF8 code
points which means that you need to specify character semantics.
Change it to
CREATE OR REPLACE PROCEDURE BROUILLE.admw009lb IS
t_titi varchar2(1 CHAR) default 'é';
BEGIN
null;
END;
/
If you have CHAR SEMANTICS then everything will work. However the limit to a
table VARCHAR2 is still 4,000 bytes. Which means that if you have a variable
defined as varchar2(2000) then you are OK because even if every character is
an accent it will only be 4000 bytes.
Does anyone have any suggestion for what might be happening here?
I have a control file that works fine if I use the Toad for Oracle import wizard
and point it to the control file. If I call the self same control file from the
sqlldr command line utility, it fails. The control file is
I’m wondering if you switched to the same directory as the files are in before
you launched SQLLDR if it would work…or do as Chris mentions and supply the
full path to the datafile
If your data is in a separate file then that file should be after INFILE
''
You only use the BEGINDATA keyword if your data is included inline in
the control file. In that case then the syntax for infile is INFILE *
What you are showing is INFILE * then a BEGINDATA with a file name
V17_standard_test.xml which I don't think is correct syntax.
I'm not sure why the Toad sql loader import wizard works with that
control file.
Ed
[TeamT]
LOAD DATA
CHARACTERSET UTF8
INFILE 'V17_standard_test.xml'
INTO TABLE IMPORTRAWXML TRUNCATE
(
SITEID constant 0
,VENDORID constant 17
,SITEFORMATID constant 2
,V17_standard_test.xml filler char(1000)
,RAWDATA LOBFILE (V17_standard_test.xml) TERMINATED BY EOF
)
On 5/11/2010 2:16 PM, Eva wrote:
Does anyone have any suggestion for what might be happening here?
I have a control file that works fine if I use the Toad for Oracle
import wizard and point it to the control file. If I call the self same
control file from the sqlldr command line utility, it fails. The control
file is
All the changes I am making have altered the control file to bring in the data
in the xml file. That is not actually what I am trying to do. The BEGINDATA line
is there to make it clear the entire file needs to be imported into the RAWDATA
column. The control file is not extracting data from v17… it is loading the
entire file into the column. If I remove that line I get errors indicating sql
loader is trying to read the file contents.
Is there anyway to determine if I am using one version of sql loader from the
command line and another from Toad?
I was able to determine that both Toad and command line are using the same
version. I realized the sql loader version is in the error message I was
sending. So I still have no clue why I get such different results from the two
methods of running the files. If I could determine what the difference is I
figure I might be able to figure out how to make it work in the command line
method!
