0

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?

Bitfiddler
  • 3,268
  • 5
  • 31
  • 42
  • What are the columns of some table. – Ed Heal Dec 16 '19 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! – Bitfiddler Dec 16 '19 at 19:51
  • A table does not have calculated values. Are you talking abouit views? – Ed Heal Dec 16 '19 at 20:29
  • @EdHeal A query can have calculated values, and we are talking about a query, so not sure what the problem is? – Bitfiddler Dec 16 '19 at 20:48
  • Your example is just selecting columns from a table. – Ed Heal Dec 16 '19 at 20:59
  • Yes, because this is just a simple example, but the query could be: Select column1+column2 as CalcColum, column3, column4 from SomeTable. Doesn't really matter what the columns are, I just want whatever the result is of whatever query I want to be displayed in a grid. I want to be able to have variables at the top so that if those variables are used a bunch of times within a complex query, I can change one place and have all the criteria change. – Bitfiddler Dec 16 '19 at 21:16
  • I think this is a valid question - When I'm doing DevOps tasks, I often need to write complex queries in SQLPLUS, and I think it is useful to have a way of setting parameters without declaring a script. – jmgonet Dec 02 '20 at 10:03

3 Answers3

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

Abra
  • 11,631
  • 5
  • 25
  • 33
  • 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 Dec 16 '19 at 20:15
  • @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. – Mansi Raval Dec 17 '19 at 07:01
  • @MansiRaval I'll take a look, thanks for the suggestion. – Bitfiddler Dec 19 '19 at 18:08
1

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.

kfinity
  • 6,703
  • 1
  • 9
  • 17
  • 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. – Bitfiddler Dec 16 '19 at 20:22
  • why do you want the grid - for readability, or for some grid feature, like export? – thatjeffsmith Dec 16 '19 at 20:28
  • Both actually ;-) – Bitfiddler Dec 16 '19 at 20:41
  • 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 – thatjeffsmith Dec 17 '19 at 09:35
0

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
jmgonet
  • 754
  • 10
  • 16