User Tools

Site Tools


database:plsql

DATE and TIMESTAMP conversion

DECLARE
  d DATE;
  ts TIMESTAMP;
BEGIN
  d := DATE '2010-12-31';
  d := TO_DATE('2010-12-31', 'YYYY-MM-DD');
  ts := TIMESTAMP '2010-12-31 23:59:59.999999';
  ts := TO_TIMESTAMP('2010-12-31 23:59:59.999', 'YYYY-MM-DD HH24:MI:SS.FF3');
END;

CASE expression

SELECT 
  -- wie ?: Operator oder if elseif elseif ...
  CASE 
    WHEN LENGTH(s.param_val) > 30 THEN SUBSTR(s.param_val, 1, 27) || '...'
    ELSE s.param_val
  END pvalue,
  -- wie switch
  CASE s.param_type
    WHEN 'S' THEN 'String'
    WHEN 'N' THEN 'Number'
    WHEN 'B' THEN 'Boolean'
    ELSE '?'
  END ptype
FROM tab1 s

PL/SQL Block

Exception Handling

 CREATE OR REPLACE TRIGGER tr_Tablename
 BEFORE UPDATE OR INSERT OR DELETE ON Tablename FOR EACH ROW
 DECLARE
     changed_table_not_visible EXCEPTION;
     PRAGMA EXCEPTION_INIT(changed_table_not_visible, -4091);
 BEGIN
     ...
 EXCEPTION
     WHEN changed_table_not_visible THEN NULL;
 END;

Mutating Table Problem

If you need to update a mutating or constraining table, you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers-an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table. (Entnommen von http://otn.oracle.com).

Misc

  • DBMSLOB.getlength(colname) * prepared statements cache: select sqltext from v$open_cursor
database/plsql.txt · Last modified: 2011/02/02 16:57 by hgoebl