2

Ok I have seen a bunch of questions attempt to answer this but all the answers seem horribly complicated or don't actually answer the question.

Is there a way to simply do something like this:

DECLARE
   v_some_variable VARCHAR2(10) := 'Find Me';
BEGIN
  select column1, column2, column3
  from someTable st
  where st.columnTarget = v_some_variable
END;

And have this displayed in a grid?

This is so simple on SQL server and is maddeningly NOT simple in Oracle.

Every permutation or combination I try from various answers either does not work and gives an error, requires defining every column and datatype from the output prior to running the query or does not output to the grid and you have to roll your own text output. All of these are very poor solutions in my opinion. What am I missing? Oracle can't be this bad can it?

Improve this question
7
  • What are the columns of some table. Commented Dec 16, 2019 at 19:41
  • There are dozens of columns of various types, some of which are sub queries, some of which are calculated. That's why all the solutions I have found to date are bad in my opinion because it would take me half an hour to go through and define every column properly. The default here should just show the results of the select, whatever they are I don't care. I should be able to add or remove columns willy nilly without having to define new variables or records or intermediate tables. Please tell me that Oracle has something as basic as this! Commented Dec 16, 2019 at 19:51
  • A table does not have calculated values. Are you talking abouit views? Commented Dec 16, 2019 at 20:29
  • @EdHeal A query can have calculated values, and we are talking about a query, so not sure what the problem is? Commented Dec 16, 2019 at 20:48
  • Your example is just selecting columns from a table. Commented Dec 16, 2019 at 20:59

3 Answers 3

Reset to default
2

It is arguably possible to print query output from PL/SQL by returning a refcursor or printing it or something. Although like you, I don't find any of those solutions very easy or intuitive.

I do a lot of SQL development in Oracle, and generally - if you want to get a result grid back, don't use a PL/SQL block. Just use plain SQL statements. 

select column1, column2, column3
  from someTable st
  where st.columnTarget = :v_some_variable; -- when you run this, it'll prompt for the value

If you don't like the prompt popup, you can also declare bind variables in SQL*Plus (the backend behind SQL Developer) like this:

var v_some_variable VARCHAR2(10)
exec :v_some_variable := 'Find Me';

select column1, column2, column3
  from someTable st
  where st.columnTarget = :v_some_variable;

If you run that as a script (F5), it won't prompt you for a value. Note that this still isn't using a PL/SQL block.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

So very close, however in SQL Developer, this prompts you for values (seems to ignore the exec statement). So although it's fine for my current need, since I only have one parameter that needs to change while I debug, there will be others where this would be very annoying since some of the sprocs have like 5 parameters.
why do you want the grid - for readability, or for some grid feature, like export?
Both actually ;-)
You can use set format ansiconsle to make the script results more readaby, and use spool and set format csv, insert, xml, json, etc to export your results
2

If you just want to use SQLPlus to write a query and execute it to see the results in a grid, then the simplest way to go is the following:

  1. Start by defining the parameter:
define p = 1000;
  1. Then you can use it:
select *
from table1 t1
join etc on etc.id = t1.etc_id
where etc.p = &p
Improve this answer

Comments

1

You need to learn to do things the Oracle way, which is not the same as the SQL Server way. It's also incredibly simple in Oracle - if you know the way Oracle does it. First we create the database table and add some data.

create table some_table (first varchar2(10), second varchar2(10));

insert into some_table values ('First', 'Find me');

Now for the PL/SQL.

declare
  v_var varchar2(10);
  v_var2 varchar2(10);
begin
  v_var := 'Find me';
  select first into v_var2 from some_table where second = v_var;
end;
/

Compare my PL/SQL with yours. Hint: Note that mine contains into

Refer to this db fiddle

Improve this answer

3 Comments

This is exactly the answer that most people provide with is emphatically NOT what I am looking for. You are defining an output variable for EVERY column you want to see and have to know the exact datatype of each column. Which is fine for a toy example with one or two columns. Try that with a real world example with (in my case) 80+ columns which are a mix of sub-queries, calculated fields and regular columns coming from 10 different tables. I'll see you in an hour.
@Bitfiddler you can explore the option of collections here, can create variable of row type of table you are using in query. no need to define every column datatype, it will take datatype of column from table.
@MansiRaval I'll take a look, thanks for the suggestion.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.