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();
