Re: pulling my hair out with procedure behavior

Ed Stevens <nospam_at_noway.nohow> wrote in message news:<k7qi4voq1dltb3jkphnrsq504pqikss8lo_at_4ax.com>...
> Platform: Oracle 8.1.7 on NT4
>
> I have a procedure that SELECTs from a specified table and writes the
> results -- via UTL_FILE.PUT_LINE -- to a comma-delimited file. One of
> the keys to its operation is building the SELECT .... FROM MY_TABLE
> statement from the results of querying ALL_TAB_COLUMNS.
>
> The procedure belongs to the schema of a user called DSA.
>
> When I execute the procedure in debug mode from a tool like TOAD,
> (while connected as SYSTEM) the procedure works flawlessly and I have
> my desired comma-delimited file at the end.
>
> When I execute the procedure from a SQLPlus command line as
>
> SQL> exec dsa.extract_user_table(parm_list)
>
> it reports PL/SQL procedure successfully completed. but it does not
> create my output file.
>
> Since it works while inside a debugging product, but not outside of
> it, I am unable to determine directly what the problem might be.
>
> SELECT ideas, comments
> FROM newsgroup_participants;

This is generally a priviledge issue. It works from one account and not from another. Best way to debug this is to start by doing what the other person said. Put messages in your code to see what executes. If you have 'if' statements or cursors, see if they fire.

Then do a dbms_output to see if your file is actually being built. Have you been able to select from ALL_TAB_COLUMNS from that account? Its quite possible someone implemented some security denying that account the ability to retrieve certain info from certain tables.

You should be able to figure out what is going on pretty quickly. Received on Wed Feb 12 2003 - 08:41:18 CST