Home » SQL & PL/SQL » SQL & PL/SQL » EXECUTE IMMEDIATE
EXECUTE IMMEDIATE [message #290410] |
Fri, 28 December 2007 14:31  |
BAshraf
Messages: 1 Registered: December 2007
|
Junior Member |
|
|
Hello,
I m new using Oracle and i have a problem with dynamic query:
I m trying to execute a query that i build in C# and to return the id of a created user in the c# code:
i received this DYNAMIC query (from C# code) :
INSERT INTO app_users (code_name, visibility, serial, creator_id, updater_id, password, email, display_name, default_role_id, enabled)
VALUES (''TEST_21'', 2, 1000, 2, 2, ''CBxoqHoenEE='', ''test@email.com'', ''admin'', 1, 1) ';
My function will return -1 if the user exist or will run the query and return the Id of the new created user. The app_users table has a column named entity_id which is a sequence number. Here is my sql which will probably be in a function :
set serveroutput on;
declare
cnt number;
t varchar(4000);
begin
t := 'INSERT INTO app_users (code_name, visibility, serial, creator_id, updater_id, password, email, display_name, default_role_id, enabled) VALUES (''TEST_21'', 2, 1000, 2, 2, ''CBxoqHoenEE='', ''test@email.com'', ''admin'', 1, 1) ';
-- This works and returns the id but i cant used it this way because i receive the query as a parameter to the function.
--INSERT INTO app_users (code_name, visibility, serial,
-- creator_id, updater_id, password, email, display_name,
-- default_role_id, enabled)
-- VALUES ('TEST_21', 2, 1000, 2,
-- 2, 'CBxoqHoenEE=', 'test@email.com'
-- 2, 'admin', 1, 1)
-- returning entity_id into cnt;
-- when using the execute this way i get an error message 
execute immediate t returning entity_id into cnt;
Dbms_output.put_line('value is :' || cnt);
end;
/
Here is the error message :
ORA-06550: line 13, column 33:
PLS-00103: Encountered the symbol "ENTITY_ID" when expecting one of the following:
into bulk
The symbol "ENTITY_ID" was ignored.
Can anyone help,
Thanks
|
|
|
Re: EXECUTE IMMEDIATE [message #290415 is a reply to message #290410] |
Fri, 28 December 2007 15:20   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Hello,
even (especially) as the new Oracle programmer, you shall study its documentation found eg. online on http://tahiti.oracle.com/
INSERT Statement
Performing SQL Operations with Native Dynamic SQL (especially Example 7.5)
(links are for 10.2, as you did not mention your Oracle version; it may differ a little)
In short: RETURNING is part of INSERT statement, so it has to be in the dynamic string too. On the opposite, EXECUTE IMMEDIATE knows only RETURNING INTO <variable> clause (nothing between these two words). So you shall use something like
EXECUTE IMMEDIATE 'INSERT INTO app_users (...)'
||' VALUES (...)'
||' RETURNING entity_id INTO :1'
RETURNING INTO cnt;
Once again, when having this (syntax) problem, consult the documentation first. It is really helpful, covered with many useful examples. Also with trying, you will learn a lot.
Quote: | I m trying to execute a query that i build in C# and to return the id of a created user in the c# code
|
Strange, why the query cannot be built in PL/SQL (provided from C# only with parameter values)?
Some kind of "generic" query execution tool?
Not a good idea as it does not BIND the parameters.
|
|
|
Re: EXECUTE IMMEDIATE [message #290453 is a reply to message #290410] |
Sat, 29 December 2007 00:40  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
In addition to what flyboy told you, I think the base problem is that you use an anonymous block in a sql script instead of a stored function.
A stored function can be called with a parameter.
|
|
|
Goto Forum:
Current Time: Fri Feb 14 10:04:39 CST 2025
|