Hello,
I'm starting in Oracle, and would like to know how I can generate a parameterized view?
Thanks from now,
Marcos Bastos
Parameterized view
- dr_gori
- 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
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:
: -O
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:
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>
-
- 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.
Thank you in advance.
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
http://www.programadoras.com.br/?p=241
Att,
Fábio Prado www.fabioprado.net
-
- Information
-
Who is online
Users browsing this forum: Bing [Bot] and 12 guests