Home » Other » Training & Certification » How many parameter u can pass in a procedure
How many parameter u can pass in a procedure [message #282323] Wed, 21 November 2007 08:48 Go to next message
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 How many Objects u can create in DB

tc
viji
Re: How many parameter u can pass in a procedure [message #282327 is a reply to message #282323] Wed, 21 November 2007 08:56 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Can't help myself ...

How many roads must a man walk down ... Smile
Re: How many parameter u can pass in a procedure [message #282329 is a reply to message #282323] Wed, 21 November 2007 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How many IM speak words is in the post?

Regards
Michel
Re: How many parameter u can pass in a procedure [message #282331 is a reply to message #282329] Wed, 21 November 2007 09:02 Go to previous messageGo to next message
deviji
Messages: 23
Registered: November 2007
Junior Member
i don't get u Michel
-viji
Re: How many parameter u can pass in a procedure [message #282332 is a reply to message #282331] Wed, 21 November 2007 09:03 Go to previous messageGo to next message
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
Re: How many parameter u can pass in a procedure [message #282348 is a reply to message #282327] Wed, 21 November 2007 11:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
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.
Re: How many parameter u can pass in a procedure [message #282362 is a reply to message #282323] Wed, 21 November 2007 12:25 Go to previous messageGo to next message
Frank
Messages: 7880
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..
Re: How many parameter u can pass in a procedure [message #282363 is a reply to message #282362] Wed, 21 November 2007 12:27 Go to previous messageGo to next message
Frank
Messages: 7880
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...
Re: How many parameter u can pass in a procedure [message #282539 is a reply to message #282363] Thu, 22 November 2007 05:03 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: How many parameter u can pass in a procedure [message #282541 is a reply to message #282323] Thu, 22 November 2007 05:08 Go to previous messageGo to next message
oujar poudel
Messages: 5
Registered: November 2007
Junior Member
hi

for your 5th question,

We can create 1000 columns in a table.

Regards..

Re: How many parameter u can pass in a procedure [message #282634 is a reply to message #282323] Thu, 22 November 2007 16:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
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
Re: How many parameter u can pass in a procedure [message #282801 is a reply to message #282634] Fri, 23 November 2007 08:46 Go to previous messageGo to next message
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
Re: How many parameter u can pass in a procedure [message #286994 is a reply to message #282323] Mon, 10 December 2007 16:47 Go to previous messageGo to next message
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)
Re: How many parameter u can pass in a procedure [message #287000 is a reply to message #282323] Mon, 10 December 2007 17:59 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Hence, answer should be == (n-1)
But what is N?
Re: How many parameter u can pass in a procedure [message #287012 is a reply to message #287000] Mon, 10 December 2007 23:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
42

Regards
Michel
Re: How many parameter u can pass in a procedure [message #287117 is a reply to message #287012] Tue, 11 December 2007 02:04 Go to previous messageGo to next message
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 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
Re: How many parameter u can pass in a procedure [message #287118 is a reply to message #287117] Tue, 11 December 2007 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: How many parameter u can pass in a procedure [message #287122 is a reply to message #287118] Tue, 11 December 2007 02:32 Go to previous messageGo to next message
Maaher
Messages: 7062
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 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Michel Cadot wrote on Tue, 11 December 2007 05:01

42

Regards
Michel


Laughing
Re: How many parameter u can pass in a procedure [message #287142 is a reply to message #287117] Tue, 11 December 2007 03:25 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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.
Re: How many parameter u can pass in a procedure [message #287146 is a reply to message #287142] Tue, 11 December 2007 03:31 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: How many parameter u can pass in a procedure [message #287148 is a reply to message #287142] Tue, 11 December 2007 03:35 Go to previous messageGo to next message
pablolee
Messages: 2836
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
Re: How many parameter u can pass in a procedure [message #287151 is a reply to message #287148] Tue, 11 December 2007 03:42 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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").
Re: How many parameter u can pass in a procedure [message #287178 is a reply to message #287151] Tue, 11 December 2007 05:59 Go to previous messageGo to next message
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
Re: How many parameter u can pass in a procedure [message #287180 is a reply to message #287178] Tue, 11 December 2007 06:01 Go to previous messageGo to next message
pablolee
Messages: 2836
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
Re: How many parameter u can pass in a procedure [message #287185 is a reply to message #287180] Tue, 11 December 2007 06:21 Go to previous messageGo to next message
deviji
Messages: 23
Registered: November 2007
Junior Member
you can create as many till your hardware(hard disk space) supports it.

--viji
Re: How many parameter u can pass in a procedure [message #287303 is a reply to message #287185] Tue, 11 December 2007 21:59 Go to previous messageGo to next message
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
Re: How many parameter u can pass in a procedure [message #287315 is a reply to message #287303] Tue, 11 December 2007 22:55 Go to previous messageGo to next message
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
Re: How many parameter u can pass in a procedure [message #287328 is a reply to message #287315] Tue, 11 December 2007 23:45 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: How many parameter u can pass in a procedure [message #287348 is a reply to message #287303] Wed, 12 December 2007 01:03 Go to previous message
Maaher
Messages: 7062
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
Previous Topic: hi
Next Topic: Insert Column Dynamically to a Table from another one
Goto Forum:
  


Current Time: Sat Dec 10 12:52:31 CST 2016

Total time taken to generate the page: 0.26722 seconds