1 2 Previous Next 15 Replies Latest reply: Jul 12, 2012 10:22 AM by 947261 RSS

    How should I declare file location for file io

    947261
      Below code gives ORA-29280 error. I don't understand how can I declare the location of files? because I don't know what is the main point of oracle?
      set serveroutput on;
      declare
        file_id utl_file.file_type;
      begin  
        file_id := utl_fıle.fopen('C:\Users\Retail\Desktop\Mey_icki', 'mey_report.txt', 'R');
      end;
      In addition, I also create direcory but I dont know where it is?

      CREATE DIRECTORY test_dir AS '/test/source';


      When I log in as sys I get below result
      SQL> host cd
      C:\app\Retail\product\11.1.0\db_1\BIN
      What should I do?
        • 1. Re: How should I declare file location for file io
          sb92075
          944258 wrote:
          Below code gives ORA-29280 error. I don't understand how can I declare the location of files? because I don't know what is the main point of oracle?
          set serveroutput on;
          declare
          file_id utl_file.file_type;
          begin  
          file_id := utl_fıle.fopen('C:\Users\Retail\Desktop\Mey_icki', 'mey_report.txt', 'R');
          end;
          In addition, I also create direcory but I dont know where it is?

          CREATE DIRECTORY test_dir AS '/test/source';
          both the directory & file must reside on DB Server filesystem
          • 2. Re: How should I declare file location for file io
            947261
            Ok, my db is in my local machine, but I can't find it?
            • 3. Re: How should I declare file location for file io
              sb92075
              944258 wrote:
              Below code gives ORA-29280 error. I don't understand how can I declare the location of files? because I don't know what is the main point of oracle?
              set serveroutput on;
              declare
              file_id utl_file.file_type;
              begin  
              file_id := utl_fıle.fopen('C:\Users\Retail\Desktop\Mey_icki', 'mey_report.txt', 'R');
              end;
              In addition, I also create direcory but I dont know where it is?

              CREATE DIRECTORY test_dir AS '/test/source';
              statement above creates internal database object & does NOTHING at the OS level.
              You must manually
              mkdir /test/source
              in order to proceed with an PL/SQL code.
              • 4. Re: How should I declare file location for file io
                947261
                I am using windows and what should I do in order to open a file in my desktop?
                • 5. Re: How should I declare file location for file io
                  sb92075
                  944258 wrote:
                  I am using windows and what should I do in order to open a file in my desktop?
                  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:241814624807
                  • 6. Re: How should I declare file location for file io
                    947261
                    thats not my question.

                    Guys do anyone know how to locate a file? I use windows and I install oracle on my machine, so I just want to read file using sql developer, but It cant find the file location?
                    • 7. Re: How should I declare file location for file io
                      sb92075
                      944258 wrote:
                      thats not my question.

                      Guys do anyone know how to locate a file?
                      some, many, most Windows users know how to use Windows GUI File Finder Wizard.
                      Try using Windows HELP to gain a clue or two.
                      • 8. Re: How should I declare file location for file io
                        sybrand_b
                        Why asking this Windows question in a forum on Oracle SQL?

                        -------------
                        Sybrand Bakker
                        Senior Oracle DBA
                        • 9. Re: How should I declare file location for file io
                          947261
                          But I am using pl/slq in order to get file therefore I opened this thread in under this category


                          When I log in as sys I get below result
                          SQL> host cd
                          C:\app\Retail\product\11.1.0\db_1\BIN
                          What should I do?

                          Edited by: 944258 on 12.Tem.2012 07:53
                          • 10. Re: How should I declare file location for file io
                            sybrand_b
                            Your 'issue' is lack of knowledge of Windows and has 0 to do with Oracle.
                            Please do not clutter up this Oracle SQL forum with off-topic questions.

                            ---------
                            Sybrand Bakker
                            Senior Oracle DBA
                            • 11. Re: How should I declare file location for file io
                              947261
                              I am just asking what should I do?

                              I want to do file io with pl/sql. I am not clutter up anything.
                              • 12. Re: How should I declare file location for file io
                                sybrand_b
                                Your question in this thread was:
                                Guys do anyone know how to locate a file? I use windows and I install oracle on my machine, so I just want to read file using sql developer, but It cant find the file location?
                                This has NOTHING to do with Oracle.
                                Please stop abusing this forum with off-topic questions.
                                Next time you will ask how you can connect an USB drive to your system.
                                ------------
                                Sybrand Bakker
                                Senior Oracle DBA
                                • 13. Re: How should I declare file location for file io
                                  947261
                                  Anyway I found the problem,

                                  I have to create directory first then use the directory name instead of path name.



                                  By the way, do not order me again!! This is not your forum!!! This is Oracle forum and I am not abusing the forum but you are abusing it now!!! You have to respect people. You can not order me due to I am new user.

                                  Anyway, the problem has been resolved. Don't extend the situation!
                                  • 14. Re: How should I declare file location for file io
                                    BluShadow
                                    944258 wrote:
                                    I am just asking what should I do?

                                    I want to do file io with pl/sql. I am not clutter up anything.
                                    Simply like this... (example)...

                                    As sys user:
                                    create or replace directory MYDIR as 'c:\myfiles';
                                    grant read,write on directory MYDIR to myuser;
                                    As myuser:
                                    declare
                                      fh utl_file.file_type;
                                      v_line VARCHAR2(32767);
                                      v_data1 VARCHAR2(200);  -- assume this file has 2 columns of data
                                      v_data2 VARCHAR2(200);
                                    begin
                                      utl_file.fopen('MYDIR', 'myfile.csv', 'r', 32767);
                                      <<file_read_lines>>
                                      loop
                                        begin
                                          utl_file.get_line(fh, v_line);
                                          v_data1 := ltrim(rtrim(regexp_substr(v_line, '[^,]+', 1, 1),'"'),'"');
                                          v_data2 := ltrim(rtrim(regexp_substr(v_line, '[^,]+', 1, 2),'"'),'"');
                                          -- do what you want with the data...
                                          dbms_output.put_line('Col1 : '||v_data1||', Col2 : '||v_data2);
                                        exception
                                          when no_data_found then
                                            exit file_read_lines;
                                        end;
                                      end loop;
                                      utl_file.fclose(fh);
                                    end;
                                    /
                                    The directory must exist on the data server (if the database is installed on your local machine then that must be your local machine) and the user under which oracle runs on that server must have operating system level access to that directory.

                                    The directory object name used in the FOPEN statement must be in UPPERCASE as it's referring to an object in the data dictionary which are upper case by default (unless you're stupid enough to create it with double quotes).
                                    1 2 Previous Next