• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 941
  • Last Modified:

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?
0
bassman592
Asked:
bassman592
  • 2
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now