dynamic sql in oracle passing data para

Question

I need to convert this in dynamic sql in oracle passing data para

Select * from Student
where effective_date = '30-APR-2022';

set serveroutput on;
declare
date_ varchar2(100) := '30-APR-2022';
sql_stmt varchar2 (10000);
begin
sql_stmt := 'select * from student
where effective_date = ''' ||to_char (date_, 'DD-MON-YYYY');
dbms_output.put_line(sql_stmt);
end;

score 0 · Answer 1 · answered Jun 14 '22 at 23:10

You can use:

set serveroutput on;
declare
  date_    varchar2(100) := '30-APR-2022';
  sql_stmt varchar2 (10000);
begin
  sql_stmt := 'select * from student where  effective_date = TO_DATE(''' || date_ || ''', ''DD-MON-YYYY'')';
  dbms_output.put_line(sql_stmt);
end;
/

or, if you are going to use EXECUTE IMMEDIATE with the dynamic SQL then you can use bind variables (which will help prevent SQL injection attacks):

set serveroutput on;
declare
  date_    varchar2(100) := '30-APR-2022';
  sql_stmt varchar2 (10000);
  v_col1   student.col1%TYPE;
  v_col2   student.col2%TYPE;
begin
  sql_stmt := 'select col1, col2 from student where effective_date = TO_DATE(:dt, ''DD-MON-YYYY'')';
  dbms_output.put_line(sql_stmt);
  EXECUTE IMMEDIATE sql_stmt INTO v_col1, v_col2 USING date_;
  DBMS_OUTPUT.PUT_LINE(v_col1 || ', ' || v_col2);
end;
/

(Assuming that there will only be a single row returned.)

db<>fiddle here

dynamic sql in oracle passing data para

1 Answers1