oracle - Synaptica srl

Oracle Tips #4 [Trigger : Mutating Tables & Autonomos Transactions]

per Ivan Revelli
in Senza categoria
on Marzo 8, 2010

Oracle Version : 10g As you know inside a Oracle row trigger you cannot access to the base table, for example when you make a trigger like this, on a table named “activity“.

CREATE OR REPLACE TRIGGER UNAME.ACTIVITY_FK_MENU_INTEGRITY
AFTER INSERT OR UPDATE
ON UNAME.ACTIVITY
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE

tmpVar NUMBER;
e_Menu_Already_Exists EXCEPTION;

BEGIN
	   Select count(*) into tmpVar from Activity aa where (aa.fk_menu = :new.fk_menu)and(aa.pk_id <> :new.pk_id);
	   
	   
	  -- if (tmpVar > 0) then 
	     --:New.FK_MENU := null;
		-- raise e_Menu_Already_Exists;
	  -- end if;
	   
END ACTIVITY_FK_MENU_INTEGRITY;

CREATE OR REPLACE TRIGGER UNAME.ACTIVITY_FK_MENU_INTEGRITY

AFTER INSERT OR UPDATE

ON UNAME.ACTIVITY

REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

DECLARE

tmpVar NUMBER;

e_Menu_Already_Exists EXCEPTION;

BEGIN

Select count(*) into tmpVar from Activity aa where (aa.fk_menu = :new.fk_menu)and(aa.pk_id <> :new.pk_id);

-- if (tmpVar > 0) then

--:New.FK_MENU := null;

-- raise e_Menu_Already_Exists;

-- end if;

END ACTIVITY_FK_MENU_INTEGRITY;

when you try to use the table making an update happen this : To avoid this error and obviusly assuming the…
Leggi tutto

Oracle Tips #3 [Extract Metadata Info ]

per Ivan Revelli
in Articoli, Articoli, Articoli, Articoli, databases
on Febbraio 19, 2010

by using some simple view in Oracle you can extract a lot of information about your DB schema, some explicit samples : (assuming that MyDBOwner is the owner of the DB and ACTIVITY is a table of a schema, this sample is succesfully tested on Oracle 10g)

-- LIST OF USERS 

select OWNER,COUNT(DISTINCT TABLE_NAME) from ALL_TAB_COLUMNS GROUP BY OWNER


-- LIST OF TABLES PER OWNER

select table_name from ALL_TAB_COLUMNS  where owner = 'MyDBOwner' group by table_name

-- LIST OF FIELDS PER TABLE

select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, CHAR_LENGTH from ALL_TAB_COLUMNS  where owner = 'MyDBOwner' AND TABLE_NAME = 'ACTIVITY'

-- FINDING PRIMARY KEYS

select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, CHAR_LENGTH from ALL_TAB_COLUMNS  where owner = 'MyDBOwner' AND TABLE_NAME = 'ACTIVITY'


-- A single character that indicates the type of constraint: 'C' = NOT NULL, 'P' = PRIMARY KEY, 'U' = UNIQUE, and 'R' = FOREIGN KEY.


SELECT UC.CONSTRAINT_NAME, UCC.* FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC WHERE UC.OWNER='MyDBOwner' AND UC.TABLE_NAME = 'ACTIVITY' AND UC.CONSTRAINT_TYPE = 'P' AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME

-- LIST OF USERS

select OWNER,COUNT(DISTINCT TABLE_NAME) from ALL_TAB_COLUMNS GROUP BY OWNER

-- LIST OF TABLES PER OWNER

select table_name from ALL_TAB_COLUMNS where owner = 'MyDBOwner' group by table_name

-- LIST OF FIELDS PER TABLE

select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, CHAR_LENGTH from ALL_TAB_COLUMNS where owner = 'MyDBOwner' AND TABLE_NAME = 'ACTIVITY'

-- FINDING PRIMARY KEYS

select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, CHAR_LENGTH from ALL_TAB_COLUMNS where owner = 'MyDBOwner' AND TABLE_NAME = 'ACTIVITY'

-- A single character that indicates the type of constraint: 'C' = NOT NULL, 'P' = PRIMARY KEY, 'U' = UNIQUE, and 'R' = FOREIGN KEY.

SELECT UC.CONSTRAINT_NAME, UCC.* FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC WHERE UC.OWNER='MyDBOwner' AND UC.TABLE_NAME = 'ACTIVITY' AND UC.CONSTRAINT_TYPE = 'P' AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME

a great article about this is :…
Leggi tutto

Oracle Tips #1 [Common Functions]

per Ivan Revelli
in oracle
on Febbraio 8, 2010

I just starting on using Oracle 10g …. how to 1 : how retrive a guid from Oracle

select sys_guid() from dual;

1	select sys_guid() from dual;

how to 2 : how retrive next value from a Sequence

select [your sequence name].nextval into   from dual ;

1	select [your sequence name].nextval into from dual ;

how to 3 : Oracle db Link , how to manage sql between multiple schema … With DB Link you can produce sql /…
Leggi tutto

Oracle 10g and XML

per Ivan Revelli
in Articoli, databases, databases, oracle, oracle
on Gennaio 28, 2010

PLSQL is a world …. and Oracle an incredible RDBMS, so if you want to produce XML from query or manage XML inside your PLSQL Trigger or Procedure you can in different ways …. One of the most simple way is using the Oracle function to do that, like :

SELECT sys_xmlgen(pk_id) from sellers;

SELECT XMLELEMENT("MAH", XMLELEMENT("pk_id", e.pk_id ||' '|| e.cod_fiscale),
                           XMLELEMENT ( "miamail", e.email)) AS "result"
FROM sellers e;

SELECT sys_xmlgen(pk_id) from sellers;

SELECT XMLELEMENT("MAH", XMLELEMENT("pk_id", e.pk_id ||' '|| e.cod_fiscale),

XMLELEMENT ( "miamail", e.email)) AS "result"

FROM sellers e;

only with this piece…
Leggi tutto

How to create a table from a select

per Ivan Revelli
in Programmazione
on Maggio 17, 2009

sometime happen that you need to create a table from a query… now we can assume that you have old_table and you wanna build a copi of entire data into another table new_table… This operation is not a standard sql but a lot DataBases can do this : in Oracle you can do this like…
Leggi tutto