Thursday, April 23, 2015

See Trigger Code

Query to see Trigger Code

SELECT text FROM dba_source 
WHERE owner = 'owner name' AND name = 'trigger name' 
ORDER BY line

 Other Important tables
  • All_triggers
  • All_Views
  • All_Tables

Thursday, April 16, 2015

Select a substring in Oracle SQL up to a specific character

Using a combination of SUBSTR and INSTR you can get a substring in Oracle SQL up to a specific character

SELECT SUBSTR('ABC_blahblahblah', 0, INSTR('ABC_blahblahblah', '_')-1) AS output
  FROM DUAL

Result:
output
------
ABC

General Syntax
:

SELECT SUBSTR(t.column, 0, INSTR(t.column, '_')-1) AS output
  FROM YOUR_TABLE t

Thursday, March 12, 2015

check if a string can be parsed to double

http://stackoverflow.com/questions/8564896/fastest-way-to-check-if-a-string-can-be-parsed-to-double-in-java


final String Digits = "(\\p{Digit}+)";
        final String HexDigits = "(\\p{XDigit}+)";

        // an exponent is 'e' or 'E' followed by an optionally
        // signed decimal integer.
        final String Exp = "[eE][+-]?" + Digits;
        final String fpRegex = ("[\\x00-\\x20]*" + // Optional leading
                                                    // "whitespace"
                "[+-]?(" + // Optional sign character
                "NaN|" + // "NaN" string
                "Infinity|" + // "Infinity" string

                // A decimal floating-point string representing a finite
                // positive
                // number without a leading sign has at most five basic pieces:
                // Digits . Digits ExponentPart FloatTypeSuffix
                //
                // Since this method allows integer-only strings as input
                // in addition to strings of floating-point literals, the
                // two sub-patterns below are simplifications of the grammar
                // productions from the Java Language Specification, 2nd
                // edition, section 3.10.2.

                // Digits ._opt Digits_opt ExponentPart_opt FloatTypeSuffix_opt
                "(((" + Digits + "(\\.)?(" + Digits + "?)(" + Exp + ")?)|" +

        // . Digits ExponentPart_opt FloatTypeSuffix_opt
                "(\\.(" + Digits + ")(" + Exp + ")?)|" +

                // Hexadecimal strings
                "((" +
                // 0[xX] HexDigits ._opt BinaryExponent FloatTypeSuffix_opt
                "(0[xX]" + HexDigits + "(\\.)?)|" +

                // 0[xX] HexDigits_opt . HexDigits BinaryExponent
                // FloatTypeSuffix_opt
                "(0[xX]" + HexDigits + "?(\\.)" + HexDigits + ")" +

                ")[pP][+-]?" + Digits + "))" + "[fFdD]?))" + "[\\x00-\\x20]*");// Optional
                                                                                // trailing
                                                                                // "whitespace"

        if (Pattern.matches(fpRegex, val))
            Double.valueOf(val); // Will not throw NumberFormatException
        else {
            // Perform suitable alternative action
        }

Wednesday, March 11, 2015

Selenium and iFrame

Before you try searching for the elements within the iframe you will have to switch Selenium focus to the iframe.
Try this before searching for the elements within the iframe:
driver.switchTo().frame(driver.findElement(By.name("iFrameTitle")));

Oracle updating sequence by a big number

sometimes in DB refresh , the rows are refreshed but corresponding sequences are not refreshed. and the difference may be as huge as 2000-3000. So how can we update the Sequence ?

  1. Can use alter sequence : to modify start value ( not good as different environments will have different definitions)
  2. can write a small java program that selects nextVal from sequence in a for loop 2k-3k
  3. Else can use this
select  level, sequence.NEXTVAL
from  dual 
connect by level <= (select max(pk) from tbl);
 
 

Monday, March 9, 2015

Jasper Report : Working with IN clause in SQL

Today had to work on a jasper report that has a IN clause in SQL.
The issue was , that the parameters were taken from UI in CSV format , so couldnt use the $X approach as described
http://stackoverflow.com/questions/6226447/passing-sql-in-parameter-list-in-jasperreport

The solution was

< parameter name="CNTRY_ID" isForPrompting="true" class="java.lang.String" / >
 < parameter name="CNTRY_ID_PARSED" isForPrompting="true" class="java.lang.String" >
        < defaultValueExpression >< ! [ CDATA [ $P { CNTRY_ID } . replaceAll(",","','") ] ] > < / defaultValueExpression>
 
   
and in the SQL query use it like

 where pt.subacct_id = gs.member_id
   and gs.CNTRY_ID IN ('$P!{CNTRY_ID_PARSED}')
 

Hope that helps

Friday, January 30, 2015

Oracle Reports Unable to Open Reports Builder 11g (Appears Minimized in Taskbar)

This helped me today
http://pitss.com/us/2013/11/25/unable-to-open-reports-builder-11g-appears-minimized-in-taskbar/

And this one
http://stackoverflow.com/questions/18185171/no-pl-sql-translation-for-the-bindtype-given-for-this-bind-variable