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
| [reply]
[d/l]
[select] |
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 ;-)
| [reply] |
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. | [reply]
[d/l]
[select] |
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. | [reply] |
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'!!
| [reply] |