execute Insert into query in a variable in procedure

Question

I have a select statement in a variable. I need to insert all the rows output of this select statement into another table. I am trying to achieve this in a procedure. Please help me. My code looks like:

CREATE OR REPLACE
PROCEDURE PRC_DUP_CHK(
V_IN_TABLE_NAME IN VARCHAR2,
V_SOURCE_FILE   IN VARCHAR2,
v_col           IN VARCHAR2,
)
AS

SQL_STMT        VARCHAR2(20000);
SQL_STMT3       VARCHAR2(20000);
V_KEY_COL_LIST  VARCHAR2(500);
V_KEY_COL_LIST1 VARCHAR2(500);
BEGIN

SELECT UDC_KEY_COLUMNS INTO V_KEY_COL_LIST FROM UTI_DUP_CHK WHERE 
UDC_TABLE_NAME=''||V_IN_TABLE_NAME||'';
SELECT REPLACE(V_KEY_COL_LIST,',','||''~''||') INTO V_KEY_COL_LIST1 FROM 
DUAL;

SQL_STMT :='SELECT REPLACE(UDC_KEY_COLUMNS,'','',''~'')  
FROM UTI_DUP_CHK WHERE UDC_TABLE_NAME='''||V_IN_TABLE_NAME||'''';

SQL_STMT3:='SELECT ('||SQL_STMT||') KEY_COLUMNS,
'||V_KEY_COL_LIST1||' KEY_VALUES, 
'''||V_IN_TABLE_NAME||''''||' ODS_TABLE, 
'''||V_SOURCE_FILE||''''||' SOURCE_FILE, 
TO_CHAR(SYSDATE,''DD-Mon-YYYY HH:MI:SS AM'') LOAD_TIME
FROM (
SELECT DISTINCT '||V_KEY_COL_LIST||',COUNT(*) CNT
FROM '||V_IN_TABLE_NAME||''|| ' WHERE '||V_COL||'>SYSDATE
GROUP BY '||V_KEY_COL_LIST||')A
WHERE A.CNT=1;';

I need to insert the output of this select query into a table ERR_DUP_CHK .I am trying to execute this like

execute immediate 'INSERT INTO UTI_ERR_DUP_CHK SQL_STMT3';

But it is not executing.Please help me.

What error message do you get? Or does it just hang? Or returns an incorrect output? — z32a7ul
– z32a7ul, Commented Apr 12, 2017 at 9:00

Alex Poole · Accepted Answer · 2017-04-12 09:40:28Z

1

The immediate problem with what you're doing is that you would need to concatenate the select statement you've built up, not include its variable name as part of another literal:

execute immediate 'INSERT INTO UTI_ERR_DUP_CHK ' || SQL_STMT3;

You also need to remove the semicolon inside the dynamic statement - that's what's causing the ORA-00911 error:

...
WHERE A.CNT=1';    -- no semicolon inside the string

You could simplify the whole procedure quite a bit though, e.g. to:

CREATE OR REPLACE PROCEDURE PRC_DUP_CHK (
  V_TABLE_NAME   IN VARCHAR2,
  V_SOURCE_FILE  IN VARCHAR2,
  v_COL          IN VARCHAR2
)
AS
  L_SQL_STMT     VARCHAR2(32767);
BEGIN
  SELECT 'SELECT ''' || REPLACE(UDC_KEY_COLUMNS, ',', '~') || ''',
      ' || REPLACE(UDC_KEY_COLUMNS, ',', '||''~''||') || ',
      :V_TABLE_NAME, 
      :SOURCE_FILE, 
      TO_CHAR(SYSDATE, ''DD-Mon-YYYY HH:MI:SS AM'') LOAD_TIME
    FROM ' || V_TABLE_NAME || ' WHERE ' || V_COL || ' > SYSDATE
    GROUP BY ' || UDC_KEY_COLUMNS || '
    HAVING COUNT(*) = 1'
  INTO L_SQL_STMT
  FROM UTI_DUP_CHK
  WHERE UDC_TABLE_NAME = V_TABLE_NAME;

  execute immediate 'INSERT INTO UTI_ERR_DUP_CHK ' || L_SQL_STMT
  using V_TABLE_NAME, V_SOURCE_FILE;
END PRC_DUP_CHK;
/

You should probably include the target table column names in the insert though. And you might also want to reconsider storing a date as a string in that target table.

edited Apr 12, 2017 at 9:40

answered Apr 12, 2017 at 9:02

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

1 Comment

Alex Poole Over a year ago

@PavaniSrujana - you have a semicolon at the end of your dynamic query, inside the string you're forming. That should not be there, and is causing that ORA-00911 error.

Collectives™ on Stack Overflow

execute Insert into query in a variable in procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related