fucntion query

[helpme]

hi,
i creatt this function where in i pass table name as parameter to the it..
How do i use this parameter in a select query?
eg here its "name1"

CREATE OR REPLACE FUNCTION Testing2 (name1 in varchar2)........

...

FOR x IN (SELECT DISTINCT id FROM name1 GROUP BY id) LOOP
...........

Thanx a lot
Sam

[Edited by helpme on 11-28-2001 at 07:48 AM]

[helpme]

a reminder

[UNIX DBA]

Is that query in the function ?

or are you writing it totally unrelated to the function?

[helpme]

ya its in the function


CREATE OR REPLACE FUNCTION Testing(a in varchar2) RETURN number IS
BEGIN

FOR x IN (SELECT DISTINCT id FROM a GROUP BY id) LOOP
blah;
blah;
end loop;
return 1;
end;


I need to replace the "a" in te query from the input parameter...

Thanx
Sam

[Shestakov]

In this case you must use dynamic SQL:
...
stmt := 'select distinct id from '||tbl_name';

GROUP BY id - not needed in this sql.

[helpme]

i tried this...
it dint work though


CREATE OR REPLACE FUNCTION Testing2(a3 in varchar2) RETURN myTableType2 IS
a2 VARCHAR2(30):='#';


lAnswer myTableType2:=myTableType2();
i NUMBER:=1;
cursor c1(tabname varchar2) is
SELECT DISTINCT id FROM tabname ;
cursor c2(tabname varchar2,id1 number) is
SELECT name FROM tabname WHERE id=id1;

BEGIN
FOR x IN c1('&a3') LOOP
lAnswer.extend;

FOR y IN c2('&a3',x.id) LOOP
IF a2 ='#' THEN
a2 := x.id||','||y.name;
ELSE
a2 := a2||','||y.name;
END IF;
END LOOP;
lAnswer(i):=myRecordType2(a2,a2);
i:=i+1;
a2:='#';
END LOOP;
RETURN lAnswer;
END;



Whee do i go wrong ..or is there a possible simpler way to go about this?
Thanx
Sam

[UNIX DBA]

I would suggest you use a cursor and pass the parameter into the cursor

CREATE OR REPLACE FUNCTION Testing(p_table_name in varchar2) RETURN number IS

DECLARE

cursor my_cursor(c_table_name in varchar2) is
SELECT DISTINCT id FROM c_table_name;

my_cursor_rec mycursor%ROWTYPE;

BEGIN

open cursor my_cursor(p_table_name);
fetch my_cursor into my_cursor_rec;

while my_cursor%FOUND loop
STATEMENTS
end loop;

END

I may have got some syntax wrong but if alot of that makes no sense then ignore it. You may want to try and teach yourself some PL/SQL. It can be extremely useful to you.

The function can only return one value and it looks like your query returns more than one. You could use a procedure to list all the values but the theory behind passing a parameter as a table to use in a query is all in that example.

Hope it might help a bit

[UNIX DBA]

I'm pretty sure you don't use those ampersands that you have.

Good luck with it

[helpme]

this is the error i get

PLS-00201: identifier 'TABNAME' must be declared...

[helpme]

i get error while doing this

cursor c1(tabname1 varchar2) is
SELECT DISTINCT id FROM tabname1;
begin
FOR x IN c1('a3') LOOP.........



error is

PLS-00201: identifier 'TABNAME1' must be declared
ne ideas?