Select into variables using execute

[Jim Anderson]

I need to select int 2 distinct variables values from different tables. The
only way that I can make a table name variable is by using the execute statement.
But the problem I get then is that the 2 variables that I define up in the
stored procedure our out of the execute statement's scope. How do I solve
that problem.

case1 (didn't work):
declare @x int, @y int , @i tinyint
set @i=3
execute ('select @x = intx , @y = inty from t_table' + @i + ' where intIdentityID=1')

case2 (didn't work):
declare @x int, @y int , @i tinyint
set @i=3
execute ('select ' + @x + ' = intx , ' + @y + ' = inty from t_table' + @i
+ ' where intIdentityID=1')

[jim anderson]

Never mind...got it...

"Jim Anderson" <jimmm@bigfoot.com> wrote:
>
>I need to select int 2 distinct variables values from different tables.
The
>only way that I can make a table name variable is by using the execute statement.
> But the problem I get then is that the 2 variables that I define up in
the
>stored procedure our out of the execute statement's scope. How do I solve
>that problem.
>
>case1 (didn't work):
>declare @x int, @y int , @i tinyint
>set @i=3
>execute ('select @x = intx , @y = inty from t_table' + @i + ' where intIdentityID=1')
>
>case2 (didn't work):
>declare @x int, @y int , @i tinyint
>set @i=3
>execute ('select ' + @x + ' = intx , ' + @y + ' = inty from t_table' + @i
>+ ' where intIdentityID=1')
>

[questexpert]

Hey Jim,
could you please share your solution.
Thanks.
I am running into a same kind of situation and wondering if the approach i am thinking will work.

CREATE OR REPLACE PROCEDURE GRANT_ACCESS (TRG_OWNER IN VARCHAR2, TRG_OBJ_NAME IN VARCHAR2)
BEGIN
GRANT &ACEESS_LEVEL ON TRG_OBJ_NAME TO TRG_OWNER
COMIT;
EXCEPTION
WHEN OTHER THEN
RAISE_APPLICATION_ERROR (“ERROR OCCURRED” - ||SQL CODE);
END GRANT_ACCESS
CREATE OR REPLACE TRIGER TRG_CAPTURE_DDL
AFTER
CREATE OR ALTER OR DROP
ON DATABASE
BEGIN/*
EXECUTE PROCEDURE GRANT_ACCESS (
SELECT
OWNER
,OBJECT_NAME
--,OBJECT_TYPE
FROM
ALL_OBJECTS
WHERE
OBJECT_TYPE IN ('TABLE', 'VIEWS')
AND OWNER NOT IN (‘SYS’, ‘SYSTEM’)
AND LAST_DDL_TIME > (SYSDATE - &No_of_Hours_x/24))*/
END TRG_CAPTURE_DDL

Do you think we can do /* .. */ ? mean within Execute () run a SQL Select statement? For passing paramaters..