fucntion query

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]

a reminder :)

Is that query in the function ?

or are you writing it totally unrelated to the function?

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

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

GROUP BY id - not needed in this sql.

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

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 :D

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

Good luck with it :)

this is the error i get

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

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?

HI,
This is the procedure I used for populating the static tables.
Instead of table_name as constant. Use it as parameter.
procedure rows_selected_cur
is
table_name1 VARCHAR2(50) :='Pal_states';
st_id1 varchar2(2) := 'AK' ;
sql_stmt varchar2(80) :='';
where_condition varchar2(80) :='';
emp_rec pal_states%rowtype;
TYPE EmpCurTyp IS REF CURSOR; -- define weak REF CURSOR type
emp_cv EmpCurTyp; -- declare cursor variable

BEGIN
--test_id :='NJ';
-- open cursor variable

where_condition := ' WHERE ST_ID = ''' || st_id1 || '''' ;
where_condition := '';
sql_stmt := 'SELECT * FROM ' || table_name1 || where_condition;
dbms_output.put_line(sql_stmt );
OPEN emp_cv FOR sql_stmt; -- INTO emp_rec; --USING st_id1;
loop
fetch emp_cv into emp_rec;
--dbms_output.put_line ('Sql_row_count ' || emp_cv%ROWCOUNT);
exit when emp_cv%notfound;
end loop;

dbms_output.put_line ('Sql_row_count ' || emp_cv%ROWCOUNT);
close emp_cv;
--dbms_output.put_line (emp_rec.st_desc);
end;

Hope this helps.

Thanks.
GD_1976.

gee thanx a lot GD..it worked....lol atlast!!!

Welcome

GD_1976.