SQL owa_util.get_procedure and $$PLSQL_UNIT

In 10g and 11g I use the “owa_util.get_procedure” function. I normally use this in packages as it will also return the name of an internal procedure or function as part of the package name, i.e. (package_name).(procedure name). I use this to provide a generic EXCEPTION template for identifying where an exception occured.

CREATE OR REPLACE procedure some_procedure is
    v_procedure_name varchar2(32);
begin
    v_procedure_name := owa_util.get_procedure;
end;

CREATE OR REPLACE PACKAGE some_package
AS
    FUNCTION v_function_name
    RETURN DATE;
END;
/
CREATE OR REPLACE PACKAGE BODY some_package
AS
    FUNCTION v_function_name
    RETURN DATE
    IS
    BEGIN
        RETURN SYSDATE;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('ERROR IN '||owa_util.get_procedure);
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
END;
/

Source: https://stackoverflow.com/questions/286549/find-out-name-of-pl-sql-procedure#286569

alter session set plscope_settings='identifiers:all'
/
Session altered.

SQL> set serverout on

SQL> create or replace package pkg
as
   procedure p1;

   procedure p2;
end pkg;
/
Package created.

SQL> create or replace package body pkg
as
   procedure get_scope (obj varchar2, line int)
   as
   begin
      for c in (select rpad (lpad (' ', 2 * (level - 1)) || name, 20, '.') || ' ' || rpad (type, 20) || rpad (usage, 20) identifier_usage_contexts
                from user_identifiers t
                where level != 1
                start with line = get_scope.line and object_name = obj
                connect by usage_id = prior usage_context_id)
      loop
         dbms_output.put_line (c.identifier_usage_contexts);
      end loop;
   end get_scope;

   procedure p1
   as
   begin
      null;
   end p1;


   procedure p2
   as
   begin
      get_scope ($$plsql_unit, $$plsql_line);
   end p2;
end pkg;

Return information about witch module call this code.

CREATE OR REPLACE FUNCTION MIBP_OWNER.FN_WHO_AM_I (p_lvl NUMBER DEFAULT 0)
    RETURN VARCHAR2 IS
    /***********************************************************************************************
    FN_WHO_AM_I returns the full ORACLE name of your object including schema and package names
    --
    FN_WHO_AM_I(0) - returns the name of your object
    FN_WHO_AM_I(1) - returns the name of calling object
    FN_WHO_AM_I(2) - returns the name of object, who called calling object
    etc., etc., etc.... Up to to he highest level
    -------------------------------------------------------------------------------------------------
    Copyrigth GARBUYA 2010
    *************************************************************************************************/
    TYPE str_varr_t IS VARRAY (2) OF CHAR (1);

    TYPE str_table_t IS TABLE OF VARCHAR2 (256);

    TYPE num_table_t IS TABLE OF NUMBER;

    v_stack        VARCHAR2 (2048) DEFAULT UPPER (DBMS_UTILITY.format_call_stack);
    v_tmp_1        VARCHAR2 (1024);
    v_tmp_2        VARCHAR2 (1024);
    v_pkg_name     VARCHAR2 (32);
    v_obj_type     VARCHAR2 (32);
    v_owner        VARCHAR2 (32);
    v_idx          NUMBER := 0;
    v_pos1         NUMBER := 0;
    v_pos2         NUMBER := 0;
    v_line_nbr     NUMBER := 0;
    v_blk_cnt      NUMBER := 0;
    v_str_len      NUMBER := 0;
    v_bgn_cnt      NUMBER := 0;
    v_end_cnt      NUMBER := 0;
    it_is_comment  BOOLEAN := FALSE;
    it_is_literal  BOOLEAN := FALSE;
    v_literal_arr  str_varr_t := str_varr_t ('''', '"');
    v_blk_bgn_tbl  str_table_t
                       := str_table_t (' IF '
                                      ,' LOOP '
                                      ,' CASE '
                                      ,' BEGIN ');
    v_tbl          str_table_t := str_table_t ();
    v_blk_bgn_len_tbl num_table_t := num_table_t ();
BEGIN
    v_stack     := SUBSTR (v_stack, INSTR (v_stack, CHR (10), INSTR (v_stack, 'FN_WHO_AM_I')) + 1) || 'ORACLE'; -- skip myself

    FOR v_pos2 IN 1 .. p_lvl LOOP -- advance to the input level
        v_pos1      := INSTR (v_stack, CHR (10));
        v_stack     := SUBSTR (v_stack, INSTR (v_stack, CHR (10)) + 1);
    END LOOP;

    v_pos1      := INSTR (v_stack, CHR (10));

    IF v_pos1 = 0 THEN
        RETURN (v_stack);
    END IF;

    v_stack     := SUBSTR (v_stack, 1, v_pos1 - 1); -- get only current level
    v_stack     := TRIM (SUBSTR (v_stack, INSTR (v_stack, ' '))); -- cut object handle
    v_line_nbr  := TO_NUMBER (SUBSTR (v_stack, 1, INSTR (v_stack, ' ') - 1)); -- get line number
    v_stack     := TRIM (SUBSTR (v_stack, INSTR (v_stack, ' '))); -- cut line number
    v_pos1      := INSTR (v_stack, ' BODY');

    IF v_pos1 = 0 THEN
        RETURN (v_stack);
    END IF;

    v_pos1      := INSTR (v_stack, ' ', v_pos1 + 2); -- find end of object type
    v_obj_type  := SUBSTR (v_stack, 1, v_pos1 - 1); -- get object type
    v_stack     := TRIM (SUBSTR (v_stack, v_pos1 + 1)); -- get package name
    v_pos1      := INSTR (v_stack, '.');
    v_owner     := SUBSTR (v_stack, 1, v_pos1 - 1); -- get owner
    v_pkg_name  := SUBSTR (v_stack, v_pos1 + 1); -- get package name
    v_blk_cnt   := 0;
    it_is_literal := FALSE;

    --
    FOR v_idx IN v_blk_bgn_tbl.FIRST .. v_blk_bgn_tbl.LAST LOOP
        v_blk_bgn_len_tbl.EXTEND (1);
        v_blk_bgn_len_tbl (v_blk_bgn_len_tbl.LAST) := LENGTH (v_blk_bgn_tbl (v_idx));
    END LOOP;

    --
    FOR src IN (
                   SELECT ' ' || REPLACE (TRANSLATE (UPPER (text), ';(' || CHR (10), '   '), '''''', ' ') || ' ' text
                   FROM all_source
                   WHERE     owner = v_owner
                         AND name = v_pkg_name
                         AND TYPE = v_obj_type
                         AND line < v_line_nbr
                   ORDER BY line
               ) LOOP
        v_stack     := src.text;

        IF it_is_comment THEN
            v_pos1      := INSTR (v_stack, '*/');

            IF v_pos1 > 0 THEN
                v_stack     := SUBSTR (v_stack, v_pos1 + 2);
                it_is_comment := FALSE;
            ELSE
                v_stack     := ' ';
            END IF;
        END IF;

        --
        IF v_stack != ' ' THEN
            --
            v_pos1      := INSTR (v_stack, '/*');

            WHILE v_pos1 > 0 LOOP
                v_tmp_1     := SUBSTR (v_stack, 1, v_pos1 - 1);
                v_pos2      := INSTR (v_stack, '*/');

                IF v_pos2 > 0 THEN
                    v_tmp_2     := SUBSTR (v_stack, v_pos2 + 2);
                    v_stack     := v_tmp_1 || v_tmp_2;
                ELSE
                    v_stack     := v_tmp_1;
                    it_is_comment := TRUE;
                END IF;

                v_pos1      := INSTR (v_stack, '/*');
            END LOOP;

            --
            IF v_stack != ' ' THEN
                v_pos1      := INSTR (v_stack, '--');

                IF v_pos1 > 0 THEN
                    v_stack     := SUBSTR (v_stack, 1, v_pos1 - 1);
                END IF;

                --
                IF v_stack != ' ' THEN
                    FOR v_idx IN v_literal_arr.FIRST .. v_literal_arr.LAST LOOP
                        v_pos1      := INSTR (v_stack, v_literal_arr (v_idx));

                        WHILE v_pos1 > 0 LOOP
                            v_pos2      := INSTR (v_stack, v_literal_arr (v_idx), v_pos1 + 1);

                            IF v_pos2 > 0 THEN
                                v_tmp_1     := SUBSTR (v_stack, 1, v_pos1 - 1);
                                v_tmp_2     := SUBSTR (v_stack, v_pos2 + 1);
                                v_stack     := v_tmp_1 || v_tmp_2;
                            ELSE
                                IF it_is_literal THEN
                                    v_stack     := SUBSTR (v_stack, v_pos1 + 1);
                                    it_is_literal := FALSE;
                                ELSE
                                    v_stack     := SUBSTR (v_stack, 1, v_pos1 - 1);
                                    it_is_literal := TRUE;
                                END IF;
                            END IF;

                            v_pos1      := INSTR (v_stack, v_literal_arr (v_idx));
                        END LOOP;
                    END LOOP;

                    --
                    IF v_stack != ' ' THEN
                        WHILE INSTR (v_stack, '  ') > 0 LOOP
                            v_stack     := REPLACE (v_stack, '  ', ' ');
                        END LOOP;

                        v_stack     := REPLACE (v_stack, ' END IF ', ' END ');
                        v_stack     := REPLACE (v_stack, ' END LOOP ', ' END ');

                        --
                        IF v_stack != ' ' THEN
                            v_stack     := ' ' || v_stack;
                            v_pos1      := INSTR (v_stack, ' FUNCTION ') + INSTR (v_stack, ' PROCEDURE ');

                            IF v_pos1 > 0 THEN
                                v_obj_type  := TRIM (SUBSTR (v_stack, v_pos1 + 1, 9)); -- get object type
                                v_stack     := TRIM (SUBSTR (v_stack, v_pos1 + 10)) || '  '; -- cut object type
                                v_stack     := SUBSTR (v_stack, 1, INSTR (v_stack, ' ') - 1); -- get object name
                                v_tbl.EXTEND (1);
                                v_tbl (v_tbl.LAST) := v_obj_type || ' ' || v_owner || '.' || v_pkg_name || '.' || v_stack;
                            END IF;

                            --
                            v_pos1      := 0;
                            v_pos2      := 0;
                            v_tmp_1     := v_stack;
                            v_tmp_2     := v_stack;

                            FOR v_idx IN v_blk_bgn_tbl.FIRST .. v_blk_bgn_tbl.LAST LOOP
                                v_str_len   := NVL (LENGTH (v_tmp_1), 0);
                                v_tmp_1     := REPLACE (v_tmp_1, v_blk_bgn_tbl (v_idx), NULL);
                                v_bgn_cnt   := NVL (LENGTH (v_tmp_1), 0);
                                v_pos1      := v_pos1 + (v_str_len - v_bgn_cnt) / v_blk_bgn_len_tbl (v_idx);
                                v_str_len   := NVL (LENGTH (v_tmp_2), 0);
                                v_tmp_2     := REPLACE (v_tmp_2, ' END ', NULL);
                                v_end_cnt   := NVL (LENGTH (v_tmp_2), 0);
                                v_pos2      := v_pos2 + (v_str_len - v_end_cnt) / 5; --- 5 is the length(' END ')
                            END LOOP;

                            IF v_pos1 > v_pos2 THEN
                                v_blk_cnt   := v_blk_cnt + 1;
                            ELSIF v_pos1 < v_pos2 THEN
                                v_blk_cnt   := v_blk_cnt - 1;

                                IF     v_blk_cnt = 0
                                   AND v_tbl.COUNT > 0 THEN
                                    v_tbl.DELETE (v_tbl.LAST);
                                END IF;
                            END IF;
                        END IF;
                    END IF;
                END IF;
            END IF;
        END IF;
    END LOOP;

    RETURN CASE v_tbl.COUNT
               WHEN 0 THEN
                   'UNKNOWN'
               ELSE
                   v_tbl (v_tbl.LAST)
           END;
END;
/

Show errors;

 

Other good example use new features Oracle 10g

DECLARE
	local_exception EXCEPTION;
	FUNCTION nested_local_function
	RETURN BOOLEAN IS
		retval BOOLEAN := FALSE;
BEGIN
	RAISE local_exception;
	RETURN retval;
	END;
BEGIN
IF nested_local_function THEN
	dbms_output.put_line('No raised exception');
END IF;
EXCEPTION
WHEN others THEN
	dbms_output.put_line('DBMS_UTILITY.FORMAT_CALL_STACK');
	dbms_output.put_line('------------------------------');
	dbms_output.put_line(dbms_utility.format_call_stack);
	dbms_output.put_line('DBMS_UTILITY.FORMAT_ERROR_BACKTRACE');
	dbms_output.put_line('-----------------------------------');
	dbms_output.put_line(dbms_utility.format_error_backtrace);
	dbms_output.put_line('DBMS_UTILITY.FORMAT_ERROR_STACK');
	dbms_output.put_line('-------------------------------');
	dbms_output.put_line(dbms_utility.format_error_stack);
END;
/
This script produces the following output:
DBMS_UTILITY.FORMAT_CALL_STACK
------------------------------
----- PL/SQL Call Stack -----
object line object
handle number
name
20909240 18 anonymous block
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
-----------------------------------
ORA-06512: at line 7
ORA-06512: at line 11
DBMS_UTILITY.FORMAT_ERROR_STACK
-------------------------------
ORA-06510: PL/SQL: unhandled user-defined exception

Oracle SQL: LAG Oracle 11g is analytic function

Very usefully new features in Oracle 11g is analytic Oracle SQL function LAG.

SQL query LAG

This code find change in address post code in journal table:

  SELECT  *
    FROM  (
              SELECT  t1.SITE_NUMBER
                     ,t1.JN_DATETIME
                     ,t1.ADR_POSTCODE
                     ,LAG (t1.ADR_POSTCODE, 1) OVER (PARTITION BY t1.SITE_NUMBER ORDER BY t1.jn_datetime) AS PREV_ADR_POSTCODE
                FROM  t1 
          )
   WHERE      adr_postcode = '00-001'
          AND PREV_ADR_POSTCODE <> '00-001'
          AND PREV_ADR_POSTCODE <> '00-000'
ORDER BY  SITE_NUMBER;

Test query data

CREATE TABLE T1
(
  SITE_NUMBER   VARCHAR2(16 BYTE),
  JN_DATETIME   DATE,
  ADR_POSTCODE  VARCHAR2(20 BYTE)
)
LOGGING 
NOCOMPRESS 
MONITORING;
SET DEFINE OFF;
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('08/01/2017 15:01:31', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20002', TO_DATE('04/20/2017 11:59:29', 'MM/DD/YYYY HH24:MI:SS'), '00-002');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('06/15/2017 04:02:41', 'MM/DD/YYYY HH24:MI:SS'), '00-003');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20004', TO_DATE('06/20/2017 20:15:13', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('12/10/1929 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('09/25/2091 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-003');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('09/14/1914 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('03/25/1911 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('11/22/2029 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
COMMIT;

SQL result

site_number jn_datetime adr_postcode prev_adr_postcode
20001 2017-08-01 15:01:31 00-001 00-003

How it works together

http://sqlfiddle.com/#!4/9a676b/1

sql – Search for a particular string in Oracle clob column

Example use string in Oracle CLOB column

Source: http://sqlfiddle.com/#!4/1878f6/164

SQL VARCHAR2 Functions and Operators on LOBs

http://docs.oracle.com/cd/B19306_01/appdev.102/b14249/adlob_sql_semantics.htm#g1016221

Source: sql – Search for a particular string in Oracle clob column – Stack Overflow

Convert string to CLOB

declare
   c1 clob := to_clob('abc');
   c2 clob;
 begin
      case c1
          when to_clob('abc') then dbms_output.put_line('abc');
          when to_clob('def') then dbms_output.put_line('def');
      end case;

      c2 := case c1
        when to_clob('abc') then 'abc'
         when to_clob('def') then 'def'
     end;
      dbms_output.put_line(c2);
end;

SQL corner: remove duplicated number

Last time I have problem with duplicated number. Our callback interfaces write to us many error_number. So first i have to find where is and how many this number are.

SELECT l.site_number, l.ADR_STREET_NO, REGEXP_REPLACE (l.ADR_STREET_NO, '([^ ]+)( \1)+', '\1')
FROM mibp_owner.locations l
WHERE REGEXP_LIKE (ADR_STREET_NO, '\d \d'); l
UPDATE mibp_owner.locations l
SET l.ADR_STREET_NO = REGEXP_REPLACE (l.ADR_STREET_NO, '([^ ]+)( \1)+', '\1')
WHERE REGEXP_LIKE (ADR_STREET_NO, '\d \d');

Function to remove double number:

CREATE OR REPLACE FUNCTION MIBP_OWNER.rem_dubble (n in VARCHAR2)
   RETURN VARCHAR2
IS
  ind integer;
  t1 varchar2(100);
  t2 varchar2(100);
BEGIN
  if is_no_dubbled(n)='N' then
    return n;
  end if;

  for i in 1..10 loop
    ind:=instr(n,' ',1,i);
    if ind=0 then
      exit;
    end if;
    t1:=trim(substr(n,1,ind));
    t2:=trim(substr(n,ind));
    if t1=t2 then
      return rem_dubble(t1);
    end if;
  end loop;
  return n;

END;
/
CREATE OR REPLACE FUNCTION MIBP_OWNER.is_no_dubbled (n in VARCHAR2)
   RETURN VARCHAR2
IS
  ind integer;
  t1 varchar2(100);
  t2 varchar2(100);
BEGIN
  if instr(n,' ')=0 then return 'N'; end if;
  for i in 1..10 loop
    ind:=instr(n,' ',1,i);
    if ind=0 then
      exit;
    end if;
    t1:=trim(substr(n,1,ind));
    t2:=trim(substr(n,ind));
    if t1=t2 then
      return 'Y';
    end if;
  end loop;
  return 'N';

END;
/

Show errors;

SQL Fiddle

  1. http://sqlfiddle.com/#!4/75b32/1/1
  2. http://rextester.com/l/oracle_online_compiler
  3. http://dbfiddle.uk/
  4. https://livesql.oracle.com/apex/livesql/file/index.html

subprogram inlining in 11g

Very usefully website 🙂 for PLSQL developers!

subprogram inlining in 11gThe release of Oracle 10g brought with it the first optimising compiler for PL/SQL. As discussed in this oracle-developer.net article, Oracle added two levels of optimisation to provide some impressive performance gains without changing a line of code.Oracle 11g has taken compiler optimisation further, in particular with the concept of subprogram inlining. With subprogram inlining, Oracle will replace a call to a subroutine (such as a function) with the subroutine code itself during compilation. One of the benefits of this is that we can continue to write well-structured, modular code without any performance penalties. For SQL-intensive PL/SQL programs, the gains from inlining might be marginal, but for procedural code, inlining might provide some greater optimisation benefits, as we will see in this article.

Source: subprogram inlining in 11g

PLSQL corner

PLSQL loop inexed by letters

But this is not portable across character sets. I think a better solution is to declare up front your index variables like this:

That way if you change character sets (e.g. to utf8) you are not reliant on a collating sequence that doesn’t match your char set.

RegEx never endig story….

It is example of use RegEx to prepare SQL data for SELECT/UPDATE operation.

/^.*/gm

  1. ^ asserts position at start of a line
  2. .* matches any character (except for line terminators)
  3. * Quantifier — Matches between zero and unlimited times, as many times as possible, giving back as needed (greedy)
  4. Global pattern flags
    1. g modifier: global. All matches (don’t return after first match)
    2. m modifier: multi line. Causes ^ and $ to match the begin/end of each line (not only begin/end of string)

This example simple replace MBS to ,'MBS'. For example when you have many selected values SQL (in one column) and need convert it to running query.

Convert it to:

Very good tools for testing: https://regex101.com/ and here is this example: https://regex101.com/r/34repv/1

How its run?

Simple by Sublimetext, Notepad++ Find/Replace command

 

Regular Expressions Cheat Sheet

A regular expression specifies a set of strings that matches it. This cheat sheet is based off Python 3’s Regular Expressions (http://docs.python.org/3/library/re.html) but is designed for searches within Sublime Text.

Special Characters

\ Escapes special characters or signals a special sequence.
. Matches any single character except a newline.
^ Matches the start of the string.
$ Matches the end of the string.
* Greedily matches 0 or more repetitions of the preceding RE.
*? Matches 0 or more repetitions of the preceding RE.
+ Greedily matches 1 or more repetitions of the preceding RE.
+? Matches 1 or more repetitions of the preceding RE.
? Greedily matches 0 or 1 repetitions of the preceding RE.
?? Matches 0 or 1 repetitions of the preceding RE.
A|B Matches A, if A is unmatched then matches B, where A and B are arbitrary REs.
{m} Matches exactly m many repetitions of the previous RE.
{m,n} Greedily matches from m many to n many repetitions of the previous RE.
{m,n}? Matches m many to n many repetitions of the previous RE.

[…] Indicates a set of characters to match.

[amk] Matches ‘a’, ‘m’, or ‘k’.
[a-z] Matches ‘a’ through ‘z’.
[a-f0-7] Matches ‘a’ through ‘f’ or ‘0’ through ‘7’.
[a\-z] Matches ‘a’, ‘-‘, or ‘z’.
[a-] Matches ‘a’ or ‘-‘.
[-a] Matches ‘a’ or ‘-‘.
[(+*)] Matches ‘(‘, ‘+’, ‘*’, or ‘)’. [] matches special characters literally.
[\w] Matches the character class for ‘\w’. See character classes.
[^5] Matches anything other than ‘5’. ‘^’ forms the complementary set only as the first character in a set.
[]()] Matches ‘]’, ‘(‘, and ‘)’. ‘]’ is taken literally only as the first character in a set.
[()\]] Matches ‘]’, ‘(‘, and ‘)’.

(…) Matches the RE inside the parenthesis and assigns a new group.
(?P…) The RE matched is accessible by the group indicated by name.

(?…) Extension notation which changes a RE’s behavior. These do not assign a new group.
(?aiLmsux) Sets the corresponding flag to each letter. Does not work within Sublime Text.
(?:…) A non-capturing version of parenthesis. The matched substring cannot be retrieved later.
(?P=name) Matches the substring matched by the group named name.
(?#…) A comment, the contents are ignored.
(?=…) Lookahead assertion, the preceding RE only matches if this matches.
(?!…) Negative lookahead assertion, the preceding RE only matches if this doesn’t match.
(?<=…) Positive lookbehind assertion, the following RE will only match if preceded with this fixed length RE.
(?<!…) Negative lookbehind assertion, the following RE will only match if not preceded with this fixed length RE.
(?(id)true|false) If group id exists then uses the true RE, else use the false RE.

Character classes

\1 Matches the contents of the group labelled by the same number. Acceptable numbers are 1-99.
\A Matches at the start of the current string.
\b Matches the empty string at the beginning or end of a word. \b matches the boundary between \w and \W.
\B Matches the empty string not at the beginning or end of a word.
\d Matches any Unicode decimal digit, including 0-9.
\D Matches any Unicode non-decimal digit.
\s Matches any Unicode whitespace character, including ‘ ‘, \t, \n, \r, \f and \v.
\S Matches any Unicode non-whitespace character.
\w Matches any Unicode word character, including a-z, A-Z, and 0-9.
\W Matches any Unicode non-word character.
\Z Matches at the end of the string.

\a Matches the ASCII Bell ().
\f Matches the ASCII Formfeed ( ).
\n Matches the ASCII Linefeed.
\r Matches the ASCII Carriage Return ().
\t Matches the ASCII Horizontal Tab.
\v Matches the ASCII Vertical Tab ( ).

SQL corner: 7 kind of SQL Joins. Including : INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN

Infographic of the 7 kind of SQL Joins. Including : INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, with or without the intersect. Very useful for web developer. Source : http://sql.sh #SQL #JOIN

7-sql-join

Source: Pinterest http://sql.sh/2401-sql-join-infographie

Other sources about JOIN

Narzędzia to projektowania baz danych

GNU-GPL

Multiple versions/licenses (each has a “free” and a “not-free” version)

  • DBVisualizer
  • Toad http://www.toadsoft.com/

Not-free

  • Microsoft Visio – (If you already have it, it does work quite well)
  • DataStudio – 30 day trial http://www.aquafold.com
  • DBWrench – 30 day trial http://www.dbwrench.com/

ER diagram tool

  • http://druid.sf.net/
  • http://schemaspy.sourceforge.net/
  • http://uml.sourceforge.net/index.php

Oracle PL/SQL: Bulk Processing with BULK COLLECT and FORALL

Bulk Processing with BULK COLLECT and FORALL· BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval· FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quicklyPL/SQL is so tightly integrated with the SQL language, you might be wondering why special features would be needed to improve the performance of SQL statements inside PL/SQL. The explanation has everything to do with how the runtime engines for both PL/SQL and SQL communicate with each other—through a context switch.

Source: Mukesh K Suthar on Oracle PL/SQL: Bulk Processing with BULK COLLECT and FORALL

BULK COLLECT

Bulk binds can improve the performance when loading collections from a queries. The BULK COLLECT INTO construct binds the output of the query to the collection. To test this create the following table.

The following code compares the time taken to populate a collection manually and using a bulk bind.

Remember that collections are held in memory, so doing a bulk collect from a large query could cause a considerable performance problem. In actual fact you would rarely do a straight bulk collect in this manner. Instead you would limit the rows returned using the LIMIT clause and move through the data processing smaller chunks. This gives you the benefits of bulk binds, without hogging all the server memory. The following code shows how to chunk through the data in a large table.

So we can see that with a LIMIT 10000 we were able to break the data into chunks of 10,000 rows, reducing the memory footprint of our application, while still taking advantage of bulk binds. The array size you pick will depend on the width of the rows you are returning and the amount of memory you are happy to use.

From Oracle 10g onward, the optimizing PL/SQL compiler converts cursor FOR LOOPs into BULK COLLECTs with an array size of 100. The following example compares the speed of a regular cursor FOR LOOP with BULK COLLECTs using varying array sizes.

You can see from this example the performance of a regular FOR LOOP is comparable to a BULK COLLECT using an array size of 100. Does this mean you can forget about BULK COLLECT in 10g onward? In my opinion no. I think it makes sense to have control of the array size. If you have very small rows, you might want to increase the array size substantially. If you have very wide rows, 100 may be too large an array size.

Proudly powered by WordPress | Theme: Baskerville 2 by Anders Noren.

Up ↑