Oracle PL/SQL stored procedure with dynamic table name-CodePudding

I tried to make the table name here dynamic and it didn't insert any records to the table. Can someone help me take a look what is wrong? Thank you! '''

Create or replace procedure input_records AS

CurTerm varchar2(4) := '1122';
CAR_TERM_HISTORY varchar2(30) := 'CAR_TERM_HISTORY_' || CurTerm;
stmt_str varchar2(1000) := 'insert into' || CAR_TERM_HISTORY
|| '(select sysdate as date_created,
date_loaded,
strm,
withdraw_code,
withdraw_reason,
withdraw_date,
unt_taken_fa,
unt_passd_fa,
tot_taken_fa,
tot_passd_fa,
tot_taken_fa_gpa,
tot_grd_points_fa,
billing_career,
cur_gpa,
cum_gpa,
registered,
acad_level_proj,
acad_level_bot,
acad_level_eot '||
'from IMS.EADTERM
where strm = CurTerm
)';


BEGIN
execute immediate stmt_str ;

END input_records;

'''

CodePudding user response：

where strm = CurTerm

I assume your CurTerm is supposed to be some sort of variable? Do you mean:

where strm = ' || CurTerm || '...

When building SQL like this, make sure that it's not possible to do a SQL injection.

CodePudding user response：

You need to change your where condition to -

'from IMS.EADTERM
where strm = ' || CurTerm || '
)';

CodePudding user response：

Thanks for everyone's comments. I missed a space after the insert into as @ MT0 pointed out.