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

Get a PLSQL code dump of all your Oracle database code.

I used to work as a part time DBA and when I was doing my supposedely menial tasks like unlocking accounts. One of the frequent request was to get an code for the object a Function or Package or Trigger. While this was trivial task to get an single object code it had to be quick. I never bothered to write any code or script to get that.  However one day a developer came and asked me for a code dump for a whole schema ( We had 2500 eligible objects). What he wanted was the code dump for every plsql object stored in the system. This included triggers, functions, procedures , packages. So I searched the internet and came up with a code to do that. It was fairly small 10 liner code which was astonishing. Curiously I never saw the function used anywhere. The function clob2file is from package dbms_xslprocessor. This one is available in oracle 11g so if you ever found yourself yearning to dump a clob object to file you should try using this. There was just a little problem I wante...
Post a Comment
Read more

Oracle bulk insert with a single insert statement ignoring errors.

Starting from Oracle database release 10g you can have DML's succeed with partial data failures. How ? Default reaction of any bulk Insert or Update to an excption is atomic failure. i.e. If you were inserting 100 rows and one of the row fails to insert due to some constraint violation or duplicate records or any other check stuff. Your whole Insert fails. Of course you can write a quick PLSQL block with a LOOP and a null exception handler/logger which will basically ignore the exception or write it down somewhere and carry out your next loop iteration insertion. But first this makes you write the code for a loop and is more resource hogging. You should not have to start coding blocks everytime you want such data insertion. Below is how you can handle it. First you need to create a table for error logging it can be in any schema. Ex. EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('EMPLOYEES' , 'EMPL_ERROR'); -- FOR CREATING ERROR LOG TABLE : TO LOG ERRORS DURING...
Post a Comment
Read more