CODEWITHSUNDEEP
plsql
contravaluebets
contravaluebets

Reputation: 85

PL/SQL nested loop error

Whats wrong with following nested loop, it throws following error:

ORA-00936: missing expression

SET SERVEROUT ON;
SET FEED OFF;
DECLARE
schema_name varchar2(100);
table_name varchar2(100);

    BEGIN

        FOR outer_rec IN (select * from  scott.table_list) LOOP  

            FOR inner_rec IN (select a.* from all_tab_columns a where a.column_name =outer_rec.table_name(+) and a.owner='SCOTT' order by a.table_name) LOOP
            dbms_output.put_line (q'[||]' ||inner_rec.column_name || q'[||'|@@@@|']');
            END LOOP;

        END LOOP;

    END;
/

Upvotes: 0

Views: 247

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Since you're using two separate cursors there's really no way to outer join the results together. A better way to handle this would be to use a single cursor that does what you need to have done, as in:

BEGIN
  FOR aRow IN (select *
                 FROM scott.table_list t
                 LEFT OUTER JOIN all_tab_columns a
                   ON (a.owner = 'SCOTT' AND
                       a.table_name = t.table_name)
                 order by a.table_name)
  LOOP
    dbms_output.put_line (q'[||]' || aRow.column_name || q'[||'|@@@@|']');
  END LOOP;
END;

Note that this also uses the ANSI syntax for a left outer join which is preferred over the old Oracle-style ((+)) syntax.

Share and enjoy.

Upvotes: 1

simplify_life
simplify_life

Reputation: 405

try this

SET SERVEROUT ON;
SET FEED OFF;
DECLARE
schema_name varchar2(100);
table_name varchar2(100);

BEGIN

    FOR outer_rec IN (select * from  scott.table_list) LOOP  

        FOR inner_rec IN (select a.* from all_tab_columns a where a.table_name =outer_rec.table_name and a.owner='SCOTT' order by a.table_name) LOOP
        dbms_output.put_line (q'[||]' ||inner_rec.column_name || q'[||'|@@@@|']');
        END LOOP;

    END LOOP;

END;

/

Upvotes: 0

Elfentech
Elfentech

Reputation: 747

I would write it this way (also, your variables are not used):

SET SERVEROUT ON;
SET FEED OFF;
DECLARE
  schema_name VARCHAR2(100);
  table_name  VARCHAR2(100);

  CURSOR c_get_tables IS
    SELECT * FROM scott.table_list;

  CURSOR c_get_all(c_name_in IN scott.table_name%TYPE) IS
    SELECT a.*
      FROM all_tab_columns a
     WHERE a.table_name = c_name_in(+)
       AND a.owner = 'SCOTT'
     ORDER BY a.table_name;

BEGIN

  FOR outer_rec IN c_get_tables
  LOOP

    FOR inner_rec IN c_get_all(outer_rec.table_name)
    LOOP
      dbms_output.put_line(q'[||]' || inner_rec.column_name ||
                           q'[||'|@@@@|']');
    END LOOP;

  END LOOP;

END;
/

Upvotes: 0

Related Questions