Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

How to select/insert/update on Oracle clob column

by kanel (Initiate)
on Apr 20, 2001 at 19:07 UTC ( #74191=perlquestion: print w/replies, xml ) Need Help??

kanel has asked for the wisdom of the Perl Monks concerning the following question:

Hi, i need to manipulate Oracle clob column. I created iTars to Oracle Metalink on how to do this with pl/sql and don't know hot to. Any idea?
  • Comment on How to select/insert/update on Oracle clob column

Replies are listed 'Best First'.
Re: How to select/insert/update on Oracle clob column
by elwarren (Curate) on Apr 20, 2001 at 23:58 UTC
    Not sure if you meant pl/sql or if you meant perl/sql, so here goes.

    When using Perl and the DBD::Oracle module you are able to select LOBs, CLOBs, and BLOBs as a normal field. No special treatment. You will need to tell DBD that you are inserting a LOB, similar to this: $sth->bind_param($field_num, $lob_value, { ora_type => ORA_CLOB }); If you meant you wanted to run PL/SQL from Perl, then you'd do something similar to this example, from the docs:
    $csr = $db->prepare(q{ BEGIN PLSQL_EXAMPLE.PROC_NP; END; }); $csr->execute;
    According to the docs you cannot use LOBs and PL/SQL together in Perl.

    HTH
      Though this is a bit off topic, note that the
      { ora_type => ORA_CLOB }
      
      while seemingly trivial, can be terribly important.

      DBD::Oracle by default binds CLOBs to Oracle datatype VARCHAR2, which truncates whitespace; that is, if you do a

      UPDATE users SET firstname='Bob   ' WHERE userid='bob'
      
      then later do a
      $db->prepare("SELECT firstname FROM users WHERE userid='bob');
      $db->execute();
      my ($firstname) = $db->fetchrow_array()
      
      you will get
      $firstname eq 'Bob'
      
      While this seems trivial, if you ever put something like a frozen data structure (via the FreezeThaw module) in a CLOB, you'll end up getting errors all over the place when you later try to thaw it if the frozen structure happens to have whitespace at the end.

      elwarren is obviously wise in the ways of Perl and Oracle ;-)

Re: How to select/insert/update on Oracle clob column
by YoungPups (Beadle) on Apr 21, 2001 at 00:07 UTC
    I could be wrong, but I think you're talking about the oracle "long" datatype. You can manipulate "long" columns by using placeholders and bind values.

    For example if you are trying to insert, instead of
    $query = qq| INSERT INTO foo VALUES ( '$bar', '$baz' )|; $sth = $dbh->prepare($query); $sth->execute();
    try:
    $query = qq| INSERT INTO foo VALUES ( ?, ? ) |; $sth = $dbh->prepare($query); $sth->execute($bar,$baz);
    $bar and $baz can now contain arbitrary data to be inserted into table foo. I find it cleaner to use placeholders in general, but I believe they are required for Oracle "long" data types.
Re: How to select/insert/update on Oracle clob column
by premchai21 (Curate) on Apr 20, 2001 at 19:46 UTC
    No not no hot to manipulate clob column with pl. iTars not work? sql iTars not mixing Oracle well.

    Seriously, you're going to need to be a little more comprehensible. Correct spelling and grammar are always good. And, if I understand you correctly (which there is a good chance I might not), you're asking us to do work for you. This is not something with which people are going to meet favorably. Show us your attempt, and the information you've already found (or not found), and ask a specific question. Then you might actually receive some help.

      No not no hot to manipulate clob column with pl. iTars not work? sql iTars not mixing Oracle well.
      take off every iTars for great 'clob column'!!
        What you say !!

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://74191]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (11)
As of 2020-07-13 10:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?