Monday, November 22, 2010

Extract filename from a Directory path in plsql

When I was searching for a Oracle API to list the file names from a folder/directory, I found the link
www.chrispoole.co.uk/tips/plsqltip2.htm

I just tried to use the script and provided the test results in this post. I did this because it seems to be a worthy script.


Test Enviroment: 
Test OS: Windows
Test DB: Oracle 10g
Tool USed: SQL Developer

SCRIPT:
SET SERVEROUTPUT ON;
DECLARE
pattern VARCHAR2(1024) := 'C:\tmp\*.*';
ns VARCHAR2(1024);
BEGIN
SYS.DBMS_OUTPUT.ENABLE(1000000);
SYS.DBMS_BACKUP_RESTORE.searchFiles(pattern, ns);
FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT) LOOP
DBMS_OUTPUT.PUT_LINE(each_file.name);
END LOOP;
END;
/

Directory


Test Results:



I welcome my blog viewers to rpovide their valuable comments to improve this script 

0 Responses to “Extract filename from a Directory path in plsql”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.