5 Replies Latest reply on Dec 26, 2012 10:57 AM by ranit B

    invalid identifier

    907442
      Hi experts,

      in below example, i facing invalid identifier error. pls resolve this issue.

      Here is my DDL scripts
       INSERT INTO emp_003 VALUES (1,'malli',20);
       INSERT INTO emp_003 VALUES (2,'ravi',20);
       
       
       create table emp (eid NUMBER,ename VARCHAR2(30),did NUMBER);
        
       INSERT INTO emp VALUES (10,'sangu',30);
       INSERT INTO emp VALUES (11,'raju',40);
      this is my anonymous block :
      DECLARE 
       v_no NUMBER := 100;
       BEGIN
       INSERT INTO emp_003 (deptno,ename,empno)
        SELECT did,ename,v_no  FROM emp
        WHERE ename = 'sangu';
      v_no := v_no +1;
       END;
      ERROR :
      Error starting at line 22 in command:
      INSERT INTO emp_003 (deptno,empno)
        SELECT did,v_no AS empno FROM emp
        WHERE ename = 'sangu'
      Error at Command Line:23 Column:14
      Error report:
      SQL Error: ORA-00904: "V_NO": invalid identifier
      00904. 00000 -  "%s: invalid identifier"
      *Cause:  
      help me on this error. in my reqirement v_no variable values keep on changing..

      Thanks,
      *Action:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
        • 1. Re: invalid identifier
          newbie
          904439 wrote:
          Hi experts,

          in below example, i facing invalid identifier error. pls resolve this issue.

          Here is my DDL scripts
          INSERT INTO emp_003 VALUES (1,'malli',20);
          INSERT INTO emp_003 VALUES (2,'ravi',20);
          
          
          create table emp (eid NUMBER,ename VARCHAR2(30),did NUMBER);
          
          INSERT INTO emp VALUES (10,'sangu',30);
          INSERT INTO emp VALUES (11,'raju',40);
          this is my anonymous block :
          DECLARE 
          v_no NUMBER := 100;
          BEGIN
          INSERT INTO emp_003 (deptno,ename,empno)
          SELECT did,ename,v_no  FROM emp
          WHERE ename = 'sangu';
          v_no := v_no +1;
          END;
          ERROR :
          Error starting at line 22 in command:
          INSERT INTO emp_003 (deptno,empno)
          SELECT did,v_no AS empno FROM emp
          WHERE ename = 'sangu'
          Error at Command Line:23 Column:14
          Error report:
          SQL Error: ORA-00904: "V_NO": invalid identifier
          00904. 00000 -  "%s: invalid identifier"
          *Cause:  
          help me on this error. in my reqirement v_no variable values keep on changing..

          Thanks,
          *Action:
          Please post your actual code here

          Edited by: newbie on Dec 26, 2012 2:31 AM
          • 2. Re: invalid identifier
            981337
            DECLARE
            v_no NUMBER := 102;
            BEGIN
            while v_no <> 120 loop
            INSERT INTO emp (SELECT (select v_no from dual),ename,did FROM emp WHERE ename = 'sangu');
            v_no := v_no +1;
            end loop;
            END;
            /
            • 3. Re: invalid identifier
              Frank Kulash
              Hi,
              904439 wrote:
              Hi experts,

              in below example, i facing invalid identifier error. pls resolve this issue.

              Here is my DDL scripts
              INSERT INTO emp_003 VALUES (1,'malli',20);
              INSERT INTO emp_003 VALUES (2,'ravi',20);
              Don't forget to post the CREATE TABLE statement for emp_003.
              create table emp (eid NUMBER,ename VARCHAR2(30),did NUMBER);
              
              INSERT INTO emp VALUES (10,'sangu',30);
              INSERT INTO emp VALUES (11,'raju',40);
              this is my anonymous block :
              DECLARE 
              v_no NUMBER := 100;
              BEGIN
              INSERT INTO emp_003 (deptno,ename,empno)
              SELECT did,ename,v_no  FROM emp
              WHERE ename = 'sangu';
              v_no := v_no +1;
              END;
              That looks okay.
              The anonyomous block above only contains 8 lines. I don't believe that it is causing the error message:
              ERROR :
              Error starting at line 22 in command:
              INSERT INTO emp_003 (deptno,empno)
              SELECT did,v_no AS empno FROM emp
              WHERE ename = 'sangu'
              Error at Command Line:23 Column:14
              Error report:
              SQL Error: ORA-00904: "V_NO": invalid identifier
              00904. 00000 -  "%s: invalid identifier"
              *Cause:  
              since that error message mentions lines 22 and 23.
              >
              help me on this error. in my reqirement v_no variable values keep on changing..
              Post your complete actual code (which may be a simplified version of what you really need to do) and the error messgae you get from that code.

              Edited by: Frank Kulash on Dec 26, 2012 5:43 AM
              #Nishe# wrote:
              You cannot use v_no in select query as it is variable and not a column.
              Don't be ridic!
              In PL/SQL, you can use a variable in a SQL statement anywhere an expression is allowed.
              • 4. Re: invalid identifier
                Manik
                Try this:
                DECLARE
                   v_no   NUMBER := 100;
                BEGIN
                   INSERT INTO emp_003 (deptno, ename, empno)
                      SELECT did, ename, (SELECT v_no FROM DUAL)
                        FROM emp
                       WHERE ename = 'sangu';
                
                   v_no := v_no + 1;
                END;
                Cheers,
                Manik.
                • 5. Re: invalid identifier
                  ranit B
                  this is my anonymous block :
                  DECLARE 
                  v_no NUMBER := 100;
                  BEGIN
                  INSERT INTO emp_003 (deptno,ename,empno)
                  SELECT did,ename,v_no  FROM emp
                  WHERE ename = 'sangu';
                  v_no := v_no +1;
                  END;
                  Could you please try using table alias??

                  I guess ,using a table alias (here 'x') will avoid the confusion between 'v_no' being a table column or a local variable.
                  No need of selecting it from dual...
                  INSERT INTO emp_003 (deptno,ename,empno)
                     SELECT x.did, x.ename, v_no  FROM emp x
                     WHERE x.ename = 'sangu';