Home » SQL & PL/SQL » SQL & PL/SQL » How to pass a whole column as parameter?
How to pass a whole column as parameter? [message #351724] Thu, 02 October 2008 14:33 Go to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
I would like to write a function similar to AVG(), which will process every row and generate a result. The problem is how do I pass the column as paramater?

In AVG() case, the caller calls AVG(table_name.column_name). However, in my case it appears that I won't be able to do so. Could anyone tell me how Oracle wrote AVG() function? I just don't know in AVG(table_name.column_name) what is passed, the object of whole column or just a Varchar2 or Char has been passed.
Re: How to pass a whole column as parameter? [message #351727 is a reply to message #351724] Thu, 02 October 2008 14:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In AVG() case, the caller calls AVG(table_name.column_name). However, in my case it appears that I won't be able to do so

Why? It appears nothing from your post.

Quote:
Could anyone tell me how Oracle wrote AVG() function?

In C.

Quote:
I just don't know in AVG(table_name.column_name) what is passed, the object of whole column or just a Varchar2 or Char has been passed

A number, the average of a string is something meaningless.

Regards
Michel
Re: How to pass a whole column as parameter? [message #351731 is a reply to message #351724] Thu, 02 October 2008 14:54 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
Thanks.

I mean, in AVG(table_name.column_name), "table_name.column_name" is just Varchar2 or something like an object consists of array of each rows?
Re: How to pass a whole column as parameter? [message #351732 is a reply to message #351731] Thu, 02 October 2008 14:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
table_name.column_name is the name of a number column.

I think you miss the basic in SQL, read SQL Reference.

Regards
Michel
Re: How to pass a whole column as parameter? [message #351742 is a reply to message #351732] Thu, 02 October 2008 15:31 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
Hi Michel, thank you for your answer and advice. Do you know if I can pass a column (not column's name as VARCHAR2 datatype) as a parameter? Or any alternative?
Re: How to pass a whole column as parameter? [message #351744 is a reply to message #351724] Thu, 02 October 2008 15:35 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
Can anyone reconstruct the AVG() function in PL/SQL?
Re: How to pass a whole column as parameter? [message #351762 is a reply to message #351744] Thu, 02 October 2008 21:43 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I don't know why you wan't to reinvent the wheel.

But anyways search for OCI interface in oracle.

Or User Defined Functions.You can create your functions Like
AVG() and many others by this interface.

You can use PL/SQL,C and java to write your code.

Try with PL/SQL first.

Have a Look at this code:-


Tom Kyte's STRAGG Function


Regards,
Rajat Ratewal

[Updated on: Thu, 02 October 2008 21:56]

Report message to a moderator

Re: How to pass a whole column as parameter? [message #351772 is a reply to message #351742] Fri, 03 October 2008 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what is your actual problem then maybe we can help you to find a solution.

Regards
Michel
Re: How to pass a whole column as parameter? [message #351793 is a reply to message #351724] Fri, 03 October 2008 03:49 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,
if you meen the averaging of average alias column you shall try rollup for averaging multiple row averages.
yours
dr.s.raghunathan
Re: How to pass a whole column as parameter? [message #351814 is a reply to message #351772] Fri, 03 October 2008 05:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I second Michel's request - tell us what you're trying to do.

It is possible to create user defined aggregate functions, but other than a String Aggregator, I've only once been in a position where it was even a possible solution.

If you can tell us what your goal is, we can probably tell you how to do it.
Re: How to pass a whole column as parameter? [message #351836 is a reply to message #351762] Fri, 03 October 2008 07:33 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
I think Rajat Ratewal is right. I am studying the OCI interface now. Thanks Rajat Ratewal.
Re: How to pass a whole column as parameter? [message #351839 is a reply to message #351836] Fri, 03 October 2008 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe he/she is right but you still didn't explain what you want to do.

Regards
Michel
Re: How to pass a whole column as parameter? [message #351845 is a reply to message #351814] Fri, 03 October 2008 07:46 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
Yes, the purpose of mine is to create my own aggregate functions, such as median, mode, harmonic mean, interquartile mean etc.

However, the question extended to if I could pass column rather than column name as a parameter. I think the answer to this is yes and no. I have thought a way which is still pass the column as a Varchart2 datatype, then reconstruct the array (the contents of that column) in the called funcation by using dynamic SQL.

While I understand in PL/SQL there is no concept of "Object" as it is OO language like Java, I think this concept is important and very useful. PL/SQL indeed can pass cursor. For this extended issue, I just would like to know a functional/easier way to pass the "Object", such as a complete table, a whole column, etc.
Re: How to pass a whole column as parameter? [message #351856 is a reply to message #351845] Fri, 03 October 2008 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Yes, the purpose of mine is to create my own aggregate functions, such as median, mode, harmonic mean, interquartile mean etc.

This is not a business purpose just may be a way to achieve it.
Anyway, have a look at the example at:
http://www.orafaq.com/forum/m/351073/102589/?#msg_351073

Quote:
I could pass column rather than column name as a paramete

Still don't understand what you mean by this.
Still waiting for an example.

Regards
Michel
Re: How to pass a whole column as parameter? [message #351871 is a reply to message #351845] Fri, 03 October 2008 09:25 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
registereduser wrote on Fri, 03 October 2008 14:46
the purpose of mine is to create my own aggregate functions, such as median, mode, harmonic mean, interquartile mean etc.



Don't try to go that far! There may be already a function for your request.

registereduser wrote on Fri, 03 October 2008 14:46

While I understand in PL/SQL there is no concept of "Object" as it is OO language like Java, I think this concept is important and very useful. PL/SQL indeed can pass cursor. For this extended issue, I just would like to know a functional/easier way to pass the "Object", such as a complete table, a whole column, etc.


Don't be too impulsive. Probably you just don't know enough PL/SQL and you may not find how to do such kind of things with that language. Oracle has those features anyway, but if don't explain your particular requirement, it's quite hard to tell what you could do.

Bye Alessandro
Re: How to pass a whole column as parameter? [message #351885 is a reply to message #351871] Fri, 03 October 2008 10:43 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
Thanks Alessandro. Thank you very much for pointing me the link as well as correcting my ill-perception. I was not aware there are such a large number of existing functions from Oracle.
Re: How to pass a whole column as parameter? [message #351887 is a reply to message #351856] Fri, 03 October 2008 10:58 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
Michel Cadot wrote on Fri, 03 October 2008 08:35

Quote:
I could pass column rather than column name as a paramete

Still don't understand what you mean by this.
Still waiting for an example.



Caller:
n_result_from_Myfunc := Myfunc(tab.col);

Called function:
CREATE FUNCTION MyFunc (part_1.part_2 VARCHAR2) RETURN NUMBER IS
...
n_max NUMBER;
n_min NUMBER;
...
BEGIN
SELECT MAX(part_2)
INTO n_max
FROM part_1;
...

That is what I want. Thanks.

Re: How to pass a whole column as parameter? [message #351888 is a reply to message #351887] Fri, 03 October 2008 11:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No this is not what you want. This is a way you think you can code what you want.

Try to explain in words what you want. I know it's hard for us techies to do, but give it a try.
If you can't, provide us with a table (create table script + insert rows script) and show us what result you want.
Explain how you get to that result.
Re: How to pass a whole column as parameter? [message #351905 is a reply to message #351888] Fri, 03 October 2008 12:38 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
I actually can do it through dynamic SQL:

CREATE FUNCTION MyFunc (param VARCHAR2) RETURN NUMBER IS
-- param needs to be in format of part_1.part_2
...
n_max NUMBER;
v_tab VARCHAR2(20);
v_col VARCHAR2(20);
...
BEGIN
v_tab := GetTable(param);
-- GetTable is my tokenizer function to get part_1
v_col := GetColumn(param);
-- GetColumn is my tokenizer function to get part_2

EXECUTE IMMEDIATE
'SELECT MAX(' || v_col ||')
INTO n_max
FROM' || v_tab || ';
...
END;

Re: How to pass a whole column as parameter? [message #351907 is a reply to message #351905] Fri, 03 October 2008 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you are on the way to create an awful application with dreadful performances.

Regards
Michel
Re: How to pass a whole column as parameter? [message #351911 is a reply to message #351907] Fri, 03 October 2008 12:50 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
Hi Michel

I believe by now you shall understand what I am trying to do. I agree it is awful. My question is, do you have better solution to pass the column between functions/procedures? I would like to concentrate the work on called function rather than on caller part. For caller, it should be as easy as possible.

Regards

Re: How to pass a whole column as parameter? [message #351917 is a reply to message #351911] Fri, 03 October 2008 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I would like to concentrate the work on called function rather than on caller part. For caller, it should be as easy as possible.

This is why you are on the way to build an application which will have dreadful performances.
Don't use generic bricks to build applications, it is a dead-end. It will not work. NEVER.

Regards
Michel
Re: How to pass a whole column as parameter? [message #352122 is a reply to message #351905] Mon, 06 October 2008 04:19 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
registereduser wrote on Fri, 03 October 2008 19:38
I actually can do it through dynamic SQL...



Why don't you want to use the proper functions, if all your data can be evaluated with them?

Are there more reasons. Why you still want to insist creating nonperforming duplicates of system function?

Bye Alessandro

[Updated on: Mon, 06 October 2008 04:19]

Report message to a moderator

Previous Topic: Starting with stored procedures
Next Topic: Need to select data from two partitions
Goto Forum:
  


Current Time: Wed Dec 07 22:28:23 CST 2016

Total time taken to generate the page: 0.11897 seconds