Wednesday, December 13, 2006

Parameterized View

I always used to think why oracle didn't provide parameterized views similar to parameterized cursors, till I found workaround for the same.

Following is an example which shows use of parameterized view:

CREATE OR REPLACE VIEW test_vw AS
SELECT empno,ename,sal
FROM emp
WHERE empno = to_number(userenv('client_info')) ;

In Stored Procedure, will have to call following procedure before executing the View query, as in following example:

exec dbms_application_info.set_client_info(7934);

select * from test_vw ;

And the output is:

EMPNO ENAME SAL
---------- ---------- ----------
7934 MILLER 13040

42 comments:

Anonymous said...

Why entry level jobs DBA jobs are so difficult to get?
Even OCP is of no use.

Anonymous said...

How can we use two or more parameters for the view?

Anonymous said...

Good Article

Anonymous said...

Hi,

I found the best way to put 2 parameters is to work around and put the parameters all in one value then separate them according to pre-defined pattern to useful values inside the view code.

Cheers,
Feras

Anonymous said...

thanks for the article.
one comment :
In Oracle/PLSQL, the userenv function can be used to retrieve information about the current Oracle session. Although this function still exists in Oracle for backwards compatibility, it is recommended that you use the sys_context function instead.
Applies To: Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
source :
http://www.techonthenet.com/oracle/functions/sys_context.php

Coder said...

You can use public package variables for the same purpose.
In this way you can easily use any number of them.

Anonymous said...

registry cleaner , just wanted to say that Registry Cleaner Info sites is realy great and I'm Glad that i found it
I've gotten exposed to quite a lot of Internet here and just wanted to give my 6 cents. Im about to write a great article for this forum about
registry cleaner , [url=http://www.registry1000.com/]registry cleaner[/url] and I'll publish it as soon as i done it.
ahtvniwxnjnjnjnnjaw
registry cleaner
cheers!

Anonymous said...

There can be billions of web web pages on the net today. Google has to crawl each 1 and do a mathematical calculation to determine which one's are worthly of this page that has built it's reputation, Page 1. It looks at site content, tags, words in bold, OBL, linking internet pages and online websites you link to. There are a million of calculations it goes via, but to make it very simple to recognize, think of it as a popularity contest and your web-site has to be socially popular. You may well will need other web pages linked to yours and you must have publications around. You would like to have submissions of all types to compete with the heavy hitters who are SEO-ing all the time. Most niche's call for day-to-day Search engine marketing to dominate. WIth that being said, you might ask yourself, where is all this time going to come from? That is the difficulty most individuals have and why not several of this internet sites to choose from perform exceptionally well. We have been dominating the SERPS for over 10 years, should you need assist, contact us [url=http://www.buybacklinks-pr9.com/buy-backlinks.html] Backlinks[/url]

Anonymous said...

hi, does anyone identify a great position for [url=http://neopets-cheats.us]Neopets Cheats[/url]?

Anonymous said...

Top blog, I had not come across justoracle.blogspot.com before in my searches!
Carry on the fantastic work!

Anonymous said...

Hi other members, i would just like to make an introduction to everyone at justoracle.blogspot.com

Your forum is great! Generally when I visit forums, I just come across junk, but this time I was very surprised, finding a informative forum containing great information.

Thanks ladies & gents at justoracle.blogspot.com and keep your wicked effort up!?

[color=#ffffff][URL=http://www.contemporary-furnishings.net/Accessories/c14/index.html] designer accessories [/URL] [URL=http://www.finger-puppets.co.uk/shop] Finger Puppets [/URL] [URL=http://www.triciadouglasinteriors.co.uk/interior-design-manchester] interior design manchester [/URL] [URL=http://www.contemporary-furnishings.net/Soft-Furnishings-Cushions/c4_15/index.html]contemporary cushions [/URL] [URL=http://www.scienceboffins.com]Childrens Entertainers [/URL] [/color]

Anonymous said...

Thank you, that was extremely valuable and interesting...I will be back again to read more on this topic.

Anonymous said...

Thanks for sharing the link, but unfortunately it seems to be down... Does anybody have a mirror or another source? Please answer to my post if you do!

I would appreciate if a staff member here at justoracle.blogspot.com could post it.

Thanks,
John

Anonymous said...

Brilliant blog, I hadn't noticed justoracle.blogspot.com earlier during my searches!
Keep up the wonderful work!

Anonymous said...

Thanks for sharing this link, but unfortunately it seems to be down... Does anybody have a mirror or another source? Please answer to my post if you do!

I would appreciate if a staff member here at justoracle.blogspot.com could post it.

Thanks,
John

Anonymous said...

Thanks for sharing the link, but unfortunately it seems to be down... Does anybody have a mirror or another source? Please reply to my post if you do!

I would appreciate if a staff member here at justoracle.blogspot.com could post it.

Thanks,
Thomas

Anonymous said...

Thanks for sharing this link, but unfortunately it seems to be offline... Does anybody have a mirror or another source? Please answer to my post if you do!

I would appreciate if a staff member here at justoracle.blogspot.com could post it.

Thanks,
James

Anonymous said...

http://friends.rambler.ru/knaccoumsaca@rambler.ru http://friends.rambler.ru/rairetabor@rambler.ru
good sites for teen girls hairstyles teen girls

Anonymous said...

Thanks for sharing this link, but unfortunately it seems to be down... Does anybody have a mirror or another source? Please answer to my post if you do!

I would appreciate if a staff member here at justoracle.blogspot.com could post it.

Thanks,
Jack

Anonymous said...

Hello there,

This is a message for the webmaster/admin here at justoracle.blogspot.com.

Can I use some of the information from this post above if I provide a link back to this site?

Thanks,
Charlie

Anonymous said...

Thanks for sharing the link, but unfortunately it seems to be down... Does anybody have a mirror or another source? Please answer to my post if you do!

I would appreciate if someone here at justoracle.blogspot.com could post it.

Thanks,
Jules

Anonymous said...

Have you considered the fact that this might work another way? I am wondering if anyone else has come across something
like this in the past? Let me know your thoughts...

Anonymous said...

Thanks for sharing the link, but unfortunately it seems to be down... Does anybody have a mirror or another source? Please answer to my post if you do!

I would appreciate if a staff member here at justoracle.blogspot.com could repost it.

Thanks,
Oliver

Anonymous said...

ESSELIORELARL
[URL=http://moon.cs.uh.edu/trac/WiserAnalyzer/ticket/16651]singles online dating[/URL]
RepEmicevem

Anonymous said...

Hello,

I have a message for the webmaster/admin here at justoracle.blogspot.com.

May I use part of the information from this post right above if I provide a link back to your site?

Thanks,
Alex

Anonymous said...

I enjoyed reading this blog post! Keep up the great work.

thanxx
scholarships

Anonymous said...

Greetings,

This is a message for the webmaster/admin here at justoracle.blogspot.com.

May I use part of the information from this post above if I provide a backlink back to this site?

Thanks,
William

Anonymous said...

Hi Guru, what entice you to post an article. This article was extremely interesting, especially since I was searching for thoughts on this subject last Thursday.

Anonymous said...

Hey,

Thanks for sharing the link - but unfortunately it seems to be down? Does anybody here at justoracle.blogspot.com have a mirror or another source?


Thanks,
Alex

Anonymous said...

Hi:

I just now registered on justoracle.blogspot.com.
I'm planning to search round a bit more and interact with fascinating individuals and discover a couple of important things.


Hopefully this message doesn't find itself in a bad category. I'm hoping you'll forgive me if it does.

--------------------

DARYL DYER
Petroleum Laboratory Assistant

Anonymous said...

Auto junk yards

Anonymous said...

Free nanny jobs

Cleaner jobs

<a href="http://www.homecareseekers.com/'>find caregiver jobs</a>

Anonymous said...

I've always liked things like sand clocks, lava lamps, and the like to well-wishing of rightful dish out space staring at it as a configuration of catharsis. In a course, it helps me with meditation, to relieve stress and strain and decent assume fro nothing. That's why since I was a kid, in preference to of dolls and cars I've perpetually at ease more of such pieces like sand clocks, lava lamps, tuneful boxes etc. So I was most charmed when I start the[url=http://www.dealtoworld.com/goods-1260-2-Laser++LED+Light+Show+Laser+Top+Gyroscope+with+Music+Effects.html] 2-Laser + LED Spry Presentation Laser Crown Gyroscope with Music Effects[/url] from DealtoWorld.com under the Toys section. It's like a musical box, a spinning head, and a moonlight reveal all rolled into one. Which is spectacular entertainment! The gyroscope transfer whirl object of around a minute. The laser explanation pretension with accompanying music makes this gyroscope a measure incomparable toy that my friends get also been most amused with.

My dogs are also pretty curious around the laser gyroscope I got from DealtoWorld.com. They each follow the gyroscope as it spins, although at earliest they kept barking at the laser insight advertise, and also because it produces music. But after they got tempered to it, they've stopped barking but simply keep following the gyroscope whenever I start spinning it. Kids are also attractive amused next to it. Off it's proper to take entertaining toys about the lineage so that you can pull out the bit of frippery trifle with on while the kids are being amused or playing with it while you go fit out foodstuffs or fall heir to changed. The gyroscope is a particular such trinket with this purpose.

The gyroscope I bought from DealtoWorld.com has a dragon as a design on it, and produces a gegenschein show with red, downcast, and common colours. Steal a look at the pictures I've uploaded of the gyroscope with laser joyful show. The music produced from the gyroscope is not that expert but facts sufficiency to entertain any new visitor to the house. The gyroscope is red and negro, making it look very imperturbable, and measure macho with that dragon imprint.

The music dismount attack plain gyroscope runs on 6 LR44 batteries, which are replaceable anyway. I've also utilized this gyroscope to surprise my girlfriend during our anniversary celebration. I did the cheesy terror of decorating the hostelry elbow-room with roses and when I led her in, I started up the gyroscope as correctly so that the laser brighten expose produces a romantic effect. I also had some battery operated candles so all the moonlight effects created a degree dreamt-up atmosphere. She loved it, not later than the way, to my relief. I also bought the candles from DealtoWorld.com. These days it seems to be my non-performance shopping put in favour of all gifts and ideas for emotional occasions.

Since Christmas is coming, this laser radiance show gyroscope can dialect mayhap be a superb Christmas favour seeking the toddler or in spite of the mollycoddle! Alternatively, the gyroscope can absolutely be a polite reckoning to the usual Christmas decorations. I can imagine placing it within a mile of the Christmas tree and perchance spinning it when guests succeed in the house. Looks like [url=http://www.dealtoworld.com]DealtoWorld.com[/url] is getting my duty yet again!

Anonymous said...

Hi there,

Thanks for sharing this link - but unfortunately it seems to be not working? Does anybody here at justoracle.blogspot.com have a mirror or another source?


Cheers,
Oliver

Anonymous said...

Greetings,

I have a question for the webmaster/admin here at justoracle.blogspot.com.

Can I use some of the information from this post right above if I provide a backlink back to your website?

Thanks,
Jules

Anonymous said...

For the wonderful info

I'll be back soon.


Thank you!


[url=http://www.optin-marketing.com]Bulk Email[/url]

Anonymous said...

Article seems to be directly copied from Oracle manual. No bid deal. You would have given much more clear explanation with more examples in your own way. Any way good effort. keep going

Anonymous said...

interracial dating sex http://loveepicentre.com/ ointernet dating

Anonymous said...

tax interest software http://buysoftwareonline.co.uk/it/product-35357/3herosoft-Dvd-to-iPad-Comverter-3-5-MacOSX submission seo seo seo software submitprobiz [url=http://buysoftwareonline.co.uk/category-200-212/Multimedia-and-Entertainment?page=3]parts database software palm[/url] win xp recovery repair all software
[url=http://buysoftwareonline.co.uk/product-35529/Chronos-F10-Launch-Studio-3-MacOSX]Chronos F10 Launch Studio 3 MacOSX - Cheap Legal OEM Software, Software Sale, Download OEM[/url] shocking software
[url=http://buysoftwareonline.co.uk/manufacturer-11/Quark][img]http://buyoem.co.uk/image/4.gif[/img][/url]

Anonymous said...

Asian: If you have a lid with a small or no visible crease you might try smoking up your eyeshadow. You can smoke out any color you like.. you just start with the darkest color along you upper lash line, apply a lighter shade of the same color and a browbone highlight that is the lightest. Its just a gradient or ombre look. Line upper and lower rims and apply mascara for added definition.
I¡¯m delighted with my little inexpensive collection, they¡¯re all brilliant in terms of quality and affordability. Have you discovered any purse-friendly treasures lately? Share your must-haves!Oh look. We¡¯ll have to talk about Kevin the elephant in the powder room, before we go any further, won¡¯t we?
PULSE FIX ME UP The New Lipsticks An Alien¡¯sEye View Brief




___________________
Blogs links:
coach outlet
coach outlet store
coach outlet online

Anonymous said...

Once an application gets submitted and the mortgage conditions of the agreement before moving on to the lorry [url=http://www.llplongtermloans.co.uk/]long term loans uk[/url] 12 month loans Fed student loans involve Stafford and Perkins financial products, as well as other primary lending software programs http://www.fhyxc.co.uk/

Anonymous said...

I'm amazed, I must say. Seldom do I come across a blog that's both educative and
interesting, and without a doubt, you've hit the nail on the head. The problem is something that not enough folks are speaking intelligently about. Now i'm very happy I
found this in my hunt for something regarding this.


Here is my web page :: webpage ()