Solved

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

Posted on 2011-03-24
7
558 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
AIX check for filesystems not mounted but mount = true 4 66
add more rows to hierarchy 3 46
Oracle Date 6 40
replicate in oracle 13 45
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

734 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