PL/SQL nested loop error

Question

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;
/

But Why? I want all the columns from all_tab_columns. Anyways, removing (+) does not give any output. — contravaluebets
– contravaluebets, Commented Mar 3, 2014 at 13:05
this (+) is important, it allows an outer join in case outer_rec.table_name has no match — Elfentech
– Elfentech, Commented Mar 3, 2014 at 13:06
you can have Outer Join only if you are using to join two tables. here "outer_rec.table_name" is variable to query . Not getting out put is because you are trying to put table name in column name. May be your intention is "a.table_name =outer_rec.table_name" — simplify_life
– simplify_life, Commented Mar 3, 2014 at 13:11

Bob Jarvis - Слава Україні · Accepted Answer · 2014-03-03 16:39:11Z

1

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.

edited Mar 3, 2014 at 16:39

answered Mar 3, 2014 at 15:08

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

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Elfentech · Accepted Answer · 2014-03-03 13:12:55Z

0

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;
/

edited Mar 3, 2014 at 13:12

answered Mar 3, 2014 at 13:04

Elfentech

7475 silver badges10 bronze badges

Comments

Glenn · Accepted Answer · 2014-03-03 13:16:37Z

0

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;

/

edited Mar 3, 2014 at 13:16

Glenn

9,2102 gold badges44 silver badges56 bronze badges

answered Mar 3, 2014 at 13:16

simplify_life

4051 gold badge4 silver badges18 bronze badges

Collectives™ on Stack Overflow

PL/SQL nested loop error

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related