Thursday, December 3, 2015

Oracle SQL

Nice tutorial



We had a scenario , where we broke a master table CLOB column , into multiple rows in child tables VARCHAR2.
Functions LISTAGG and XMLAGG didn't help us , we keep getting 'concatinated string too large' error.... the above link helped us

Wednesday, October 14, 2015

Using Case in WHERE clause

Dynamic Conditional Where Clause in SQL

SELECT < col1 > , < col2 >
FROM < table >
WHERE col1 like DECODE(:Param_one,'ALL','%',:Param_one) or
    NVL(col2,'#') = case
        when :Param_one = 'ALL' then
            '#'
        else
            col2
        end

Monday, September 21, 2015

Oracle Making Key Value Pairs

Returning a 'table' from a PL/SQL function
      
With collections, it is possible to return a table from a pl/sql function.
First, we need to create a new object type that contains the fields that are going to be returned:

create or replace type t_col as object (
  i number,
  n varchar2(30)
);
/


Then, out of this new type, a nested table type must be created.

create or replace type t_nested_table as table of t_col;
/


Now, we're ready to actually create the function:

create or replace function return_table return t_nested_table as
  v_ret   t_nested_table;
begin
  v_ret  := t_nested_table();

  v_ret.extend;
  v_ret(v_ret.count) := t_col(1, 'one');

  v_ret.extend;
  v_ret(v_ret.count) := t_col(2, 'two');

  v_ret.extend;
  v_ret(v_ret.count) := t_col(3, 'three');

  return v_ret;
end return_table;
/


Here's how the function is used:

select * from table(return_table);

     1 one
     2 two
     3 three


Returning a dynamic set

Now, the function is extended so as to return a dynamic set.
The function will return the object_name and the object_id from user_objects whose object_id is in the range that is passed to the function.

create or replace function return_objects(
  p_min_id in number,
  p_max_id in number
)
return t_nested_table as
  v_ret   t_nested_table;
begin
  select
  cast(
  multiset(
    select
      object_id, object_name
    from
      user_objects
    where
      object_id between p_min_id and p_max_id)
      as t_nested_table)
    into
      v_ret
    from
      dual;

  return v_ret;
 
end return_objects;
/


And here's how the function is called.

select * from table(return_objects(37900,38000));

Sunday, September 20, 2015

Oracle Case In-Sensitive like

Since 10gR2, Oracle allows to fine-tune the behaviour of string comparisons by setting the NLS_COMP and NLS_SORT session parameters:
 
SQL> SET HEADING OFF
SQL> SELECT *
  2  FROM NLS_SESSION_PARAMETERS
  3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT
BINARY

NLS_COMP
BINARY


SQL>
SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH
  2  FROM DUAL;

         0

SQL>
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

SQL>
SQL> SELECT *
  2  FROM NLS_SESSION_PARAMETERS
  3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT
BINARY_CI

NLS_COMP
LINGUISTIC


SQL>
SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH
  2  FROM DUAL;

         1
You can also create case insensitive indexes:
 
create index
   nlsci1_gen_person
on
   MY_PERSON
   (NLSSORT
      (PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')
   )
;

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

Wednesday, January 28, 2015

Handling classpath issues

Came across this issue today
java.lang.NoSuchMethodError    org.apache.commons.codec.binary.Base64.encodebase64

Eventually realized this was because the class Base64 was picked from wrong jar file... the ques was which one , I mean as i got this in web application ... so the hierarchy is not easy to trace.

I tried adding the following line before the exception
logger.debug(Base64.class.getProtectionDomain().getCodeSource().getLocation());

And then eventually modified the classpath settings to modify the issue.

Wednesday, January 21, 2015

Tomcat startup window disappears ?

https://navnith.wordpress.com/2008/12/31/tomcat-startup-window-disappears/

on Command Prompt type: catalina.bat run
this will ensure that the window stays and shows you what the error was which was causing the window to disappear