Results 1 to 6 of 6

Thread: Execute immediate in database programming

  1. #1
    Join Date
    Jul 2009
    Posts
    154

    Execute immediate in database programming

    Hello sir,

    I am working on a forms and report development for my own decided project and need some help from you.Can you tell me in which type of situations,we can use the EXECUTE IMMEDIATE command.

    What is the actual purpose of using this statement and which type of work in the database can be performed through this.I am waiting for your reply.

  2. #2
    Join Date
    May 2008
    Posts
    2,393

    Execute immediate in database programming

    The EXECUTE IMMEDIATE statement is very useful term in the database programming.It can executes some dynamic SQL statement or anonymous PL/SQL block.You can implement it with the SQL statements but cannot get reflected and implemented directly in PL/SQL.

    EXECUTE IMMEDIATE statement is the replacement of DBMS_SQL which is used in the oracle
    8i version.

  3. #3
    Join Date
    Feb 2008
    Posts
    1,856

    Usage tips for EXECUTE IMMEDIATE statement

    Tips for EXECUTE IMMEDIATE statement
    There are some tips described here,how and when use the command -

    1- EXECUTE IMMEDIATE can't commit a DML transaction automatically,you have to enter commit explicitly where as the DDL commands automatically get committed.
    2- Multiple-row queries are not supported by EXECUTE IMMEDIATE statement.
    3- No need to put the semi-colon during execution of SQL statements but the semi-colon is required for executing a PL/SQL block at the end.
    4- You can't implement it in the Forms Development, Forms 6i front-end as it is on PL/SQL 8.0.6.3 is not supported.

  4. #4
    Join Date
    Jan 2008
    Posts
    1,521

    Example of EXECUTE IMMEDIATE statement

    You can retrieve the stored value in the database tables.To access the values from the tables using EXECUTE IMMEDIATE statement would be as follows-

    Code:
    declare
    Ace_var   varchar2(20);
    begin
     execute immediate 'select count(1) from employees'
       into Ace_var;
     dbms_output.put_line(Ace_var);
    end;
    The above written code will retrieve the values from the employees table.

  5. #5
    Join Date
    Apr 2008
    Posts
    1,949

    Insert values using EXECUTE IMMEDIATE

    Insert values using EXECUTE IMMEDIATE :

    The EXECUTE IMMEDIATE statement can be used to insert the values in particular table.Before inserting the values through the PL/SQL,you need to create a separate table.

    To insert the values in the My_table using EXECUTE IMMEDIATE would be as follows-

    Code:
    create or replace procedure Val_insert_Proc as
        begin
            for lp_var in 1 .. 10000
            loop
                execute immediate insert into mytable values ( :x ) using lp_var;
            end loop;
       end;

  6. #6
    Join Date
    May 2008
    Posts
    2,015

    Calling procedure using EXECUTE IMMEDIATE

    You can call the stored procedure using this statement in the PL/SQL block.Just make sure the function to which you are going to call is exist or not.If it doesn't exist in the database catalog then you need to create it first with the name specified and include the name with the EXECUTE IMMEDIATE statement.

    To call a procedure using EXECUTE IMMEDIATE,you can use this code -

    Code:
    DECLARE
      2    Result VARCHAR2(60);
      3  BEGIN
      4    EXECUTE IMMEDIATE  Proc1('[optional_arguments]');
      5    EXECUTE IMMEDIATE Proc1 ('[optional_arguments]') INTO :Result
      6    USING OUT Result;
      7  END;

Similar Threads

  1. Creating Database link in database
    By Lachlann in forum Software Development
    Replies: 3
    Last Post: 28-01-2010, 12:17 PM
  2. Socket programming: Is any new Programming Language?
    By Kushan in forum Software Development
    Replies: 3
    Last Post: 14-11-2009, 10:13 AM
  3. How to execute Server.Execute function in ASP.NET (C#)
    By RasMus in forum Software Development
    Replies: 3
    Last Post: 25-09-2009, 12:30 PM
  4. Replies: 3
    Last Post: 13-12-2008, 12:49 PM
  5. convert filemaker pro database to access database
    By Czack in forum MS Office Support
    Replies: 3
    Last Post: 15-04-2007, 01:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •