Querying all_tab_columns using stored procedure

Wed, 26 Jun 2002 00:30:01 -0700

Title: RE: recording SQLPlus activity
Dear All,
I'm created stopre procedure like below:
 
CREATE OR REPLACE PROCEDURE X1990.test(ptable varchar2) IS
   CURSOR c1 IS
      SELECT   column_name ||
               '||'''      ||
               ','         ||
               '''||' col
      FROM     all_tab_columns
      WHERE owner = 'X2002'             AND
            table_name LIKE UPPER (ptable)
      ORDER BY column_id;
 
   c1rec                         c1%ROWTYPE;
BEGIN
   OPEN c1;
 
   LOOP
      FETCH c1 INTO c1rec;
      EXIT WHEN c1%NOTFOUND;
   dbms_output.put_line(c1rec.col);
   END LOOP;
 
   CLOSE c1;
END;
 
The above procedure return blank...
But when I'm querying the all_tab_columns directly from sqlplus it's returned some values.
 
for your information, I'm accessing the store procedure from another schema. How to solve this problem???
 
regards,
 
Ahmadsyah A.N.

Reply via email to