14 Replies Latest reply: Jul 25, 2013 8:03 AM by michaelrozar17 RSS

    DBMS result in exel

    Jhon

      hi ,

       

      I write one procedure which return value from dbms_output.

      i want to generate value of dbms_output in excel ..

       

      let example

      declare

      num number:=10;

      begin

      dbms_output.put_line('number is:'||num);

      end;

       

      want dbms out put in excel by write code not by manualy

        • 1. Re: DBMS result in exel
          BCV

          Hi,

          Using as_xlsx package, You can generate Result in XL files.

           

          begin

          as_xlsx.QUERY2SHEET('select * from emp'); -- write your SQL Query here

          as_xlsx.SAVE('PS_TEMP_DIR','my.xlsx'); -- mention your directory here, give name for your XL File.

          end;

           

          Cheers.

          • 2. Re: DBMS result in exel
            michaelrozar17
            • 3. Re: DBMS result in exel
              Marwim

              Hello,

               

              there are examples in the FAQ Re: 5. How do I read or write an Excel file?

              In the end it depends whether you want to generate the file at the client or at the server. So without further information you won't get specific answers.

               

              Regards

              Marcus

              • 4. Re: DBMS result in exel
                Jhon

                Hi michal/all

                 

                 

                firstly ,I want to clear all picture about this issue.. i  fire a lot of query daily basis and then paste output in excel sheet manually .so i thought write procedure where we select all table which i need ,then export dbms_out into excel sheet in my desktop folder....

                 

                1> currently I am working in oracle xe 10g express database for testing purpose(Home work) .after that we will work on oracle 10 g DB

                2>want to generate dbms_output result in excel sheet in my desktop folder.

                 

                i didn't  understand your reply.

                1>it always show variable must be declare. 

                2> where direcotry will create.


                if possible then suggest me with one simple example.


                Regards

                jhon...

                • 5. Re: DBMS result in exel
                  Jhon

                  Hi,

                   

                  Any body  help.

                  • 6. Re: DBMS result in exel
                    Marwim

                    so i thought write procedure where we select all table which i need ,then export dbms_out into excel sheet in my desktop folder...

                     

                    The easiest would be to run the SELECT in SQL*PLUS and SPOOL the output to a file at your desktop. With MARKUP HTML you get an output that can be opened in Excel.

                     

                    HTML colum format

                     

                    Regards

                    Marcus

                    • 7. Re: DBMS result in exel
                      michaelrozar17

                      i  fire a lot of query daily basis and then paste output in excel sheet manually

                      If you want to automate then one of the option would be to write a shell script that connects to your database and issue your SELECT statement with each columns separated by a delimiter and output it to a flat/text file. Then you would need to transfer the file from the Unix box where you ran the script to your local machine. Sample script:

                      $ cat con_sql.sh
                      #!/bin/ksh
                      sqlplus -s abc/abc_123@dbname <<EOF > /output/path/output_sql.csv
                      select id||','||lname||','||lstatus||','||city||','||state
                      from XXXX;
                      exit;
                      EOF
                      
                      if [ $? -ne 0 ]
                      then
                          echo "Error Connecting"
                      else
                          echo "Success"
                      fi
                      $
                      
                      

                      Running the above script will populate data into file located at /output/path/output_sql.csv. After transferring to the local machine you can open the file using the Excel sheet (give the proper delimiter while opening if the delimiter is not comma)

                      • 8. Re: DBMS result in exel
                        BCV

                        HI,

                                 Sorry This as_xlsx package is not available in 10g.

                        There is no way to generate the result in Files , other than Spooling Option in 10g.

                         


                        • 9. Re: DBMS result in exel
                          EdStevens

                          Jhon wrote:

                           

                          Hi,

                           

                          Any body  help.

                          Patience, Grasshopper

                           

                          You posted this follow-up a mere 41 minutes after your previous post. 

                           

                          This forum is not a chat line, and it is not paid support.

                           

                          Everyone here has a job for which they are paid, and this forum is not it.

                           

                          No one is responsible for monitoring it and giving a quick response. 

                           

                          Furthermore, it is a global forum.  The person with the information you seek may very well live 20 time zones away from you and was going to bed just as you posted. He will not even see your post for several more hours. 

                           

                          Your original post went up in the middle of the night for half the world.

                           

                          No one with the information you seek is deliberately withholding it until you sound sufficiently desperate.

                          • 10. Re: DBMS result in exel
                            Marwim

                            There is no way to generate the result in Files , other than Spooling Option in 10g.

                            There are ways to generate this kind of files. Example:

                            XML_SPREADSHEET can generate the output into a CLOB and you can spool the content of a CLOB using dbms_output (Output CLOB), though I would not recommend this solution.

                             

                            BTW: Spooling is the only option to write to the client in any Oracle version when you use SQL*PLUS. You need a program running on the client to write to the client. You cannot do it from a serverside procedure.

                             

                            Regards

                            Marcus

                            • 11. Re: DBMS result in exel
                              Jhon

                              i write below script  ..

                               

                              $ cat exl.sh

                              sqlplus -s hr/hrhr@xe <<EOF > output_sql.csv

                              select deptno||','||dname||','||loc

                              from dept;

                               

                               

                               

                               

                               

                               

                              select ename||','||empno||','||job from emp;

                               

                               

                               

                               

                               

                               

                              exit;

                               

                               

                              if [ $? -ne 0 ]

                              then

                                  echo "Error Connecting"

                              else

                                  echo "Success"

                              fi

                              $

                               

                              1>it generate output in same path whare we write scrpit .but can i genrate output.csv file  in c:/floder name .if yes then what i write.

                              2>emp table return 14 row ,after 10 row it again show column name.how i eliminate this.

                              • 12. Re: DBMS result in exel
                                Jhon

                                Hey,

                                 

                                I am pl/sql develpoer. so why i use shell script.

                                 

                                can pl/sql  provide solution  to generate dbms output in excel sheet..  pl/sql have any procedure or package for that.

                                • 13. Re: DBMS result in exel
                                  Marwim

                                  Jhon wrote:

                                   

                                  Hey,

                                   

                                  I am pl/sql develpoer. so why i use shell script.

                                   

                                  can pl/sql  provide solution  to generate dbms output in excel sheet..  pl/sql have any procedure or package for that.

                                  You mean another solution than I showed in my post? XML_SPREADSHEET is pure PL/SQL and the procedure to spool a CLOB with dbms_output is pure PL/SQL.

                                   

                                  How to create a text file from PLSQL on client machine?

                                  • 14. Re: DBMS result in exel
                                    michaelrozar17

                                    1>it generate output in same path whare we write scrpit .but can i genrate output.csv file  in c:/floder name .if yes then what i write.

                                    There are 2 ways to do this:

                                    1. After running the script you would need to transfer the output file manually to your local (using SSH Client etc)

                                    2. You can use FTP command inside the same shell script to transfer files - this way once the output file is generated, the FTP command could start the process of transferring files automatically.

                                    2>emp table return 14 row ,after 10 row it again show column name.how i eliminate this.

                                    This is due to the page size. You can include the below sqlplus settings to avoid such things.

                                    sqlplus -s hr/hrhr@xe <<EOF > output_sql.csv
                                    set feed off
                                    set head off
                                    set pages 0
                                    set verify off
                                    select deptno||','||dname||','||loc
                                    from dept;