Issue with Benthic Golden Workspace

Question

Sorry for the very generic title, can't think of the best way to put it in short words. I have queries for some exports for different customers. They're the same queries for each customer but are filtered by an ID. I wanted to create tabs for each different type of export and have variables for the different customers in each tab so I can easily run them on any DB instance for any customer. However, the last statement is a query and I want it to return in the results window, but it's telling me it's expecting an INTO statement. I'm not great with cursors, am not sure if that'd be the right answer, but figured I'd ask you all since you all have helped me quite a bit in the past. Thanks.

Tab Example:

DECLARE  lEventID    INTEGER;
         lCustID     INTEGER := 1 -- Development Customer1
         --lCustID     INTEGER := 2 -- Development Customer2
         --lCustID     INTEGER := 3 -- Development Customer3
         --lCustID     INTEGER := 4 -- Development Customer4
         --lCustID     INTEGER := 5 -- Development Customer5
         --lCustID     INTEGER := 101 -- Testing Customer1
         --lCustID     INTEGER := 102 -- Testing Customer2
         --lCustID     INTEGER := 103 -- Testing Customer3
         --lCustID     INTEGER := 104 -- Testing Customer4
         --lCustID     INTEGER := 105 -- Testing Customer5
         --lCustID     INTEGER := 201 -- Production Customer1
         --lCustID     INTEGER := 202 -- Production Customer2
         --lCustID     INTEGER := 203 -- Production Customer3
         --lCustID     INTEGER := 204 -- Production Customer4
         --lCustID     INTEGER := 205 -- Production Customer5

BEGIN

    SELECT  EventID INTO lEventID
    FROM    Event
    WHERE   EventName = 'Event1'

    SELECT  Field1,
            Field2,
            Field3
    FROM    Table1
    WHERE   EventID = lEventID
      AND   CustomerID = lCustID;

END;

Now, I do believe I could probably change the DECLARE to var, remove the BEGIN and END;, and then put the first query as a subquery in the second query and that should work. However, I'm trying to keep this as structured as it is in the Package it's in. I don't want to run the Procedure this code is in in the Package because there are update and insert statements. I want to run this query only so I can see what might happen before actually making it do what I want it to do. I'm going to be sharing this workspace with a fellow employee and I don't want them to get too confused. In the original package, the query is put into a cursor which is an out parameter for the procedure which eventually gets exported. If you can help, I truly appreciate it. Also, Benthic Golden is the utility us developers use to interact with Oracle as that is what our company has licenses for (we cannot use other suites I believe). Thank you very much.

Answer 1

Within the PL/SQL block you have to select into something; either explicitly into a local variable (that you can print, as Najzero showed), or a locally declared cursor for later manipulation or printing, or an externally declared (ref) cursor.

Based on your comment, rather than any experience with this client, it sounds like you should be able to do this:

var reccursor refcursor;

DECLARE
    lEventID    INTEGER;
    lCustID     INTEGER := 1;
BEGIN

    SELECT  EventID INTO lEventID
    FROM    Event
    WHERE   EventName = 'Event1'

    -- open an externally-declared ref cursor to hold the result of the query
    OPEN :reccursor FOR
        SELECT  Field1,
                Field2,
                Field3
        FROM    Table1
        WHERE   EventID = lEventID
          AND   CustomerID = lCustID;
END;
/

Which I imagine is pretty similar to what your procedure does, except it will be opening the out parameter ref cursor rather than a bind variable.

This is the same as you'd do in SQL*Plus or SQL Developer, but they would need an explicit print :reccursor to see the contents of that, whereas it sounds like your client handles it automatically.