PLSQL NOT_DATA_FOUND and CURSORS

Here we are common problem in PLSQL. We try find answer for one common question in programing databases. Is the specyfic record exist in other table or not exist? Some examples below show how to menage this problem:

NOT_DATA_FOUND Example
DECLARE
    VAR_SUPP_NM VARCHAR2(100);
    VAR_SUPP_ID  NUMBER := 1;
    WHILE_VAR CHAR := 'Y';
BEGIN
  SELECT SUPP_NM
    INTO VAR_SUPP_NM
    FROM TEST.SUPPLIER
   WHERE SUPP_ID = VAR_SUPP_ID;

 DBMS_OUTPUT.PUT_LINE('DATA FOUND');

exception
  when no_data_found then 
  	-- here some code when data ont exist
	DBMS_OUTPUT.PUT_LINE('SQL DATA NOT FOUND');

END;

In line 11 and 14 we have place to insert own code. Other solution we found when we use define explicit cursor:

Explicit Cursor Used to Fetch Just One Value
CREATE FUNCTION f_get_name (ip_emp_id IN NUMBER) RETURN VARCHAR2
AS
CURSOR c IS SELECT ename FROM emp WHERE emp_id = f_get_name.ip_emp_id;
lv_ename emp.ename%TYPE;
BEGIN
  OPEN c;
  FETCH c INTO lv_ename;
  IF (SQL%NOTFOUND) THEN
     RAISE NO_DATA_FOUND;
  ENDIF;
  FETCH c INTO lv_ename;
  IF (SQL%FOUND) THEN
    RAISE TOO_MANY_ROWS;
  ENDIF;
  CLOSE c;
  RETURN lv_ename;
END;
Explicit Cursors and Bulk Processing
CREATE OR REPLACE PROCEDURE refresh_store_feed AS
TYPE prod_array IS TABLE OF store_products%ROWTYPE INDEX BY BINARY_INTEGER;
l_prod prod_array;
CURSOR c IS
  SELECT product
    FROM listed_products@some_remote_site;
BEGIN
  OPEN C;
  LOOP
    FETCH C BULK COLLECT INTO l_prod LIMIT 100;
  FOR i IN 1 .. l_csi.COUNT
    LOOP
     /* ... do some procedural code here that cannot be done in SQL to l_csi(i) ... */
    END LOOP;
  FORALL i IN 1 .. l_csi.COUNT
    INSERT INTO store_products (product) VALUES (l_prod(i));
    EXIT WHEN c%NOTFOUND;
  END LOOP;
CLOSE C;
END;
/

REF Cursors in Brief

Though there are two other sections in this chapter that cover REF cursors more fully (“Static REF Cursors” and “Dynamic REF Cursors”), a brief overview regarding these types of cursors warrants mention in this section due to their structure: they must be explicitly opened, fetched from, and closed. If you are returning a result set to a client, a great way to avoid lots of repetitive code is to use a REF cursor. Essentially, the cursor you open usually depends on what input you receive from the requesting client. Listing 10-4 provides a brief example of a REF cursor scenario in PL/SQL:

REF Cursors Work Dynamically, But Are Declared

DECLARE
prod_cursor sys_refcursor;
BEGIN
	IF ( input_param = 'C' )
	THEN
		OPEN prod_cursor FOR
		SELECT * FROM prod_concepts
			WHERE concept_type = 'COLLATERAL'
				AND concept_dt < TO_DATE( '01-JAN-2003', 'DD-MON-YYYY');
	ELSE
		OPEN prod_cursor FOR
		SELECT * FROM prod_concepts
			WHERE concept_category = 'ADVERTISING';
	END IF;
LOOP
A Select … Into Cursor vs. a For … In Cursor
BEGIN
    FOR x IN (SELECT * FROM dual) LOOP ... END LOOP;
	END;
DECLARE
	CURSOR c IS SELECT * FROM dual;
BEGIN
	OPEN c;
	LOOP
		FETCH c INTO …
		EXIT WHEN c%NOTFOUND;
			…
		END LOOP;
		CLOSE c;
	END;
	BEGIN
		SELECT *INTO ...FROM dual;
		END;
	DECLARE
		CURSOR c IS SELECT * FROM dual;
		l_rec dual%ROWTYPE;
	BEGIN
	OPEN c;
	FETCH c INTO l_rec;
	IF (SQL%NOTFOUND)
	THEN
		RAISE NO_DATA_FOUND;
	END IF;
	FETCH c INTO l_rec;
	IF (SQL%FOUND)CHAPTER 
	THEN
		RAISE TOO_MANY_ROWS;
	END IF;
	CLOSE c;
END;

 

Hits: 6

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

Hits: 8

RegEx never endig story….

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

/^.*/gm and substitution: '\0',

Explanation

  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)

Example

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.

SELECT * FROM TABLE WHERE ID IN(...)

AAA
AAB
AAC

Convert it to:

SELECT * FROM TABLE WHERE ID IN(
'AAA',
'AAB',
'AAC')

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

other good option to test yours regex query is:

  • https://www.regextester.com/
  • https://www.freeformatter.com/regex-tester.html

How its run?

Simple by Sublimetext, Notepad++, Visual Code –> 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 ( ).

Hits: 20

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.

CREATE TABLE bulk_collect_test AS
SELECT owner,
       object_name,
       object_id
FROM   all_objects;

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

SET SERVEROUTPUT ON
DECLARE
  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab    t_bulk_collect_test_tab := t_bulk_collect_test_tab();
  l_start  NUMBER;
BEGIN
  -- Time a regular population.
  l_start := DBMS_UTILITY.get_time;

  FOR cur_rec IN (SELECT *
                  FROM   bulk_collect_test)
  LOOP
    l_tab.extend;
    l_tab(l_tab.last) := cur_rec;
  END LOOP;

  DBMS_OUTPUT.put_line('Regular (' || l_tab.count || ' rows): ' || 
                       (DBMS_UTILITY.get_time - l_start));
  
  -- Time bulk population.  
  l_start := DBMS_UTILITY.get_time;

  SELECT *
  BULK COLLECT INTO l_tab
  FROM   bulk_collect_test;

  DBMS_OUTPUT.put_line('Bulk    (' || l_tab.count || ' rows): ' || 
                       (DBMS_UTILITY.get_time - l_start));
END;
/
Regular (42578 rows): 66
Bulk    (42578 rows): 4

PL/SQL procedure successfully completed.

SQL>

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.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab t_bulk_collect_test_tab;

  CURSOR c_data IS
    SELECT *
    FROM bulk_collect_test;
BEGIN
  OPEN c_data;
  LOOP
    FETCH c_data
    BULK COLLECT INTO l_tab LIMIT 10000;
    EXIT WHEN l_tab.count = 0;

    -- Process contents of collection here.
    DBMS_OUTPUT.put_line(l_tab.count || ' rows');
  END LOOP;
  CLOSE c_data;
END;
/
10000 rows
10000 rows
10000 rows
10000 rows
2578 rows

PL/SQL procedure successfully completed.

SQL>

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.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab    t_bulk_collect_test_tab;

  CURSOR c_data IS
    SELECT *
    FROM   bulk_collect_test;

  l_start  NUMBER;
BEGIN
  -- Time a regular cursor for loop.
  l_start := DBMS_UTILITY.get_time;

  FOR cur_rec IN (SELECT *
                  FROM   bulk_collect_test)
  LOOP
    NULL;
  END LOOP;

  DBMS_OUTPUT.put_line('Regular  : ' || 
                       (DBMS_UTILITY.get_time - l_start));

  -- Time bulk with LIMIT 10.
  l_start := DBMS_UTILITY.get_time;

  OPEN c_data;
  LOOP
    FETCH c_data
    BULK COLLECT INTO l_tab LIMIT 10;
    EXIT WHEN l_tab.count = 0;
  END LOOP;
  CLOSE c_data;

  DBMS_OUTPUT.put_line('LIMIT 10 : ' || 
                       (DBMS_UTILITY.get_time - l_start));

  -- Time bulk with LIMIT 100.
  l_start := DBMS_UTILITY.get_time;

  OPEN c_data;
  LOOP
    FETCH c_data
    BULK COLLECT INTO l_tab LIMIT 100;
    EXIT WHEN l_tab.count = 0;
  END LOOP;
  CLOSE c_data;

  DBMS_OUTPUT.put_line('LIMIT 100: ' || 
                       (DBMS_UTILITY.get_time - l_start));

  -- Time bulk with LIMIT 1000.
  l_start := DBMS_UTILITY.get_time;

  OPEN c_data;
  LOOP
    FETCH c_data
    BULK COLLECT INTO l_tab LIMIT 1000;
    EXIT WHEN l_tab.count = 0;
  END LOOP;
  CLOSE c_data;

  DBMS_OUTPUT.put_line('LIMIT 1000: ' || 
                       (DBMS_UTILITY.get_time - l_start));
END;
/
Regular  : 18
LIMIT 10 : 80
LIMIT 100: 15
LIMIT 1000: 10

PL/SQL procedure successfully completed.

SQL>

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.

Hits: 293

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

Up ↑