Home » SQL & PL/SQL » SQL & PL/SQL » how create view in procedure
how create view in procedure [message #228054] Sat, 31 March 2007 01:29 Go to next message
mfa786
Messages: 210
Registered: February 2006
Location: karachi
Senior Member
hi master
sir i use this code for createing view but oracle give me error


create or replace procedure MFATTT (tt VARCHAR2) is

begin

create view aamir2 as select accid from accbal;

end;

please give me idea ho i create view in procedure

thank you

aamir
Re: how create view in procedure [message #228056 is a reply to message #228054] Sat, 31 March 2007 01:47 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
u may try as bellow
create or replace procedure MFATTT as

Re: how create view in procedure [message #228057 is a reply to message #228056] Sat, 31 March 2007 01:54 Go to previous messageGo to next message
narayanan.v
Messages: 29
Registered: September 2005
Junior Member
scott@AKSHAY>create or replace procedure MFATTT (tt VARCHAR2) is
  2  begin
  3  
  4  execute immediate 'create view aamir2 as select * from emp';
  5  
  6  end;
  7  /

Procedure created.

Elapsed: 00:00:00.09
scott@AKSHAY>exec mfattt('A');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
scott@AKSHAY>select object_name,object_type,created from dba_objects where object_name = 'AAMIR2';

OBJECT_NAME                    OBJECT_TYPE        CREATED
------------------------------ ------------------ ---------
AAMIR2                         VIEW               31-MAR-07

Elapsed: 00:00:00.00




Please try the above, you have to use EXECUTE IMMEDIATE
Re: how create view in procedure [message #228059 is a reply to message #228054] Sat, 31 March 2007 01:55 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Doc is your friend >>>http://www.oracle.com/pls/db10g/portal.portal_demo3?selected=1


regards
Taj
Re: how create view in procedure [message #228065 is a reply to message #228059] Sat, 31 March 2007 02:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
First ask yourself why you would want to create a view from a procedure. In general, you should do your DML from scripts, not from stored procedures.
Re: how create view in procedure [message #228077 is a reply to message #228057] Sat, 31 March 2007 05:10 Go to previous messageGo to next message
mfa786
Messages: 210
Registered: February 2006
Location: karachi
Senior Member
no sir that command not create view

and

sir i want give name from outsid not fix view name every time i give new name whin i call proc. i send view name

please give idea thank

Re: how create view in procedure [message #228082 is a reply to message #228077] Sat, 31 March 2007 05:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
mfa786 wrote on Sat, 31 March 2007 12:10
no sir that command not create view

and

sir i want give name from outsid not fix view name every time i give new name whin i call proc. i send view name

please give idea thank



Sorry, you lost me after "no sir"
I really, seriously have no clue what you mean.
Re: how create view in procedure [message #228085 is a reply to message #228082] Sat, 31 March 2007 06:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Are you looking for a "dynamic view name", a changing view name? Let me quote Frank here:
Posted by Frank
First ask yourself why you would want to create a view from a procedure. In general, you should do your DML from scripts, not from stored procedures.


Why? Why would anyone create objects on the fly? Your DBA will go mental after some time.

MHE
see my error [message #228086 is a reply to message #228082] Sat, 31 March 2007 06:54 Go to previous messageGo to next message
mfa786
Messages: 210
Registered: February 2006
Location: karachi
Senior Member

sir
see my code

create or replace PROCEDURE fahimaamir
as
BEGIN
execute immediate 'create view muhammad as select * from accbal';
END;

and system give me this error


SQL> exec fahimaamir;
BEGIN fahimaamir; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "MUHAMMAD.FAHIMAAMIR", line 4
ORA-06512: at line 1



please give me idea how i create dynamic view

thank's

aamir

Re: see my error [message #228087 is a reply to message #228086] Sat, 31 March 2007 06:58 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Normally, you would ask your DBA to give you the rights to create views if you receive an error like this.
But since your DBA is mental by now, you migth as well do it yourself:
grant create view to <your_username>
(connect as a user with dba privileges to do this).

Question remains: why do you want to do this??
Re: see my error [message #228091 is a reply to message #228087] Sat, 31 March 2007 08:34 Go to previous messageGo to next message
mfa786
Messages: 210
Registered: February 2006
Location: karachi
Senior Member
Sir I am only one developer + dba of my company

Answer of your question why I create view in produce.

Sir my manager give me task I give column selection form to him
That form have all column that give the full information of running process in company

I create view mfa that have 90 column and give form to boss that base on mfa view
He want select column name from that form and create query and pass that query to procedure and procedure create new view I design report on basis on that view
Report show only those column that in view

Because of this I create view in procedure

Thatís why

Please give me idea of my problem

Re: see my error [message #228103 is a reply to message #228091] Sat, 31 March 2007 12:33 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Aha. It looks like you're creating your own ad-hoc reporting tool (or not-so-very-ad-hoc actually, since you have to create the view based on the selection you manager gives you).

However, there are tools out there that can do exactly this for you... And a wild guess would be that they are better at it. Take a look at Discoverer for example.

And by the way, wouldn't it be easier if your manager just tells you which columns he/she wants in the report and then have you creating it?
Re: see my error [message #228126 is a reply to message #228091] Sun, 01 April 2007 01:42 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
As a DBA you should get familiar with this guide.
Chapter 22 describes main concept of managing users. you can read more about it in the Security Guide. List of all privileges can be found here, you are interested in the SYSTEM privilege CREATE VIEW like skooman adviced you before. Do NOT use any privilege containing ANY word until you understand it and have VERY strong reason for it (I have not find it yet).

Just out of curiousity
- how many tables does your user contain (as you 'report' content of one table)?
- have you thought about dropping the view after usage?

I do not know which 'report' tool do you use. When it does not contain support of 'column selection', why dou you not enhance it with this support?
Or (as adviced by skooman again), try to use standard reporting tool creating only report(s) which are desired by your manager.
Re: see my error [message #228156 is a reply to message #228126] Sun, 01 April 2007 17:37 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Great forums think alike Wink
forums.oracle.com/forums/thread.jspa?forumID=75&threadID=491837
Re: how create view in procedure [message #228164 is a reply to message #228054] Sun, 01 April 2007 21:36 Go to previous messageGo to next message
freethinking
Messages: 10
Registered: November 2006
Junior Member
look my resolution
create a goble temporary table with only one huge string column,
in your procedure,retrieve data and concatenate all column you need ,separate it by comma others you like, insert it into the temporary table , so you can use it after your procdure,

create global temporary table my_test (ttt varchar2(1000)) on commit preserve rows;
in your procedure
insert into my_test select columna||','||columnb from your_table;

maybe sometimes it can work Razz
Re: how create view in procedure [message #228182 is a reply to message #228164] Mon, 02 April 2007 00:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
... which is about as bad a 'solution' as the one the OP is after.
Re: how create view in procedure [message #228293 is a reply to message #228182] Mon, 02 April 2007 07:55 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
This thread is cracking me up. Hahahehe, how can it be that the OP is giving the same exact responses in two different forums (ala William's post) to completely different feedback messages?

My only though is that he has a keyboard where each key creates an entire message, limiting what he is able to respond with (thereby limiting relevant responses), and these are the closest matches.
Re: how create view in procedure [message #228295 is a reply to message #228293] Mon, 02 April 2007 07:58 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Which makes me wonder:
what is MFA? Wink
Previous Topic: Finding timestamp and username info of a query
Next Topic: inner join with system tables takes too long!!!!!!!
Goto Forum:
  


Current Time: Thu Dec 08 03:56:42 CST 2016

Total time taken to generate the page: 0.29870 seconds