Bro. Doug has asked for the wisdom of the Perl Monks concerning the following question:

Esteemed monks,

I've been dealing with this problem for some time now, and posing a question to the monks seems the right move. First, let me apologize for the generic nature of the code I'm posting: I work in a highly confidential environment, and had to seek permission before posting this.

The problem: When I use an Oracle schema user (SCHEMA) to update and insert LOB's, it works. However, if I use a non-schema user (NONSCHEMA) with all the necessary grants and synonyms set in place, the insert/update fails.

In this case, table SCHEMA.TABLE has an alias for NONSCHEMA to access it as well. 
# works
my $dbh = DBI->connect( 'dbi:Oracle', 'SCHEMA', $pwd ) ;
my $sth = $dbh->prepare( 'update TABLE set BLOBCOL=:data' );
$sth->bind( ':data', $binarystuff, {ora_type=>ORA_BLOB } );
$sth->finish ;

# doesn't work
my $dbh = DBI->connect( 'dbi:Oracle', 'NONSCHEMA', $pwd ) ;
my $sth = $dbh->prepare( 'update TABLE set BLOBCOL=:data' );
$sth->bind( ':data', $binarystuff, {ora_type=>ORA_BLOB } );
$sth->finish ;

# The error:
# Bad file descripor: ORA-04043: object SCHEMATABLE.TABLE 
# does not exist. (DBD SUCCESS: OCIDescribeAny(view)/LOB
# refetch) at <file> line ##.
#

[download]

Again, I apologize for distilling this down into a short snippet of generic code, but I'm not authorized to show more.

My hope is that another esteemed monk will recognize something in this error and give a pointer. As you can see, there is a LOB refetch occurring behind the scenes, and this is where the whole process seems to fail.

Peace, monks
Bro. Doug :wq

Replies are listed 'Best First'.
Re: LOB's refetch improperly
by bart (Canon) on Mar 19, 2007 at 18:28 UTC
    Can you insert anything other than BLOBs (in other columns/tables)? I'd expect no.

    The error message says "object SCHEMATABLE.TABLE does not exist". Concentrate on that as a clue. What or who is "SCHEMATABLE"? Can you access it as NONSCHEMA from other access methods, for example in SQL*Plus?

[reply]
      Thanks for the quick reply.

      SCHEMATABLE.TABLE does not exist, as the message says. I'm not sure where the message is generated from, as its internal to DBD::Oracle. This is, indeed, where the strangeness occurs.

      I can insert things other than LOBs. I only have this problem with LOBs. Its trying to update the LOBs that I run into this strange issue. I've tried both BLOBs and CLOBs here, and found that with these two I get that error, but all other data types seem to work (anything non-lob, at least).

      I have other programs (webapps) built using the same framework for web/db apps, and none of them have this issue. Some of them use the same codebase that generates this error.

      The synonym is correct. Nobody, including a large team of DBAs, can fathom where this comes from.

      I've used NONSCHEMA to connect to sqlplus and executed successfully a statement updating SCHEMA.TABLE.BLOBCOL with to_blob('1243').
      Bro. Doug :wq
[reply]
        More and more, this begins to sound like a real DBD::Oracle/DBI bug to me, instead of just a user error. Perhaps you'd take this best to the DBI-Users mailing list, where the real DBI experts hang out, including the author of both DBI and DBD::Oracle: Tim Bunce.
[reply]
Re: LOB's refetch improperly
by andye (Curate) on Mar 19, 2007 at 18:29 UTC
    Probably this is just a typo, but might as well check the basics:

    In your description you say that "table SCHEMA.TABLE has an alias for NONSCHEMA to access it". But the error message refers to "object SCHEMATABLE.TABLE" rather than SCHEMA.TABLE. Could the alias be set to SCHEMATABLE.TABLE rather than SCHEMA.TABLE ?

    On other possibilities: Does the error message refer to the prepare() line? If so, can you successfully do an update without a blob in it?

    Best wishes, andye

[reply]
      Andye,

      Thanks for taking time to respond. I just posted another reply regarding SCHEMATABLE.TABLE providing more details, but to summarize: its not a typo. DBD::Oracle seems to be putting this together on the LOB refetch.

      More specific to your questions: 1> No, I haven't tried setting up the alias like that. I think I may. 2> It happens on execute, not prepare.

      I appreciate the attention that monks are giving this. All ideas are welcome, and I'll happily provide more code samples to illustrate various ideas on request.

      Bro. Doug :wq
[reply]
        Hi again Doug.

        A different idea: I'm looking at this bit of the Oracle::DBD docs: "One further wrinkle: for inserts and updates of LOBs, DBD::Oracle has to be able to tell which parameters relate to which table fields. In all cases where it can possibly work it out for itself, it does, however, if there are multiple LOB fields of the same type in the table then you need to tell it which field each LOB param relates to"

        Although if that was the problem then it would be really weird for it to work as one user but not as another - but maybe the aliases are screwing things up somehow. Does it help if you use

        $sth->bind_param($idx, $value, { ora_type=>ORA_CLOB, ora_field=>'foo' });

        ?

        HTH, andye

[reply]
[d/l]
Re: LOB's refetch improperly
by dmorgo (Pilgrim) on Mar 19, 2007 at 19:19 UTC
    My comments should be read as coming from the proverbial "beginner mind", since I know nothing about Oracle database access. Please forgive their likely stupidity.

    On Google, noschema gives more than double the number of hits that nonschema gives. Seems suspicious. Could Oracle treat these differently? Just wondering. Probably nothing.

    Why is there no semicolon after the second line? Is that normal?

[reply]
      Alas... its not normal. It's a shameful typo, which I shall correct :)
      Bro. Doug :wq
[reply]

Back to Seekers of Perl Wisdom