Re: syntax - query's scalar results to a varible?

matt_at_mailinator.com wrote:

<stuff about 'printing' a variable in PL/SQL>

Hi Matt,

in TOAD the SQL Editor has a set of output tabs at the bottom. One of these is called 'dbms ouput' (or similar). Before you run your query, click the button on this tab to turn DBMS_OUTPUT scanning on. You can set the scan frequency there as well (I tend to use 5 secs).

Now, in the sql editor you will have something like this :

--define variable

DECLARE v_roleID INTEGER;

--assign value to variable (based on input parameter's roleName)
BEGIN
   SELECT u.RoleID
   INTO v_roleID
   FROM edd_tblRoles u
   WHERE u.RoleName = 'Admin';
END;
--attempt to display contents of variable
--(for immediate debugging satisfaction)
BEGIN
   dbms_output.put_line(v_roleID);
END; TOAD will do the equivalent of SET SERVEROUTPUT ON which is an SQL*Plus command. The PL/SQL version is, as you have found, DBMS_OUTPUT.ENABLE(size);

When you run your code, the output will be displayed on the dbms_output tab.

Now, your comment about 'immediate debugging satisfaction' is only slightly wrong. DBMS_OUTPUT is displayed (say in SQL*Plus) at the very end of the command/script/whatever it was that generated it. In TOAD the output will appear every 'n' seconds where 'n' is your scanning frequency as set above.

Of course, you only need the BEGIN/END/SELECT INTO stuff above if you are writing PL/SQL stored procedures (packages/functions/procedures) or anonymous blocks. In straight SQL it is simply :

   SELECT u.RoleID
   INTO v_roleID
   FROM edd_tblRoles u
   WHERE u.RoleName = 'Admin';

Run that in TOAD and the result(s) are displayed on the lower part of the screen in the tab named 'results' (or something similar according to your version.

There's a pretty good TOAD support group on yahoo groups (yahoogroups.com) where some of the TOAD developers hang out (and me !)

Have fun.

Cheers,
Norm. [TeamT]

PS. Any replies will be read in a week when I come back from holiday :o) Received on Fri Jul 07 2006 - 14:45:39 CDT