Oracle SQL procedure with table name as input using dynamic SQL

Question

I see a lot of similar questions already answered including here, here and here. The list goes on.

What makes mine different? These other questions seem to be very simple, one line sql statements. I have a more complex merge statement that spans multiple lines and no matter how I have tried to put the statement together it gives me a compilation error. Below is one such attempt.

It is a long statement, the only dynamic parts are 2 nonconsecutive lines at the very beginning as shown below. I have tried to make the whole statement a string and execute it, but I get an error that the string is too long, plus this makes it very hard to read so it is undesirable. I have also tried breaking up the 2 parts that require dynamic sql into 2 execute immediate blocks, but that throws a compilation error as well.

My code

create or replace procedure table_sync(
table_name in varchar2,
source_node in varchar2
)

is

begin
    execute immediate 
        'merge into ' || table_name || ' dest' /* ---- first line ---- */

        using (select date_time, version_date, data_entry_date, value
                'from username.' || table_name || '@' || source_node /* ---- second line ---- */
                where data_entry_date < (sysdate - 10)) src
        on    ( dest.date_time       = src.date_time  and 
                dest.version_date    = src.version_date
                )
        when  matched then 
            update 
                set
                    dest.data_entry_date = src.data_entry_date,
                    dest.value           = src.value
                    where
                        (case 
                          .
                          .
                          .

Is there a way to put combine this dynamic statement?

Thank you

You seem to be missing some quotes there, also, try assigning the dynamic query string to a clob and then execute that with `execute immediate stmnt'. — Viorel
– Viorel, Commented Feb 18, 2020 at 18:58
@Viorel The goal of the script is to make sure two tables are in sync from different servers. The rest is the business logic depending if one has a later data_entry_date or one is protected etc. I will look at assigning as a clob. What part should be a string? Only the first part or the whole statement? — Jeff Tilton
– Jeff Tilton, Commented Feb 18, 2020 at 19:15
The entire statement has to be, I will write a fiddle, as an example. — Viorel
– Viorel, Commented Feb 18, 2020 at 19:18

Viorel · Accepted Answer · 2020-02-18 23:18:05Z

0

Using merge to achieve this is a good approach, but your statement is missing some quotes. Here is a fiddle with an example, similar to what you are trying to do.

From that fiddle, create test data:

create table table1(id varchar2(30), username varchar2(30), fullname varchar2(30));
create table table2(id varchar2(30), username varchar2(30), fullname varchar2(30));

insert into table1 values('a1', 'b1', 'c1');
insert into table1 values('a2', 'b2', 'c2');
insert into table1 values('a3', 'b3', 'c3');
insert into table1 values('a4', 'b4', 'c4');

insert into table2 values('a1', 'b1', 'c1');
insert into table2 values('a2', 'b2', 'c2');
insert into table2 values('a3', 'b3', 'c3');

Your function:

CREATE OR replace PROCEDURE Table_sync(table_name IN VARCHAR2)
IS
  stmnt CLOB;
BEGIN
    stmnt := 'merge into ' || table_name || ' dest '
             || 'using (select id, username from table1) src '
             || 'on (dest.id = src.id) '
             ||
    ' when matched then update set dest.fullname = src.username || src.id '
             || ' where dest.username like ''%2'' '
             || ' when not matched then insert (id, username, fullname) values(src.id, src.id||src.username, src.username||src.username) '
;

EXECUTE IMMEDIATE stmnt;
END table_sync;

If the record in table1 exists with the same value in col1 as the column in table2, then it will update it based on a condition, if the record does not exist, it will insert it.

You can write all your conditions inside that where statement and pay attention to the 2xsingle quotes used for quoting values in the dynamic query.

edited Feb 18, 2020 at 23:18

answered Feb 18, 2020 at 19:29

Viorel

3493 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeff Tilton Over a year ago

it is unfortunate that I have to make the whole statement a string. I will attempt this later this afternoon or tomorrow morning and see how it goes. Thanks for your help!

Viorel Over a year ago

No problem! That is the statement you want to execute, so you have to make it a string, you can (and should) bind variables and pass values with using, something like execute immediate 'select a from abc where b = :1' using some_variable;. Good luck!

Collectives™ on Stack Overflow

Oracle SQL procedure with table name as input using dynamic SQL

My code

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

My code

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related