9 Replies Latest reply: Mar 11, 2010 9:58 AM by 728534 RSS

    create a text file from stored procedure

    658086
      hello,
      I need to create a logfile with name YYYYMMDD.log into c:\mydir\MYPARAM\

      In my oracle package I've written this (not working) code:



      FUNCTION mysf (parm IN VARCHAR2) RETURN INTEGER IS

      logfile     utl_file.file_type;
      myline      VARCHAR2(200);
      logdir     VARCHAR2(200);
      v_logfile     VARCHAR2(50);
           
      BEGIN

      DBMS_OUTPUT.ENABLE;

      logdir := 'c:\mydir\' || parm || '\';

      sql_txt := 'SELECT TO_CHAR(sysdate, ''yyyymmdd'') FROM DUAL';
      EXECUTE IMMEDIATE sql_txt INTO v_logfile;
                
      logfile := utl_file.fopen(logdir, v_logfile || '.log', 'A');

      myline := 'bla bla';
      utl_file.put_line(logfile, myline);

      ...


      but no file is created...anything....

      what is my mistake?

      thanks a lot

      Edited by: itmick on 11-mar-2010 6.15

      Edited by: itmick on 11-mar-2010 6.15

      Edited by: itmick on 11-mar-2010 6.15
        • 1. Re: create a text file from stored procedure
          sybrand_b
          Your mistake is you expect the file on your client pc.
          However, utl_file will create files on the database server only.

          ----------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: create a text file from stored procedure
            658086
            but there is no file on db server...

            is my code wrong?
            • 3. Re: create a text file from stored procedure
              Marwim
              Are you sure your DB-Server has an Oracle Directory named 'c:\...'
              SELECT * FROM all_directories
              gives you a list of valid directories for utl_file (If the DBA has granted WRITE to your user).

              If you need a new directory you have to create it on your server file system, grant write access to your database and then create an new Oracle Directory
              CREATE DIRECTORY mydir AS 'c:\mydir';
              GRANT READ,WRITE ON DIRECTORY mydir TO myuser;
              Regards
              Marcus

              Edited by: Marwim on 11.03.2010 15:42
              • 4. Re: create a text file from stored procedure
                658086
                I have mydir

                or better, previously I have created c:\mydir hoping that its subdirectories are achievable...(I need c:\mydir\MYPARAM...)

                but even if I create the right directory no file is created.....

                :(


                Is my code correct?
                • 5. Re: create a text file from stored procedure
                  Marcus2014
                  Are you getting any errors? Post your code so we adn check it out.
                  You have to define an Oracle directory object in the database for every folder you are interested in writing to.
                  logdir has to be an oracle directory, not a file specification.

                  Edited by: Marcus 2014 on Mar 11, 2010 9:07 AM
                  • 6. Re: create a text file from stored procedure
                    Marwim
                    Did you create the directory as described above?
                    If you need a new directory you have to create it on your server file system, grant write access to your database and then create an new Oracle Directory
                    CREATE DIRECTORY mydir AS 'c:\mydir';
                    GRANT READ,WRITE ON DIRECTORY mydir TO myuser;
                    And no, subdirectories are not available, you have to create an Oracle Directory for each subdirectory.

                    Regards
                    Marcus
                    • 7. Re: create a text file from stored procedure
                      728534
                      Hi,
                      Are you sure there is a directory??
                      there are 2 things involved.
                      1) You should go and create a directory called c:\mydir\MYPARAM
                      So if you click on start->run and type c:\mydir\MYPARAM (hit enter) you should be inside that directory.
                      2) Assuming step 1 is succesfull only 50% of the work is done. Now still you have to login as a super user in oracle
                      and issue these commands
                      CREATE DIRECTORY mydir AS 'c:\mydir\myparam';
                      GRANT READ,WRITE ON DIRECTORY mydir TO myuser;

                      Then you run your piece of code and paste the error you get, if any.

                      Regards,
                      Bhushan
                      • 8. Re: create a text file from stored procedure
                        658086
                        thanks to all of you guys

                        now it runs, I have only removed mydir (with command DROP DIRECTORY mydir) and recreate it....

                        do you know if it's possible check if a directory exists runtime?

                        I thought this:

                        select count(*) from all_directories where directory_name = 'mydir';

                        is there another way?

                        thanks again!
                        • 9. Re: create a text file from stored procedure
                          728534
                          Hi,
                          select count(*) from all_directories where directory_name = 'mydir';
                          Will always give you ZERO i guess :)
                          it should be
                          select count(*) from all_directories where directory_name = upper('mydir');
                          Cheers!!!
                          Bhushan