Skip to main content

Excel export for Oracle database.

This tool / utility helps you export your data based on query resultset in the excel format.

Requirments : JRE 1.6 or above

You need a JDBC URL in the format  : 
jdbc:oracle:thin:@host.com:1521:SID
to connect with Oracle database.

Provide filename , username , password , and query you need your data to be extracted from and that's it .

You can also have list of jdbc urls stored in a file. Your query would run against all the databases listed in the file and you will get one combined file. This feature is very useful for having data extracted from multiple databases.

Download latest version from source forge .

https://sourceforge.net/projects/oracle-excel-extract/files/
Download Oracle Excel Extract
Below are usage options. For help use -h : Ex java -jar Oracle_xls_extract.jar -h 

java -jar oracle_xls_extract.jar



Picked up _JAVA_OPTIONS: -Djava.net.preferIPv4Stack=true usage: Main -f,--jdbcfile <arg> File path containing jdbc urls file format should be bmsd_dev1 jdbc:oracle:thin:@ftdcsoradbs01.ftdc.cummins.com: 1521:BMSD1 -h,--help show help. -j,--jdbcUrl <arg> Database JDCB URL EX jdbc:oracle:thin:@arlioradbs1.arli.cummins.com:15 21:prd1 -o,--orausrpass <arg> Database usermname/password with slash in oracle format .. Use either (u and p ) or o -p,--password <arg> Database Password -q,--query <arg> SQL Query -r,--result <arg> File Result output -t,--filetype <arg> File Format / Type (CSV ,XLS) Default : XLS -u,--username <arg> Database UserName USAGE : /usr/java6/jre/bin/java -jar $HOME/oracle_xls_extract.jar -u uname -p xx -j jdbc:oracle:thin:@arlioradbs1.arli.cummins.com:1521:prd1 -q "select * from bms_preferences" /usr/java6/jre/bin/java -jar $HOME/oracle_xls_extract.jar -u uname -p xx-q "select * from bms_preferences" </arg></arg></arg></arg></arg></arg></arg></arg>

Comments

Post a Comment

Please leave your relevant comments and questions only.

Popular posts from this blog

Use the Microsoft Visual Studio Code(VScode) with Java Maven project.

The VS Code is the go to editor now a days for the latest technologies and scripting options. VS Code as we all know works seamlessly with Python and JavaScripts, and offcourse the .NET family. But VS Code has many extensions which makes it big Java IDEs run for their money. Lets explore what we could do with VSCode if we are Java developer. Install VScode , no brainer. Jus type download Visual Studio code in google. Alrright once we have VSCode. Open it. Install Maven and Java extension. 2. Go to files and a small window will now appear on left side navigation bar. 3. Select New Maven project. 4. Select the archtype from dropdown. 5. As usual input the grpid, artifactid etc. 6. Done. Go through a video which has much detailed navigation flow. Till next time !!!
Post a Comment
Read more

Cyclic blocking session removal script written by me for Oracle Database.

Plenty of times we have cyclic database blocking sessions. We have the script which runs in loop and kills them , only to see new blocking sessions have resurfaced. This is a classic scenario that happens many time due to poor application design. A was blocking B , B was blocking C. Unless you have some automated blocking session clearing script. You would run a script at point in time killing session of A. Giving the lock ownership to B.   B could realize it late that he has the lock and not commit his work. So DBA again goes in and sees block kills B's session. In meantime Frustrated A logs in and start his activity again queuing him up in wait for lock retrieval . This goes on and you end up running the script 5-10 times to kill these sessions to finally clear the blocks. Note this is not a deadlock which is normally apprehended and identified by oracle. In such case we had written below plsql to clear the sessions. This basically goes in and check for locks ever...
Post a Comment
Read more

Introduction to Oracle Analytical functions , rank , denserank.

In this video we tried to demonstrate how oracle anaytical function works. How to do Top N query with oracle's rank and dense_rank function. Also explained is how outputs and functionality of rank and dense_rank differs , what should be used when. The demonstration video is from Linux Mint OS and I have used my own Query Light application. Hope you find it enlightening.   Here are some of the screen prints.  Use of Oracle Rank Analytical Function. Oracle Top N Query using rank analytical functions.
Post a Comment
Read more