I am receiving the following error when I execute a function whose purpose is to create dynamic sql ==> ORA-00932: inconsistent datatypes: expected %s got %s. Ultimately, I need to create an Apex report which provides credit totals by course and degree program (both of which are variable, and are based on a term code prompt). I have tried replacing the variable :var with a hard-coded value, and have successfully executed the sql code (in l_qry) using SQL Developer... so I'm thinking the problem is probably with my bind variable. Thanks in advance for any assistance you might provide.
create or replace function f_credsxpgm_term (
iterm IN VARCHAR2)
RETURN varchar2 IS
l_qry varchar2(32767);
l_out_qry varchar2(32767);
BEGIN
l_qry := 'select ssbsect_subj_code||ssbsect_crse_numb, ';
FOR r1 IN (select distinct x.sgbstdn_degc_code_1 deg,
decode(x.sgbstdn_degc_code_1,'000000','000',
substr(x.sgbstdn_majr_code_1,1,1)||substr(x.sgbstdn_majr_code_1,3,2)) maj,
decode(x.sgbstdn_degc_code_1,'000000','NOT DECLARED', nvl(stvuniv_abbrev_text,'NONE')) descpt
from sfrstcr, stvuniv, sgbstdn x
where x.sgbstdn_majr_code_1 = stvuniv_code_value
and x.sgbstdn_pidm = sfrstcr_pidm
and sfrstcr_rsts_code in ('RE','NG','WC')
and sfrstcr_levl_code not in ('09')
and sfrstcr_term_code = f_credsxpgm_term.iterm
and x.sgbstdn_term_code_eff =
(select max(sgbstdn_term_code_eff) from sgbstdn
where x.sgbstdn_pidm = sgbstdn_pidm
and sgbstdn_term_code_eff <= f_credsxpgm_term.iterm))
LOOP
l_qry := l_qry|| 'sum(decode(x.sgbstdn_degc_code_1||substr(x.sgbstdn_majr_code_1,1,1)||substr(x.sgbstdn_majr_code_1,3,2),''' ||
r1.deg||r1.maj || ''',nvl(ssbsect_credit_hrs,0),0)) ' ||'"'|| r1.descpt ||'"'|| ',';
END LOOP;
l_qry := rtrim(l_qry,',');
l_qry := l_qry || ' from stvmajr, sgbstdn x, ssbsect, sfrstcr ' ||
' where sfrstcr_term_code = :var'||
' and sfrstcr_rsts_code in (''RE'',''NG'',''WC'')' ||
' and sfrstcr_levl_code not in (''09'')' ||
' and sfrstcr_crn = ssbsect_crn' ||
' and sfrstcr_term_code = ssbsect_term_code' ||
' and sfrstcr_pidm = x.sgbstdn_pidm' ||
' and x.sgbstdn_term_code_eff = ' ||
' (select max(sgbstdn_term_code_eff) from sgbstdn' ||
' where x.sgbstdn_pidm = sgbstdn_pidm' ||
' and sgbstdn_term_code_eff <= sfrstcr_term_code)' ||
' and x.sgbstdn_majr_code_1 = stvmajr_code' ||
' and x.sgbstdn_camp_code not in (''T'',''Y'')' ||
' group by ssbsect_subj_code||ssbsect_crse_numb, '||
' substr(x.sgbstdn_majr_code_1,1,1)||substr(x.sgbstdn_majr_code_1,3,2)' ;
dbms_output.put_line(l_qry);
execute immediate l_qry into l_out_qry using iterm;
dbms_output.put_line(l_out_qry);
return l_out_qry;
end;
/