Vertical Record Listing 1

[martymarty2]

thread186-966141 - topic originally covered here. I am working for a company that has a legacy system running on Oracle 8.I. Plans are to upgrade and convert to a new system in the latter part of this year.
We are heavily involved in cleaning the database, so run many single-table, single-record lookup scripts. Default output from those queries is in a horizontal listing, so I was excited to find the older thread.
I am getting the following errors also referred to in the original thread. In that case the user was on an older 7.X release. When he tested in rlse 8, he apparently got it to work. Here is the error:

ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 192
ORA-06512: at "MARTIND.DH_FILE", line 14
ORA-06512: at line 33
ORA-06512: at line 95

SP2-0310: unable to open file "C:\temp\TempVWriter"

Thanks for any assistance you can give.

-Dave

 

[Turkbear]

Hi,
see if the ideas here help:

http://www.orafaq.com/forum/t/42445/0/

or

http://www.jlcomp.demon.co.uk/faq/utl_file.html

Looks like a permissions isssue or a missing file ..



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[martymarty2]

Turkbear:
Thanks for the links. I am getting closer, but still having the problem. In our environment, I do not have permissions to create directories or modify users. So I have to just point to my home directory. Not having much luck, but just have to hit the right display for the home DIR.
Thanks again, I'm pointed in the right direction at least.

 

[LKBrwnDBA]

You need to check the setting of the UTL_FILE_DIR parameter:

 show parameter UTL_FILE_DIR

That/Those are the only valid destinations where Oracle will try to write a file.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[martymarty2]

LKB...
the "show parameter UTL_FILE_DIR" command displays nothing at the system prompt nor at the SQLPLUS prompt, except errors. Get an unrecognized keyword error at the sys prompt, and table or view does not exist at the SQLPLUS prompt.
Is this a logical I can set up for LOGIN? One of the other threads shown above says to declare it in the initSID.ora, but I am not given that permission.
Thanks,
Dave

 

[LKBrwnDBA]

What errors?
Did you enter the command like this example?:

SQL>  show parameter utl_file_dir

NAME                          TYPE        VALUE
----------------------------- ----------- ---------------------
utl_file_dir                  string      /u09/data
SQL>

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[martymarty2]

Yes< I did type it exactly as shown. This was the result:

SQL> show parameter utl_file_dir

ORA-00942: table or view does not exist

SQL>

 

[Dagon]

Are you saying this is on an Oracle 7 database ? It was a bit unclear from your original posting. I doubt if utl_file existed in Oracle 7.

Retired (not by choice) Oracle contractor.

 

[martymarty2]

Dagon:
Not Oracle 7, but it is still somewhat old -- Oracle 8.I

 

[karluk]

I'm afraid your Oracle id doesn't have enough privileges to effectively troubleshoot this issue. LKBrwnDBA is right that you should check the setting of the UTL_FILE_DIR parameter. But the "show parameter" sql*plus command requires select on the dictionary view v_$parameter, which you must not have. That's why you are getting an ORA-00942 error when you try to run the command.

 

[martymarty2]

Thanks, Karluk,
I appreciate all the responses from you and the other guys. Since I am in the end-user area rather than IT, privileges are tight and unlikely to be loosened much. We have an Access "view" into the database, so I will probably go back to using results from an Access query copied and transposed into Excel. A bit awkward and not fast, but it works.
Again, thanks to all you guys for the advice.

Dave