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.

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.

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.

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.

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;

---

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;

---