Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • 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!
  • Students Click Here

*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.

Students Click Here


Vertical Record Listing

Vertical Record Listing

Vertical Record Listing

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.


RE: Vertical Record Listing

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.

RE: Vertical Record Listing

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

RE: Vertical Record Listing

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.

RE: Vertical Record Listing

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

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

RE: Vertical Record Listing

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


RE: Vertical Record Listing

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.

RE: Vertical Record Listing

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

RE: Vertical Record Listing

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.

RE: Vertical Record Listing

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.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

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