Parameterized View

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Thu, 26 Jan 2006 10:52 am

Hello, I'm starting in Oracle, and I wonder how I can generate a PARAMETERIZED VIEW?

Thanking you in advance, Marcos Bastos
mbfilho
Location: Aracaju-SE

Poston Thu, 26 Jan 2006 2:40 pm

Hello mbfilho you cannot put parameters in a VIEW. (at least, not how it's done with cursors or functions).

What you can do is the following: place the values in a public place. (for example, a table of parameters). Hence, the view query this table and you can retrieve the desired parameter.

Another way is to use the \"global variables\" of SQL that are the contexts.
Here's an example:
Code: Select all
system@O8IR3:SQL>create or replace context my_ctx USING
2 my_procedure;

Contexto criado.

system@O8IR3:SQL>create or replace procedure my_procedure(
2 p_name in varchar2, p_value in number)
3 as
4 begin
5 dbms_session.set_context( 'my_ctx', p_name, p_value );
6 end;
7 /

Procedimento criado.

system@O8IR3:SQL>grant execute on MY_PROCEDURE to scott;

Operação de Grant bem-sucedida.

scott@O8IR3:SQL>create or replace view V_TESTE as
2 select deptno, dname from dept where deptno > sys_context(
'my_ctx', 'deptno')
3 UNION ALL
4 select deptno, dname from dept where deptno < sys_context(
'my_ctx', 'deptno')
5 ;

View criada.

scott@O8IR3:SQL>exec system.MY_PROCEDURE('deptno', 30);

Procedimento PL/SQL concluído com sucesso.

scott@O8IR3:SQL>select * from V_TESTE;

DEPTNO DNAME
------------------ --------------
40 OPERATIONS
10 ACCOUNTING
20 RESEARCH

scott@O8IR3:SQL>

:-
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 30 Jun 2011 10:16 am

What to do when you don't have SYSTEM privileges?

Thank you in advance.
sp66d_rac6r
Location: Sorocaba-SP

Poston Mon, 15 Aug 2011 5:00 pm

mbfilho, rather than creating a table to filter the view, you can also create a package specification with a variable, as in the example from the link below: http://www.programadoras.com.br/?p=241 Att, Fabio Prado http://www.fabioprado.net
fbifabio
Location: São Paulo - SP


  • See also
    Replies
    Views
    Last Post


      Return to SQL

      Who is online

      Users browsing this forum: No registered users and 3 guests