CODEWITHSUNDEEP
sqloracle-database
mani teja varma
mani teja varma

Reputation: 183

Converting the update statement dynamically to select

I would like to convert an update statement to select statement dynamically in PL/SQL.

For example update temp set b='apple' where a=1 should be converted to select count(*) from temp where a=1

Upvotes: 0

Views: 356

Answers (2)

Jon Heller
Jon Heller

Reputation: 36892

Do you really need to dynamically modify SQL, or are you just looking to get the number of rows modified? If you only need the number of rows modified, then SQL%ROWCOUNT can provide that:

create table temp as
select 1 a, 'orange' b from dual;

declare
    v_sql varchar2(32767) := q'[update temp set b='apple' where a=1]';
begin
    execute immediate v_sql;
    dbms_output.put_line('Rows updated: '||sql%rowcount);
end;
/

Output:

Rows updated: 1

If you really need to dynamically modify SQL, then you're in a world of pain. Parsing SQL 100% correctly is almost impossible, unless you can guarantee that your input SQL will conform to a small range of values.

If you need to parse and reformat SQL, hopefully you can use regular expressions like in Tejash's answer. If you need something more powerful, take a look at my open source program PLSQL_LEXER. If you still need more power, then take a look at Antlr. But as the solutions get more powerful they grow exponentially more complex.

Upvotes: 1

Popeye
Popeye

Reputation: 35920

If your query is dynamic then you can try the following:

-- DATA PREPARATION
WITH MY_QUERY ( QRY ) AS (
    SELECT
        q'#update temp set b='apple' where a=1#'
    FROM
        DUAL
)
-- ACTUAL QUERY
SELECT
    'SELECT COUNT(*) FROM '
    || TABLENAME
    || ' '
    || WHERE_CLAUSE AS UPDATED_QUERY
FROM
    (
        SELECT
            REGEXP_SUBSTR(QRY, '[^ ]+', 1, 2) AS TABLENAME,
            REGEXP_SUBSTR(QRY, 'where (.)+$', 1, 1) AS WHERE_CLAUSE
        FROM
            MY_QUERY
    );


--
OUTPUT:
--
SELECT COUNT(*) FROM temp where a=1

Your query(String) is wrapped in an alternative quoted string(q'#....#') as it contains quotes.

db<>fiddle demo

Upvotes: 2

Related Questions