I used to do Oracle development many many years ago. I have spent most of the past 15 years doing mainly SQL Server, and where I have done Oracle work, been insulated from the workings by Web services etc.
My question seems to have been asked on the web a few times, but it seems difficult somehow to communicate - at least judging by the answers. I very much appreciate that tools and functionality differ, know I have to learn new things, but this is so simple, yet seems so hard to do.
I am looking to do some ad-hoc queries on the database. At the moment we are using SQL Navigator - I am open to using other tools...
In SQL Server Management Studio, if you open a query window, type a bit of SQL that retuns a value or a set, you get a nice display of the rows or values in a results window.
I've gathered that with Oracle PL/SQL things are a bit different, worked out that I need to return a cursor - but how do I get a tool to display the results?
I started simple:
declare
my_id number := 356655;
cursor c1 is select my_id from dual;
begin
open c1;
end;
This runs fine - but how do I see the results? Are there any tools that deal with this as 'nicely' as SSMS? I am used to being able to do a lot of this, including stuff like
(perhaps not exactly the right syntax? but you get the idea...)
declare
my_id number := 356655;
cursor c1 is select name from my_table where id = my_id;
begin
open c1;
And having the results displayed to me as text/grid. Ideally that there is a nice solution. Some spiffy new tool, maybe?
With SQL Developer or SQL*Plus you can use a bind variable declared before the PL/SQL block:
variable rc refcursor;
declare
my_id number := 356655;
begin
open :rc for select my_id from dual;
end;
/
print rc
RC
-------------------------------
356655
You can also use a bind variable within the query, which can be useful:
variable my_id number;
variable rc refcursor;
execute :my_id := 356655;
begin
open :rc for select :my_id from dual;
end;
/
print rc
The variable and print commands are covered in the SQL*Plus documentation, which largely applies to SQL Developer as well - that has its own documentation, including the commands that are carried over from SQL*Plus.
If you have a function that returns a ref cursor then you can call that in a query, as select func(val) from dual, and then the results can go in a grid; or you can call the function (or procedure) with the same :rc bind variable and print it. But I'm not sure either is helpful if you are only doing ad hoc queries.
On the other hand, using a PL/SQL block for an ad hoc query seems a little heavy-handed, even if your queries are complicated. You'd need a good reason to open a cursor for a select statement from within a block, rather than just running the select directly. (Not sure if that's a SQL Server thing or if you actually have a real need to do this!). If you're just running a query inside the block, you don't need the block, even if you want to keep a bind variable for the values you're using in the query:
variable my_id number;
execute :my_id := 356655;
select :my_id from dual;
:MY_ID
----------
356655