5 Replies Latest reply: Aug 14, 2009 4:06 PM by Arunkumar Ramamoorthy-Oracle RSS

    how to read the file name from directory...

    marias
      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...
        • 2. Re: how to read the file name from directory...
          GVR
          What version are you on?

          Check [http://www.tek-tips.com/viewthread.cfm?qid=1416869&page=8]
          • 3. Re: how to read the file name from directory...
            marias
            i am getting this error...

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

            SQL>
            • 4. Re: how to read the file name from directory...
              JustinCave
              What version of Oracle are you using? I would tend to guess that either
              - You are using a version of Oracle that predates the DBMS_BACKUP_RESTORE package
              - The DBMS_BACKUP_RESTORE package has not been installed
              - Or you don't have access to that package

              I would strongly prefer the Java based solution over using DBMS_BACKUP_RESTORE for anything other than its intended purpose. The Java solution is likely to be far more robust-- X$ tables are undocumented and subject to change at any time without notice.

              Justin
              • 5. Re: how to read the file name from directory...
                Arunkumar Ramamoorthy-Oracle
                marias wrote:
                i am getting this error...

                SQL> DECLARE
                2 p_directory VARCHAR2(1024) := '/d01/oradata/atlanta_gi/2007_10_1_12_31';
                3 p_null VARCHAR2(1024);
                4
                5 BEGIN
                6 SYS.DBMS_BACKUP_RESTORE.searchFiles(p_directory, p_null);
                7
                8 FOR x IN (select fname_krbmsft fname from x$krbmsft) LOOP
                9 DBMS_OUTPUT.PUT_LINE(x.fname);
                10 END LOOP;
                11 END;
                12 /
                SYS.DBMS_BACKUP_RESTORE.searchFiles(p_directory, p_null);
                *
                ERROR at line 6:
                ORA-06550: line 6, column 2:
                PLS-00201: identifier 'SYS.DBMS_BACKUP_RESTORE' must be declared
                see if you have privilege to access SYS.DBMS_BACKUP_RESTORE package. Try DESC SYS.DBMS_BACKUP_RESTORE and see if you are able to see the procedures and functions in it.
                ORA-06550: line 6, column 2:
                PL/SQL: Statement ignored
                ORA-06550: line 8, column 43:
                PL/SQL: ORA-00942: table or view does not exist
                Do you have the table x$krbmsft? try simple select * from x$krbmsft.
                ORA-06550: line 8, column 11:
                PL/SQL: SQL Statement ignored
                ORA-06550: line 9, column 24:
                PLS-00364: loop index variable 'X' use is invalid
                ORA-06550: line 9, column 3:
                PL/SQL: Statement ignored

                SQL>