2

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?

kpollock
  • 3,899
  • 9
  • 42
  • 61

3 Answers3

3

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
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Presumably the table select would be similar? Interestingly, SQL Navigator seem to pick up that this is SQL*Plus syntax and ignores it. I'm not up on the differences between SQL Developer and SQL*Plus. is SQL Developer the industry standard tool these days? – kpollock Mar 31 '14 at 10:24
  • @kpollock - not sure there is a standard; a lot of people use Toad or PL/SQL Developer, but SQL Developer has the advantage of being free. It's basically a GUI version of SQL*Plus with bits of OEM. I still use SQL*Plus most of the time. Not sure what you mean about the 'table select' though. If you're doing a simple select you don't need the PL/SQL bits around it, and the results can either go to a grid or to the worksheet script output pane, depending on how you run it (as a statement or as a script). Which probably won't make much sense unless/until you use that client... – Alex Poole Mar 31 '14 at 10:29
  • "Using a PL/SQL block for an ad hoc query seems a little heavy-handed". Can you explain what the 'easy' way is then? Some might be complex, some might be simple. It's just part of my way of working - inspecting the data, finding issues, trying things out. I am d/l SQL Developer now.. – kpollock Mar 31 '14 at 10:29
  • @kpollock - I mean I'm not sure why you're using PL/SQL at all, rather than plain SQL. I realise the example you gave is a trivial one for learning how it works, but it's not clear if your real queries actually need a PL/SQL element or can be simple `select` statements. Your description of what you do in SSMS sounds like you do just want a simple `select` though? – Alex Poole Mar 31 '14 at 10:35
  • Okay this is a bit like T-SQL vs SQL, but I must admit to thinking of it as all SQL. I am just doing ad-hoc queries of unknown complexity. Selects mainly, often with variables. What's the easy way to do it? – kpollock Mar 31 '14 at 10:39
  • trying your 2nd example gets me prompted for values - was that the intention? And thanks for all this - much more helpful that "read the manual n00b..." – kpollock Mar 31 '14 at 10:42
  • @kpollock - oh, right, if you run as a statement then it'll prompt for the bind variables. If you run as a script then it won't, as it'll get them from the `variable`/`execute`. You could look at substitution variables too. If you are doing a `select`, regardless of the complexity of the query itself, stick to plain SQL. You can hard-code any values you want to set; using bind variables just makes it easier to change them in one place (well, and is more efficient, but that doesn't matter so much for ad hoc). – Alex Poole Mar 31 '14 at 10:47
  • I am not sure I know what you mean by the 'plain sql' equivalent (including getting output). Can I ask for a very quick example? – kpollock Mar 31 '14 at 10:50
  • @kpollock - the third query in my answer is the 'plain SQL' equivalent of the second one; it's a straight `select`, not a `select` wrapped in a PL/SQL block (`declare`/`begin`/`end`). You should really only use PL/SQL for things you can't do in 'plain' SQL. – Alex Poole Mar 31 '14 at 10:59
  • Sorry, didn't see that edit. 3rd example prompts me for input for my_id rather than using the assigned value... even when I use "run statement" – kpollock Mar 31 '14 at 11:09
  • @kpollock - you need to use 'run script' to avoid the prompt. (Or hard-code the value, of course *8-) – Alex Poole Mar 31 '14 at 11:14
  • 'Run Script' did it. Which means it displays as text not a grid. Oh well, guess there is no way round that. thanks. – kpollock Mar 31 '14 at 11:16
1

I use Oracle SQL Developer.

Anyway, this should work in any oracle sql client:

If you just want to see your results, you can use 

dbms_output.put_line('Foo' || somevar || ' bar');

Before this, run

 SET SERVEROUTPUT ON

Check the examples at docs.oracle.com

tjati
  • 5,761
  • 4
  • 41
  • 56
1

I would suggest using sql developer available free from the oracle website. There is a button which allows you to run sql as a script which will get back what you want. SSMS doesn't work with pl/sql.

Yas V
  • 96
  • 2