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?
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)
