CODEWITHSUNDEEP
oracle-database
Sameek Kundu
Sameek Kundu

Reputation: 147

Getting 'Invalid Column Index' error in oracle

Getting invalid column error in the following code , in oracle. But when I am executing the query assigned to the cursor separately, it works just fine. Please let me know what is the mistake I am doing :

CREATE OR REPLACE PROCEDURE etl_mergeEmployeeAttendee
(
  v_EmployeeNumber IN VARCHAR2 DEFAULT NULL ,
  v_AttendeeName OUT NVARCHAR2,
  v_EmployeeAttendeeId OUT NUMBER,
  v_AttendeeTitle OUT NVARCHAR2,
  v_Company OUT NVARCHAR2,
  v_Relationship OUT NVARCHAR2,
  v_Operation OUT NUMBER,
  v_Error OUT NUMBER
)
AS
  v_Employee_AttendeeId NUMBER(10,0);
  v_Employee_Id NUMBER(10,0);
  v_Attendee_Name NVARCHAR2(50);
  v_Attendee_Title NVARCHAR2(50);
  v_Com_pany NVARCHAR2(50);
  v_Relation_ship NVARCHAR2(50);

  CURSOR sel_prod_attendee_csr
     IS SELECT a.EmployeeAttendeeId ,
          a.EmployeeId ,
          a.AttendeeName ,
          a.AttendeeTitle ,
          a.Company ,
          a.Relationship 
        FROM EmployeeAttendee a,
             Employee b
        WHERE b.EmployeeNumber = v_EmployeeNumber
          AND a.EmployeeId = b.EmployeeId;

BEGIN
  OPEN sel_prod_attendee_csr;
  FETCH sel_prod_attendee_csr INTO
    v_Employee_AttendeeId, v_Employee_Id,v_Attendee_Name,v_Attendee_Title,v_Com_pany,v_Relation_ship;
  IF sel_prod_attendee_csr%FOUND= true THEN

    /* found */
    BEGIN
      v_Operation := 1 ;
      v_AttendeeTitle := v_Attendee_Title ;
      v_Company := v_Com_pany ;
      v_Relationship := v_Relation_ship ;
      v_AttendeeName :=v_Attendee_Name;
    END;
  ELSE
    v_Operation := 2 ;-- Insert
  END IF;
  CLOSE sel_prod_attendee_csr;
END;

Upvotes: 0

Views: 1990

Answers (1)

David Aldridge
David Aldridge

Reputation: 52336

Best advice you can get -- do not use explicit cursors when you can use an implicit cursor. They are generally faster, need less code, are easier to maintain, and you're less likely to hit an error like this.

Rewrite it to the form:

For sel_prod_attendee_csr in
  select a.EmployeeAttendeeId ,
         ...
  from   ...
loop
  etl_mergeEmployeeAttendee.EmployeeAttendeeId = a.EmployeeAttendeeId;
  ...

As you see, you can dump the ugly "v_" prefix and namespace the variables with the procedure name as well.

If you just want a single row then just use a SQL statement:

  select a.EmployeeAttendeeId ,
         ...
  into   etl_mergeEmployeeAttendee.EmployeeAttendeeId
  from   ...

Given the name of the procedure, I suspect that you'd be much better off dumping the PL/SQL cursor code entirely and just running a straight SQL statement, though.

Upvotes: 1

Related Questions