Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!nx01.iad01.newshosting.com!newshosting.com!novia!newscon06.news.prodigy.com!prodigy.net!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!m79g2000cwm.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:32:49 -0700
Organization: http://groups.google.com
Lines: 38
Message-ID: <1152300769.175082.88520@m79g2000cwm.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>
   <1152299326.131940.113510@s53g2000cws.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 1152300773 31122 127.0.0.1 (7 Jul 2006 19:32:53 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 7 Jul 2006 19:32:53 +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: m79g2000cwm.googlegroups.com; posting-host=134.163.253.126;
   posting-account=59uCjQ0AAADOOe3Tqs7UfOdEKKZ1o9cJ
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:270616

it has also occured to me, that perhaps what i am trying to do is
completely impossible or improper in pl/sql.

my whole point of setting a query's scalar result to a variable, is to
use that variable elsewhere, in multiple places (thus removing
redundant queries).

ala [proc usage]:

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

	--use variable, first time
	OPEN cur_results FOR
	SELECT RoleName FROM edd_tblRoles WHERE RoleID = v_roleID;

	--use variable, second time
	OPEN cur_results2 FOR
	SELECT UserName FROM edd_tblUserRoles WHERE RoleID = v_roleID;


...but that doesnt even compile: "V_ROLEID": invalid indentifier


should i just drop this avenue of thought? it could be something that
TSQL and many app languages are used to, but maybe im trying to do
something unconventional in PL/SQL.


thanks!
matt