Usually, getting the data out of the database was fairly easy with a SQL select statement, yet, bringing the data into an excel worksheet was sort of a recurring PITA: I would start SQL Developer, execute the select statement, copy the result set (ctrl-c), open Excel, paste the result set (ctrl-v) then I'd adjust the widths of the columns, and only then I'd save the resulting excel sheet.
Not that these steps are too hard, but I always felt that should be easier. So, I have written the procedure xlsx_writer.sql_to_xlsx. This procedure takes an SQL statement and the name of an Excel file to be written, executes the SQL statement and writes the Excel file.
In SQL*Plus, that would look like:
Of course, this can be written in one line, I have used four lines because of the width limit in this blog.
Why writing a own procedure every SQL Tool can do that with a click
ReplyDeleteBecause 1) it's fun and 2) my tool is SQL*Plus and SQL*Plus cannot do that with a click.
ReplyDeletewhat type of license has it ?
ReplyDeleteThanks it helps me a lot.
ReplyDeleteI have some doubts
What is the significance of book_r, kindly let me know the pseudo code for this because am getting an error saying BOOK_r must be declared
Can u please help me out for xlsx_writer
ReplyDeletebook_r is defined in the package specification @Naidu
ReplyDeletecompile xlsx_writer.pks first then xlsx_writer.pkb (standard PL/SQL practice)
ReplyDeleteCan you write the excel file on the CLIENT side? Thank you
ReplyDelete