Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Maria Alejandra.

Asked: February 16, 2017 - 2:55 pm UTC

Last updated: February 18, 2017 - 4:27 am UTC

Version: Oracle 10

Viewed 10K+ times! This question is

You Asked

It is possible to use bind variables in a view?
It is possible to replace the values ​​10, 11, 12 and VISOR by BindVariables or how can I optimize this view?


CREATE OR REPLACE VIEW VIEW1 AS
SELECT a.column1, a.column2
FROM TABLA1 a
WHERE a.column3 IN (10, 11, 12)
AND a.column4 >= (SELECT max(b.fecha) FROM TABLA2 b WHERE b = 'VISOR');

and Connor said...

Use a context,eg

SQL> create or replace context my_ctx using my_proc
  2  /

Context created.

SQL> create or replace procedure my_proc( p_a in number, p_b in date, p_c in varchar2 )
  2  as
  3  begin
  4          dbms_session.set_context( 'my_ctx', 'a', to_char(p_a) );
  5          dbms_session.set_context( 'my_ctx', 'b', to_char(p_b,'yyyymmddhh24miss') );
  6          dbms_session.set_context( 'my_ctx', 'c', p_c );
  7  end;
  8  /

Procedure created.

SQL> create or replace view my_view
  2  as
  3  select sys_context( 'my_ctx', 'a' ) a,
  4         sys_context( 'my_ctx', 'b' ) b,
  5         sys_context( 'my_ctx', 'c' ) c
  6    from dual;

View created.

SQL> exec my_proc( 42, sysdate, 'hello world' );

PL/SQL procedure successfully completed.

SQL> select * from my_view;

A          B               C
---------- --------------- ---------------
42         20060503131617  hello world


The sys_context can be part of the select, the where, etc etc - its just like a bind variable.

https://docs.oracle.com/database/121/DBSEG/app_context.htm#DBSEG011

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Complete

A reader, February 17, 2017 - 5:12 am UTC

I am new to this. So how could be VIEW1 rewritten using context?
Connor McDonald
February 18, 2017 - 4:23 am UTC 

create or replace
view OBJECTS_BY_SCHEMA as
select *
from dba_objects
where owner = sys_context('MY_CONTEXT','NOMINATED_SCHEMA')


and then just set the context to the value you want, and then do "select * from OBJECTS_BY_SCHEMA"

Maria Alejandra, February 17, 2017 - 9:02 am UTC


Constants instead of using context variables?

Maria Alejandra, February 17, 2017 - 9:10 am UTC

Is it efficient to use constants defined in a package specification instead of using context variables?

eg:
create or replace PACKAGE PK_VIEWS AS
v_constant01 varchar2(100) := 'TC';
END PK_VIEWS;

--
CREATE OR REPLACE FORCE VIEW MY_VIEW("TDC_COD", "TDC_DES") AS
SELECT TDC_COD, TDC_DES
FROM MY_TABLE
WHERE TDC_COD = PK_VIEWS.v_constant01;


Connor McDonald
February 18, 2017 - 4:27 am UTC

THe main issue with that approach is that it doesnt work :-)

SQL> create or replace PACKAGE PK_VIEWS AS
  2  v_constant01 varchar2(100) := 'TC';
  3  END PK_VIEWS;
  4  /

Package created.

SQL>
SQL> --
SQL> CREATE OR REPLACE VIEW MY_VIEW as
  2  SELECT *
  3  FROM dual
  4  WHERE dummy = PK_VIEWS.v_constant01;
WHERE dummy = PK_VIEWS.v_constant01
              *
ERROR at line 4:
ORA-06553: PLS-221: 'V_CONSTANT01' is not a procedure or is undefined


You would have to return the value via a package *function*...
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library