Invalid SQL statement error with refcursor

Question

I am trying to get the results of the underlying statements. The SQL statements works without any problems. However, in order to print the results I would like to use refcursor. I get the following errors:

ORA-00900: invalid SQL statement ORA-01008: not all variables bound ORA-00900: invalid SQL statement

VARIABLE reader refcursor;
DECLARE
  line varchar2(32767);
BEGIN
  line := 'SELECT role_id,';
  FOR n IN (SELECT name
              FROM (SELECT competence.skill_role.role_id,
                           competence.skill_label.name,
                           competence.skill_role.target_value
                      FROM competence.skill_role,
                           competence.skill_label
                     WHERE competence.skill_label.skill_id =
                           competence.skill_role.skill_id
                       AND competence.skill_label.language_id = 1)
                       matrix_result ) LOOP
    line := line || '(decode(name,''' || n.name ||
            ''',target_value)) "' || n.name || '",';
  END LOOP;
  line := RTRIM(line, ',') ||
    ' FROM (SELECT competence.skill_role.role_id,
                   competence.skill_label.name,
                   competence.skill_role.target_value
              FROM competence.skill_role, competence.skill_label
             WHERE competence.skill_label.skill_id =
                   competence.skill_role.skill_id
               AND competence.skill_label.language_id = 1) matrix_result';
--dbms_output.put_line(line);
--execute immediate line;
 OPEN :reader FOR line;
END;
/
PRINT :reader;

Table data

CREATE TABLE competence.skill_role
  (skill_id  NUMBER,
   role_id NUMBER,
   target_value NUMBER)
/
INSERT ALL 
INTO competence.skill_role VALUES (3432030, 1421866, 2)
INTO competence.skill_role VALUES (3434962, 1421866, 2)
INTO competence.skill_role VALUES (3488025, 3488804, 4)


SELECT * FROM competence.skill_role

SKILL_ID ROLE_ID     target_value
---------- -------   -----------
3432030 1421866       2
3434962 1421866       2
3488025 3488804       4


CREATE TABLE competence.skill_label
  (skill_id  NUMBER,
   name vchar2 (30))
/
INSERT ALL 
INTO competence.skill_label VALUES (3432030, 'Alueen projektipätevyys')
INTO competence.skill_label VALUES (3434962, 'Moottorin koekäyttö')
INTO competence.skill_label VALUES (3488025, 'Etähallintajärjestelmät')


SELECT * FROM arc_competence.skill_label

SKILL_ID NAME
---------- -------
3432030, Alueen projektipätevyys
3434962, Moottorin koekäyttö
3488025, Etähallintajärjestelmät

I would like to have the following result from the first query. From your answer (if I understood correctly), it seems that I need to run the resultant query manually to get the answer. I'd like to have the result without running the resultant query :-) I don't have the access to the client machine at the moment but I am going there now.

ROLE_ID     Alueen projektipätevyys  Moottorin koekäyttö    Etähallintajärjestelmät          
1421866          2                      2
3488804                                                            4       

Answer 1

If I correct your code so that it compiles

• Your INSERT ALL statements are missing a SELECT
• The name column in skill_label is defined as a vchar2(30) rather than a varchar2(30)
• Your anonymous block references a column language_id that your DDL does not include

the code runs without error. If the only problem is that you want to combine the first two rows into a single row, you just need to add a MAX to all the columns other than ROLE_ID and to add a GROUP BY role_id to your query.

SQL> ed
Wrote file afiedt.buf

  1  CREATE TABLE skill_role
  2    (skill_id  NUMBER,
  3     role_id NUMBER,
  4*    target_value NUMBER)
SQL> /

Table created.

SQL> ed
Wrote file afiedt.buf

  1  INSERT ALL
  2  INTO skill_role VALUES (3432030, 1421866, 2)
  3  INTO skill_role VALUES (3434962, 1421866, 2)
  4  INTO skill_role VALUES (3488025, 3488804, 4)
  5* select * from dual
SQL> /

3 rows created.


SQL> ed
Wrote file afiedt.buf

  1  CREATE TABLE skill_label
  2    (skill_id  NUMBER,
  3*    name varchar2 (30))
SQL> /

Table created.

SQL> ed
Wrote file afiedt.buf

  1  INSERT ALL
  2  INTO skill_label VALUES (3432030, 'Alueen projektipΣtevyys')
  3  INTO skill_label VALUES (3434962, 'Moottorin koekΣytt÷')
  4  INTO skill_label VALUES (3488025, 'EtΣhallintajΣrjestelmΣt')
  5* select * from dual
SQL> /

3 rows created.

SQL> commit;

Commit complete.

SQL> variable reader refcursor;

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2    line varchar2(32767);
  3  BEGIN
  4    line := 'SELECT role_id,';
  5    FOR n IN (SELECT name
  6                FROM (SELECT skill_role.role_id,
  7                             skill_label.name,
  8                             skill_role.target_value
  9                        FROM skill_role,
 10                             skill_label
 11                       WHERE skill_label.skill_id =
 12                             skill_role.skill_id
 13                         )
 14                         matrix_result ) LOOP
 15      line := line || 'max(decode(name,''' || n.name ||
 16              ''',target_value)) "' || n.name || '",';
 17    END LOOP;
 18    line := RTRIM(line, ',') ||
 19      ' FROM (SELECT skill_role.role_id,
 20                     skill_label.name,
 21                     skill_role.target_value
 22                FROM skill_role, skill_label
 23               WHERE skill_label.skill_id =
 24                     skill_role.skill_id
 25                 ) matrix_result ' ||
 26       ' GROUP BY role_id' ;
 27    dbms_output.put_line(line);
 28    --execute immediate line;
 29    OPEN :reader FOR line;
 30* END;
 31  /

PL/SQL procedure successfully completed.

SQL> print reader

   ROLE_ID Alueen projektipΣtevyys Moottorin koekΣytt÷ EtΣhallintajΣrjestelmΣt
---------- ----------------------- ------------------- -----------------------
   1421866                       2                   2
   3488804                                                                   4