-
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?
-
write a perl function to replace all & with \&.
Jeff Hunter
-
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???
-
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>
-
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.
Sonali
-
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>
-
I don't know if this will help:
execute pr_test_sign (100 , 'test'||'&'||'test.com');
-
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.
Sonali
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|