CODEWITHSUNDEEP
oracle-databaseplsqlsys-refcursor
user3134422
user3134422

Reputation: 57

error in dynamic sql with pivot clause

I'm having trouble figuring out the problem with this plsql block:

1  declare
2  l sys_refcursor;
3  v varchar2(2000);
4  begin
5  v :=  q'[
6  select * from (
7  select  etudiant2.nom,  etudiant2.prenom,  forme ||''||session_e formesession, note
8  from etudiant2
9  )
10  pivot
11  (sum(null) for formesession in ('TP01', 'TP02', 'TP03', 'TD01', 'TD02'))]';
12  execute immediate v into l;
13* end;
SQL> /
declare
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 12

what is the problem here, I tried a lot of things... maybe 'PIVOT' just does not work with dynamic sql?

Upvotes: 0

Views: 199

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50047

I don't think the PIVOT clause has anything to do with the error you're getting. To open a SYS_REFCURSOR using a string containing a SELECT statement I think you'll want to use

OPEN L FOR V;

instead of

execute immediate v into l;

Share and enjoy.

Upvotes: 1

Related Questions