RE: Help for insert query using DBD oracle

[Michael Nhan]

Hi,

What version of the client are you using and what version of DBD::Oracle 
are you using?  What is the version of your oracle rac db? I've no problem 
inserting your text string into a simple table with a clob field in my 2 
node rac instance.

Michael

On Wed, 3 Feb 2010, Lopez Mariz, Luis Fernando  wrote:

Date: Wed, 3 Feb 2010 09:53:55 -0600
From: "Lopez Mariz, Luis Fernando " <luis.fernando.lopezma...@citi.com>
To: "'dbi-users@perl.org'" <dbi-users@perl.org>
Subject: RE: Help for insert query using DBD oracle

Guys

Sorry to disturb you but I really need your advice, any suggestion is 
appreciated.
Let me explain  quickly, one application  built in perl was working fine with DBD 
oracle  for many years . One month ago, DB team want to test in lab with RAC cluster 
technology of Oracle ,so DB team did  migrate all my tables, permissons, etc to RAC 
enviroment , so  I  did change the connection propierties to point to RAC enviroment, 
it give me a huge problem related with CLOB data type , already try to set oratypes 
=> ORACLOB or oratypes => 8 without success. I did discover , if I try insert 
less of 200 characters in to CLOB column , the insert query is success , if the size 
is more than 200 characters I get ORA-03113 error.

To give  more detail how I make the connection:


sub Insert_RAC {

$sql_stmt= "INSERT INTO smtools.sm_stg_tb 
(REQUEST_ID,APP_STATUS,WEB_URL,APP_SERVERS,LDAP_GROUP,WEB_PORT,SSL_SECURITY,PROTECTED_URI,UNPROTECTED_URI,AGENT_NAME,AGENT_COOKIES,AGENT_HDRS,AGENT_TIMEOUT,STG_SERVERS,ASSIGNED_TO,SUBMITTED_DATE,LAST_UPDATED,LAST_UPDATED_BY,COMP_DATE,COMPLETED_DATE,PRIV_COMMENTS,COMMENTS,NOUSER,FAILEDLOGIN,UNAUTH,AGENT_CONFIG_OBJECT)
 VALUES 
(200700180011138,'COMPLETED','https://traderecords.europe.citigroup.net','trimsastxp14','internal','443',NULL,'%2F','%2FHelloTrims','153622-titaneur-trimsasmdp14-443,153622-titaneur-trimsasmdp15-443)',NULL,NULL,NULL,NULL,'DUMMY
 
USER',to_date('8/9/2007','MM/DD/YYYY'),to_date('9/17/2008','MM/DD/YYYY'),'kf29023',to_date('3/22/2008','MM/DD/YYYY'),'Mar
 22 2008 12:06:55',' ',:FL,'YES','YES','NO','153622-titaneur-443-aco')";

@loginfo = ("101 112 119 101 105 115 116 111 111 108 342 420","221 112 114 116 107 
48 52 119 116 105 117 420 240");
   &connectDatabase(*loginfo);
   $sth = $dbh->prepare($sql_stmt);
       $sth->bind_param 
(":FL",'-%3CBR%3E-%3CBR%3E-%3CBR%3test-%3CBR%3E-%3CBR%3E-%3CBR%3E-%3CBR%3E-%3CB-%3CBR%3E-%3CBR%3E-%3CBR-%3CBR%3E-%3CBR%3E-%3CBR%3test-1234-%3CBR%3E-%3CBR%3E-%3CBR%3test-%3CBR%3E-%3CBR%3E-%3CBR%3E-%3CBR%3E-%3CB-%3CBR%3E-%3CBR%3E-%3CBR-%3CBR%3E-%3CBR%3E-%3CBR%3test-1234-%3CBR%3E-%3CBR%3E-%3CBR%3test-%3CBR%3E-%3CBR%3E-%3CBR%3E-%3CBR%3E-%3CB-%3CBR%3E-%3CBR%3E-%3CBR-%3CBR%3E-%3CBR%3E-%3CBR%3test-1234-%3CBR%3E-%3CBR%3E-%3CBR%3test-%3CBR%3E-%3CBR%3E-%3CBR%3E-%3CBR%3E-%3CB-%3CBR%3E-%3CBR%3E-%3CBR-%3CBR%3E-%3CBR%3E-%3CBR%3test-1234-%3CBR%3E-%3CBR%3E-%3CBR%3test-%3CBR%3E-%3CBR%3E-%3CBR%3E-%3CBR%3E-%3CB-%3CBR%3E-%3CBR%3E-%3CBR-%3CBR%3E-%3CBR%3E-%3CBR%3test-1234-%3CBR%3E-%3CBR%3E-%3CBR%3test-%3CBR%3E-%3CBR%3E-%3CBR%3E-%3CBR%3E-%3CB-%3CBR%3E-%3CBR%3E-%3CBR-%3CBR%3E3test-1234',{ora_type
 => ORA_CLOB , ora_field=> COMMENTS});
   $rv = $sth->execute || &WriteDie("Execute failed: $DBI::errstr");


   $sth->finish;


   &closeDatabase();

}
Thanks for your help


--
                ---//---
Time flies like the wind. Fruit flies like bananas.
        --- Groucho Marx

Either write something worth reading or do something worth writing.
        --- Benjamin Franklin

A meeting is an event at which the minutes are kept and the hours are lost