Materialized Views

REFRESH FAST Categories

There are three ways to categorize a materialized view’s ability to be fast refreshed.

  1. It can never be fast refreshed.
  2. It can always be fast refreshed.
  3. It can be fast refreshed after certain kinds of changes to the base table but not others.

For the first case Oracle will raise an error if you try to create such a materialized view with its refresh method defaulted to REFRESH FAST. In the example below table T does not have a materialized view log on it. Materialized views based on T cannot therefore be fast refreshed. If we attempt to create such a materialized view we get an error.

create materialized view MV REFRESH FAST as select * from t2 ; as select * from t2 * ERROR at line 3: ORA-23413: table "SCOTT"."T2" does not have a materialized view log

Source: Materialized Views: REFRESH FAST Categories

Hits: 42

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

Hits: 586

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

Hits: 24

PL/SQL Errors: ORA-04061: existing state of package has been invalidated

I suspect you’re only reporting the last error in a stack like this:

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "schema.package" has been invalidated
ORA-04065: not executed, altered or dropped package body "schema.package"
ORA-06508: PL/SQL: could not find program unit being called: "schema.package"

If so, that’s because your package is stateful:

The values of the variables, constants, and cursors that a package declares (in either its specification or body) comprise its package state. If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful; otherwise, it is stateless.

When you recompile the state is lost:

If the body of an instantiated, stateful package is recompiled (either explicitly, with the “ALTER PACKAGE Statement”, or implicitly), the next invocation of a subprogram in the package causes Oracle Database to discard the existing package state and raise the exception ORA-04068.

After PL/SQL raises the exception, a reference to the package causes Oracle Database to re-instantiate the package, which re-initializes it…

You can’t avoid this if your package has state. I think it’s fairly rare to really need a package to be stateful though, so you should revisit anything you have declared in the package, but outside a function or procedure, to see if it’s really needed at that level. Since you’re on 10g though, that includes constants, not just variables and cursors.

But the last paragraph from the quoted documentation means that the next time you reference the package in the same session, you won’t get the error and it will work as normal (until you recompile again).

As of Oracle Database 11g Release 2 (11.2.0.2), Oracle Database treats a package as stateless if its state is constant for the life of a session (or longer). This is the case for a package whose items are all compile-time constants.

A compile-time constant is a constant whose value the PL/SQL compiler can determine at compilation time. A constant whose initial value is a literal is always a compile-time constant. A constant whose initial value is not a literal, but which the optimizer reduces to a literal, is also a compile-time constant. Whether the PL/SQL optimizer can reduce a nonliteral expression to a literal depends on optimization level. Therefore, a package that is stateless when compiled at one optimization level might be stateful when compiled at a different optimization level. For information about the optimizer, see “PL/SQL Optimizer”.

Each session that references a package item has its own instantiation of that package. If the package is stateful, the instantiation includes its state. The package state persists for the life of a session, except in these situations:

  • The package is SERIALLY_REUSABLE.For details, see “SERIALLY_REUSABLE Packages”.
  • The package body is recompiled.If the body of an instantiated, stateful package is recompiled (either explicitly, with the “ALTER PACKAGE Statement”, or implicitly), the next invocation of a subprogram in the package causes Oracle Database to discard the existing package state and raise the exception ORA-04068.After PL/SQL raises the exception, a reference to the package causes Oracle Database to re-instantiate the package, which re-initializes it. Therefore, previous changes to the package state are lost. (For information about initialization, see “Package Instantiation and Initialization”.)
  • Any of the session’s instantiated packages are invalidated and revalidated.All of a session’s package instantiations (including package states) can be lost if any of the session’s instantiated packages are invalidated and revalidated. For information about invalidation and revalidation of schema objects, see Oracle Database Advanced Application Developer’s Guide.

It sounds like what you want is to be able to list all packages that may potentially have state.

What you’re looking for is just packages that have any global variables or constants. For a single package, this is quite simple by inspection. To look across all packages in a schema, however, you could use PL/Scope:

First, log in as the schema owner, turn on PL/Scope in your session:

alter session set plscope_settings='IDENTIFIERS:ALL';

Then, recompile all your package bodies.

Then, run this query to find all the variables and constants declared at the package level:

select object_name AS package,
       type,
       name AS variable_name
from user_identifiers
where object_type IN ('PACKAGE','PACKAGE BODY') and usage = 'DECLARATION' and type in ('VARIABLE','CONSTANT') and usage_context_id in ( select usage_id
  from user_identifiers
  where type = 'PACKAGE' );

I’d suggest the resulting list of packages will be your target.

If you’re on 11gR2, constants no longer cause this problem, so you’d use this query instead:

select object_name AS package,
       type,
       name AS variable_name
from user_identifiers
where object_type IN ('PACKAGE','PACKAGE BODY') and usage = 'DECLARATION' and type = 'VARIABLE' and usage_context_id in ( select usage_id
  from user_identifiers
  where type = 'PACKAGE' );

Source

  1. Annals of Oracle’s Improbable Errors: ORA-04061: existing state of package has been invalidated
  2. https://stackoverflow.com/questions/19376440/ora-06508-pl-sql-could-not-find-program-unit-being-called/19380746

Hits: 1152

sql – Search for a particular string in Oracle clob column

Example use string in Oracle CLOB column

create table aTable(
      id number,
      aClobColumn  clob
    );

insert into aTable values (1,'value' );
insert into aTable values (1,'values are like this' );

select * from aTable where dbms_lob.substr( aClobColumn , 4000, 1 )='value';

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

select * from my_table where dbms_lob.instr(product_details,'NEW.PRODUCT_NO')>=1

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;

Hits: 531

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

Oracle 10gR2+ conditional PL/SQL Compilation

Examples

Example 1
create or replace procedure p
as
begin
  $IF $$debug_code
  $THEN
    dbms_output.put_line( 'Our debug code' );
    dbms_output.put_line( 'Would go here' );
  $END
  dbms_output.put_line( 'And our real code here' );
end;
/

alter procedure P compile
plsql_ccflags = 'debug_code:true' reuse settings
/

Example 2
create or replace procedure p2
as
begin
 $if $$plsql_debug
 $then
    $error 'This program must be compiled with PLSQL_DEBUG disabled' $end
 $end
 dbms_output.put_line( 'This is where it happens!' );
end;
/
Procedure created
ALTER SESSION SET PLSQL_DEBUG=TRUE
/
alter procedure p2 compile
/
Warning: Procedure altered with compilation errors.
show err
Errors for PROCEDURE P2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5      PLS-00179: $ERROR: This program must be compiled with PLSQL_DEBUG
         disabled

Source:

  1. http://www.oracle.com/technetwork/database/features/plsql/overview/plsql-conditional-compilation-133587.pdf
  2. Oracle 10gR2 – Conditional PL/SQL Compilation – AMIS Oracle and Java Blog

Hits: 13

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

Hits: 8

PLSQL corner

PLSQL loop inexed by letters

declare
  k_ascii_a constant pls_integer := ascii('A');
  l_index varchar2(1);
begin
  for i in 1 .. 10 loop -- Change 10 for whatever is your limit
    l_index := chr(k_ascii_A + i - 1);
    dbms_output.put_line('Index is ' || l_index);
  end loop;
end;

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

declare
  k_index_char constant varchar2(26) := 'ABCDEF...Z';
  l_index varchar2(1);
begin
  for i in 1 .. 10 loop -- Change 10 for whatever is your limit
    l_index := substr(k_index_char, i, 1);
    dbms_output.put_line('Index is ' || l_index);
  end loop;
end;

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.

Hits: 7

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

Hits: 110

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:

FUNCTION DBMS_SESSION.UNIQUE_SESSION_ID
RETURN VARCHAR2;

Notes

UNIQUE_SESSION_ID is functionally identical to the DBMS_PIPE.UNIQUE_SESSION_NAME function; however, their return values are not identical. Be very careful not to write code that assumes that these two functions are equivalent.

The UNIQUE_SESSION_ID function does not raise any exceptions.

Example

To display the value of the unique id for the current session, specify the following:

DECLARE
   my_unique_id   VARCHAR2(30);
BEGIN
   my_unique_id := DBMS_SESSION.UNIQUE_SESSION_ID;
   DBMS_OUTPUT.PUT_LINE('UNIQUE ID: '||my_unique_id);
END;

An example of output from executing the preceding PL/SQL block follows:

UNIQUE ID: F000E4020000

— Source https://www.toadworld.com/platforms/oracle/w/wiki/3246.dbms-session-unique-session-id

When you have problem receive information about session in Oracle PLSQL database for example purposes journalling. Look bellow on SQLs witch rewrite two or more rows:

SELECT *
FROM v$session s
WHERE username IS NOT NULL 
AND s.AUDSID = SYS_CONTEXT ('userenv', 'sessionid')
ORDER BY username, osuser;

You need add two condition to receive one row.

WHERE username IS NOT NULL 
AND s.AUDSID = SYS_CONTEXT ('userenv', 'sessionid') 
AND S.SID = SYS_CONTEXT ('userenv', 'sid')

Final query return unique session row:

SELECT *
FROM v$session s
WHERE username IS NOT NULL AND s.AUDSID = SYS_CONTEXT ('userenv', 'sessionid') AND S.SID = SYS_CONTEXT ('userenv', 'sid')
ORDER BY username, osuser;

Hits: 106

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

Hits: 11

Pomiar wydajności bazy danych

Przy wyborze hostingu warto sprawdzić jak prezentuje się wydajność zakupionych serwerów może w tym pomóc skrypt http://www.linux.pl

Po przeróbkach skryptu zainstalowałem go na serwerze http://ultimasolution.pl/projects/bechmark_phpmysql/?iterations=50000&amp;amp;tabletype=MYISAM

i oto co wyszło:

Testing a(n) MYISAM table using 50000 rows.

Successfully created database speedtestdb
Sucessfully created table speedtesttable

This MySQL instance does NOT support MYISAM tables
Table Type Verified: ..

Done. 50000 inserts in 1.98958 seconds or

25131 inserts per second.

Done. 50000 row reads in 6.0E-5 seconds or

833333333 row reads per second.

Done. 50000 updates in 6.0E-5 seconds or

833333333 updates per second.

Inne pomysły na zgadanie wydajności:

 

Hits: 7

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

Up ↑