Home » SQL & PL/SQL » SQL & PL/SQL » function parameters
function parameters [message #185772] Thu, 03 August 2006 09:05 Go to next message
Jolanda
Messages: 68
Registered: September 2004
Member
hello you all,

I have a function in PL/SQL has to query a table and give back a result. However, the select statement should get a variable tablename and a variable selection criteria, because it should perform on more tables......How can I make this work?

example

function func_get_number(p_table_name IN VARCHAR2,
p_prefix IN VARCHAR2)

IS

v_number NUMBER;

BEGIN

select p_prefix.p_table_name
INTO v_number
FROM p_table_name

return v_number

END func_get_number;

ofcourse this doesn't work...Can anyone provide me an example of how I can make this work?

Thanks in advance

[Updated on: Thu, 03 August 2006 09:34]

Report message to a moderator

Re: function parameters [message #185782 is a reply to message #185772] Thu, 03 August 2006 10:18 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
use EXECUTE IMMEDIATE

[Updated on: Thu, 03 August 2006 10:19] by Moderator

Report message to a moderator

Re: function parameters [message #185785 is a reply to message #185772] Thu, 03 August 2006 10:38 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Try this one...

create or replace function func_get_number(p_table_name IN VARCHAR2,p_prefix IN VARCHAR2)
return number
IS
v_query varchar2(200) ;
v_number NUMBER;

BEGIN
     v_query := ' select ' ||p_table_name||'.'||p_prefix;
	 v_query := v_query || '  from '||p_table_name ||' where rownum =1';
	 execute immediate( v_query ) into v_number;
return v_number;

END func_get_number;



Naveen
Re: function parameters [message #185839 is a reply to message #185785] Thu, 03 August 2006 16:40 Go to previous messageGo to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Navkrish,

Thank you for your piece of code. That really works fine!...
I wonder if that also works for nested statements (joins).
Something like :

UPDATE my_table a
SET a.time = a.old_time,
a.day = a.old_day,
a.indication = 'J'
WHERE a.my_number <
(SELECT MAX(b.my_number)
FROM my_table2 b
WHERE b.my_id = a.my_id
AND b.my_time = a.my_time
AND b.code = a.code);


thanks
Re: function parameters [message #185875 is a reply to message #185772] Fri, 04 August 2006 00:15 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

ya, using execute immediate you can perform...


DECLARE
  v_query long;
BEGIN
  v_query := 'Update .....';
  execute immediate( v_query );
END



Naveen

Re: function parameters [message #186096 is a reply to message #185772] Sat, 05 August 2006 15:26 Go to previous messageGo to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hello you all,

I tried to use the function above, that works. However, if I want to expand it I get undefined error messages.

I am trying to do this:

v_query := MAX (' select ' || p_prefix || '_NUMBER';
v_query := v_query || ' from '||p_table_name || ' WHERE ' || p_prefix ||' _CODE = ' || p_code;
v_query := v_query || ' AND' || p_prefix || '_ID = p_id' || 'AND' || p_prefix || '_DATE = ' || p_date);

execute immediate( v_query ) into v_number;


I am looping through a couple of tables. So the tablename is a parameter and the column names are also the same except that a prefix is different. I need the MAX prefix_NUMBER from a set of records where ID = p_id AND DATE = p_date eg.

The error message I am getting is: Invalid character...But I guess that's not the real error message.

Can anyone help me out by checking my piece of code and if necessary changing it?


Thanks
Re: function parameters [message #186166 is a reply to message #186096] Sun, 06 August 2006 12:50 Go to previous messageGo to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hi You all,

Can anyone tell me why this isn't working?
p_tablename and p_prefix I do get as function parameters.
And the below update statement is what I want, but in dynamic sql.

v_query := 'update' || p_tablename;
v_query := v_query || 'SET' || p_prefix ||' _IND_PROECESSED = P' ;
v_query := v_query || 'where' || p_prefix ||'_NUMBER = 1' || 'AND rownum < v_rownum';
execute immediate v_query;


v_rownum := 10000

UPDATE my_table a
SET a.xxxx_ind_processed = 'P'
WHERE a.xxxx_number = 1
AND rownum < v_rownum;

Hope someone can see what goes wrong here.

Thanks
Re: function parameters [message #186293 is a reply to message #186166] Mon, 07 August 2006 06:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you do a dbms_output.put_line of your v_query just before the execute immediate you can see the exact statement it tries to execute.
(hint: note for any missing spaces)
Re: function parameters [message #186298 is a reply to message #186166] Mon, 07 August 2006 06:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Thanks for letting us know what the error message is.

Looking at your code, you've forgotten pretty much all the spaces in your SQL.
eg
v_query := 'update' || p_tablename;

will return 'updateTABLE' rather than 'update TABLE' which is what you need.

[edited for typos]

[Updated on: Mon, 07 August 2006 06:52]

Report message to a moderator

Re: function parameters [message #186301 is a reply to message #185772] Mon, 07 August 2006 06:38 Go to previous message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Also I guess u would be facing error in this line

v_query := v_query ||'SET'|| p_prefix ||' _IND_PROECESSED = P' ; 


It should be

v_query := v_query ||' SET '|| p_prefix ||' _IND_PROECESSED = ''P'' ' ; 


So, its always better to follow what Frank has mentioned..(dbms_output.put_line )

Naveen
Previous Topic: Column heading query
Next Topic: How to use DBMS_LOB package ..Please help
Goto Forum:
  


Current Time: Fri Dec 09 11:37:45 CST 2016

Total time taken to generate the page: 0.09892 seconds