SQL & PL/SQL

Hi All,

i'm having a little trouble with a pivot query. Oracle (10g R2) is raising:

ORA-00604: error occurred at recursive SQL level1
ORA-01003: no statement parsed

My problem is clear enough. When i do a pivot query to display a list of records where the column headers are countries and the row headers are months the system balks when it returns a country with a single quote in it. e.g.

COUNTRY_NAME='KOREA, DEMOCRATIC PEOPLE'S REPUBLIC OF'

I know i need to catch the single quote literal and handle it. I'm just having no success in managing it and i'm a little out of ideas. Can anyone point me to a nice solution?

The full procedure is listed below: The


PROCEDURE getCountryInvPlan (
iINV_PLAN_DEPOT_ID IN NUMBER,
iITEM IN VARCHAR2,
rc IN OUT pkg_DMT_INV_PLAN.t_rc,
oErrorMsg OUT VARCHAR2)
IS
v_sql VARCHAR2(32767);
BEGIN


v_sql := 'select MONTH_BUCKET ';
FOR rec IN
(select distinct(COUNTRY_NAME) from DMT_INV_PLAN_DEPOT_COUNTRY where INV_PLAN_DEPOT_ID= iINV_PLAN_DEPOT_ID )
LOOP
v_sql := v_sql
|| ',MAX(DECODE(COUNTRY_NAME,'''
|| rec.COUNTRY_NAME
|| ''',PATIENT_DEMAND)) "'
|| rec.COUNTRY_NAME
|| '"';
END LOOP;
v_sql := v_sql
|| ', DECODE(MONTH_CLOSED,0,''OPEN'',1,''CLOSED'') AS MONTH_STATUS '
|| ' FROM (SELECT DMT_INV_PLAN_DEPOT_COUNTRY.MONTH_BUCKET, DMT_INV_PLAN_DEPOT_COUNTRY.COUNTRY_NAME, DMT_INV_PLAN_DEPOT_COUNTRY.PATIENT_DEMAND, DMT_INV_PLAN_ITEM.MONTH_CLOSED FROM DMT_INV_PLAN_DEPOT_COUNTRY, DMT_INV_PLAN_ITEM '
|| ' where DMT_INV_PLAN_DEPOT_COUNTRY.INV_PLAN_DEPOT_ID = DMT_INV_PLAN_ITEM.INV_PLAN_DEPOT_ID '
|| ' AND DMT_INV_PLAN_DEPOT_COUNTRY.ITEM_DESCRIPTION = DMT_INV_PLAN_ITEM.ITEM_DESCRIPTION '
|| ' AND DMT_INV_PLAN_DEPOT_COUNTRY.MONTH_BUCKET = DMT_INV_PLAN_ITEM.MONTH_BUCKET '
|| ' AND DMT_INV_PLAN_DEPOT_COUNTRY.INV_PLAN_DEPOT_ID= ' || iINV_PLAN_DEPOT_ID
|| ' AND DMT_INV_PLAN_DEPOT_COUNTRY.ITEM_DESCRIPTION='''|| iITEM ||''') '
|| ' GROUP by MONTH_BUCKET, MONTH_CLOSED order by MONTH_BUCKET';
OPEN rc FOR v_sql;

oErrorMsg:= v_sql;

EXCEPTION
WHEN NO_DATA_FOUND
THEN
oErrorMsg := 'NO DATA FOUND';

WHEN OTHERS
THEN
oErrorMsg := 'ERROR';
oErrorMsg := v_sql;
END getCountryInvPlan;