473,327 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Dynamically execute PL/SQL statement

I am looking for the PL/SQL equivalent of the VBScript Exec and/or Eval
functions, i.e. I want to be able to dynamically create a statement, then
execute it in the current PL/SQL context, e.g.

declare
x integer := 5;
begin
ExecuteStatement('x := 10');
dbms_output.put_line(x); -- should put "10"
if EvaluateExpression('x*2 = 20') then
dbms_output.put_line('Yes');
else
dbms_output.put_line('No');
end if; -- should put 'Yes'
end;
EXECUTE IMMEDIATE doesn't seem to work, because it runs the statement in the
global context, not inside the context of the current function.
Jul 19 '05 #1
3 20355
"Agoston Bejo" <gu***@freemail.hu> wrote in message news:<cl**********@news.caesar.elte.hu>...
[...] because it runs the statement in the
global context, not inside the context of the current function.


You could you use global variables in packages, but it is often a bad
practice, prone to errors.

You can write:

execute immediate ('begin pkname.glob_var0 := something; end;');

Kamal
Jul 19 '05 #2
Jan
DECLARE

v NUMBER;

BEGIN

EXECUTE IMMEDIATE
'BEGIN :1:=10;END;'
USING OUT v;

DBMS_OUTPUT.PUT_LINE(v);

END;

Jan
Jul 19 '05 #3
GQ
"Agoston Bejo" <gu***@freemail.hu> wrote in message news:<cl**********@news.caesar.elte.hu>...
I am looking for the PL/SQL equivalent of the VBScript Exec and/or Eval
functions, i.e. I want to be able to dynamically create a statement, then
execute it in the current PL/SQL context, e.g.

declare
x integer := 5;
begin
ExecuteStatement('x := 10');
dbms_output.put_line(x); -- should put "10"
if EvaluateExpression('x*2 = 20') then
dbms_output.put_line('Yes');
else
dbms_output.put_line('No');
end if; -- should put 'Yes'
end;
EXECUTE IMMEDIATE doesn't seem to work, because it runs the statement in the
global context, not inside the context of the current function.

What is it you would like to do that you can't do ?
The following is an example using an anonymous block
to create a table in the schema running the script,
followed by the same thing in a procedure (that could take
parameters).

Declare
v_obj number;
v_tblsp varchar2(30);
Begin
Select count(*) into v_obj
from user_tables
where table_name = 'T_EMP';
If v_obj = 0 then
Select tablespace_name into v_tblsp
from user_tables
where table_name = 'EMPLOYEE';
execute immediate 'Create table T_EMP (c1 number primary key, c2
varchar2(20))'||
' tablespace '|| v_tblsp;
end if;
End;
/
Create or replace procedure testx authid current_user as
v_obj number;
v_tblsp varchar2(30);
Begin
Select count(*) into v_obj
from user_tables
where table_name = 'T_EMP';
If v_obj = 0 then
Select tablespace_name into v_tblsp
from user_tables
where table_name = 'EMPLOYEE';
execute immediate 'Create table HR_OWN.T_EMP (c1 number, c2
varchar2(20))'||
' tablespace '|| v_tblsp;
end if;
End;
/
Jul 19 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Arijit Chatterjee | last post by:
Dear Friens, I am writing a SP.But storeing a query in a variable.But at the time of execution generating error.Exam =================== Declare @query varchar{500) Set @query = 'Select * from...
1
by: Mark | last post by:
Is there a way to execute a statement that is built dynamically by a .NET application. For example I have a loop that is reading values from a database and I want to do something like the...
3
by: TJS | last post by:
trying to use code below to create dynamic variables but no success, what am I missing ? -------------------------- for i = 0 to 2 dim vString as string = "Dim submenu" & Cstr(i) & " As New...
1
by: sjallard | last post by:
Hi, I'd like to do something like that (see explanations after the code snippet) : Sub mainSub (foo as String) Dim msScript As New ScriptControl msScript.Language = "VBScript" Dim someVar...
0
by: jad2006 | last post by:
i created CREATE PROCEDURE SP_STMT_STMTRETRIEVE10(IN p_client_id varchar(32) ,IN p_TIN varchar(32)) P1: BEGIN DECLARE p_customer_id varchar(32); DECLARE stmt VARCHAR(100); DECLARE cursor1...
3
by: ChildProgrammer | last post by:
I am trying to upload data into an Access db from vb.net. The data was parsed out from text files. Everything seems to be working fine up till the .execute point. The debugger insists there is a...
5
by: ravysters | last post by:
hi.. i know that to execute dynamic statements in postgres we have the EXECUTE statement.. but postgres support the execute using statement like in oracle...
3
by: Agoston Bejo | last post by:
I am looking for the PL/SQL equivalent of the VBScript Exec and/or Eval functions, i.e. I want to be able to dynamically create a statement, then execute it in the current PL/SQL context, e.g. ...
3
by: osman7king | last post by:
I use the EXECUTE statement in a procedure which has 2 parameters, one is to hold the query, and the other for the value used in the query create or replace function delete_from_table1(character...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.