Home » SQL & PL/SQL » SQL & PL/SQL » Create simple view
Create simple view [message #37417] Wed, 06 February 2002 20:23 Go to next message
diaz
Messages: 58
Registered: October 2001
Member
hi, i'd like to creat a simple view through a procedure..
the code is like this :

create or replace procedure cview_case3
(myeop varchar(6)) as
begin
create or replace view isitest as
select * from tagihan01 where eop=myeop;
end cview_case3;
end;
/

and i've got the err message like this :

SQL> create or replace procedure cview_case3 (myeop varchar(6)) as
begin
create or replace view isitest as
select * from tagihan01 where eop=myeop;
end cview_case3;
2 3 4 5 6
7 /

Warning: Procedure created with compilation errors.

SQL> execute cview_case3(200110);
BEGIN cview_case3(200110); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:

can you tell me how to work it out ?
thanks
Re: Create simple view [message #37422 is a reply to message #37417] Thu, 07 February 2002 00:39 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
hi

try this

create or replace procedure cview_case3
(myeop varchar(6)) as
begin
execute immediate
'create or replace view isitest as
select * from tagihan01 where eop='||myeop;
end;
/

cheers
pratap
Re: Create simple view [message #37426 is a reply to message #37422] Thu, 07 February 2002 01:12 Go to previous messageGo to next message
diaz
Messages: 58
Registered: October 2001
Member
hi, thanks for the reply
but still won't work

SQL> create or replace procedure cview_case3
(myeop varchar(6)) as
begin
execute immediate
'create or replace view isitest as
select * from tagihan01 where eop='||myeop;
end;
/

2 3 4 5 6 7 8
Warning: Procedure created with compilation errors.
Re: Create simple view - expanded question [message #37443 is a reply to message #37422] Thu, 07 February 2002 16:19 Go to previous messageGo to next message
diaz
Messages: 58
Registered: October 2001
Member
thank you very much pratap, it's working now..
but, how can i manage the view so that it won't have to be created everytime i called the procedure ?

actually i need something like one fix table to be queried from web page (asp page)
but, unfortunately i have this 'eop' thing that have to be taken care of..

can you give me some idea to solve it ?

thanks again
Re: Create simple view [message #37448 is a reply to message #37417] Thu, 07 February 2002 23:29 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
Hi,

You can avoid creating view.every time you query
add the clause " where eop=myeop".this will do the job.

hope it helps!!!

cheers
pratap
Re: Create simple view - expanded question [message #246071 is a reply to message #37443] Tue, 19 June 2007 15:14 Go to previous messageGo to next message
phantc
Messages: 1
Registered: June 2007
Junior Member
Unable to create view inside a procedure
Re: Create simple view [message #246072 is a reply to message #37417] Tue, 19 June 2007 15:22 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
phantc,
Please read & FOLLOW the #1STICKY post at the top of this forum
http://www.orafaq.com/forum/t/42428/74940/
Re: Create simple view - expanded question [message #246073 is a reply to message #246071] Tue, 19 June 2007 15:27 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Clue: "create view" privilege is mandatory.
Clue: role are not enabled inside a view.

Regards
Michel

[Updated on: Tue, 19 June 2007 15:28]

Report message to a moderator

Previous Topic: Regd CONCAT_ALL
Next Topic: How indexes improve performace?
Goto Forum:
  


Current Time: Mon Dec 05 13:11:55 CST 2016

Total time taken to generate the page: 0.05365 seconds