Error Handling / Exceptions

Oracle® Database PL/SQL Language Reference 11g Release 2 (11.2)

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#LNPLS007

Uni Potsdam - Wiki (Stefan Uhlmann)

http://fara.cs.uni-potsdam.de/~uhlmann/19/ch09.html

For Cursor Loop - Example

  set serveroutput on;
  --
  DECLARE
    v_anzahlDS NUMBER(20);
  BEGIN
    FOR n IN (SELECT owner, table_name
                FROM all_tables
               WHERE owner = 'SCOTT' order by 1)
    LOOP
      execute immediate 'select count(*) from '|| n.owner ||'.'|| n.table_name INTO v_anzahlDS;
      dbms_output.put_line(n.table_name || '   ' || v_anzahlDS);
    END LOOP;
  END;

Top Down mit PL/SQL

CREATE OR REPLACE PROCEDURE get_EmpData
IS
  -- Cursor deklarieren
  CURSOR c_emp
  IS
    SELECT ename
         , job
         , sal
      FROM emp
    ORDER BY job;
  -- Variablen deklarieren
  v_erg VARCHAR2(3000) := NULL;
BEGIN
  FOR i IN c_emp
  LOOP
    v_erg := i.ename ||',' || i.job ||',' || i.sal;
    dbms_output.put_line(v_erg);
  END LOOP;
END get_EmpData;

SWE mit PL/SQL

-- ---------------------------------------------

-- Grundstruktur eines PL/SQL-Blockes, um eine

-- Liste (Array, Collection) abzuarbeiten.

--

-- Eine FOR-Schleife gibt alle DB-User aus,

-- die das SELECT-Statement zurückgibt.

-- ---------------------------------------------

set serveroutput on;
--
DECLARE

BEGIN
   FOR i IN (SELECT username FROM all_users)
   LOOP
     dbms_output.put_line(i.username);
   END LOOP;
EXCEPTION
   WHEN others THEN
     dbms_output.put_line('sqlerrm : '||sqlerrm);
END;

-- ---------------------------------------------

-- Die Grundstruktur eines PL/SQL-Blockes, um 

-- eine Matrix (2-dimensionales Array), zu be-

-- arbeiten.

--

-- Die Main - FOR-Schleife gibt alle DB-User an

-- die Sub - FOR-Schleife. Diese 2. Schleife gibt 

-- dann den Usernamen und die Tabellen, die dem 

-- DB-User gehören aus.

-- ---------------------------------------------

DECLARE
BEGIN
  FOR i IN (SELECT username FROM all_users)
  LOOP
    FOR j IN (SELECT table_name FROM all_tables
               WHERE owner = i.username
                 AND table_name LIKE 'XML%'
              ORDER BY table_name)
    LOOP
      dbms_output.put_line(i.username || '   ' || j.table_name);
    END LOOP;
  END LOOP;
EXCEPTION
  WHEN others THEN
    dbms_output.put_line('sqlerrm : '||sqlerrm);
END;

-- ---------------------------------------------

-- Das geht natürlich einfacher und viel schneller, 

-- weil der Kontext-Switch zwischen PL/SQL und SQL-

-- Engine wegfällt ...

-- ---------------------------------------------

SELECT au.username
     , allt.table_name
  FROM all_users au
     , all_tables allt
 WHERE allt.owner = au.username
   and table_name like 'XML%'
order by table_name
;

-- ---------------------------------------------

-- oder evtl. auf diesem Weg ...

-- ---------------------------------------------

SELECT table_name
  FROM all_tables
 WHERE owner NOT IN ('SYSTEM'
                   , 'SYS'
                   , 'SYSAUX'
                   , 'ORDSYS'
                   , 'WMSYS'
                   , 'MDSYS'
                   , 'EXFSYS'
                   , 'TSMSYS'
                   , 'XDB'
                   , 'DBSNMP'
                   , 'OUTLN'
                   , 'CTXSYS'
                   , 'APPQOSSYS')
   AND table_name LIKE 'VOLL%'
    OR table_name LIKE '%VOLL%'
ORDER BY owner
;

Anonymer PL/SQL-Block, um eine ID aus einer Sequenz zu füllen

Allgemein 12. März 2014 Hinterlasse einen Kommentar

declare

  --

  v_seq_nr NUMBER(18) := 0;

  --

  CURSOR c_xd_id

  IS

    SELECT xds.xd_id

      FROM xml_dokument_satz xds

     WHERE xds.id = 1;

  --

begin

  --

  FOR i IN c_xd_id 

  LOOP

    select seq_xds.nextval 

      into v_seq_nr 

      from dual;

    --

    UPDATE XML_DOKUMENT_SATZ

       SET ID = v_seq_nr

     WHERE xd_id = i.xd_id;

  END LOOP;

  --

  commit;

  --

exception

  when others then

    dbms_output.put_line('sqlerrm : '||sqlerrm);

end;

PL/SQL - Blockstruktur

Anonymer PL/SQL-Block

DECLARE

…

BEGIN

…

EXCEPTION

…

END;

Stored Program Units

--------Prozedur --------

CREATE PROCEDURE <procedure_name>

(p_parameter1  IN <typ>, p_parameter2  OUT <typ>, p_parameter2  IN OUT <typ>)

IS/AS

<Deklarationen>

BEGIN

…

EXCEPTION

…

END <procedure_name>;

--------Funktion --------

CREATE FUNCTION <function_name> (p_parameter1  IN <typ>, p_parameter2  OUT <typ>, p_parameter2  IN OUT <typ>)

RETURN <typ>

IS/AS

<Deklarationen>

BEGIN

…

RETURN <ausdruck>

EXCEPTION

…

END <function_name>;

--------Aufrufe --------

VARIABLE   v_<name>   <TYP>

EXECUTE     :v_<name>   :=  <function_name>(<value>)

DECLARE  v_<name>   <TYP>;

BEGIN

v_<name>   :=  <function_name>(<value>)

END;

EXECUTE dbms_output.put_line(<function_name>(<value>));

SELECT <function_name>(<value>)

  FROM <tabelle>;

Beispiel:

select mytools.get_Value(5) from dual;

SQL dynamisch ausführen mit Bind Variablen

##################### Wert in Variable ##########################
 

PROCEDURE s_leseLetzteLfdNr ( p_i_tabelle IN VARCHAR2 )

/****************************************************************
 *  BESCHREIBUNG: Die ID des letzten Transfervorganges wird

 *  aus der Steuertabelle abgefragt.
 *
 *  PARAMETER:    p_i_tabelle IN          Tabelle, deren Daten

 *                                        transferiert werden.
 *
 *  FEHLER:        
 *                 
 *
 *  CHANGE HISTORY:
 *  Datum       Name            Beschreibung
 *  ----------  -------------- ----------------------------------
 *  ...
 **************************************************************/
IS
 

  sqlStr VARCHAR2(1000);

  g_offset NUMBER(30) := 1;

BEGIN
  -- Die letzte laufende Nummer aus der Remote-DB lesen
  -- und in der Konfigurationstabelle ablegen.
 
  sqlStr := 'SELECT max_id
               FROM remote_tabelle@remoteUser
              WHERE tabname = :get_table
                AND attribut = ''SPALTE''';
   
  DBMS_OUTPUT.PUT_LINE(sqlStr);
   
  EXECUTE immediate sqlStr INTO g_offset USING p_i_tabelle;
   
END s_leseLetzteLfdNr;


##################### Update in Tabelle ####################### 

PROCEDURE s_schreibeLetzteLfdNr ( p_i_tabelle IN VARCHAR2 )

/***************************************************************
 *  BESCHREIBUNG: Die ID des letzten Transfervorganges wird

 *  aus der Steuertabelle (remote_tabelle) abgefragt und in 
 *  der Konfigurationstabelle (config_table) festgehalten.
 *
 *  PARAMETER:    p_i_tabelle IN          Tabelle, deren Daten
 *                                        transferiert werden.
 *
 *  FEHLER:        
 *                 
 *
 *  CHANGE HISTORY:
 *  Datum       Name            Beschreibung
 *  ----------  -------------- ------------------------------
 *  ...
 ************************************************************/
IS
 

  sqlStr VARCHAR2(1000);

BEGIN
  -- Die letzte laufende Nummer aus der Remote-DB lesen
  -- und in der Konfigurationstabelle ablegen.
 
    sqlStr := 'UPDATE config_table
                  SET max_id = (
                    SELECT max_id
                      FROM remote_tabelle@remoteUser
                     WHERE tabname = :get_table
                        AND attribut = ''SPALTE'')
                WHERE t_name = :get_table';
   
    DBMS_OUTPUT.PUT_LINE(sqlStr);

    EXECUTE immediate sqlStr USING p_i_tabelle, p_i_tabelle;
   
END s_schreibeLetzteLfdNr;

http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_dynamic_sql.htm
http://stackoverflow.com/questions/7816402/using-bind-variables-with-dynamic-select-into-clause-in-pl-sql  
http://www.oracle-base.com/articles/9i/dynamic-binds-using-contexts.php