OraFAQ Forum: Training & Certification » How many parameter u can pass in a procedure

Home » Other » Training & Certification » How many parameter u can pass in a procedure

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

How many parameter u can pass in a procedure [message #282323]

Wed, 21 November 2007 08:48

deviji
Messages: 23
Registered: November 2007

Junior Member

hi,
plz ans for these.....
1) How many DB can be created in a machine
2) How many column can be used in a Select Statement
3) How many tables tht can be joined in a select statement
4) How many SUB QUR tht can be written in a session
5) How many column can we hav in a Table
6) What is the Max size of Procedure
7) How many parameter u can pass in a procedure
Cool

Cool

How many Objects u can create in DB

tc
viji

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #282327 is a reply to message #282323]

Wed, 21 November 2007 08:56

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

Can't help myself ...

How many roads must a man walk down ... Smile

Smile

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #282329 is a reply to message #282323]

Wed, 21 November 2007 08:59

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

How many IM speak words is in the post?

Regards
Michel

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #282331 is a reply to message #282329]

Wed, 21 November 2007 09:02

deviji
Messages: 23
Registered: November 2007

Junior Member

i don't get u Michel
-viji

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #282332 is a reply to message #282331]

Wed, 21 November 2007 09:03

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

After reading this ... you will understand what micheal want to say >>>http://tkyte.blogspot.com/2006/01/im-speak.html

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #282348 is a reply to message #282327]

Wed, 21 November 2007 11:23

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

MarcS wrote on Wed, 21 November 2007 06:56

Can't help myself ...

How many roads must a man walk down ... :)

The answer, my friend, is blowing in the wind. The answer is blowing in the wind.

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #282362 is a reply to message #282323]

Wed, 21 November 2007 12:25

Frank
Messages: 7901
Registered: March 2000

Senior Member

deviji wrote on Wed, 21 November 2007 15:48

Cool

How many Objects u can create in DB

Ah, good old u.. He was famous for creating objects in DB. Word is he once created 12 at a time, and up till 42 in a single day.
Too bad he passed away. Even sadder that a lot of people still haven't heard..

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #282363 is a reply to message #282362]

Wed, 21 November 2007 12:27

Frank
Messages: 7901
Registered: March 2000

Senior Member

Quote:

7) How many parameter u can pass in a procedure

sob.. Yet another thing u was good at..
I onced watch him pass a bucket full...

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #282539 is a reply to message #282363]

Thu, 22 November 2007 05:03

Maaher
Messages: 7065
Registered: December 2001

Senior Member

http://www.orafaq.com/forum/fa/449/0/

I nearly drowned in my own coffee while reading this thread!

MHE

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #282541 is a reply to message #282323]

Thu, 22 November 2007 05:08

oujar poudel
Messages: 5
Registered: November 2007

Junior Member

hi

for your 5th question,

We can create 1000 columns in a table.

Regards..

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #282634 is a reply to message #282323]

Thu, 22 November 2007 16:46

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

There are a number of ways to find the answers to your questions. For some simple things, you can create a test case and use a larger and larger number, until you get an error that tells you that you have exceeded the maximum. You can also search the online documentation. You can search on the words in your questions or you can search on more general terms like "limits". Here are a few examples of the sections of the online documentaiton that contain answers to some of your questions:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/limits003.htm#i288032

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_packages.htm#g1041980

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/limits.htm#LNPLS018

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #282801 is a reply to message #282634]

Fri, 23 November 2007 08:46

deviji
Messages: 23
Registered: November 2007

Junior Member

thank you so much Ms.Barbara Boehmer,
i visited the sites mentioned by you and found good stuff on that.
thanks a lot

best wishes
viji

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #286994 is a reply to message #282323]

Mon, 10 December 2007 16:47

cmontr
Messages: 5
Registered: December 2007

Junior Member

The answers should be depending on you hardware...you can create as many as databases and tables as long as you have the sources. Hence, answer should be == (n-1)

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287000 is a reply to message #282323]

Mon, 10 December 2007 17:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Hence, answer should be == (n-1)
But what is N?

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287012 is a reply to message #287000]

Mon, 10 December 2007 23:01

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

42

Regards
Michel

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287117 is a reply to message #287012]

Tue, 11 December 2007 02:04

deviji
Messages: 23
Registered: November 2007

Junior Member

1) How many DB can be created in a machine

depends on the HDD space you can make many DB's no restriction.

2) How many column can be used in a Select Statement

1000

3) How many tables that can be joined in a select statement

before 8i it was only 15 but after tht. itz increased to 255 i think but not sure.

4) How many SUB QUR that can be written in a session

in a session u can create many.
i think the question sld be

" How many SUB QUR tht can be written in a SELECT statement"

if so then the above said ans for this also.

5) How many column can we hav in a Table

ORA-01792: maximum number of columns in a table or view is 1000
Cause: An attempt was made to create a table or view with more than 1000 columns, or to add more columns to a table or view which pushes it over the maximum allowable limit of 1000. Note that unused columns in the table are counted toward the 1000 column limit.
Action: If the error is a result of a CREATE command, then reduce the number of columns in the command and resubmit. If the error is a result of an ALTER TABLE command, then there are two options: 1) If the table contained unused columns, remove them by executing ALTER TABLE DROP UNUSED COLUMNS before adding new columns; 2) Reduce the number of columns in the command and resubmit.

6) What is the Maximum size of Procedure

4 GB

Cool

Cool

How many Objects u can create in DB

depends on the HDD space. no restrictions............

cross check the answers. any variations are there, inform me.

thanks for the participants
cheers
--viji

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287118 is a reply to message #287117]

Tue, 11 December 2007 02:09

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Better post your source at the same time as the answers, then we can estimate if they are valid ones.

Regards
Michel

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287122 is a reply to message #287118]

Tue, 11 December 2007 02:32

Maaher
Messages: 7065
Registered: December 2001

Senior Member

@deviji:
I successfully selected 3999 columns from dual. I did it in both 9iR2 and 10gR2. My conclusion is that your answer to question 2 is wrong. I generated the select statement with this query:

SELECT *
FROM   (SELECT     /*+ALL_ROWS*/
                   ', ' || LEVEL || ' column_' || LEVEL x
        FROM       DUAL
        CONNECT BY LEVEL < 4000)

All you need to do is modify the first line of the result set and add a "from dual" and you're off. I didn't check the rest of your answers (yet) but I'd certainly double check.

MHE

[Updated on: Tue, 11 December 2007 02:32]

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287132 is a reply to message #287012]

Tue, 11 December 2007 02:59

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Michel Cadot wrote on Tue, 11 December 2007 05:01

42

Regards
Michel

Laughing

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287142 is a reply to message #287117]

Tue, 11 December 2007 03:25

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

Senior Member
Account Moderator

deviji

8) How many Objects u can create in DB

depends on the HDD space. no restrictions............

Actually, there IS a restriction. Regarding the fact that object names should contain only aphanumerics along with several special characters, maximum allowed length of an object name and object name uniqueness, we might, actually, hit the number of objects limit. Not that it is probable, but possible - yes.

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287146 is a reply to message #287142]

Tue, 11 December 2007 03:31

Frank
Messages: 7901
Registered: March 2000

Senior Member

I somehow think you will run into some internal limit before that.. some id column or some sequence or something.

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287148 is a reply to message #287142]

Tue, 11 December 2007 03:35

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Quote:

Regarding the fact that object names should contain only aphanumerics along with several special characters, maximum allowed length of an object name and object name uniqueness

Although, of course you can have more than 1 object of the same name in different schemas. As Frank says, you would hit other limits loooooong before you broke the above mentioned limits Smile

Smile

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287151 is a reply to message #287148]

Tue, 11 December 2007 03:42

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

Senior Member
Account Moderator

No objections.

My intention wasn't mentioning all possible causes. I just wanted to point out that there ARE restrictions (as opposed to Deviji's "no restrictions").

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287178 is a reply to message #287151]

Tue, 11 December 2007 05:59

deviji
Messages: 23
Registered: November 2007

Junior Member

hi,
here i mentioned 'no restriction' not on object names but on number of objects you can create...

--viji

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287180 is a reply to message #287178]

Tue, 11 December 2007 06:01

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Yes, and Littlefoot pointed out that there is a logical restriction. Granted that restriction is huge, but it is not true to say that there is no restriction on the number of objects that can be created

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287185 is a reply to message #287180]

Tue, 11 December 2007 06:21

deviji
Messages: 23
Registered: November 2007

Junior Member

you can create as many till your hardware(hard disk space) supports it.

--viji

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287303 is a reply to message #287185]

Tue, 11 December 2007 21:59

deviji
Messages: 23
Registered: November 2007

Junior Member

hi Maaher,
just visit this site
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/limits003.htm#i288032

it says number of columns per table is 1000. that is why i said the columns you can retrieve from a table could be 1000.
but i forgot about joins.

so can any one tell me what could be the maximum number of columns one can retrieve in a single select statement?

and Michel i think you answered '42' for number of parameters one can pass to a procedure isn't it?
see this

http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96624/e_limits.htm

it says,
parameters passed to a function or procedure 64K
so.......what can we conclude from this?

regards,
viji

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287315 is a reply to message #287303]

Tue, 11 December 2007 22:55

deviji
Messages: 23
Registered: November 2007

Junior Member

amazing.............
see this site
http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/limits.htm

it says, number of parameters you can pass to a function, procedure is 65536

cheers
viji

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287328 is a reply to message #287315]

Tue, 11 December 2007 23:45

Frank
Messages: 7901
Registered: March 2000

Senior Member

You'd better rethink your logic if you ever run into one of those limits.
Why are you so keen on knowing how many columns you can have in a select, or in fact the other limits?
And as for Michel's 42: google for that number if it does not ring a bell. It is about the most famous number.

Report message to a moderator

Re: How many parameter u can pass in a procedure [message #287348 is a reply to message #287303]

Wed, 12 December 2007 01:03

Maaher
Messages: 7065
Registered: December 2001

Senior Member

deviji wrote on Wed, 12 December 2007 04:59

hi Maaher,
just visit this site
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/limits003.htm#i288032

it says number of columns per table is 1000. that is why i said the columns you can retrieve from a table could be 1000.
but i forgot about joins.

I had to test it myself. It's a logical deduction you made, but apparently not correct.

I'd listen to Frank: if anyone is hitting any of these limits, the design probably needs a makeover. It sure would be a nightmare to maintain. One of the limits I have ever encountered in my professional life is the size of a VARCHAR2.

MHE

Report message to a moderator

Previous Topic:	hi
Next Topic:	Insert Column Dynamically to a Table from another one

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 17:27:23 CST 2024