Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

Dynamic sql in procedure for inserting

Rafal SJul 2 2020 — edited Jul 2 2020

Dear Oracle Family

I am struggling with a problem below:

I want users to provide information in stored proc like user_id,country_id ,category_id and based on that i would like to run insert(or merge) statement.

So i was thinking that if i generate string as 'insert into test_table values user_id,country,country_id' and then execute immediate that it would work  but its not.

I tried to play a bit with bind variables but without any luck ;/

below You can see only select statement as try to see how it would work.

Additionally my second requirement is that sometimes users can provide group of countries for ex EUROPE(i have mapping from europe to countries in other table )-but not sure how to touch that also so i focused on 1 country per execute for now.

Thank You for any tips !!!

CREATE or replace PROCEDURE add_user_test (user_id in VARCHAR2,country in VARCHAR2,category_id integer ) AS

sqlrun varchar2(1024) :='select '||user_id||' from dual ';

BEGIN

   -- dbms_output.put_line(sqlrun);

   EXECUTE IMMEDIATE sqlrun ;

END;

exec add_subscriptions_test('sas')

This post has been answered by KayK on Jul 2 2020
Jump to Answer
Comments
Post Details
Added on Jul 2 2020
11 comments
621 views