Solved

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

Posted on 2011-03-24
7
548 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Getting TNS:Connect timeout occurred while opening the application 5 81
Oracle SQL syntax check  without executing 6 57
help on oracle query 5 45
awk sed 8 45
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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…

831 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