Oracle string quote escape tool for plsql and sql.

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

Comments

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

Oracle Connect By Clause to Perform Hierarchical Queries.

If we want to perform hierarchical queries we need to use connect by clause in our sql query. Hierarchical query is any query wherin you want to display parent child relationship with the case that a parent may have multiple children and those have their own creating a tree like structure. One classic scenario highlighted on almost all sites is employee table wherein employee's manager is stored in the employee row the manager id is again one of the employee in the employee table. But we are not going to take it as you can reference it almost on any site that hosts oracle based content. We are going to do a query based on DBA_DEPEDENCIES or ALL_DEPENDANCIES if you do not have access to former. In DBA_DEPENDANCY table we have hierarchy of REFERENCED_NAME(parent) and the referring objects in column NAME(child ) that have dependency on the parent. with this in mind we would need to set our connect by clause as prior NAME= REFERENCED_NAME This confirms that REF

Modify Windows file Change / modified date.

There is no straightforward command in windows to change the file modify date (Commonly referred to as timestamp) . I have spent significant time searching UNIX touch -t like command equivalent in windows but could not find a command that could achieve this feat. However there is a a way around through a VBS . This VBScript would let you modify the file timestamp (Modified date ) to the one you give in the parameter. In below example it would be 29-Oct-2015. Sub ChangeModifiedDate(strFolder, strFile, dteNew) Dim oShell Dim objFolder Set oShell = CreateObject("Shell.Application") Set oFolder = oShell.NameSpace(strFolder) oFolder.Items.Item(strFile).ModifyDate = dteNew End Sub changemodifieddate "D:\myfolder\fmx","new_empl.fmx","29-10-2015" So here are the things to change. Change the last line 1 st argument is the windows folder. 2nd Argument is the file name 3rd Argument is the Date modified desired. One needs

QueryLight

Search This Blog