Home » SQL & PL/SQL » SQL & PL/SQL » EXECUTE IMMEDIATE
EXECUTE IMMEDIATE [message #290410] Fri, 28 December 2007 14:31 Go to next message
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 Sad
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: Timestamp date conversion
Next Topic: Rollback
Goto Forum:
  


Current Time: Wed Dec 07 04:32:42 CST 2016

Total time taken to generate the page: 0.11329 seconds