OraFAQ Forum: SQL & PL/SQL » Create view in PLSQL

Home » SQL & PL/SQL » SQL & PL/SQL » Create view in PLSQL (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Create view in PLSQL [message #622776]

Fri, 29 August 2014 06:35

kmuthu
Messages: 2
Registered: August 2014
Location: Chennai

Junior Member

I have created view like below;

create or replace view <viewname> as select * from <tablename> where creation_date <= (sysdate-30);

Is there possible to pass the date value using cursor?

My requirement is, i dont want to give the date value directly in the create view script.
Want to get the value from function and pass that value to creation_date column at the time of create view.

Can you please help me to write the plsql procedure for the same.

Report message to a moderator

Re: Create view in PLSQL [message #622777 is a reply to message #622776]

Fri, 29 August 2014 06:39

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Why would you bother? Simply

create view v_name as select * from table_name;

Include WHERE clause when selecting from that view, i.e.

select * From v_name
where creation_date <= whichever_date_you_want

Report message to a moderator

Re: Create view in PLSQL [message #622795 is a reply to message #622777]

Fri, 29 August 2014 11:34

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Quote:

My requirement is, i dont want to give the date value directly in the create view script.
Want to get the value from function and pass that value to creation_date column at the time of create view.

At first place, you shouldn't create views for underlying SQLs having varying predicates. In your case, you want to provide the date dynamically.

In my opinion, you don't require a view(unless you tell us the reason for creating a view). Why? Because, all you need to do is, in SQL*Plus declare a variable and assign the required value and then, go ahead with the date variable for the SQL.

Report message to a moderator

Re: Create view in PLSQL [message #622796 is a reply to message #622795]

Fri, 29 August 2014 11:35

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I missed to see LF's post.

You can apply my suggestion in LF's second query.

Report message to a moderator

Re: Create view in PLSQL [message #622859 is a reply to message #622796]

Sun, 31 August 2014 21:18

kmuthu
Messages: 2
Registered: August 2014
Location: Chennai

Junior Member

We could not change the select query. Actually select query is always same. We will select the records based on the view result set which one created from the given date.

That's why we have planned to create the view inside of pl/sql.

Any one please provide the sample program.

Requirement:
1) Pass the date value in function as argument
2) Get the date value at the time of create view.

Report message to a moderator

Re: Create view in PLSQL [message #622860 is a reply to message #622859]

Sun, 31 August 2014 22:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What happens when two different folks what to query at the same time with different date?

What problem are you really trying to solve?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: Create view in PLSQL [message #622861 is a reply to message #622860]

Mon, 01 September 2014 00:03

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If you can't change SELECT statement, I wonder:
a) why not?
b) which part of it can you not change?

If you can't change ANYTHING, not a single word, then create a global temporary table (GTT) whose description is the same as the view's you planned to use. Before selecting from the GTT, populate it with data that is unique for a user who is going to use it (which means that you'd use different date value while performing INSERT INTO GTT). Finally, SELECT FROM GTT.

Report message to a moderator

Previous Topic:	char datatype - ora-01722
Next Topic:	SQL to calculate call cost for varying time durations

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 20:45:45 CDT 2024