Parameterized view

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
mbfilho
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Thu, 26 Jan 2006 10:46 am
Location: Aracaju-SE

Hello,

I'm starting in Oracle, and would like to know how I can generate a parameterized view?

Thanks from now,
Marcos Bastos
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

Hello MBFILE

Can not place parameters in a view. (at least not as is done with cursors or functions).

What you can do is the following: Put the values ??in a public place of the bank. (For example, a parameter table) ... hence, the view queries this table and you can recover the desired parameter.

Another way is to use the "global variables" of SQL that are the contexts.
there is an example:

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> 
 
: -O
sp66d_rac6r
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 9
Joined: Tue, 02 Dec 2008 10:43 am
Location: Sorocaba-SP

What to do when you do not have System privileges?

Thank you in advance.
User avatar
fbifabio
Moderador
Moderador
Posts: 199
Joined: Tue, 22 Feb 2011 1:51 pm
Location: São Paulo - SP
Contact:

MBFILE, instead of creating a table to filter the view, you can also create only one specification package with a variable, as in the example of the link below:
http://www.programadoras.com.br/?p=241
Att,

Fábio Prado www.fabioprado.net
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Bing [Bot] and 12 guests