| Re: Select the column values in a single line.. [message #373003] |
Wed, 21 March 2001 19:22 |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Try something like this...
CREATE OR REPLACE FUNCTION tab_lov (
tablename IN VARCHAR2,
fieldname IN VARCHAR2,
condition IN VARCHAR2
)
RETURN VARCHAR2
IS
TYPE my_cur_type IS REF CURSOR;
c1 my_cur_type;
sql_str VARCHAR2 (200);
where_clause VARCHAR2 (100) := ' where ' || condition;
lov VARCHAR2 (1000);
tmp_val VARCHAR2 (80);
BEGIN
IF condition IS NULL THEN where_clause := NULL; END IF;
sql_str := 'select ' || fieldname || ' from ' || tablename || where_clause;
--DBMS_OUTPUT.PUT_LINE ( sql_str );
OPEN c1 FOR sql_str;
LOOP
FETCH c1 INTO tmp_val;
EXIT WHEN c1%NOTFOUND;
lov := lov || tmp_val;
--DBMS_OUTPUT.put_line (lov);
END LOOP;
RETURN lov;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20500, 'ERROR: Unspecified problem in Funct. TAB_LOV.', TRUE);
END;
/
SQL>select tab_lov('a', 'b', 'b is not null') from dual;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
