PL/SQL: Query using bind variables

Question

Could you pls help to understand if it is possible to make this work using bind variables.

STEP#1 :

SELECT sql_query INTO d_sql_query FROM RPU WHERE track_id = v_track_id AND track_id_serv = v_track_id_serv;

=> for eg, d_sql_query can be like ‘CD_UNBIL.trans_dt >= TO_DATE('20200702 12:11:03', 'YYYYMMDD HH24:MI:SS')’ or d_sql_query can have a bigger condition

Later we add this d_sql_query to a bigger query as follows: -

STEP#2

query := 'SELECT CDDATA.msg_id, CDDATA.msg_id2, CDDATA.msg_id_serv,

           CDDATA.split_row_num, CDDATA.cdr_data_partition_key,….

………

                AND RPU_WORK.tracking_id = :tracking_id AND RPU_WORK.tracking_id_serv = :tracking_id_serv AND '|| NVL(d_sql_query, ' 1=1 ');

As you can see that d_sql_query is appended, so the whole query looks like

...
AND RPU_WORK.tracking_id = :tracking_id AND RPU_WORK.tracking_id_serv = :tracking_id_serv AND '|| NVL(CD_UNBIL.trans_dt >= TO_DATE('20200702 12:11:03', 'YYYYMMDD HH24:MI:SS'), ' 1=1 ');

I am looking to make it like (using bind variables as below).

AND RPU_WORK.tracking_id = :tracking_id AND RPU_WORK.tracking_id_serv = :tracking_id_serv AND '|| NVL(CD_UNBIL.trans_dt >= :b1, ' 1=1 ');

I can use "execute immediate <step#2> using " once i have the query as above.

pmdba · Accepted Answer · 2020-07-15 14:05:18Z

1

Could you construct the end of your query something like this?

...
AND RPU_WORK.tracking_id = :tracking_id 
AND RPU_WORK.tracking_id_serv = :tracking_id_serv 
AND case when :b1 is not null
    then
        case when CD_UNBIL.trans_dt >= TO_DATE(:b1, 'YYYYMMDD HH24:MI:SS')
        then 1
        else 0
        end
    else 1 
    end = 1

answered Jul 15, 2020 at 14:05

pmdba

7,0832 gold badges10 silver badges17 bronze badges

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

2 Comments

Chandra Bhushan Over a year ago

The problem here is that the d_sql_query can have any condition with any operator, not just date as shown above

pmdba Over a year ago

You're not going to be able to convert fully to bind variables that way. As long as the "where" clause can contain user input, you'll have to concatenate text and will have potential SQL injection issues.

Jon Heller · Accepted Answer · 2020-07-15 23:04:58Z

You can use my open source program PLSQL_Lexer to dynamically find and replace literals with bind variables.

For example:

--Convert literals to bind variables.
declare
    v_sql_expression clob := q'[CD_UNBIL.trans_dt >= TO_DATE('20200702 12:11:03', 'YYYYMMDD HH24:MI:SS')]';
    v_new_sql_expression clob;
    v_tokens token_table := token_table();
    v_bind_number number := 0;
begin
    v_tokens := plsql_lexer.lex(v_sql_expression);

    for i in 1 .. v_tokens.count loop
        if v_tokens(i).type in ('text', 'numeric') then
            v_new_sql_expression := v_new_sql_expression || ':b'||v_bind_number;
            v_bind_number := v_bind_number + 1;
        else
            v_new_sql_expression := v_new_sql_expression || v_tokens(i).value;
        end if;
    end loop;

    dbms_output.put_line('New expression: '||v_new_sql_expression);
end;
/

Output:

New expression: CD_UNBIL.trans_dt >= TO_DATE(:b0, :b1)

This code will have some problems - the TO_DATE has two variables instead of one, date literals like date '2000-01-01' would need special handling because it's actually 3 tokens instead of one, this won't prevent SQL injection (as pmdba pointed out), you'll need to adjust the above code to read from the right tables and then store and apply the literals, et cetera.

It might help to fully explain what problem you're trying to solve - why not simply apply the dynamic expressions as literals instead of trying to use bind variables?

Thanks for your suggestion Jon :) Actually, this is the legacy code. Our DBA has suggested using bind variables coz with different values for d_sql_query, the final query is also changing to a new sql_id every time and DBA is not able to optimize it.

Collectives™ on Stack Overflow

PL/SQL: Query using bind variables

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related