Skip to Main Content

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 ';


   -- dbms_output.put_line(sqlrun);



exec add_subscriptions_test('sas')

This post has been answered by KayK on Jul 2 2020
Jump to Answer
Post Details
Added on Jul 2 2020