This discussion is archived
14 Replies Latest reply: Jul 25, 2013 6:03 AM by michaelrozar17 RSS

DBMS result in exel

Jhon Explorer
Currently Being Moderated

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 Journeyer
    Currently Being Moderated

    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 Pro
    Currently Being Moderated
  • 3. Re: DBMS result in exel
    Marwim Expert
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    Hi,

     

    Any body  help.

  • 6. Re: DBMS result in exel
    Marwim Expert
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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;