QueryLight

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 !!!

How often do we have to write an insert with explicit column list: May be because the source we are inserting from do not have all the columns. Or the destination table has that dreaded long column that you have to exclude. Keep below SQL handy (Oracle database 11g onwards only). To quickly get a comma seperated list of columns for a table. SELECT LISTAGG(column_name , ',') WITHIN GROUP (ORDER BY column_id ) column_list FROM all_Tab_columns where table_name = upper(:TABLE_NAME) and owner = upper(:OWNER) GROUP BY table_name;

Here are the fixes and new development done. Latest Update and Fixes Added ability to write xlsx in new office xlsx format. Added ability to view code with F4. Added additional describe capabilities with F4. Download Page :  Go to Project HomePage https://sourceforge.net/projects/query-light-light-orcl-client/

Many production oracle database instances do not have auto incrementing tablespaces. You would need to know beforehand before users start getting ORA-01688 unable to extend tablespace errors. You would probably have some sort of monitoring on the tablespaces already , OEM shows alerts about tablespaces readily as well. But its good to have a query handy to quickly check the tablespace usages you have in your system. select df.tablespace_name ,totalusedspace "totalusedspace_mb" , (df.totalspace - tu.totalusedspace) "Free_MB", df.totalspace "Total_MB", round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name ;

Oracle string quote escape tool : There will be a times when you are writing dynamic sql and that sql  has lot of quote constants in where clause or in your select list , escaping these quotes can be quite cumbersome if you don't have the right tool. If you have a large query or string which has quotes in it  and you do not want to bother checking the escapes and want to use the escaped query / string readily in your plsql. Below tool will help you generate escaped string from Oracle  plsql standpoint , just copy and paste it in your code It's not ours to take. Escape quotes

There are times when writing a code when we want to write files with Oracle utl_file package, but we do not have access to v$parameter tables or do not know if the directory object exists in Oracle or not. There are also many times cases when you are tracing error in multi hundres of lines of code and do not know if the file writing is causing error or its some other code in your plsql. One can quickly test file writing is happening or not with the help below small code. DECLARE ---------- Oracle utl file write test. L_HANDLER UTL_FILE.FILE_TYPE; BEGIN L_HANDLER := UTL_FILE.FOPEN ('/xfer/data', 'utl_test.txt', 'W'); UTL_FILE.PUTF (L_HANDLER, 'UTL_FILE Test Success'); UTL_FILE.FCLOSE (L_HANDLER); END;