This discussion is archived
14 Replies Latest reply: Mar 11, 2013 5:47 AM by APC RSS

How to read the file name....

Muthu Newbie
Currently Being Moderated
Hello all,

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,
Muthu
  • 1. Re: How to read the file name....
    jeneesh Guru
    Currently Being Moderated
    You can use JAVA inside the database- it is simple

    AskTom:reading files in a directory -- how to get a list of available files
  • 2. Re: How to read the file name....
    Muthu Newbie
    Currently Being Moderated
    Thanks Jeneesh,

    But I am trying through PL SQL. I tried by using below program but its giving error.

    SQL> DECLARE
    2 p_directory VARCHAR2(1024) := '/tmp';
    3 p_null VARCHAR2(1024);
    4 BEGIN
    5 DBMS_BACKUP_RESTORE.searchFiles(p_directory, p_null);
    6 FOR x IN (select fname_krbmsft fname from x$krbmsft) LOOP
    7 DBMS_OUTPUT.PUT_LINE(x.fname);
    8 END LOOP;
    9 END;
    10 /
    DBMS_BACKUP_RESTORE.searchFiles(p_directory, p_null);
    *
    ERROR at line 5:
    ORA-06550: line 5, column 1:
    PLS-00201: identifier 'DBMS_BACKUP_RESTORE.SEARCHFILES' must be declared
    ORA-06550: line 5, column 1:
    PL/SQL: Statement ignored
    ORA-06550: line 6, column 43:
    PL/SQL: ORA-00942: table or view does not exist
    ORA-06550: line 6, column 11:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 7, column 22:
    PLS-00364: loop index variable 'X' use is invalid
    ORA-06550: line 7, column 1:
    PL/SQL: Statement ignored

    Please guide me what could be the issue.

    Thanks and Regards,
    Muthu
  • 3. Re: How to read the file name....
    EdStevens Guru
    Currently Being Moderated
    Muthu wrote:
    Thanks Jeneesh,

    But I am trying through PL SQL. I tried by using below program but its giving error.
    You obviously didn't read the link that Janeesh provided. Tom's very first statement is "We cannot do this with PLSQL directly "

    <snip>
    Please guide me what could be the issue.
    Go back and read the link.

    If your only tool is a hammer, every problem looks like a nail. It's Time you started to learn how to use more tools.
    >
    Thanks and Regards,
    Muthu
    Edited by: EdStevens on Mar 2, 2013 8:16 AM
  • 4. Re: How to read the file name....
    Solomon Yakobson Guru
    Currently Being Moderated
    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.
    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>
    SY.
  • 5. Re: How to read the file name....
    Vite DBA Pro
    Currently Being Moderated
    Hi Muthu,

    Another method that doesn't involve undocumented packages and sys tables is using the preprocessor feature with external tables.

    http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21nanda-312277.html

    Regards
    Andre
  • 6. Re: How to read the file name....
    riedelme Expert
    Currently Being Moderated
    Vite DBA wrote:
    Another method that doesn't involve undocumented packages and sys tables is using the preprocessor feature with external tables.

    http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21nanda-312277.html
    Cool link! Thanks for posting
  • 7. Re: How to read the file name....
    Solomon Yakobson Guru
    Currently Being Moderated
    Issue is you have to pass directory name somehow.

    SY.
  • 8. Re: How to read the file name....
    riedelme Expert
    Currently Being Moderated
    Solomon Yakobson wrote:
    Issue is you have to pass directory name somehow.

    SY.
    Solomon,

    Can you explain your comment? I went to the link and got the directory example of external table file preprocessing to work without much trouble - pretty much what the OP asked for. I had to create a directory but did not have to pass one anywhere

    What am I missing?
    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.
  • 9. Re: How to read the file name....
    Muthu Newbie
    Currently Being Moderated
    Hello Solomon,

    Thanks for your reply,

    I did as per your direction but, I couldn't see the output for the below query.

    SQL> set serveroutput on
    SQL> DECLARE
    2 p_directory VARCHAR2(1024) := ' /tmp';
    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 /

    PL/SQL procedure successfully completed.

    What i'm missing..

    Please guide me....

    Regards,
    Muthu
  • 10. Re: How to read the file name....
    Muthu Newbie
    Currently Being Moderated
    Please any one suggest solution for the above issue....

    Regards,
    Muthu
  • 11. Re: How to read the file name....
    ranit B Expert
    Currently Being Moderated
    Muthu wrote:
    Hello all,

    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,
    Muthu
    Why does your thread look so much similar to this one- how to read the file name from directory... ?
    >
    hi,
    i have 10 pdf files in the dir '/d01/oradata/atlanta_gi/2007_10_1_12_31'
    i need to get/load those 10 file names using PL/SQL
    how to achive this?

    Note: file names only not the file contents...
  • 12. Re: How to read the file name....
    APC Oracle ACE
    Currently Being Moderated
    Muthu wrote:
    Please any one suggest solution for the above issue....
    What results do you get if you just run the query?
    select fname_krbmsft fname from sys.v$krbmsft
    Cheers, APC
  • 13. Re: How to read the file name....
    Muthu Newbie
    Currently Being Moderated
    Hi APC,

    Thank you for your reply,

    Your query output..

    SQL> select fname_krbmsft fname from sys.v$krbmsft;

    no rows selected

    Regards,
    Muthu
  • 14. Re: How to read the file name....
    APC Oracle ACE
    Currently Being Moderated
    Solomon Yakobson wrote:
    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.
    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.


    Cheers, APC

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points