CODEWITHSUNDEEP
oracle-databaseplsqlcursor
ajcarracedo
ajcarracedo

Reputation: 265

PL/SQL: PLS-00382: expression is of wrong type. Statement ignored

I have a 'person' table with two fields 'person_name' and 'person_age'.

i want write a procedure that return sys_refcursor but calculating a extra field 'is_old'. por example:

PROCEDURE people_load(p_name IN VARCHAR2, P_RESULT OUT SYS_REFCURSOR) IS
    BEGIN
      DECLARE
      isOld BOOLEAN := false;
      CURSOR cursorTemp IS SELECT person_name, person_age, is_old
                           WHERE person_name = p_name;
      BEGIN
          FOR _p IN cursorTemp
          LOOP
            IF _p.person_age > 75 THEN         
              _p.is_old:=TRUE;
            END IF;  
          END LOOP;          
            ¿¿P_RESULT:=cursorTemp; //open P_RESULT for (open cursorTemp);??
        END;
     END people_load;

i dont know how to assign temporal cursor 'cursorTemp' to OUT param 'P_RESULT' to returning the result.

Upvotes: 0

Views: 1633

Answers (2)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112352

You should do the calculation in the select.

 CURSOR cursorTemp IS
    SELECT
      person_name,
      person_age,
      CASE WHEN person_age>75 THEN 1 ELSE 0 END AS is_old
    WHERE person_name = p_name;

Upvotes: 0

Martin Schapendonk
Martin Schapendonk

Reputation: 13496

  1. You can not use a BOOLEAN in SQL, only in PL/SQL.
  2. You can not loop through a cursor and recalculate a column.
  3. You declare a variable isOld and never use it.

I suggest you calculate the is_old within the cursor. I changed it to contain 1 (true) or 0 (false).

  PROCEDURE people_load(p_name IN VARCHAR2, P_RESULT OUT SYS_REFCURSOR) IS
    BEGIN
      DECLARE
      CURSOR cursorTemp IS SELECT person_name, person_age, case when person_age > 75 then 1 else 0 end is_old
                           WHERE person_name = p_name;
      BEGIN
            P_RESULT := cursorTemp; //open P_RESULT for (open cursorTemp);
        END;
     END people_load;

Upvotes: 2

Related Questions