14 Replies Latest reply on Jul 25, 2013 1:03 PM by michaelrozar17

    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.

          • 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)

                    1 person found this helpful
                    • 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;