Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2011
    Posts
    7

    Unanswered: Updating View Parameters

    I have created a context and a package for updating view parameters like so

    Code:
    create or replace context PnB_CONTEXT using PKG_PLANS;
    
    create or replace
    package PKG_PLANS as 
    
            procedure PRC_SET_PLANS_CONTEXT 
            (
              p_project        in      varchar2,
              p_blank        in      varchar2
            );
      
            procedure PRC_CLEAR_PLANS_CONTEXT 
            (
              p_project        in      varchar2
             );
    
    end PKG_PLANS;
    
    create or replace
    package body PKG_PLANS as
    
              procedure PRC_SET_PLANS_CONTEXT 
              (
              p_project        in      varchar2,
              p_blank        in      varchar2
              )
            as
            begin
      
             dbms_session.set_context('PnB_CONTEXT', p_project,p_blank);
     
           end PRC_SET_PLANS_CONTEXT;
     
           procedure PRC_CLEAR_PLANS_CONTEXT 
           (
             p_project        in      varchar2
           )
           as
           begin
     
            dbms_session.clear_context('PnB_CONTEXT', p_project);
     
           end PRC_CLEAR_PLANS_CONTEXT;
           
    end PKG_PLANS;
    Now am running into a couple of issues. The first one is it appears it appears set_context requires 2 input parameters even though am only going to use one. I have thus improvised with a blank parameter p_blank. If I do not provide this it will not compile.

    The second issue is am actually unable to set the views parameters with this script

    Code:
    PnB_CONTEXT.PRC_CLEAR_PLANS_CONTEXT('project');
    PnB_CONTEXT.PRC_SET_PLANS_CONTEXT('project','');
    Is my context created correctly? The error am getting when I try to update the parameters is the command is unknown.

  2. #2
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Hi,
    you have a misunderstanding of the context - concept.
    A Context is always a PAIR consisting of a parameter and its value.

    This is a complete simplified example:

    Code:
    drop table PROJECTS purge;
    drop context TEST_CTX;
    
    -- we create a table with 2 columns: one holds the project name, 
    -- the second one holds the name of an employee working on this project
    create table projects (prj_name varchar2(32), prj_emp varchar2(32), constraint uq_prj_nam_emp unique (prj_name,prj_emp));
    
    -- now fill it with some data
    insert into projects values ('PRJ_A', 'SCOTT');
    insert into projects values ('PRJ_A', 'ADAMS');
    insert into projects values ('PRJ_B', 'BLAKE');
    insert into projects values ('PRJ_B', 'KING');
    insert into projects values ('PRJ_B', 'SMITH');
    insert into projects values ('PRJ_A', 'JONES');
    commit;
    
    -- create a simple context - package
    create or replace package TEST_CTX
    as
       procedure setCTXVal(pAttribute in varchar2, pValue in varchar2);
       procedure clearCTX (pAttribute in varchar2 default null);
    end TEST_CTX;
    /
    
    create or replace package body TEST_CTX
    as
       procedure setCTXVal(pAttribute in varchar2,pValue in varchar2)
       as
       begin
          dbms_session.set_context('TEST_CTX',upper(pAttribute),pValue);
       end setCTXVal;
       
       procedure clearCTX(pAttribute varchar2 default null)   -- if null the entire context is cleared
       as
       begin
          DBMS_SESSION.CLEAR_CONTEXT(namespace => 'TEST_CTX', attribute => upper(pAttribute));
       end clearCTX;
    begin
         null;
    end TEST_CTX;
    /
    
    -- create the context itself
    create context TEST_CTX using TEST_CTX;
    
    -- and create a view showing only the employees of the currently set context
    create or replace view VEMP as
    select * from projects where upper(prj_name) = sys_context('TEST_CTX','PROJECT_NAME');
    Now, everything is set up, so let's test it:

    Code:
    C:\>sqlplus test
    
    SQL*Plus: Release 11.2.0.2.0 Production on Fri Aug 19 17:12:14 2011
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    Enter password:
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> exec test_ctx.setCTXVal('PROJECT_NAME','PRJ_A');
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from vemp;
    
    PRJ_NAME                         PRJ_EMP
    -------------------------------- --------------------------------
    PRJ_A                            ADAMS
    PRJ_A                            JONES
    PRJ_A                            SCOTT
    
    SQL> exec test_ctx.clearCTX('PROJECT_NAME');
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from vemp;
    
    no rows selected
    
    SQL> exec test_ctx.setCTXVal('PROJECT_NAME','PRJ_B');
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from vemp;
    
    PRJ_NAME                         PRJ_EMP
    -------------------------------- --------------------------------
    PRJ_B                            BLAKE
    PRJ_B                            KING
    PRJ_B                            SMITH
    
    SQL>
    Last edited by magicwand; 08-19-11 at 12:32. Reason: typos fixed
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •