Re: Question about External Tables

From: Joe Cirillo <joe.cirillo_at_hp.com>
Date: Tue, 16 Aug 2011 05:42:20 -0700 (PDT)
Message-ID: <51447ab6-0dba-42ec-96d2-ca49cbaa29be_at_a4g2000yqg.googlegroups.com>

On Aug 15, 2:15 am, Jack Schmidt <thejackschm..._at_gmail.com> wrote:
> UPDATE:
> I wrote a test pl/sql package using the file_utl to read and print a line.
>
> In my database server, I am able to execute it and it prints out the information fine.  If I connect via Oracle client and execute the pl/sql, I get an error.
>
> ERROR at line 1:
> ORA-29283: invalid file operation
> ORA-06512: at "SYS.UTL_FILE", line 488
> ORA-29283: invalid file operation
> ORA-06512: at "TEST.DBG_EXT_UTIL", line 8
> ORA-06512: at line 1
>
> Here is my debug pl/sql package.
>
> create or replace package dbg_ext_util as
>   procedure dbg_test_read;
> end;
> /
> show errors;
>
> create or replace package body dbg_ext_util as
>
>     procedure dbg_test_read
>     is
>         v1 varchar2(32767);
>         f1 utl_file.file_type;
>     begin
>         f1 := utl_file.fopen('EXT_TEST','temp.csv','R');
>         utl_file.get_line(f1,v1);
>         utl_file.fclose(f1);
>         dbms_output.put_line(v1);
>     end;
> end;
> /
> show errors;

Try testing from the database server again but this time connect thru the listener instead of directly to the database. We have seen issues where the environment settings from directly logging in did not match the environment settings for the environment that started the listener and it resulted in this same symptoms. In release 11.1.0.7.0 there was also an issue with the lsnrctl executable that override the umask settings to something other then 0022. If you test from the server connecting thru the listener and can duplicate the error you may be seeing one of these issues. Received on Tue Aug 16 2011 - 07:42:20 CDT