Toad World® Forums

Data generationa


#1

Hi there

Please, look the example of insert statement, generated by TOAD Generate
Data utility.

INSERT INTO “QUEST”.“OW_XX” (
“CUST_ID”, “NAME”, “ADDRESS”, “BALANCE”, “CREDIT_LMT”, “OPERATER”,
“DATPOSEGA”
) VALUES (
215494973, ‘gykUUOuwEytdmlocTAsOGVAGUeAiLsXwjVEKAVUQgbgnTnErlfuc’,
‘2842 NW Round Mountain Vlg’,
1264567046891,89, 4702781590167,41, ‘XLZCeCPxjhodKGBXTI’,
TO_DATE(‘19-FEB-2078’, ‘DD-MON-RRRR’)
);

My Ora setting is one that uses comma (,) as a decimal separator so,
the generated statement is totaly wrong.
( 4702781590167,41 should be 4702781590167.41 ) and of course I get a
“to many parameters” error message.

Any ideas ?? Is this the bug or whatever’? Also if I use Oracle random
number generator, the results is the same.

Regards!

Hi there

Please, look the example of insert statement, generated by TOAD Generate Data
utility.

INSERT INTO “QUEST”.“OW_XX” (
“CUST_ID”, “NAME”, “ADDRESS”, “BALANCE”, “CREDIT_LMT”, “OPERATER”,
“DATPOSEGA”
) VALUES (
215494973, ‘gykUUOuwEytdmlocTAsOGVAGUeAiLsXwjVEKAVUQgbgnTnErlfuc’,
‘2842 NW Round Mountain Vlg’,
1264567046891,89, 4702781590167,41, ‘XLZCeCPxjhodKGBXTI’,
TO_DATE(‘19-FEB-2078’, ‘DD-MON-RRRR’)
);

My Ora setting is one that uses comma (,) as a decimal separator so, the
generated statement is totaly wrong.
( 4702781590167,41 should be 4702781590167.41 ) and of course I get a “to many
parameters” error message.

Any ideas ?? Is this the bug or whatever’? Also if I use Oracle random number
generator, the results is the same.

Regards!

Attachment not added (content type not allowed): “acip.vcf”

Podpis.jpeg


#2

That looks like a bug – we’ll have to wait until John D. looks into
and replies ….

In the meantime, you might be able to enclose such numbers within quotes to
avoid the problem – but you’d have to mass edit for every statement
and occurrence L

Bert
Podpis.jpeg


#3

You mean John B.
Podpis.jpeg


#4

Afternoon,


My Ora setting is one that uses comma (,) as a decimal
separator so, the generated statement is totaly wrong.
( 4702781590167,41 should be 4702781590167.41 ) and of
course I get a “to many parameters” error message.

What do you have set for NLS_NUMERIC_CHARACTERS in the database in
question? It should be ,. (comma dot) I suspect for you if you use comma
as the decimal separator.

Also, does this match up to what you have in your regional settings on
WIndows?

Any ideas ?? Is this the bug or whatever’? Also if I use
Oracle random number generator, the results is the same.

Hmmm.

alter session set nls_numeric_characters=",.";
select dbms_random.value from dual;

   VALUE

,07970899283

Note the leading comma as decimal point?

alter session set nls_numeric_characters=".,";
select dbms_random.value from dual;

   VALUE

.09830083616

Note the leading dot as decimal point?

And now, being silly:

alter session set nls_numeric_characters=":%";
select dbms_random.value from dual;

   VALUE

:04193858276

So, I suspect that if you are getting the same problem with
dbms_random, then you are not using the correct setting for your
NLS_NUMERIC_CHARACTERS.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#5

Hi Bert,

That looks like a bug - we’ll have to wait until John D.
looks into and replies …

it might be a bug, but I’m not convinced because the OP is getting the
same problem with DBMS_RANDOM.VALUE (I presume .VALUE anyway), so it’s
not a Toad bug! It must be an incorrect NLS_NUMERIC_CHARACTERS setting.

At least, that’s what I think!

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#6

Hey Aci,

Norm has a very good point. Another thing you can check is your “Decimal
separator” and “Thousand separator” settings in your Toad
options. The basic insert statement you provided with your original post:

INSERT INTO “QUEST”.“OW_XX” (

“CUST_ID”, “NAME”, “ADDRESS”, “BALANCE”, “CREDIT_LMT”, “OPERATER”, “DATPOSEGA”

) VALUES (

215494973, ‘gykUUOuwEytdmlocTAsOGVAGUeAiLsXwjVEKAVUQgbgnTnErlfuc’, ‘2842 NW
Round Mountain Vlg’,

1264567046891,89, 4702781590167,41, ‘XLZCeCPxjhodKGBXTI’, TO_DATE(‘19-FEB-2078’,
‘DD-MON-RRRR’)

);

suggests you used the Internal random data engine when generating data. This
engine uses the standard Windows locale settings for numeric / floating point
values; however the standard Windows settings are overridden by the Decimal and
Thousand Separator settings in the Toad options. You can find these settings in
Toad Options à General à Numeric characters. The Internal data generation
engine will use these settings for floating point and currency values when
generating the script.

If you plan on using the internal generator, I would check these settings and
modify them as necessary to correctly handle currency and floating point values
on your machine.

I hope this information helps. Let us know if you continue to run into issues
using this feature!

-John


#7

Hi John

I have checked all NLS and other settings and I always use comma as a
decimal separator as
it is standard in my region.
Your suggested workaround works fine if I change decimal separator to
dot in my TOAD setting .
I am now waiting for my customer responses because I thing that changing
TOAD setting will also
have an impact on reports and SQL scripts.

Thank you and best regards!

John Bowman wrote:

Hey Aci,

Norm has a very good point. Another thing you can check is your
“Decimal separator” and “Thousand separator” settings in your Toad
options. The basic insert statement you provided with your original post:

INSERT INTO “QUEST”.“OW_XX” (

“CUST_ID”, “NAME”, “ADDRESS”, “BALANCE”, “CREDIT_LMT”,
“OPERATER”, “DATPOSEGA”

) VALUES (

215494973,
‘gykUUOuwEytdmlocTAsOGVAGUeAiLsXwjVEKAVUQgbgnTnErlfuc’, ‘2842 NW Round
Mountain Vlg’,

1264567046891,89, 4702781590167,41, ‘XLZCeCPxjhodKGBXTI’,
TO_DATE(‘19-FEB-2078’, ‘DD-MON-RRRR’)

);

suggests you used the Internal random data engine when generating
data. This engine uses the standard Windows locale settings for
numeric / floating point values; however the standard Windows settings
are overridden by the Decimal and Thousand Separator settings in the
Toad options. You can find these settings in Toad Options à General à
Numeric characters. The Internal data generation engine will use
these settings for floating point and currency values when generating
the script.

If you plan on using the internal generator, I would check these
settings and modify them as necessary to correctly handle currency and
floating point values on your machine.

I hope this information helps. Let us know if you continue to run
into issues using this feature!

-John

Hi John

I have checked all NLS and other settings and I always use comma as a decimal
separator as
it is standard in my region.
Your suggested workaround works fine if I change decimal separator to dot in my
TOAD setting .
I am now waiting for my customer responses because I thing that changing TOAD
setting will also
have an impact on reports and SQL scripts.

Thank you and best regards!

John Bowman wrote:

Hey Aci,

Norm has a very good point. Another thing you can check is your
“Decimal separator” and “Thousand separator”
settings in your Toad options. The basic insert statement you provided with
your original post:

INSERT INTO “QUEST”.“OW_XX” (

"CUST_ID", "NAME", "ADDRESS", "BALANCE", "CREDIT_LMT", "OPERATER",
"DATPOSEGA"

) VALUES (

215494973, 'gykUUOuwEytdmlocTA sOGVAGUeAiLsXwjV EKAVUQgbgnTnErlf uc', '2842
NW Round Mountain Vlg',

1264567046891, 89, 4702781590167, 41, 'XLZCeCPxjhodKGBXTI ', TO_DATE('19-
FEB-2078' , 'DD-MON-RRRR' )

);

suggests you used the Internal random data engine when generating data. This
engine uses the standard Windows locale settings for numeric / floating
point values; however the standard Windows settings are overridden by the
Decimal and Thousand Separator settings in the Toad options. You can find
these settings in Toad Options à General à Numeric characters. The
Internal data generation engine will use these settings for floating point
and currency values when generating the script.

If you plan on using the internal generator, I would check these settings
and modify them as necessary to correctly handle currency and floating point
values on your machine.

I hope this information helps. Let us know if you continue to run into
issues using this feature!

-John

Attachment not added (content type not allowed): “acip.vcf”
Podpis.jpeg


#8

Morning Aci,

I have checked all NLS and other settings and I always use
comma as a decimal separator as it is standard in my region.
Your suggested workaround works fine if I change decimal
separator to dot in my TOAD setting .
I am now waiting for my customer responses because I thing
that changing TOAD setting will also have an impact on reports and
SQL scripts.
I’m not convinced, but I may be misunderstanding you.

When you say that changing your Toad session’s defaults will affect
reports and scripts, do you mean those that are run by other people?

The internal storage of numbers in Oracle doesn’t actually store the
decimal point or the commas, but the number is stored in Oracle’s own
internal representation for floating point numbers. The decimal point is
implied within the stored data.

It is only displyed - using comma or dot or whatver you have set in
NLS_NUMERIC_CHARACTERS - when you SELECT the data. So your reporst and
other scripts should be ok.

Cheers,
Norm. [TeamT]

Thank you and best regards!

John Bowman wrote:

Hey Aci,

Norm has a very good point. Another thing you can
check is your “Decimal separator” and “Thousand separator”
settings in your Toad options. The basic insert statement
you provided with your original post:

INSERT INTO “QUEST”.“OW_XX” (

“CUST_ID”, “NAME”, “ADDRESS”, “BALANCE”,
“CREDIT_LMT”, “OPERATER”, “DATPOSEGA”

) VALUES (

215494973,
‘gykUUOuwEytdmlocTAsOGVAGUeAiLsXwjVEKAVUQgbgnTnErlfuc’,
‘2842 NW Round Mountain Vlg’,

1264567046891,89, 4702781590167,41,
‘XLZCeCPxjhodKGBXTI’, TO_DATE(‘19-FEB-2078’, ‘DD-MON-RRRR’)

);

suggests you used the Internal random data engine when
generating data. This engine uses the standard Windows
locale settings for numeric / floating point values; however
the standard Windows settings are overridden by the Decimal
and Thousand Separator settings in the Toad options. You
can find these settings in Toad Options --> General -->
Numeric characters. The Internal data generation engine
will use these settings for floating point and currency
values when generating the script.

If you plan on using the internal generator, I would
check these settings and modify them as necessary to
correctly handle currency and floating point values on your machine.

I hope this information helps. Let us know if you
continue to run into issues using this feature!

-John