Oracle Anonymous Block Select Table

In preparation to creating a stored procedure to automate some queries I'm running, I created an anonymous block to test something. This is what I have:

DECLARE
   InvoiceDate   VARCHAR2 (9)   := '05APR2015';

Begin
      Select
            b.customer,
            a.invoice_dt,
                a.invoice_number,
            a.invoice_total
      From invoices a
      Join customers b
      On a.cust_id = b.cust_id
      Where a.invoice_dt >= InvoiceDate
      Order By b.customer,      a.invoice_dt,      a.invoice_number;
End;

When I run this, it tells me an INTO clause is expected in this SELECT statement.

Ok, I get that I need to put the result into a variable, but I don't know exactly how to do that. Should I declare a table variable, or a cursor? And if my goal is to see results from this (just as if I'd run the query with a hard-coded date instead of a variable) do I need to add dbms output?
bassman592Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
if you just want to see the results, then skip the variables and use a cursor for loop
adjust the dbms_output  to whatever formatting you need

DECLARE
    invoicedate VARCHAR2(9) := '05APR2015';
BEGIN
    FOR x IN (  SELECT b.customer,
                       a.invoice_dt,
                       a.invoice_number,
                       a.invoice_total
                  FROM invoices a JOIN customers b ON a.cust_id = b.cust_id
                 WHERE a.invoice_dt >= invoicedate
              ORDER BY b.customer, a.invoice_dt, a.invoice_number)
    LOOP
        DBMS_OUTPUT.put_line(
            x.customer || ' ' || x.invoice_dt || ' ' || x.invoice_number || ' ' || x.invoice_total
        );
    END LOOP;
END;
0
sdstuberCommented:
If you'll need to use the data in some way, then you might want to bulk collect it into collection variables.  Iterate on those to do your dbms_output and then reuse them in other code later on

that might look something like this...


DECLARE
    CURSOR cur
    IS
          SELECT b.customer,
                 a.invoice_dt,
                 a.invoice_number,
                 a.invoice_total
            FROM invoices a JOIN customers b ON a.cust_id = b.cust_id
           WHERE a.invoice_dt >= invoicedate
        ORDER BY b.customer, a.invoice_dt, a.invoice_number;

    TYPE cust_invoice_tab IS TABLE OF cur%ROWTYPE
        INDEX BY PLS_INTEGER;

    invoices    cust_invoice_tab;

    invoicedate VARCHAR2(9) := '05APR2015';
BEGIN
    OPEN cur;

    FETCH cur BULK COLLECT INTO invoices;

    CLOSE cur;

    FOR i IN 1 .. invoices.COUNT
    LOOP
        DBMS_OUTPUT.put_line(
               invoices(i).customer
            || ' '
            || invoices(i).invoice_dt
            || ' '
            || invoices(i).invoice_number
            || ' '
            || invoices(i).invoice_total
        );
    END LOOP;

    FOR i IN 1 .. invoices.COUNT
    LOOP
        --- do something else with the data now
        NULL;
    END LOOP;
END;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bassman592Author Commented:
Those are both great options, thanks! The second is closer to my eventual intent, so I'll award the points to that one, although I guess it doesn't matter.

Thanks for the complete - and quick - response.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.