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 Oracle SQL function LAG. SQL query LAG This code find change in address post code in journal table: Test query data 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


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: In line 11 and 14 we have place to insert own code. Other solution we found when... Continue Reading →

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

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.

Unique session ID

The UNIQUE_SESSION_ID function returns a character string unique to the session among all sessions currently connected to the database. The return string can be up to 24 bytes in length. Multiple calls to the function from the same session will always return the same string. The program header follows:

Notes UNIQUE_SESSION_ID is functionally identical... Continue Reading →

Narzędzia to projektowania baz danych

GNU-GPL OpenSystemArchitect http://www.codebydesign.com/ Power*Architect http://code.google.com/p/power-architect/ Druid http://druid.sourceforge.net/ ERW http://erw.dsi.unimi.it/ Dia http://live.gnome.org/Dia XML to DDL (python scripts) http://xml2ddl.berlios.de/ Graphiz - I believe I've read of people using it to reverse engineer from DDL http://graphviz.org/ 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,... Continue Reading →

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

Up ↑