& Character

[rpardip]

Is there any way of inserting data into a table without using
SET ESCAPE \ and without placing \ before "&" character ?

The problem is; I am doing inserts into a table from a PERL program. There will be lots of data containing "&" characters.
How do I handle this?

[marist89]

write a perl function to replace all & with \&.

[rpardip]

Thanks for your response!

But, Do we still have to use SET ESCAPE ON apart from writing a PERL function????

Is there any other method other than this???

[pwoneill]

use "set define off"

<font face="courier">
SQL> create table test (col1 varchar2(30));

Table created.

SQL> insert into test values ('&testing');
Enter value for testing:
old 1: insert into test values ('&testing')
new 1: insert into test values ('')

1 row created.

SQL> set define off
SQL> insert into test values ('&testing');

1 row created.

SQL> select * from test;

COL1
------------------------------

&testing

SQL>
</font>

[sonaliak]

Is there any way to do this in PL/Sql ??
eg:
CREATE OR REPLACE PROCEDURE pr_test_sign
(inRESID number, inLocation varchar2)
IS

Begin
set define off;
insert into a4branchtemp2 (TEMP_WORK_ID, TEMP_WORK_ITEM_CODE)
values(inRESID, inLocation);

set define on;
COMMIT;
End pr_test_sign;
/

I know I cannot have DDL inside PLSQL block.. this is just an eg of what I want to do.
execute pr_test_sign(100, 'test@test.com');

What I want to do is insert the @ sign from the procedure into a table.

Note: Our application will use ODBC and I have their latest version for Oracle8.

[pwoneill]

I don't think you have to worry about the @ sign. In any case the "set var value" commands are only for sqlplus, not for SQL or PL/SQL.

If you're using the execute command, you're probably doing it from sqlplus, in which case if you had to worry about it like you did an &, you could just type set define off before you type execute, or put it in the script ahead of the execute commands.

however the @ shouldn't cause any problems.

this is without changing any sqlplus variables
<font face="courier">
SQL> insert into test values ('whatever@somewhere.org');

1 row created.

SQL> select * from test;

COL1
------------------------------

&testing
whatever@somewhere.org

SQL>
</font>

[mber]

I don't know if this will help:

execute pr_test_sign (100 , 'test'||'&'||'test.com');

[sonaliak]

I should have been more clear on this ..
Yes, it does work with worksheet or Sql* plus.
But not through ODBC for some reason.
If you use ODBC 32bit test, Oracle 8 and ODBC driver 8.00.57 or any other may be even 8.00.62.
It doesn't work.