Toad cut/paste converts newlines. SQL statements fail.

Toad for Oracle & DevOps Toolkit
1

Select a field that contains the 13 10 newline format. Access the field with Toad’s Popup Editor; ctl A, then paste it in the sql tab as

select * from table where text = ‘something
with a 13 10 newline’;

It will not find it.

Do the same with a text field containing the 10 newline format. It’s found.

I suppose this is all old news… surprised us at $1000+ a pop. There must be a setting somewhere. Anyone?

Thanks
Toad 10.1

2

There is an option, Oracle > General

‘Newline format for character data’

            ‘Windows sytle (CR/LF)’

            ‘Unix style (LF)’

Jeff

3

exactly how are you writing the query to get the 13 10 in your where clause?
Don’t just hit enter in the editor to get the 13 10. Do it like this:

Select * from table

where text e = ‘something with a ’ || CHR(13) || CHR(10) || ’ newline’

Doing it this way will make Toad setting irrelevant.

4

[ Attachment(s) from Eva included below]

I have a bar delimited text file that imports beautifully if I use the Toad data
import table wizard. I need to automate this though, so I really need it to load
nicely via SQLLoader. No matter what I do to my control file it fails. I was
wondering if anyone could tell me what the import wizard does so I can try to
make SQLLoader do that or automate that wizard process? I have attached the
control file so you can see what I have been working with. The char details were
added because I have some very large strings coming in and they were not being
accepted despite the field size being set correctly.

Any ideas would help!

Thanks, Eva

Attachment(s) from Eva

1 of 1 File(s)

otherformat.ctl

5

Hi Eva,

The import wizard and sql loader are two completly different animals.

We need more information than “it fails”. There are lots of ways to fail. Can
you give the error message?

One thing you can do to get an idea of the format that SQL Loader is looking for

  • (assuming there is already some data in your table) - go to the Schema
    Browser, right-click on your table, choose “Export Data”, then in the dialog
    that appears, set the export format to “SQL Loader”. If your table is large,
    then instead of going to the schema browser, go to the editor and run a query
    like "select * from table where rownum

-John

6

Thanks for the quick reply!

There are actually no error messages and SQL*Loader appears to run perfectly. No
rows are rejected, no errors are reported in the log, but every single field is
blank. There is a char value in every field for every record. It is just blank.
Again, the import wizard brings it all in beautifully.

I tried your suggestion of exporting it for SQL*Loader and got the exact same
file I have attached here with the char indications removed. Any other ideas?

7

Hey Eva;

I’ve had great luck telling SQL Loader the hex value for the separator
character. By bar, I conclude you mean “|”. In SQL Plus, you can
find the hex value by:

SELECT rawtohex(‘|’) from dual;

That gives 7c as the hex value.

In your control file (or control section) you would have:

…

INTO TABLE …

FIELDS TERMINATED BY X’7c’
quotes, those are supposed regular single quotes :wink:

I’m sure if your control file has the above, you’ll find it works
just fine.

Roger S.

8

I am doing some text manipulation in XML functions. I need to do a replace
function that looks like this: replace(mh.MEDNAME,’&’,’&’) but Toad keeps
seeing everything that begins with the & symbol as a variable. Is there anyway
to get around this so I can run this replacement function in debug?

Thanks,
Eva

9

On 14/10/10 13:31, Eva wrote:

.... Is there anyway to get around this so I can run this
replacement function in debug?

Set Define Off?

or

In Toad's Options,

Execute/Compile -> Prompt For Substitution variables. Uncheck it.

Cheers,
Norm. [TeamT]

10

One last way - toad options -> search for login or glogin (these are the sql plus standard user defined startup scripts) and add set define off in one of these files - now this behavior will be the default for both toad and sql plus :slight_smile:

11

I had a similar problem on a previous project. Setting define off works okay, but only for the current session. The big problem was having to tell the DBAs to do that each time they went to compile the package. My solution, I’m not saying it’s a good one, just that it resolved the problem, was to change the code to use a chr(38) function in place of the ‘&’ so that sql*plus doesn’t see it.

You could try something like replace(column, chr(38), chr(38)||’ ')

12

Interesting idea, but that makes it permanent in all of your sessions, meaning you then can’t run a script where you do intend to be prompted without ‘set define on’ to reverse it, and you still have the problem of sending it to someone else (a DBA perhaps) who doesn’t have that in their startup.

My method of changing the code to use chr(38)'s in place of physical &'s in the code makes for slightly un-pretty code, but nobody gets incorrectly prompted no matter how their settings are.