Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

martymarty2 (TechnicalUser) (OP)
28 Jan 10 13:08
thread186-966141: formatting output from SELECT * - 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
Helpful Member!  Turkbear (TechnicalUser)
28 Jan 10 13:39
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 ..

profile

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

martymarty2 (TechnicalUser) (OP)
29 Jan 10 15:51
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 (MIS)
1 Feb 10 12:50

You need to check the setting of the UTL_FILE_DIR parameter:

CODE

show parameter UTL_FILE_DIR
That/Those are the only valid destinations where Oracle will try to write a file.
3eyes
 

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

martymarty2 (TechnicalUser) (OP)
2 Feb 10 11:06
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 (MIS)
2 Feb 10 12:20


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

CODE

SQL>  show parameter utl_file_dir

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

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

martymarty2 (TechnicalUser) (OP)
2 Feb 10 16:21
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 (MIS)
2 Feb 10 16:58
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 (TechnicalUser) (OP)
2 Feb 10 17:25
Dagon:
Not Oracle 7, but it is still somewhat old --  Oracle 8.I
karluk (MIS)
3 Feb 10 11:20
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 (TechnicalUser) (OP)
3 Feb 10 12:05
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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close