Viewing query results with a parameters in Oracle

Question

I need to run big queries (that was a part of SP) and look at their results (just trying to find a bug in a big SP with many unions. I want to break it into parts and run them separately). How can I do that if this SP have few parameters? I don't want to replace them in code, it would be great just to add declare in a header with a hardcode for this parameter.

I've tried something like this:

DECLARE

p_asOfDate DATE :=  '22-Feb-2011';

BEGIN

SELECT * from myTable where dateInTable < p_asOfDate;

END

But it says that I should use INTO keyword. How can I view this results in my IDE? (I'm using Aqua data studio)

I need to do that very often, so will be very happy if will find a simple solution

elfwyn · Accepted Answer · 2011-02-21T14:23:35.910

You are using an anonymous block of pl/sql code. In pl/sql procedures you need to specify a target variable for the result.

So you first need to define a variable to hold the result in the declare section and then insert the result data into it.

DECLARE
  p_asOfDate DATE :=  '22-Feb-2011';
  p_result myTable%ROWTYPE;
BEGIN
  select * into p_result from myTable where dateInTable < p_asOfDate;
END

That said you will probaply get more than one row returned, so I would use a cursor to get the rows separately.

DECLARE
  CURSOR c_cursor (asOfDate IN DATE) is 
    select * from myTable where dateInTable < asOfDate;
  p_asOfDate DATE :=  '22-Feb-2011';
  p_result myTable%ROWTYPE;
BEGIN
  OPEN c_cursor(p_asOfDate);
    loop
      FETCH c_cursor into p_result;
      exit when c_cursor%NOTFOUND;
      /* do something with the result row here */
    end loop;
  CLOSE c_cursor;
END

To output the results you can use something like this for example:

dbms_output.put_line('some text' || p_result.someColumn);

Alternatively you can execute the query on an sql command-line (like sqlplus) and get the result as a table immediately.

I hope I understood your question correctly...

update

Here is a different way to inject your test data:

Use your tools sql execution environemnt to submit your sql statement directly without a pl/sql block.

Use a "&" in front of the variable part to trigger a prompt for the variable.

select * from myTable where dateInTable < &p_asOfDate;

The Result should be displayed in a formatted way by your tool this way. I do not know about Aqua, but some tools have functions to define those parameters outside the sql code.

Thanks for your answer. Yes, it helped, but can I show the results in a grid? When I'm running simple select script in Aqua, I'm getting full-functional grid with filters, scrolls and easy copy to csv. Is there some way to show results not in text view, but in that grid? It could be very difficult to design output line, for any script if I need to check 20 scripts or more — Archeg, Feb 21 '11 at 13:13

Viewing query results with a parameters in Oracle

1 Answers1