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

Answer 1

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.