Solved

how can I debug with Pl*Sql Developer when the procedure reads a file from Unix ?

Posted on 2011-03-24
7
562 Views
Last Modified: 2012-05-11
I develop in Pl*Sql, then run the procedures via Unix / Sql * Plus, the program reads flat files on Unix.

But how do I debug this, since the Pl*Sql Windows session doesn't recognize Unix ? Do I put the file on Windows ? If so, how do I read that ?

I read the Unix files like this:

fh_inp := UTL_FILE.FOPEN([UNIX FILE LOCATION],'fep_clm_clm_line_nwr76579.dat','R',2000);

UTL_FILE.GET_LINE (fh_inp, input_rec2);



0
Comment
Question by:Alaska Cowboy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 35209504
you can't read remote files,  in this case your pc is "remote" to the db server.

you'll have to put your files on the unix server.

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 75 total points
ID: 35209514
even when you are debugging from a windows client like pl/sql developer, toad or sql developer,  the pl/sql itself is still executing on the db so the unix file is local to the execution there
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35209574
sdstuber, ok, thanks.

are you saying I should be able to debug normally ? I always thought I couldn't debug from Pl*Sql Developer when reading a flat file from Unix.
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
LVL 74

Expert Comment

by:sdstuber
ID: 35209585
shouldn't be a problem,  you can't "step into" the utl_file procedures themselves anyway,  so the location of those files is irrelevant.
0
 
LVL 2

Assisted Solution

by:choukssa
choukssa earned 50 total points
ID: 35209628

PL/SQL file operations are performed on server machine i.e. the machine where oracle is installed.

Consider the example below
CREATE OR REPLACE DIRECTORY MY_DIR AS '/uo1/my_data'
/

set serveroutput on
declare
    f utl_file.file_type;
    s varchar2(200);
begin
    f := utl_file.fopen('MY_DIR','sample2.txt','R');
    loop
        utl_file.get_line(f,s);
        dbms_output.put_line(s);
    end loop;
exception
    when NO_DATA_FOUND then
        utl_file.fclose(f);
end;

Open in new window

/

Though I am running the SQL block on the client machine.  The dIrectory MY_DIR has to be on the server machine.

You can find directories defined all_directories

--choukssa
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35209651
stdstuber and choukssa, ok, good, I guess I can do it. and I should have tried it first, but I just had it in mind that I can't do it. will give it a whirl, thanks.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35210162
oops, guess I should have tried it first. but I had it locked in my brain it couldn't be done.

so it worked great !

Thanks.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question