This discussion is archived
6 Replies Latest reply: Dec 4, 2013 4:12 AM by Werot RSS

Problems when loading External Table into a Collection

MungoHenning Newbie
Currently Being Moderated

Hi,

I've got an external table setup fine in the database such that a "select *" from it from within SQL Developer works fine.

 

I then transfer this query and wrap it up into an apex_collection.create_collection_from_query() call, and when I run this code off a pushbutton all i get is:

 

ORA-20104: create_collection_from_query Error:ORA-29913: error in executing ODCIEXTTABLEFETCH callout


So I take the same sql query, navigate to "SQL Workshop" within Apex and the drop it into "SQL Commands" and it works fine.

 

Anyone encountered this error in these circumstances - why does it work in SQL Workshop fine but not in an Apex application?

I'm wondering if there's something to do with the "anonymous" user - my apex application has been setup with No Authentication, so could that be the issue?

 

Any help appreciated; thankyou in advance.

 

Mungo

  • 1. Re: Problems when loading External Table into a Collection
    jariola Guru
    Currently Being Moderated

    Hi,

     

    Try grant read privilege to directory for ANONYMOUS or APEX_PUBLIC_USER., depending with one is used by APEX.

    GRANT READ ON DIRECTORY your_dir TO APEX_PUBLIC_USER

     

    Regards,

    Jari

  • 2. Re: Problems when loading External Table into a Collection
    MungoHenning Newbie
    Currently Being Moderated

    Hi Jari,

    Obliged as always for your kind help. I've experimented with granting directory permission (read, write and execute) on the directory in question to Anonymous as well as apex_public_user, all to no effect.

    I forgot to add in my opening query that my external table makes use of the new "Preprocessor" directive: it's not really reading from an external file, it's getting its data from a shell script.

    Whether I bother with dressing the results up within an apex_collection seems to be irrelevant: a plain page with a select star from the external table comes back

    with "report error: ORA-29913: error in executing ODCIEXTTABLEFETCH callout". Damn.

     

    The real galling bit is that the same blooming query within SQL Workshop works fine and dandy; it's just dressing it up in Apex that throws it.

     

    I'm doing further "what if" experiments as I think of new avenues to explore. Latest one that i haven't yet done is to create a new application that does require authentication, and see if that placates the

    grumpy beast that is Apex.

     

    As always, any further thoughts or advice appreciated.

     

    Regards

     

    Mungo

     

    P.S. Any way to winkle out less cryptic meaning than the joyous "ORA-20104: create_collection_from_query Error:ORA-29913: error in executing ODCIEXTTABLEFETCH callout" ? My frustration is such that I'd like

    to personally thank the compiler of this error message for their total lack of consideration - it might as well say "There has been an error" for all the use it is.

  • 3. Re: Problems when loading External Table into a Collection
    Mike Kutz Expert
    Currently Being Moderated

    I doubt grants to any of the proxy accounts (anonymous/apex_public_user) will do any good in any situation as these are suppose to have (and only have) CREATE SESSION privilege.

     

    Try grants to:

    parsing schema for the app

    workspace schema

    APEX_{version}

     

    I've also noticed that the "references" grant on the table/view can help out (but mostly for the 'dependance check' utility).

     

    Other ideas

    maybe you have some bad data (hence the ODCE{blah}Fetch() error)

  • 4. Re: Problems when loading External Table into a Collection
    MungoHenning Newbie
    Currently Being Moderated

    Hi Mike,

    Ta for the reply.

    I originally had all the logging files turned off in the external table definition, but I've put them back on to see if this generates any mileage.

    When I re-run my apex query I get an additional line in the logfile (beyond the usual preamble):

    KUP-03154: wait for child process

     

    No other clues spotted; only the logfile is being populated.

     

    Can someone else have a wee test at this using apex: create an external table on a plain text file and then see if you can create (say) an interactive report on it?

    Help appreciated.

     

    Regards

     

    Mungo

  • 5. Re: Problems when loading External Table into a Collection
    Mike Kutz Expert
    Currently Being Moderated

    Luckily, I already had an external table ready to test.

    If the parsing schema is the same as the owning schema, everything is fine.

     

    If the parsing schema is not the owning schema,

    I needed to grant READ and WRITE (for the logs) on the directory to the parsing schema.

    (This is for a non-preprocessor EXTERNAL TABLE.)

    note : I also turned off any usage of ROWID in APEX

     

    With the WAIT FOR CHILD PROCESS error, I suspect you will need to add the EXECUTE (and READ) privilege to the directory that holds the executable.

    The executable code should be in a different directory than your data directory.  Last thing you want is someone to WRITE a virus to a directory they can EXECUTE.

     

    If you still get this problem, add to debugging code to your script (including re-directing STDERR to a file).

     

    If you still have problems, post up in the Export/Import/SQL*Loader/External Tables forum and reference this thread.

    https://forums.oracle.com/community/developer/english/oracle_database/export_import_sql_loader_%26_external_tables

     

    MK

  • 6. Re: Problems when loading External Table into a Collection
    Werot Newbie
    Currently Being Moderated

    Hi Mungo,

     

    I have exactly the same problem. I have an external table that executes a script that performs the following:

     

    #!/bin/bash

    cd /ora01/datasets

    /bin/ls -l

     

    It basically lists all the files in that directory and stores the output in a table. I can do select on the table in SQL Developer and also in the SQL Workshop but ONLY if the AUTOCOMMIT box is unticked. As soon as I tick autocommit I get the error you mentioned above, which is the same I get when I run my Apex app: ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-29913: error in executing ODCIEXTTABLEFETCH callout .

     

    Besides, I also get the same error in the logs: KUP-03154: wait for child process.

     

    I would really appreciate some help as this is driving me nuts and this the only thing I have found on Google related to this topic.

     

    Thanks a lot.

     


Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points