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

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 Received on Fri Jul 07 2006 - 14:32:49 CDT