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

From: <matt_at_mailinator.com>
Date: 7 Jul 2006 12:48:37 -0700
Message-ID: <1152301717.212791.19820@35g2000cwc.googlegroups.com>

matt_at_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 Received on Fri Jul 07 2006 - 14:48:37 CDT