Solved

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

Posted on 2011-03-24
7
541 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
  • 3
  • 3
7 Comments
 
LVL 73

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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
null value 15 92
EXECUTE IMMEDIATE 5 52
Queries 15 34
1 FROM DUAL wont work with additional columns ?? 4 21
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Why Shell Scripting? Shell scripting is a powerful method of accessing UNIX systems and it is very flexible. Shell scripts are required when we want to execute a sequence of commands in Unix flavored operating systems. “Shell” is the command line i…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

914 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now