-
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]
-
-
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?