Jump to content
itzkashi

Error : invalid identifier

Recommended Posts

Hi,

please find the sample data below ; 

Requirement is to build the insert statement dynamically through the look up table(test_lkp) so that if any change in the columns can be handled through the look up table rather than modifying the script. To achieve this i have created the look up table TEST_LKP with the same structure as TEST and inserting values as V_COL.  i am getting an error for variable "v_col" Is there any way to achieve this

DROP TABLE TEST ; 
DROP TABLE TEST_LKP;
DROP TABLE TEST_REF;
DROP TABLE TEST_Q;
 CREATE TABLE TEST (COL VARCHAR2(10));
 CREATE TABLE TEST_LKP (COL VARCHAR2(10));
 CREATE TABLE TEST_REF (VAL VARCHAR2(10));
 CREATE TABLE TEST_Q (ENAME VARCHAR2(10));

 INSERT INTO TEST_LKP VALUES ('v_col'); 
 INSERT INTO TEST_REF VALUES ('ENAME');
 INSERT INTO TEST_Q VALUES ('TOM'); 

 

 

code :  

SET SERVEROUTPUT ON;

declare

   v_col  VARCHAR2(30);
   q VARCHAR2(1000);

  CURSOR c1 IS
SELECT  'INSERT  INTO TEST ' || 'SELECT ' || COL || ' FROM TEST_Q '  DMLS  FROM TEST_LKP;
BEGIN

  SELECT val INTO v_col FROM TEST_REF; 
    dbms_output.put_line(v_col);

  FOR i IN c1
   loop
  dbms_output.put_line(i.DMLS);
  execute immediate i.dmls;
  end loop;
END;
/



erorr :

Error report -
ORA-00904: "V_COL": invalid identifier
ORA-06512: at line 17
00904. 00000 -  "%s: invalid identifier"
*Cause:  
*Action:
ENAME
INSERT  INTO TEST SELECT v_col FROM TEST_Q


Expected :

i want v_col to be replaced with ENAME as

INSERT  INTO TEST SELECT ENAME FROM TEST_Q

what wrong i am doing here?

Share this post


Link to post
Share on other sites

Hello Itzkashi,

Rule #1: When its from cursor, you cannot bind variables to it.

The below code is the simple form of your's and works fine.

DECLARE
    v_col   VARCHAR2(100);
BEGIN
    SELECT
        val
    INTO
        v_col
    FROM
        test_ref;

    EXECUTE IMMEDIATE 'INSERT  INTO TEST SELECT '
    || v_col
    || ' FROM TEST_Q';
END;
/

 

Thank you,

Boobal Ganesan. Author of Advanced PLSQL Programming: The Definitive Reference

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×