CODEWITHSUNDEEP
plsqloracle12c
Abhinav Dhiman
Abhinav Dhiman

Reputation: 755

How to include a variable in a execute immediate statement?

I am trying to select a variable in execute immediate statement, however, I am not sure about the syntax.

Code I'm trying to run is something like this:

DECLARE
PREV_PAR VARCHAR2(1);
VC_NUM VARCHAR2(2);
BEGIN
SELECT 'P' INTO PREV_PAR FROM DUAL;
SELECT '01' INTO VC_NUM FROM DUAL;
EXECUTE IMMEDIATE 'CREATE TABLE TEMP_TABLE AS (SELECT '||PREV_PAR||' as prev_par,'||VC_NUM||'  as vc_code from dual)';
END;

What am I doing incorrectly?

Upvotes: 1

Views: 303

Answers (1)

Mahamoutou
Mahamoutou

Reputation: 1555

You need to add two more quotes sign to surround each of your varchar variables like below.

DECLARE
PREV_PAR VARCHAR2(1);
VC_NUM VARCHAR2(2);
BEGIN
SELECT 'P' INTO PREV_PAR FROM DUAL;
SELECT '01' INTO VC_NUM FROM DUAL;
EXECUTE IMMEDIATE 'CREATE TABLE TEMP_TABLE AS (SELECT '''||PREV_PAR||''' as prev_par,'''||VC_NUM||'''  as vc_code from dual)';
END;
/

Because the final statement at runtime must be like this :

CREATE TABLE TEMP_TABLE AS (SELECT 'P' as prev_par, '01'  as vc_code from dual)

Upvotes: 1

Related Questions