Topic: SQL*Plus and PL/SQL >> How to create a view dynamically?
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: How to create a view dynamically? |
| anand |
Posted: Aug 14, 2007 07:15:39 AM |
Total Post: 2
Joined: Aug, 2007
|
hello friends,
I am trying to create a view dynamically in a procedure. My friend gave a idea of using EXECUTE IMMEDIATE('CREATE VIEW...') . I tried out that, but that is not working.
anyone have a better idea? |
|
|
 anand |
| Posted: Aug 14, 2007 07:45:42 AM |  |
|
Total Post: 2
Joined: Aug, 2007
|
sorries.. worked out the problem with subqueries itself.. :)
|
|
|
|
|
| Mitesh |
| Posted: Aug 28, 2007 05:29:25 AM | |
|
Total Post: 6
Joined: Aug, 2007
|
Hi
Anand
Your friend is saying correct that u have to execute immediate ,
it will work.
i am giving u below example.
before executing this pl/sql , u have to first create table testindex with column name 'a'.
After that execute below code.
declare
-- Local variables here
v_a integer;
strSql varchar2(500);
v_viewname varchar2(100) :='v_testindex';
begin
-- Test statements here
execute immediate 'create or replace view ' || v_viewname || ' as select * from testindex';
execute immediate 'create or replace view ' || v_viewname || ' as select * from testindex';
--below query should be return single row
execute immediate 'select a from ' || v_viewname || ' t where t.a=1' into v_a;
dbms_output.put_line('value of v_a is ' || v_a);
end;
Thanks
Mitesh Bhimjiyaani
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 327 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
