insert string(special characters) from unix to oracle, base64?

[Billa-User]

hello,

i have following issue:

i want to insert a string(special characters) from a shell script into oracle. when a string contains character like ', then i have problems with the insert. for example i want to insert string << a'b'c&d" >>.

so i got a information to use BASE64 to encode/decode the string because then the BASE64 character set string contains no special characters.

it isn't easy to prepare ("devalue") all characters in unix for the insert in oracle.

is it the right solution for this ?
have anybody experience with this issue ?

example when you get an error:

insert into test_base64 (TEXT)
values ( 'a'b'c&d"' );

SQL> SQL> 2 3 Enter value for d: old 3: ( 'a'b'c&d"' )
new 3: ( 'a'b'c"' )
( 'a'b'c"' )
*
ERROR at line 3:
ORA-00917: missing comma


so i create follwing shell script:

- encode BASE64 with perl
- decode BASE64 with oracle procedure

read TEXT

export TEXT

perl -MMIME::Base64 -e 'print encode_base64 $ENV{'TEXT'};' | read BASE64TEXT

echo "${BASE64TEXT}"

sqlplus scott/@ORACLE_SID <
-- test table information:
-- create table test_base64
-- (text varchar2(1000) );
select '${TEXT}' from dual; <- you get an error

select utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw('${BASE64TEXT}'))) from dual;

insert into test_base64 (TEXT)
values
( '${TEXT}' ); <- you get an error

insert into test_base64 (TEXT)
values
( utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw('${BASE64TEXT}')))
)
; <- you OK


commit;
EOF

regards,tom

[OldSchool]

which parts of the examples, if any, are you running out of a shell script?

1. check the oracle sql reference to see what oracle uses for an "escape character",

2. you may need surround the "special characters" with escape'd double quotes?

I don't think this is a "unix" issue per-se, but I could be wrong.

[Michael Mike Reaser]

OldSchool> I don't think this is a "unix" issue per-se, but I could be wrong.

I spent over 13 years working at HP's Response Center in Atlanta (is it still called the Response Center, and is it still in Atlanta?) on the team responsible for all of the language and database products provided and/or supported by HP. At least thru early 1998, Oracle constantly maintained that their software had No Problems At All, and if a customer was having troubles using it on a specific OS then the "troubles" were due to that OS.

[OldSchool]

Mike-- most vendors still say that.

my point is that if has problems with:
"example when you get an error:

insert into test_base64 (TEXT)
values ( 'a'b'c&d"' );

SQL> SQL> 2 3 Enter value for d: old 3: ( 'a'b'c&d"' )
new 3: ( 'a'b'c"' )
( 'a'b'c"' )
*
ERROR at line 3:
ORA-00917: missing comma"

then he might have issues w/ escaping characters at the oracle level. if its in a script / here-doc, then he's probably gonna have to play w/ various combinations of qoutes, double qoutes and so on to get the job done....depends on who is stripping what and when...

I once worked w/ a DB script that required something like 4 or 5 consecutive "\" chars as it wrote a file out of a here-doc, and the file was then used as input to ftp job (or some such) so the initial shell stripped one, then the read stripped one, then ftp got some and so on....

it can be a bear to figure out who did what (and what they want to see).

it would help if the example were a little clearer as to what is expected to go into the DB and what was entered....ie
input: 'a'b'c"'
expected entry in db: a'b'c"

[Hein van den Heuvel]

I don't think you want to go all the way to base64, but it is not a bad idea.

The alternative is a script section to 'quote' or special case the 'funny' characters in the string

Replace each single quote (') with two single quotes ('').

Put each ampersand (&) at the end of a string, and append the rest (yuck).

For example:

select 'a''b''c&'||'d"' "Quotes" from dual;
Quotes
--------
a'b'c&d"

Or... tell the SQL client to stop interpreting.

set define off;
select 'a''b''c&d"' "Quotes" from dual;
Quotes
--------
a'b'c&d"


rtfm! Google!

Hein.

[Billa-User]

hello,

@it would help if the example were a little clearer as to what is expected to go into the DB and what was entered....
ie input: 'a'b'c"'
expected entry in db: a'b'c"

i have to insert a string (it can include special character's) , which is entered in unix, into a oracle table.

we have a in house application and a user enters a string . then the string should insert with another programm into the oracle table.

example / work flow:

1. main-script:
--------------------------
# enter string
read string

# start insert script
./insert-script "${string}"
--------------------------

2. insert script :
--------------------------
ins_string="${1}"
sqlplus -s "connect" @ insert-sql.sql "${ins_string}"

3. insert sql script :
--------------------------
def ins_string='&1.'

xxxxxxxxxxxxxxxxx
with base64 it easy to pass parameter (string) from one shell script to another and pass parameter to an sql script.

regards,tom