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));

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')
   )
;

Understanding Eclipse Files

Faced this issue today

• http://stackoverflow.com/questions/478985/slim-down-and-or-understand-the-eclipse-files-in-a-dynamic-web-project
• http://stackoverflow.com/questions/24331527/maven-wont-compile-java-7-with-1-7-jdk
• http://stackoverflow.com/questions/7802328/edit-the-java-facet-setting-in-eclipse
• http://stackoverflow.com/questions/4810268/eclipse-and-facets

Some more info

• http://stackoverflow.com/questions/7186676/whats-in-an-eclipse-classpath-project-file
• http://stackoverflow.com/questions/26477692/eclipse-the-specified-jre-installation-does-not-exist

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

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

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
        }

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")));