Dynamic sql with for loop PL/SQL

Question

The following query needs to convert to dynamic SQL without hard code cursor SQL, using l_query, I do not know the l_query it will come as a parameter. Inside the loop, I need to execute another insert query ( l_insert_query) that also comes as a parameter.

Your counsel would be much appreciated

DECLARE
    CURSOR cust
    IS
        SELECT *
          FROM customer
         WHERE id < 500;
BEGIN
    l_query := 'SELECT * FROM customer  WHERE id < 5';
    l_insert_query :=
        'insert into data ( name, mobile) values ( cust.name,cust.mobile)';

    FOR r_cust IN cust
    LOOP
        EXECUTE IMMEDIATE l_insert_query;
    END LOOP;
END;

What do you know about the queries? How are you supposed to programmatically determine that, presumably, "cust.name" in l_insert_query is supposed to reference the name column from the customer table in l_query? If you know nothing about l_query, you have no idea how many tables it is querying from or what columns it returns let alone what alias a human might choose for the table name. — Justin Cave
– Justin Cave, Commented Aug 14, 2020 at 7:49
If both l_query and l_insert_query is parameter, It seems that your problem involve to semantic analysis of those two parameter. First you should build an algorithm to read those two parameter, check if both is valid SQL query, and l_query is suitable to run l_insert_query . With that algorithm, you could do whatever l_insert_query want to do, using dynamic SQL or maybe only SQL is enough. — Pham X. Bach
– Pham X. Bach, Commented Aug 14, 2020 at 8:01
So l_insert_query will always reference columns as cust.<<column name>>? Why would you design a system this way? If l_insert_query was just insert into data( name, mobile ) and l_query was select name, mobile from customer where id < 5, you could at least concatenate the two to generate the insert statement dynamically. — Justin Cave
– Justin Cave, Commented Aug 14, 2020 at 8:10
As it stands, you'd probably need to use dbms_sql to execute l_query, dynamically interrogate the cursor handle to get a list of column names, search l_insert_query for any cust. references, replace those with bind variables, iterate over the dbms_sql cursor handle calling l_insert_query and passing in bind variables. That's a whole pile of rather pointless string manipulation and dbms_sql is not a trivial package to work with (and debugging and maintenance are going to be much harder). — Justin Cave
– Justin Cave, Commented Aug 14, 2020 at 8:12
@JustinCave - "Why would you design a system this way? " Some solution architects regard the database as an obstacle to be overcome and broken when it can't be ignored. — APC
– APC, Commented Aug 14, 2020 at 10:58

Alex Poole · Accepted Answer · 2020-08-14 08:54:48Z

You could do this with a dynamic PL/SQL block:

declare
  l_query varchar2(100) := 'SELECT * FROM customer  WHERE id < 5';
  l_insert varchar2(100) := 'insert into data ( name, mobile) values ( cust.name,cust.mobile)';
  l_plsql varchar2(4000);
begin
  l_plsql := '
begin
  for cust in (' || l_query || ') loop
    ' || l_insert || ';
  end loop;
end;
';

  dbms_output.put_line(l_plsql);
  execute immediate l_plsql;
end;
/

The l_plsql statement ends up as a generated PL/SQL block using the cursor query and insert statement:

begin
  for cust in (SELECT * FROM customer  WHERE id < 5) loop
    insert into data ( name, mobile) values ( cust.name,cust.mobile);
  end loop;
end;

db<>fiddle

But that you can do this doesn't mean you should. This is vulnerable to SQL injection, and doesn't seem like a very safe, sensible or efficient way to handle data manipulation in your system.

Collectives™ on Stack Overflow

Dynamic sql with for loop PL/SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related