| Procedure [message #373258] |
Fri, 06 April 2001 11:12  |
Marcus Fernando
Messages: 34
Registered: September 2000
|
Member |
|
|
I'm trying do make a procedure that returns the columns of a list of tables passed by parameter. The code is :
PROCEDURE RETURN_COLLUMNS (TableList in VARCHAR2) IS
CURSOR C1 IS
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS
WHERE TABLE_NAME IN (RETURN_COLLUMNS.TableList);
BEGIN
for rc1 in c1 loop
dbms_output.put_line(rc1.column_name);
end loop;
END;
If i put just one table in the parameter, the procedure works fine. If i put more that one table, the procedure give me no lines neither errors.
I ran the query in SQL PLUS and it worked fine, the tables exists, i promiss you.
I tried to call the procedure with the following parameters:
EXEC RETURN_COLLUMNS('CLIENT, EMP');
EXEC RETURN_COLLUMNS('''CLIENT'', ''EMP''');
What am i doing wrong ?
|
|
|
|
| Re: Procedure [message #373261 is a reply to message #373258] |
Fri, 06 April 2001 12:44  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
I wish I knew what the problem was, I tried REF CURSORS too "open c1 for 'select... in(:lst)' using table_list;" but it aslo didn't work.
This old < 8i code works though...
CREATE OR REPLACE PROCEDURE proc1 (in1 VARCHAR2)
AS
vid INTEGER;
rows INTEGER;
qstr VARCHAR2 (100);
col1 VARCHAR2 (30);
BEGIN
DBMS_OUTPUT.put_line (in1);
vid := DBMS_SQL.open_cursor;
qstr := 'select column_name from user_tab_columns where table_name in (' || in1 || ')';
DBMS_OUTPUT.put_line (qstr);
DBMS_SQL.parse (vid, qstr, DBMS_SQL.native);
DBMS_SQL.define_column (vid, 1, col1, 30);
rows := DBMS_SQL.execute (vid);
WHILE DBMS_SQL.fetch_rows (vid) > 0
LOOP
DBMS_SQL.column_value (vid, 1, col1);
DBMS_OUTPUT.put_line (col1);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
/
begin
proc1('''TAB1'', ''TAB2''');
end;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
