Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Execute immediate in database programming

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 04-03-2010
Member
 
Join Date: Aug 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.

Reply With Quote
  #2  
Old 04-03-2010
Member
 
Join Date: May 2008
Posts: 2,383
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.
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
  #3  
Old 04-03-2010
Member
 
Join Date: Feb 2008
Posts: 1,845
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.
Reply With Quote
  #4  
Old 04-03-2010
Member
 
Join Date: Jan 2008
Posts: 1,515
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.
Reply With Quote
  #5  
Old 04-03-2010
Member
 
Join Date: Apr 2008
Posts: 1,948
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;
Reply With Quote
  #6  
Old 04-03-2010
Member
 
Join Date: May 2008
Posts: 2,007
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;
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Execute immediate in database programming"
Thread Thread Starter Forum Replies Last Post
Creating Database link in database Lachlann Software Development 3 28-01-2010 01:17 PM
Socket programming: Is any new Programming Language? Kushan Software Development 3 14-11-2009 11:13 AM
How to execute Server.Execute function in ASP.NET (C#) RasMus Software Development 3 25-09-2009 01:30 PM
JetBrains Introduces a New Programming Paradigm With its Meta Programming System JoeFrat Software Development 3 13-12-2008 01:49 PM
convert filemaker pro database to access database Czack MS Office Support 3 15-04-2007 02:06 AM


All times are GMT +5.5. The time now is 09:07 AM.