14 Replies Latest reply: Mar 11, 2013 7:47 AM by APC RSS

    How to read the file name....

    Muthu
      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
          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
            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
              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
                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
                  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
                    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
                      Issue is you have to pass directory name somehow.

                      SY.
                      • 8. Re: How to read the file name....
                        riedelme
                        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
                          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
                            Please any one suggest solution for the above issue....

                            Regards,
                            Muthu
                            • 11. Re: How to read the file name....
                              ranit B
                              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
                                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
                                  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
                                    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