Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!35g2000cwc.googlegroups.com!not-for-mail
From: matt@mailinator.com
Newsgroups: comp.databases.oracle.server
Subject: Re: syntax - query's scalar results to a varible?
Date: 7 Jul 2006 12:48:37 -0700
Organization: http://groups.google.com
Lines: 51
Message-ID: <1152301717.212791.19820@35g2000cwc.googlegroups.com>
References: <1151961674.498148.323250@h44g2000cwa.googlegroups.com>
   <ut2ja2pgtie1i6bonsu8m62e1h477157s8@4ax.com>
   <1152113110.784774.234160@b68g2000cwa.googlegroups.com>
   <vo0oa25lvadknvhpp2jr40j8hgb8vkb5uj@4ax.com>
   <1152297758.067743.115940@m79g2000cwm.googlegroups.com>
NNTP-Posting-Host: 134.163.253.126
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1152301722 1260 127.0.0.1 (7 Jul 2006 19:48:42 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 7 Jul 2006 19:48:42 +0000 (UTC)
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.8.0.4) Gecko/20060508 Firefox/1.5.0.4,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: 35g2000cwc.googlegroups.com; posting-host=134.163.253.126;
   posting-account=59uCjQ0AAADOOe3Tqs7UfOdEKKZ1o9cJ
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:270618

matt@mailinator.com wrote:
> i had thought it would be something like:
>
> 	SET serveroutput ON;
> 	EXEC dbms_output.enable;
>
> 	--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;


this appears to be wrong. in SQL*Plus, the proper syntax is:

	SET serveroutput ON;
	exec dbms_output.enable;

	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 =
p_roleName;

		 --display contents of variable
		 dbms_output.put_line(v_roleID);
	END;

...the dbms has to be in the same BEGIN/END chunk. (it appears
begin/end chunks operate differently in PL/SQL than in TSQL. in sql
server one can include multiple statements and run them all at once;
that doesnt seem to be so happy in oracle).

so the syntax for dbms is figured out. the larger question, of whether
my general plan of using varaibles to eliminate repetitive queries, is
still unknown to me.


matt