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