SQL & PL/SQL

Hello,

I am trying to make sense of an error I am seeing on my production environment but not in my test environment. The procedure has an error when it goes to re-create the table it dropped earlier. This table is a temp data table that is populated with another process. What I don't understand is why this doesn't work in production but does test when the procedure is identical in both places. This procedure was also running just fine for months then started having this error on Friday.

I am seeing the ORA-00911: invalid character when the procedure gets to EXECUTE IMMEDIATE LS_CRTABLE;

My test environment is virtually identical and has the same data yet I don't see any issues. If anyone has any ideas I would sure be grateful.

Thanks,
-Jase

Procedure:

PROCEDURE SP_SUM_PKPD(
ai_status out number,
as_errm out varchar2)
IS

cursor C_GET_PK_SPECIES is
select distinct SPECIES
from ADMET.V_IS_PKPD_RSLT
WHERE SPECIES <>'NA'
and SPECIES not in (
select SPECIES from (select count(*), SPECIES
from ADMET.V_IS_PKPD_RSLT
group by SPECIES
having count(*) <20));

cursor C_GET_PK_RSLT_TYPE IS --(ls_species varchar2) IS
select distinct ADMET_RSLT_TYPE_CD
from ADMET.V_IS_PKPD_RSLT
WHERE ADMET_RSLT_TYPE_CD NOT LIKE 'Brain%';
-- where SPECIES=ls_species;
--and tissue_ID=li_tissue;

li_table PLS_INTEGER := 0;
li_loop PLS_INTEGER := 0;
li_exist pls_integer := 0;
-- li_species pls_integer := 0;
LS_ERRM varchar2(512) := '';
LS_PK_DIS varchar2(32767) := '';
LS_PK_LIST varchar2(32767) := '';
LS_PK_FROM varchar2(32767) := '';
LS_PK_SELECT varchar2(32767) := '';
ls_table_name varchar2(125):= '';
LS_ANA_TABLE VARCHAR2(4000) := '';
LS_DROP_TABLE VARCHAR2(512) := '';
LS_CRTABLE VARCHAR2(32767) := '';
LS_GRANT_TABLE VARCHAR2(4000) := '';
LI_STATUS PLS_INTEGER;
LE_PROBLEM EXCEPTION;
BEGIN
-- create table for PK PO
--FOR LR IN C_GET_PK_SPECIES LOOP
--
--DBMS_OUTPUT.put_line('beging '||lr.SPECIES);
FOR LR1 IN C_GET_PK_RSLT_TYPE LOOP --(lr.SPECIES) LOOP
IF LI_LOOP = 0 THEN
LS_PK_LIST := ' WM_CONCAT(DECODE(ADMET_RSLT_TYPE_CD,'''
||LR1.ADMET_RSLT_TYPE_CD||''', '' ''|| RSLT)) AS '||LR1.ADMET_RSLT_TYPE_CD;
-- LS_PK_DIS := LR.SPECIES||'_'||LR1.ADMET_RSLT_TYPE_CD;
LS_PK_DIS := LR1.ADMET_RSLT_TYPE_CD;
ELSE
LS_PK_LIST:= LS_PK_LIST||', '||'WM_CONCAT(DECODE (ADMET_RSLT_TYPE_CD,'''
||LR1.ADMET_RSLT_TYPE_CD||''', '' ''|| RSLT)) AS '||LR1.ADMET_RSLT_TYPE_CD;
LS_PK_DIS := LS_PK_DIS||', '||--|LR.SPECIES||'_'||
LR1.ADMET_RSLT_TYPE_CD;
END IF;
--DBMS_OUTPUT.put_line('LI_LOOP = '||LI_LOOP);
LI_LOOP := LI_LOOP + 1;

END Loop;
LI_LOOP := 0;
/* IF LI_TABLE = 0 THEN
DBMS_OUTPUT.put_line('LS_PK_LIST = '||LS_PK_LIST);
LS_PK_SELECT:= 'SELECT distinct T.ADMET_PROJECT_NAME,T.CMPD_BATCH, T.BATCH_ID, '||
' T.STRAIN,T.SPECIES,T.TISSUE, T.ROA,T.DOSE, '
||LS_PK_DIS;
--DBMS_OUTPUT.put_line('LS_PK_SELECT 0 = '||LS_PK_SELECT);
LS_PK_FROM:='(SELECT ADMET_PROJECT_NAME,CMPD_BATCH,BATCH_ID, STRAIN,SPECIES,TISSUE,ROA,DOSE,'
||LS_PK_LIST
||' from ADMET.V_IS_PKPD_RSLT GROUP by ADMET_PROJECT_NAME,CMPD_BATCH, BATCH_ID,STRAIN,SPECIES,TISSUE,ROA,DOSE) T'||LI_table;

LS_PK_FROM:=' ADMET.V_IS_PKPD_RSLT T '
||' LEFT JOIN '||LS_PK_FROM||' ON T.CMPD_BATCH =T'||LI_table||'.CMPD_BATCH';

--DBMS_OUTPUT.put_line('LS_PK_FROM 0 = '||LS_PK_FROM);
ELSE
LS_PK_SELECT:= LS_PK_SELECT||','||LS_PK_DIS;
-- DBMS_OUTPUT.put_line('LS_PK_SELECT = '||LS_PK_SELECT);
LS_PK_FROM:= LS_PK_FROM
||' LEFT JOIN (SELECT ADMET_PROJECT_NAME, CMPD_BATCH, BATCH_ID,STRAIN,SPECIES,ROA,TISSUE'
||LS_PK_LIST||' from ADMET.V_IS_PKPD_RSLT '
-- || ' where SPECIES = '''||LR.SPECIES
||''' GROUP by ADMET_PROJECT_NAME,CMPD_BATCH, BATCH_ID,STRAIN,SPECIES,ROA,TISSUE) T'||LI_table
||' ON T.CMPD_BATCH =T'||LI_table||'.CMPD_BATCH ';
-- DBMS_OUTPUT.put_line('LS_PK_FROM = '||LS_PK_FROM);
-- DBMS_OUTPUT.put_line('LS_PK_SELECT = '||LS_PK_SELECT);
END IF;
*/ LI_table:=LI_table+1;
-- DBMS_OUTPUT.put_line('LS_PK_SELECT = '||LS_PK_SELECT);
-- dbms_output.put_line('FROM = '||SUBSTR(LS_PK_FROM, -1, 11));
-- END LOOP;
ls_table_name := 'IS_PKPD';
DBMS_OUTPUT.put_line('ls_table_name = '||ls_table_name);
select COUNT(*)
into li_exist
from all_objects
where object_type = 'TABLE'
and owner='ADMET'
and object_name = ls_table_name;
--dbms_output.put_line('li_exist = '||li_exist);
dbms_output.put_line('ls_table_name = '||ls_table_name);
if li_exist > 0
then
LS_DROP_TABLE := 'DROP TABLE ADMET.'||ls_table_name;
EXECUTE IMMEDIATE LS_DROP_TABLE;
--dbms_output.put_line('PK table droped '||LS_DROP_TABLE);
end if;

--DBMS_OUTPUT.put_line('LS_PK_SELECT = '||LS_PK_SELECT);
--DBMS_OUTPUT.put_line('LS_PK_from = '||LS_PK_from);
DBMS_OUTPUT.put_line('CRETE TABLE = '||ls_table_name);
LS_CRTABLE:= 'CREATE TABLE ADMET.'||ls_table_name
||' AS '||LS_PK_SELECT||' FROM '||LS_PK_FROM;

LS_CRTABLE := 'CREATE TABLE ADMET.'||ls_table_name||
' AS SELECT ADMET_PROJECT_NAME,ADMET_STUDY_CD, CMPD_BATCH, BATCH_ID,'
||' STRAIN,SPECIES,TISSUE,ROA,DOSE, VEHICLE,PK_FREQUENCY,END_POINTS,N,RELATED_CMPD,PK_FOR_CMPD,'
||LS_PK_LIST||' from ADMET.V_IS_PKPD_RSLT '
||' GROUP by ADMET_PROJECT_NAME,ADMET_STUDY_CD,CMPD_BATCH, BATCH_ID,'
||' STRAIN,SPECIES,TISSUE,ROA,DOSE,VEHICLE, PK_FREQUENCY,END_POINTS,N,RELATED_CMPD,PK_FOR_CMPD';

--DBMS_OUTPUT.put_line('LS_CRTABLE = '||LS_CRTABLE);
EXECUTE IMMEDIATE LS_CRTABLE;
-- DBMS_OUTPUT.put_line('ANALIZE = '||ls_table_name);
LS_GRANT_TABLE := 'GRANT SELECT ON ADMET.'||ls_table_name
||' TO BIOASSAY WITH GRANT OPTION';
EXECUTE IMMEDIATE LS_GRANT_TABLE;
LS_GRANT_TABLE := 'GRANT SELECT ON ADMET.'||ls_table_name
||' TO PIPELINE WITH GRANT OPTION';
EXECUTE IMMEDIATE LS_GRANT_TABLE;
LS_GRANT_TABLE := 'GRANT SELECT ON ADMET.'||ls_table_name
||' TO ADMETWEB WITH GRANT OPTION';
EXECUTE IMMEDIATE LS_GRANT_TABLE;
LS_ANA_TABLE := 'ANALYZE TABLE ADMET.'||ls_table_name
|| ' ESTIMATE STATISTICS SAMPLE 25 PERCENT';
EXECUTE IMMEDIATE LS_ANA_TABLE;
--dbms_output.put_line('LI_table = '||LI_table);
-- dbms_output.put_line('LI_LOOP = '||LI_LOOP);

ai_status := 1;
as_errm := ' PKPD table is created.';
EXCEPTION
when others then
DBMS_OUTPUT.put_line('Problem '|| SQLERRM );
DBMS_OUTPUT.put_line('Problem '|| SQLCODE );
ai_status := SQLCODE;
as_errm := 'Error is '|| SQLERRM ;
-- rollback;
END SP_SUM_PKPD;