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. Source: https://stackoverflow.com/questions/286549/find-out-name-of-pl-sql-procedure#286569 Return... Continue Reading →

Oracle SQL: LAG Oracle 11g is analytic function

Very usefully new features in Oracle 11g is analytic function LAG Query This code find change in address post code in journal table Test data Result site_number jn_datetime adr_postcode prev_adr_postcode 20001 2017-08-01 15:01:31 00-001 10-001 20002 2017-04-20 11:59:29 00-001 20-002 20003 2017-06-15 04:02:41 00-001 30-003   http://sqlfiddle.com/#!4/57db1f/2/0

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

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. Function to remove double number: SQL Fiddle http://sqlfiddle.com/#!4/75b32/1/1 http://rextester.com/l/oracle_online_compiler http://dbfiddle.uk/ 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... Continue Reading →

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 ^ asserts position at start of a line .* matches any character (except for line terminators) * Quantifier — Matches between zero and unlimited times, as many times as possible, giving back as needed (greedy) Global pattern flags g modifier: global.... Continue Reading →

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 Source: Pinterest http://sql.sh/2401-sql-join-infographie Other sources about JOIN https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm https://www.techonthenet.com/oracle/joins.php - This Oracle tutorial explains how to use JOINS (inner and outer) in... Continue Reading →

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

Up ↑