Archivos de un directorio

Estoy haciendo un programa en PL/SQL y necesito listar los archivos que estan en cierto directorio. Ese directorio ya esta accesible para las funciones de UTL_FILE.
¿Cómo puedo listar los archivos que se encuentran ese directorio?
De antemanos
Muchas grcias
1

1 respuesta

Respuesta
1
Basicamente debes utilizar un comando de sistema operativo. Es decir, ahora depende del sistema operativo donde estes ejecutando el PL/SQL.
La nota 199223.1 en el metalink explica como interactuar con comandos de Sistema Operativo, a continuacion un extracto de ella (Copyrigth Oracle Corporation).
PL/SQL developers often need their code to interact with the Operating System in
which the program is running, and/or the Session of the user. There are numerous
requirements( example ftp , mail ,executing os commands etc ..) for
which the developer needs to interact with the Operating System .This Article
addresses those requirements as well as general Solutions to interact with the
Operating system and troubleshooting any errors which occur .
SCOPE & APPLICATION
-------------------
For Application Developers whose PL/SQL programs need to interact with OS
facilities which are not directly supported by PL/SQL.
Some of the requirements are :
1. Running Operating system commands from PL/SQL
2. Doing FTP from PL/SQL
3. Sending email from PL/SQL
4. Accessing Environment variables from PL/SQL
5. Passing Environment variables to PL/SQL
6. Calling external Procedures from PL/SQL
7. Writing the contents of BLOB/CLOB to a file and vice versa from PL/SQL
...
Os interaction through pl/sql (os commands, email, ftp, external procedures)
---------------------------------------------------------------------------
The Application Developer requires that he use OS provided facilities, external
programs inside PL/SQL for the features which are not directly supported by PL/SQL.
Some of the requirements are :
------------------------------
1. Running Operating system commands from PL/SQL
2. Doing FTP from PL/SQL
3. Sending email from PL/SQL
4. Accessing Environment variables from PL/SQL
5. Passing Environment variables to PL/SQL
6. Calling external Procedures from PL/SQL
7. Writing the contents of BLOB/CLOB to a file and vice versa from PL/SQL
8. Trouble Shooting
Running Operating system commands from PL/SQL
---------------------------------------------
This is accomplished by using external Procedures to call programs written in C
and Java which accomplish the task of running the OS commands.It is
necessary to have a certified C compiler to run these examples .The OS commands
will be executed in the context of extproc executable . If you are using daemon
(Note:14082.1) then OS commands will be executed by daemon and it is required
that daemon is running on the system to execute the OS command.
For unix :
a. Calling Operating System Commands from PL/SQL using External Procedures Note:99136.1
b. Dynamic SQL and System Commands Using DBMS_PIPE Note:14082.1
For Windows :
a. How to Launch Windows NT OS Commands From an External Procedure Note:130799.1
b. How to Execute NT Operating System Command From PL/SQL Note:136527.1
This can also be accomplished by using Java stored Procedure (JSP).This doesn't
require for external compiler except the JVM installed with Oracle.The OS
commands will be executed in context with JVM of Oracle.
a. How to Perform a System Call From a Java Stored Procedure Note:109095.1
b. How to Call Operating System Commands from PL/SQL - The Java Way Note:165256.1
Doing FTP from PL/SQL
---------------------
This is accomplished by using either external procedures or Java Stored Procedures.
If you have implemented the above interface (running os commands ) then you can
execute O/S ftp command passing it an input file with the all the ftp commands
to execute .
exec shell('ftp -i inputfilename host');
For Windows:
Exec shell('ftp -s:inputfilename host');
a. How to perform FTP operations from within PL/SQL (part I) Note:159034.1
b. How to perform FTP operations from within PL/SQL (part II) Note:159061.1
c. How to perform FTP operations from within PL/SQL (part III) Note:159062.1
@ Execute remote shell script through external procedure Note:111780.1
Sending email from PL/SQL
-------------------------
You can use Oracle provided Package UTL_SMTP to send email . The Limitations of
UTL_SMTP are it confirms to RFC 821 and support for attachments and MIME headers
is not there . This has to be implemented by the Developer using the basic API
provided by UTL_SMTP. Alternative Solution is to use Java Stored Procedures or
External Procedures from PL/SQL to support attachments .
a. Mail Processing from within JSP / PL/SQL Note:73321.1
b. How to Generate E-mail within PL/SQL Routines Note:66347.1
c. How to Send E-mail With Attachments from PL/SQL Using Java Stored Procedures Note:120994.1
Accessing Environment variables from PL/SQL
-------------------------------------------
There is no direct way to obtain an environment variable value, because the PL/SQL
engine runs in its own environment. The concept of "environment variables" is
then "lost." One possibility could be to call an external procedure (Oracle 8)
via a C program or a JSP but this would spawn a new process which would query the
environment with a different set of environment variables.
You can only pass the EXIT code from a PL/SQL block to a calling batch
script/program.
a. Using a PL/SQL Variable to Set the EXIT code of a SQL script Note:6841.1
b. How to pass EXIT values from PL/SQL to batch files in Windows Environment Note:191585.1
Passing Environment variables to PL/SQL
---------------------------------------
This is possible since you can run PL/SQL code embedded in a shell script and run
the shell script.The shell then replaces the Environment variables with their values.
a. ¿How to pass an UNIX environemnt variable value to a PLSQL block? Note:182760.1
b. validate an Oracle login from a shell script Note:5033.1
Calling external Procedures from PL/SQL
---------------------------------------
An external procedure is a third generation language routine stored in a
dynamic link library (DLL) called by PL/SQL block. Before calling the DLL,
a library must be created and registered with PL/SQL. Once registered, it
can be called from any PL/SQL program (i.e. Stored Procedure, Function,
Triggers, etc.). At run time, PL/SQL loads the library dynamically then
calls the routine as if it were a PL/SQL subprogram.This gives PL/SQL power
and features of 3GL in PL/SQL.
a. Complete Guide to PL/SQL External Procedures in Oracle8 PLSQL-8 : Note:76411.1
b. Creating External Procedures on Windows NT Note:68061.1
c. Calling C routines from PL/SQL in Oracle8 on Digital UNIX Note:50868.1
d. Setting up External Procedure Calls Revisited Note:119425.1
e. PL/SQL 8.0 External Procedures: Setting up the Demonstration NOTE:47484.1
f. Adding Regular Expressions to Oracle Through External Procedures Note:119426.1
g. How to Run External Procedures as a Non-oracle User Note:1018659.102
Writing the contents of BLOB/CLOB to a file and vice versa from PL/SQL
-----------------------------------------------------------------------
Currently, the built-in package DBMS_LOB provides a mechanism for reading from
a binary OS file and loading the data into a BLOB (DBMS_LOB. LoadFromFile)
through PL/SQL. However, there is no corresponding mechanism for reading from a
BLOB and then writing the data to an OS file. Text files can be written using
the UTL_FILE package, but binary files of any size cannot. Thus, users have had
to resort to 3GL solutions and external procedures.
a. Example How To Unload LOBs to a File in Oracle 8 (SCR 1276) PLSQL-80 : Note:150107.1
b. Example How To Unload LOBs to a File in Oracle 8i (SCR 1275) PLSQL-81 : Note:150104.1
c. WRITING BLOB/CLOB/BFILE CONTENTS TO A FILE USING EXTERNAL PROCEDURES Note:70110.1
Trouble Shooting
----------------
The Configuration problems with extproc, Database, Listener lead to errors
like ORA-28575
a. External Procedures - Troubleshooting ORA-28575 Errors Note:70638.1
b. OERR ORA-28575 unable to open RPC connection to external procedure agent Note:50062.1
c. ORA-28576 Lost RPC Connection to External Procedure Agent Note:107624.1
d. External Procedure Calls Fail with ORA-28575 TNS-12154 Note:159751.1
e. External Procedure (EXTPROC) Calls Fail with ORA-28575 NET-815 : Note:1082437.6
f. ORA-28575,06512 WHEN TRYING TO EXECUTE A 3GL CALLOUT Note:1012140.102
g. Configuring SQL*Net for External Procedures (EXTPROC) Note:1033759.6
RELATED DOCUMENTS
-----------------
PL/SQL User? S Guide and Reference Guide
¿Application Developer? S Guide
PL/SQL Supplied Packages Guide

Añade tu respuesta

Haz clic para o

Más respuestas relacionadas