CODEWITHSUNDEEP
oracle-sqldeveloper
Pavani Srujana
Pavani Srujana

Reputation: 81

execute Insert into query in a variable in procedure

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.

Upvotes: 0

Views: 248

Answers (1)

Alex Poole
Alex Poole

Reputation: 191425

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.

Upvotes: 1

Related Questions