| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: How do I get a directory listing via PL/SQL
Hi,
There is a complicated way. I assume that this is not from forms. You
want to do this in sql*plus.
You need to use utl_file, dbms_pipe package and a c program. C program
will listen to pipe.
When it receives a message from the pipe it is going to create dir
listing to a known folder
with a known filename. Then pl/sql program will read the file using
utl_file package.
I have done all those. The source and sample run are in the following.
My database was V7.3.4.5, my c compiler was
MS visual c++ v6.0 with sp3 on w98. This sample is just to show it can
be do-able.
Good Luck
Hakan
SQLWKS> create or replace package plsdbg as
2> procedure put(info varchar2);
3> procedure put(n number);
4> end plsdbg;
5> /
SQLWKS> create or replace package body plsdbg as
2> procedure put(info varchar2) is
3> status integer; -- ret. val. required for the
4> -- send_message function, not used here
5> begin
6> dbms_pipe.pack_message(info);
7> status := dbms_pipe.send_message('ora$plsql_debug');
8> end;
9> --
10> procedure put(n number) is
11> chr varchar2(44);
12> status integer;
13> begin
14> chr := to_char(n);
15> dbms_pipe.pack_message(chr);
16> status := dbms_pipe.send_message('ora$plsql_debug');
17> end;
18> end plsdbg;
19> /
Server Output ON
SQLWKS> declare
2> my_var number(15);
3> in_file UTL_FILE.FILE_TYPE;
4> text_line VARCHAR2(100);
5> begin
6> my_var := 99;
7> -- write to pipe
8> plsdbg.put('Create Listing');
9> -- wait for c program to write directory listing, can be pipe
communication again
10> FOR i IN 1..1000000 LOOP
11> my_var := my_var + i;
12> END LOOP;
13> -- now read the directory listing from the file
14> in_file := UTL_FILE.FOPEN('f:\dirlist', 'dirlist.txt', 'R');
15> FOR i IN 1..10000 LOOP
16> UTL_FILE.GET_LINE(in_file, text_line);
17> DBMS_OUTPUT.PUT_LINE(text_line);
18> END LOOP;
19> -- close file, only expected error is end of file, but you need
to add more error handling
20> EXCEPTION
21> WHEN OTHERS THEN
22> UTL_FILE.FCLOSE(in_file);
23> DBMS_OUTPUT.PUT_LINE(' ');
24> DBMS_OUTPUT.PUT_LINE('************End of demo');
25> END;
26>
Volume in drive C has no label
Volume Serial Number is 1434-1AF6
Directory of C:\
COMMAND COM 93,880 05-11-98 7:01p COMMAND.COM
WINDOWS <DIR> 11-11-99 8:01p WINDOWS
IBM <DIR> 11-11-99 8:01p IBM
USBTEST LOG 0 03-09-00 2:01p USBtest.log
CONFIG SYS 12 01-06-00 10:13a CONFIG.SYS
CFGSAFE <DIR> 11-11-99 8:01p CFGSAFE
IBMINFO <DIR> 11-11-99 8:01p IBMINFO
THINKPAD <DIR> 11-11-99 8:01p THINKPAD
CDROM <DIR> 11-11-99 8:01p CDROM
IBMTOOLS <DIR> 11-11-99 8:01p IBMTOOLS
XIRCOM <DIR> 11-12-99 4:13a XIRCOM
AUTOEXEC SYD 290 11-28-99 8:01p AUTOEXEC.SYD
SYSBOOT SEC 512 01-28-99 3:23p SYSBOOT.SEC
AUTOEXEC BAT 359 02-23-00 11:16a autoexec.bat
MYDOCU~1 <DIR> 11-11-99 8:01p My Documents
PROGRA~1 <DIR> 11-11-99 8:01p Program Files
MYMUSI~1 <DIR> 11-29-99 8:26p My Music
LIPREFS JS 151 11-30-99 10:04p liprefs.js
MYDOWN~1 <DIR> 11-15-99 5:54p My Download Files
MYDOCU~1 ZIP 2,522,641 11-11-99 6:52p My Documents.zip
NCDTREE <DIR> 12-12-99 7:39p NCDTREE
GRPHFLT <DIR> 12-24-99 6:25p GRPHFLT
DRIVERS <DIR> 01-06-00 10:09a DRIVERS
TEMP <DIR> 02-01-00 11:10a temp
SCANDISK LOG 2,584 03-09-00 12:54p SCANDISK.LOG
LISTDIR EXE 163,900 03-09-00 10:28p listdir.exe
10 file(s) 2,784,329 bytes
16 dir(s) 436,436,992 bytes free
************End of demo
pl/sql source:
create or replace package plsdbg as
procedure put(info varchar2);
procedure put(n number);
end plsdbg;
/
create or replace package body plsdbg as
procedure put(info varchar2) is
status integer; -- ret. val. required for the
sample run in pl/sql:
set serveroutput on
declare
my_var number(15);
in_file UTL_FILE.FILE_TYPE;
text_line VARCHAR2(100);
begin
my_var := 99;
-- write to pipe
plsdbg.put('Create Listing');
-- wait for c program to write directory listing, can be pipe
communication again
FOR i IN 1..1000000 LOOP
my_var := my_var + i;
END LOOP;
-- now read the directory listing from the file
in_file := UTL_FILE.FOPEN('f:\dirlist', 'dirlist.txt', 'R');
FOR i IN 1..10000 LOOP
UTL_FILE.GET_LINE(in_file, text_line);
DBMS_OUTPUT.PUT_LINE(text_line);
#ifdef RCSID
static char *RCSid =
"$Header: readpipe.c,v 1.5 94/04/07 11:40:33 emendez:thwang Exp $ ";
#endif /* RCSID */
/* Copyright (c) 1991 by Oracle Corporation */ /*
NAME
readpipe.c - read a server named pipe
NOTES
described in ORACLE7 Server Application Developer's Guide
MODIFIED (MM/DD/YY)
thwang 02/05/96 - Modified for Solaris because of alignment bus
errors
emendez 04/07/94 - merge changes from branch 1.3.710.2
gdoherty 04/06/94 - merge changes from branch 1.3.710.1
emendez 02/02/94 - Fix for bug 157576
gdoherty 01/31/94 - make oci header inclusion for ansi or k+r
adaptive
rkooi 12/15/92 - add some comments
tssmith 12/15/92 - Added break in for(;;) loop
tssmith 12/12/92 - Creation
/*
* readpipe.c
*
* This OCI program demonstrates how to read messages from
* a named pipe. This program can be run in
* a window or terminal session, and be used to print messages
* from a PL/SQL source program (an anonymous block or a
* stored procedure).
*
* This is very useful in debugging PL/SQL programs.
*
* First, you must create a PL/SQL package and package body that packs
* your message(s) and sends them to a named pipe.
* In the example below, two 'put' procedures are implemented,
* overloaded by type: VARCHAR2 and NUMBER. This you can extend
* as required for additional types.
*
* Store this package in the server in an appropriate schema,
* and grant execute privilege on it to the users who require it.
*
* You will need to grant execute privilege on the dbms_pipe package
* to the owner of the plsdbg package below.
*
* Note that this example uses only a single named pipe. It could
* be confusing if more than one PL/SQL program were writing to
* the same pipe (unless some identifying protocol is established).
* It would certainly be confusing if several OCI programs like
* the one below were reading the same pipe.
*
* Here is the example package:
*
* create or replace package plsdbg as
* -- the procedure put is overloaded for varchar strings
* -- and numbers. extend as needed.
* procedure put(info varchar2);
* procedure put(n number);
* end;
* /
*
* create or replace package body plsdbg as
*
* procedure put(info varchar2) is
* status integer; -- ret. val. required for the
* -- send_message function, not used here
* begin
* dbms_pipe.pack_message(info);
* status := dbms_pipe.send_message('ora$plsql_debug');
* end;
*
* procedure put(n number) is
* chr varchar2(44);
* status integer;
* begin
* chr := to_char(n);
* dbms_pipe.pack_message(chr);
* status := dbms_pipe.send_message('ora$plsql_debug');
* end;
* end;
* /
*
* In a PL/SQL program, you call plsdbg to write messages to the
* pipe 'ora$plsql_debug' like this:
* ...
* declare
* my_var integer;
* ...
* begin
* my_var := 42;
* ... -- program procedes until debug output is needed
* plsdbg.put('Hmm, my_var is--');
* plsdbg.put(my_var);
* -- and the OCI program reading ora$plsql_debug will
* -- print the text message, and the value of my_var
* ...
*/
#include <stdio.h>
#include <string.h>
#include <windows.h>
/* These header files must be included for the
type information, and #defined constants. */
#include <oratypes.h>
#include <ocidfn.h>
#ifdef __STDC__
#include <ociapr.h>
#else
#include <ocikpr.h>
#endif
/* demo constants and structs */
#include <ocidem.h>
/* Declare the CDA, LDA, and HDA. */
Cda_Def lda;
ub4 hda[HDA_SIZE/sizeof(ub4)]; /* ub1 hda[HDA_SIZE]; */
Cda_Def cda;
/* Define a string that holds the anonymous PL/SQL block.
The block calls the DBMS_PIPE procedures to get messages written to the pipe named ora$plsql_debug. */
#define GETNEXTITEM "\
declare\
s integer;\
chr varchar2(200);\
begin\
chr := '';\
s := dbms_pipe.receive_message('ora$plsql_debug');\
if s = 0 then\
dbms_pipe.unpack_message(chr);\
end if;\
:status := s;\
:retval := chr;\
end;"
/* Error-handling function */
void errrpt();
void main(argc, argv)
sword argc;
text **argv;
{
text username[128];
ub1 retval[132];
sword status;
int i;
FILE *stream;
int counter=1; // char printer_name[40]; char print_command[60] = "dir > f:\\dirlist\\dirlist.txt"; /* Prepare username/password, from command line orelse use scott/tiger as the default if none given on the command line. */
if (argc > 1)
strncpy((char *) username, (char *) argv[1],
(sword) sizeof (username) - 1);
else
strcpy((char *) username, "SCOTT/tiger_at_herp");
/* Connect to ORACLE. */
if (orlon(&lda, hda, username, -1,
(text *) 0, -1, 0))
{
printf("Cannot connect as %s. Exiting...\n", username);
exit(1);
printf("connected\n");
/* Open the cursor -- must quit if we cannot. */
if (oopen(&cda, &lda, (text *) 0, -1, -1, (text *) 0, -1)) {
printf("Error opening cursor. Exiting...\n");
exit(1);
/* Parse the anonymous PL/SQL block. */
if (oparse(&cda, (text *) GETNEXTITEM,
(sb4) -1, 0, (ub4) 2))
{
errrpt();
exit(1);
/* Bind the status program variable. */
if (obndrv(&cda, (text *) ":status", -1, (ub1 *) &status,
(sword) sizeof (sword), SQLT_INT,
-1, (sb2 *) 0, (text *) 0, -1, -1))
{
errrpt();
exit(1);
/* Bind the return string (retval). */
if (obndrv(&cda, (text *) ":retval", -1, (ub1 *) retval,
(sword) sizeof (retval), SQLT_STR,
-1, (sb2 *) 0, (text *) 0, -1, -1))
{
errrpt();
exit(1);
/* Loop to look for print messages on the pipe */
printf("listening...\n");
for (i=1;i<10;i++)
{
if (oexec(&cda))
{
errrpt();
break;
}
if (status != 0)
printf("Abnormal pipe status: %d\n\r", status);
else
// printf("\a\a\a%s\n\r", retval);
system(print_command);
MessageBox(NULL, retval, "I updated the directory listing, now use
utl_file", MB_OK);
}
}
/* Report errors. */
void errrpt()
{
sword rv;
text msg[1024];
/* use oerhms to get error messages longer than 70 characters */
rv = oerhms(&lda, cda.rc, msg, (sword) sizeof (msg));
printf("ORACLE ERROR\n");
printf("%.*s\n", rv, msg);
}
kevin_g_at_my-deja.com wrote:
> > Hi, > > I need to write a PL/SQL procedure that will get a file list from a > particular directory on our oracle server. Is there a package that can > do this. > > Thanks, > Kevin > > Sent via Deja.com http://www.deja.com/ > Before you buy.Received on Thu Mar 09 2000 - 21:50:16 CST
 |
 |