Muthu wrote:You obviously didn't read the link that Janeesh provided. Tom's very first statement is "We cannot do this with PLSQL directly "
But I am trying through PL SQL. I tried by using below program but its giving error.
Please guide me what could be the issue.Go back and read the link.
Thanks and Regards,Edited by: EdStevens on Mar 2, 2013 8:16 AM
SQL> connect sys as sysdba Enter password: Connected. SQL> grant execute on DBMS_BACKUP_RESTORE to scott; Grant succeeded. SQL> create view v$krbmsft as select * from x$krbmsft; View created. SQL> grant select on v$krbmsft to scott; Grant succeeded. SQL> connect scott Enter password: Connected. SQL> set serveroutput on SQL> DECLARE 2 p_directory VARCHAR2(1024) := 'C:\TEMP'; 3 p_null VARCHAR2(1024); 4 i number := 1; 5 BEGIN 6 SYS.DBMS_BACKUP_RESTORE.searchFiles(p_directory, p_null); 7 FOR x IN (select fname_krbmsft fname from sys.v$krbmsft) LOOP 8 DBMS_OUTPUT.PUT_LINE(x.fname); 9 EXIT WHEN i = 3; 10 i := i + 1; 11 END LOOP; 12 END; 13 / C:\TEMP\acbrd-0050.csv C:\TEMP\afiedt.buf C:\TEMP\A_3136_4000.log PL/SQL procedure successfully completed. SQL>
Vite DBA wrote:Cool link! Thanks for posting
Another method that doesn't involve undocumented packages and sys tables is using the preprocessor feature with external tables.
Solomon Yakobson wrote:Solomon,
Issue is you have to pass directory name somehow.
Directory created. Table created. MOD_DT MOD_TIME FILE_TYPE FILE_SIZE FILE_NAME --------- ---------- ---------- ---------- ---------------------------------------- 15-FEB-13 06:59 AM <DIR> . 15-FEB-13 06:59 AM <DIR> .. 14-FEB-13 01:15 PM <DIR> archive 14-FEB-13 01:17 PM 2,048 hc_orcl.dat 22-DEC-05 04:07 AM 31,744 oradba.exe 04-MAR-13 06:51 AM 2,610 oradim.log 14-FEB-13 01:38 PM 1,536 PWDorcl.ora 04-MAR-13 06:50 AM 3,584 SPFILEORCL.ORA 8 rows selected. Directory dropped. Table dropped.
Muthu wrote:Why does your thread look so much similar to this one- how to read the file name from directory... ?
I have a doubt on reading file name.
I have 10 pdf files in the dir '/d01/tem/'
I need to get/load those 10 file names using PL/SQL. Meaning I need to get the file names only not the file contents...
Please help me to achieve this....
Thanks and Regards,
Solomon Yakobson wrote:Thi ssolution creates objects in the SYS schema, which is widely regarded as bad practice, and also involves granting rights on a object which shows up in the [url http://docs.oracle.com/cd/B19306_01/server.102/b25166/reports.htm#DVADM70936] Data Vault's Access to Sensitive Objects Report. So I do not think this ahould be regarded as a safe approach, compared to writing a java stored procedure.
There is no public synonym for DBMS_BACKUP_RESTORE, so you need to prefix it with SYS and make sure your user has execute privilege on the package. This will fix call to DBMS_BACKUP_RESTORE, but you have another issue. - fixed tables. Only SYS can read them. You'd have to login as SYSDBA, create a view around x$krbmsft and grant select on it to your user.