Saturday, April 10, 2010

Parameterized View in Oracle

There is nothing like parameterize view in Oracle, but you can use parameterize view in oracle following way. There is two possible option, You can use the User Environment variable to manage a session variable (dbms_application_info package), another way is write a package to get and set value parameterize view in the global variable or sys_context, later on a view can use the value from, which called so called parameterized view.


Example:


Creating Table


CREATE TABLE APPLICATIONS (
    APPLICATION_CN             VARCHAR2 (10 BYTE) NOT NULL ENABLE,
    APPLICATION_NAME           VARCHAR2 (20 BYTE) NOT NULL ENABLE,
    APPLICATION_DESCRIPTION    VARCHAR2 (200 BYTE),
    APPLICATION_STATUS         VARCHAR2 (5 BYTE) DEFAULT 'True'
  );

Inserting Data in to the table


Insert into APPLICATIONS (APPLICATION_CN,APPLICATION_NAME,APPLICATION_DESCRIPTION,APPLICATION_STATUS) 
values ('10001','ERP','Enterprise resource Planning ','True');

Insert into APPLICATIONS (APPLICATION_CN,APPLICATION_NAME,APPLICATION_DESCRIPTION,APPLICATION_STATUS) 
values ('10002','Oracle ERP','Oracle Enterprise resource Planning ','True');

Insert into APPLICATIONS (APPLICATION_CN,APPLICATION_NAME,APPLICATION_DESCRIPTION,APPLICATION_STATUS) 
values ('10003','Web Base Application','Web Base Application','True');

Insert into APPLICATIONS (APPLICATION_CN,APPLICATION_NAME,APPLICATION_DESCRIPTION,APPLICATION_STATUS) 
values ('10004','Desktop Application','Desktop Application','True');

Commit;

Create a Package


create or replace
PACKAGE TestParam_VW_PKG AS
PROCEDURE P_SET_APPLICATION_CN (  
    p_Application_CN            VARCHAR2
  );

  FUNCTION F_GET_APPLICATION_CN RETURN VARCHAR2;
END TestParam_VW_PKG;

Create a Package body for Get and Set operation


 
create or replace
PACKAGE BODY TestParam_VW_PKG AS
  g_Application_CN            VARCHAR2(10);
  PROCEDURE P_SET_APPLICATION_CN (  
    p_Application_CN          VARCHAR2
  ) AS
  BEGIN
    g_Application_CN := p_Application_CN;
  END;

  FUNCTION F_GET_APPLICATION_CN RETURN VARCHAR2
  IS
  BEGIN
    RETURN g_Application_CN;
 END;
END TestParam_VW_PKG;

Create a View base on the Packege


CREATE OR REPLACE FORCE VIEW APPLICATION_VW ("APPLICATION_CN", "APPLICATION_NAME", "APPLICATION_DESCRIPTION", "APPLICATION_STATUS")
AS
  SELECT APPLICATION_CN,
    APPLICATION_NAME,
    APPLICATION_DESCRIPTION,
    APPLICATION_STATUS
  FROM  APPLICATIONS
  WHERE APPLICATION_CN = TestParam_VW_PKG.F_GET_APPLICATION_CN;

How to use the view


SQL> Exec TESTPARAM_VW_PKG.P_SET_APPLICATION_CN('10001');

PL/SQL procedure successfully completed.

SQL> SELECT APPLICATION_CN, APPLICATION_NAME, APPLICATION_DESCRIPTION, APPLICATION_STATUS
FROM APPLICATION_VW ;
APPLICATION_CN APPLICATION_NAME APPLICATION_DESCRIPTION        APPLICATION_STATUS
--------------- -------------------- ----------------------------- ------------------
10001           ERP       Enterprise resource Planning   True

No comments:

Post a Comment