Clob insert problem on a Oracle db with DBI

I have a problem inserting a line of text into a CLOB data field in
Oracle. The version of PERL is 5.8 running on a AIX server 5.2. It
looks like when I'm doing the insert, it is looking at the text to
execute. The text is a sql statement that I'm trying to load.

Any help would be greatly appreciated.

Thanks.

Ron

Below is my insert statement and the error ORACLE error that I'm
getting. =20

PERL statement to do the insert:



$sqlexec =3D qq{INSERT INTO ua_ps_sql_text_tble (sql_id,
sql_type, market, sql_text)
values ('$prog', '$ext', '$space_out',
'$sql_line')};
$sth=3D$dbh->prepare($sqlexec);=20
$sth->execute;


On the 5th line down about 2/3 in is the <*> where the possble error
occurs:



DBD::Oracle::st execute failed: ORA-00917: missing comma (DBD ERROR:
error possibly near <*> indicator at char 367 in 'INSERT INTO
ua_ps_sql_text_tble (sql_id, sql_type, market, sql_text)
values ('uaad2dar', 'sqr', ' ', 'begin-SELECT
distinct PROG.EMPLID NID.NATIONAL_ID prog.admit_term from PS_ACAD_PROG
PROG, PS_ACAD_PLAN PLAN, PS_PERS_NID NID where PROG.EMPLID \=3D
NID.EMPLID AND SYSDATE between (prog.action_dt - 5) and (prog.action_dt
+ 5) AND PROG.ACAD_CAREER \=3D \'<*>UGRD\' AND PROG.INSTITUTION \=3D
\'ALBNY\' AND PROG.PROG_STATUS \=3D \'AC\' AND PROG.acad_prog not in
(\'UNIHS\',\'GENS2\',\'GENST\',\'GENCO\',\'UIELP\',\'VISIT\' ,\'SANON\',\
'SASUN\',\'CROSS\',\'HSVIS\') AND PROG.EFFDT \=3D (select max(EFFDT) =
from
PS_ACAD_PROG PROG1 where PROG1.EMPLID \=3D PROG.EMPLID AND
PROG1.ACAD_CAREER \=3D PROG.ACAD_CAREER AND PROG1.STDNT_CAR_NBR \=3D
PROG.STDNT_CAR_NBR AND PROG1.PROG_STATUS \=3D \'AC\' AND PROG1.acad_prog
not in
(\'UNIHS\',\'GENS2\',\'GENST\',\'GENCO\',\'UIELP\',\'VISIT\' ,\'SANON\',\
'SASUN\',\'CROSS\',\'HSVIS\') AND PROG1.EFFDT <\=3D sysdate) AND
PROG.EFFSEQ \=3D (select max(EFFSEQ) from PS_ACAD_PROG PROG2 wh

*********************************************************
Ron Cetnar
=20
Supervising Programmer/Analyst/Oracle DBA
University applications Development (UAD)
=20
Building: Management Service Center - 100=20
Room 110
1400 Washington Ave
Albany, NY 12222
Work Phone: 518 437-4535
Fax Number: 518 437-4540
Email Address: rcetnar@uamail.albany.edu
=20
**********************************************************
=20

RE: Clob insert problem on a Oracle db with DBI

Ron S Cetnar wrote:
> I have a problem inserting a line of text into a CLOB data field in
> Oracle. The version of PERL is 5.8 running on a AIX server 5.2. It
> looks like when I'm doing the insert, it is looking at the text to
> execute. The text is a sql statement that I'm trying to load.
>=20
> Any help would be greatly appreciated.
>=20
> Thanks.
>=20
> Ron
>=20
> Below is my insert statement and the error ORACLE error that I'm
> getting.
>=20
> PERL statement to do the insert:
>=20
> $sqlexec =3D qq{INSERT INTO ua_ps_sql_text_tble (sql_id,
> sql_type, market, sql_text)
> values ('$prog', '$ext', '$space_out',
> '$sql_line')};
> $sth=3D$dbh->prepare($sqlexec);
> $sth->execute;

Use bind parameters instead, to avoid quoting problems:

$sqlexec =3D qq{INSERT INTO ua_ps_sql_text_tble
(sql_id,sql_type,market,sql_text
VALUES
(?,?,?,?)};
$dbh->do($sqlexec, undef, $prog, $ext, $space_out, $sql_line);

Regards,
Philip

RE: Clob insert problem on a Oracle db with DBI

Ron,

I suggest you:

1. Use bind variables for your insert statement. It might be that some
character in your variables which is causing the problem.
2. Look at the PL/SQL block that is part of the error message. The
statement fragment "PROG.EMPLID NID.NATIONAL_ID prog.admit_term" looks
like it is missing commas.

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: Ron S Cetnar [mailto:RCetnar@uamail.albany.edu]=20
Sent: Tuesday, January 30, 2007 1:10 PM
To: dbi-users@perl.org
Subject: Clob insert problem on a Oracle db with DBI

I have a problem inserting a line of text into a CLOB data field in
Oracle. The version of PERL is 5.8 running on a AIX server 5.2. It
looks like when I'm doing the insert, it is looking at the text to
execute. The text is a sql statement that I'm trying to load.

Any help would be greatly appreciated.

Thanks.

Ron

Below is my insert statement and the error ORACLE error that I'm
getting. =20

PERL statement to do the insert:



$sqlexec =3D qq{INSERT INTO ua_ps_sql_text_tble (sql_id,
sql_type, market, sql_text)
values ('$prog', '$ext', '$space_out',
'$sql_line')};
$sth=3D$dbh->prepare($sqlexec);=20
$sth->execute;


On the 5th line down about 2/3 in is the <*> where the possble error
occurs:



DBD::Oracle::st execute failed: ORA-00917: missing comma (DBD ERROR:
error possibly near <*> indicator at char 367 in 'INSERT INTO
ua_ps_sql_text_tble (sql_id, sql_type, market, sql_text)
values ('uaad2dar', 'sqr', ' ', 'begin-SELECT
distinct PROG.EMPLID NID.NATIONAL_ID prog.admit_term from PS_ACAD_PROG
PROG, PS_ACAD_PLAN PLAN, PS_PERS_NID NID where PROG.EMPLID \=3D
NID.EMPLID AND SYSDATE between (prog.action_dt - 5) and (prog.action_dt
+ 5) AND PROG.ACAD_CAREER \=3D \'<*>UGRD\' AND PROG.INSTITUTION \=3D
\'ALBNY\' AND PROG.PROG_STATUS \=3D \'AC\' AND PROG.acad_prog not in
(\'UNIHS\',\'GENS2\',\'GENST\',\'GENCO\',\'UIELP\',\'VISIT\' ,\'SANON\',\
'SASUN\',\'CROSS\',\'HSVIS\') AND PROG.EFFDT \=3D (select max(EFFDT) =
from
PS_ACAD_PROG PROG1 where PROG1.EMPLID \=3D PROG.EMPLID AND
PROG1.ACAD_CAREER \=3D PROG.ACAD_CAREER AND PROG1.STDNT_CAR_NBR \=3D
PROG.STDNT_CAR_NBR AND PROG1.PROG_STATUS \=3D \'AC\' AND PROG1.acad_prog
not in
(\'UNIHS\',\'GENS2\',\'GENST\',\'GENCO\',\'UIELP\',\'VISIT\' ,\'SANON\',\
'SASUN\',\'CROSS\',\'HSVIS\') AND PROG1.EFFDT <\=3D sysdate) AND
PROG.EFFSEQ \=3D (select max(EFFSEQ) from PS_ACAD_PROG PROG2 wh

*********************************************************
Ron Cetnar
=20
Supervising Programmer/Analyst/Oracle DBA
University applications Development (UAD)
=20
Building: Management Service Center - 100=20
Room 110
1400 Washington Ave
Albany, NY 12222
Work Phone: 518 437-4535
Fax Number: 518 437-4540
Email Address: rcetnar@uamail.albany.edu
=20
**********************************************************
=20

This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.