Authorization Schemes Script & Video Demo

Oracle Application Express is a rapid development tool for Web applications on the Oracle database.
Post Reply
admin
Posts: 2061
Joined: Fri Mar 31, 2006 12:59 am
Location: Pakistan
Contact:

Authorization Schemes Script & Video Demo

Post by admin »

Code: Select all

BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE ERPSTUFF_EMPS';
      EXECUTE IMMEDIATE 'DROP TABLE ERPSTUFF_DEPTS';      
  EXCEPTION
      WHEN OTHERS THEN NULL;
  END;
  /

CREATE TABLE  "ERPSTUFF_EMPS" 
   (	"EMPNO" NUMBER(4,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 90000 INCREMENT BY 10 START WITH 8000 NOCACHE NOT NULL ENABLE, 
	"ENAME" VARCHAR2(50) NOT NULL ENABLE, 
	"JOB" VARCHAR2(50), 
	"MGR" NUMBER(4,0), 
	"HIREDATE" DATE NOT NULL ENABLE, 
	"SAL" NUMBER(7,2) NOT NULL ENABLE, 
	"COMM" NUMBER(7,2), 
	"DEPTNO" NUMBER(4,0) NOT NULL ENABLE, 
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"CREATED_BY_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"LAST_UPDATED_DATE" DATE NOT NULL ENABLE, 
	"LAST_UPDATED_BY" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	 CONSTRAINT "ERPSTUFF_EMPS_PK" PRIMARY KEY ("EMPNO")
  USING INDEX  ENABLE
   )
/

CREATE TABLE  "ERPSTUFF_DEPTS" 
   (	"DEPTNO" NUMBER(4,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9000 INCREMENT BY 10 START WITH 50 NOCACHE NOT NULL ENABLE, 
	"DNAME" VARCHAR2(50), 
	"LOC" VARCHAR2(50), 
	 CONSTRAINT "ERPSTUFF_DEPTS_PK" PRIMARY KEY ("DEPTNO")
  USING INDEX  ENABLE
   )
/

CREATE OR REPLACE FORCE EDITIONABLE VIEW "ERPSTUFF_EMPS_V" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO", "CREATED_DATE", "CREATED_BY_USER", "LAST_UPDATED_DATE", "LAST_UPDATED_BY") AS 
  select EMPNO,
       ENAME,
       JOB,
       (select "ENAME" from "ERPSTUFF_EMPS" x where x."EMPNO" = a."MGR") "MGR",
       HIREDATE,
       SAL,
       COMM,
       (select "DNAME" from "ERPSTUFF_DEPTS" x where x."DEPTNO" = a."DEPTNO") "DEPTNO",
       CREATED_DATE,
       CREATED_BY_USER,
       LAST_UPDATED_DATE,
       LAST_UPDATED_BY
from "ERPSTUFF_EMPS" a
/

ALTER TABLE  "ERPSTUFF_EMPS" ADD CONSTRAINT "ERPSTUFF_EMPS_DEPT_FK" FOREIGN KEY ("DEPTNO")
	  REFERENCES  "ERPSTUFF_DEPTS" ("DEPTNO") ENABLE
/
ALTER TABLE  "ERPSTUFF_EMPS" ADD CONSTRAINT "ERPSTUFF_EMPS_MGR_FK" FOREIGN KEY ("MGR")
	  REFERENCES  "ERPSTUFF_EMPS" ("EMPNO") ENABLE
/
CREATE INDEX  "ERPSTUFF_EMPS_MGR" ON  "ERPSTUFF_EMPS" ("MGR")
/
CREATE INDEX  "ERPSTUFF_EMPS_DEPTNO" ON  "ERPSTUFF_EMPS" ("DEPTNO")
/

CREATE OR REPLACE EDITIONABLE TRIGGER  "ERPSTUFF_EMPS_BIU" 
    before insert or update   
    on ERPSTUFF_EMPS  
    for each row  
begin  
    if inserting then  
        :new.created_date := sysdate;  
        :new.created_by_user := coalesce(sys_context('APEX$SESSION','APP_USER'),user);  
    end if;  
    :new.last_updated_date := sysdate;  
    :new.last_updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);  
end; 
/

BEGIN
    APEX_UTIL.REMOVE_USER(p_user_name => 'MAHRUKH');
    APEX_UTIL.REMOVE_USER(p_user_name => 'SIKANDAR');

    APEX_UTIL.CREATE_USER(
        p_user_name                     => 'MAHRUKH',
        p_first_name                    => 'MAHRUKH',
        p_last_name                     => 'MALIK',
        p_description                   => 'ERPSTUFF YOUTUBE CHANNEL VIDEO',
        p_email_address                 => 'mahrukh.malik@erpstuff.com',
        p_web_password                  => 'password1',
        p_developer_privs               => 'CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
        p_default_schema                => 'WKSP_ERPSTUFF',
        p_change_password_on_first_use  => 'N',
	p_attribute_01                  => 'MANAGER');

    APEX_UTIL.CREATE_USER(
        p_user_name                     => 'SIKANDAR',
        p_first_name                    => 'SIKANDAR',
        p_last_name                     => 'MALIK',
        p_description                   => 'ERPSTUFF YOUTUBE CHANNEL VIDEO',
        p_email_address                 => 'sikandar.malik@erpstuff.com',
        p_web_password                  => 'password1',
        p_developer_privs               => 'CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
        p_default_schema                => 'WKSP_ERPSTUFF',
        p_change_password_on_first_use  => 'N',
	p_attribute_01                  => 'USER');
END; 
Malik Sikandar Hayat
admin@erpstuff.com
Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests