Oracle basics

 

 

Create foreign key

ALTER TABLE Schema.CONTRACT ADD
   CONSTRAINT FK_CONTRACT_PRODUCT
   FOREIGN KEY (PRODUCT_CODE)
   REFERENCES Schema.C_PRODUCT (PRODUCT_CODE)

Adding Grant to user

grant

select on C_PRODUCT to XXXACC

Must be called from XXX_DATA user, or other user which has privilagies to grant rules.

grant [select, update, delete,insert]  on C_PRODUCT to XXXACC

 

Create Sequence

create

sequence seq01 INCREMENT BY 1 MINVALUE 0 MAXVALUE 99999 START WITH 0;

 

Create Identity in Oracle 9i

First at all, create Sequence:

CREATE SEQUENCE seq01 INCREMENT BY 1 MINVALUE 0 MAXVALUE 99999 START WITH 0;

then you must create trigger, which gets new ID from sequence and than it fill Id attribute of new record before inserting.

CREATE

OR REPLACE TRIGGER [Schema].tg_bi_test
BEFORE INSERT
ON [SCHEMA].SEQUENCEDTABLE
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar NUMBER;

BEGIN
   tmpVar := 0;
   SELECT seq01.NEXTVAL INTO tmpVar FROM dual;
   :NEW.id := tmpVar;
   EXCEPTION
      WHEN OTHERS THEN
      – Consider logging the error and then re-raise
      RAISE;
END tg_bi_test;

Create stored procedure

CREATE

OR REPLACE PROCEDURE [SCHEMA].AddBank( Bank_Code CHAR,
  Bank_Name CHAR,
  pCountry CHAR
  DEFAULT ‘XX’)
IS
BEGININSERT INTO BANK (BankCode,BankName,Country)  VALUES(Bank_Code, Bank_Name,pCountry);
END AddBank;

Call stored procedure

exec

ADDBANK(‘aaa’,‘bbb’);

(NHibernate call:)
var query = session.CreateSQLQuery("call ADDBANK(:Bank_Code, :Bank_Name,:pCountry)");
query.SetString("Bank_Code", "0808");
query.SetString("Bank_Name", "Franklin’s bank");
query.SetString("pCountry", "XX");
var l = query.ExecuteUpdate();