PL/SQL - dbms output the result of an execute immediate

Question

I would like to be able to print out all results for a query (that should be filtered by the PK from TableA) and do this for each PK in TABLEA. This is what i have so far:

 DECLARE
    CURSOR Curs IS SELECT DISTINCT PKID FROM TABLEA;
    BEGIN
    FOR rec IN Curs 
          LOOP
              EXECUTE IMMEDIATE 
              'SELECT * FROM (
              SELECT cola,
              FKTABLEA,
              colc,
              lag (cold,1) OVER (ORDER BY cold) AS cold
              FROM tableB
              WHERE FKTABLEA = :1)
              WHERE colc != cold
              order by cola' using Curs.PKID;

              DBMS_OUTPUT.PUT_LINE('OUTPUT ALL RESULTS FROM THE QUERY HERE');
          END LOOP;     
    END;

Codo · Accepted Answer · 2011-05-29 07:45:52Z

4

There is no need to use EXECUTE IMMEDIATE. And there's only the fully manual way of printing all the results:

DECLARE
    CURSOR Curs IS SELECT DISTINCT PKID FROM TABLEA;
BEGIN
    FOR rec IN Curs LOOP
        FOR r IN (
            SELECT * FROM (
                SELECT cola,
                    FKTABLEA,
                    colc,
                    lag (cold,1) OVER (ORDER BY cold) AS cold
                FROM tableB
                WHERE FKTABLEA = rec.PKID)
            WHERE colc != cold
            order by cola )
        LOOP
            DBMS_OUTPUT.PUT_LINE(r.cola || ',' || r.colb || ',' || r.colc || ',' || r.cold);
        END LOOP;
    END LOOP;     
END;

answered May 29, 2011 at 7:45

Codo

79.7k18 gold badges176 silver badges218 bronze badges

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

Collectives™ on Stack Overflow

PL/SQL - dbms output the result of an execute immediate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related