Select into variables using execute


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Select into variables using execute

  1. #1
    Jim Anderson Guest

    Select into variables using execute


    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')


  2. #2
    jim anderson Guest

    Re: Select into variables using execute


    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')
    >



  3. #3
    Join Date
    Jul 2009
    Posts
    1

    RE: Select into variables using execute

    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..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center