SQL & PL/SQL

Hello,

I'm building an application that creates a stored procedure based on a load of custom metadata. A new procedure should be created when metadata is added or changed. I generate a ddl statement for this based on my metadata and then create the procedure by calling EXECUTE IMMEDIATE v_string.

Somthing strange is going on however. When I call the procedure in question PA_RT_MAP.pr_generate_mapping_proc this doesn't seem to work.
I get following error on the stored proc:
(S41) Expecting: , . ; @ AS CONNECT CROSS FULL GROUP HAVING identifier INNER INTERSECT JOIN LEFT MINUS NATURAL ORDER PARTITION RETURN RETURNING RIGHT SAMPLE START SUBPARTITION UNION VERSIONS WHERE

The procedure is correctly generated but has invalid as state and above error message. Nothing is wrong with the ddl statement however (an example is included in my posting so you can see the syntax is perfectly ok). And when I recompile the procedure without changing any code the error dispappears and the generated stored proc becomes valid.

I'm working with TOAD so I thought this might be a bug, but in sqlplus I have the same problem. When I put the generated ddl statement in a string and I write an anonymous pl/sql block that executes the string with EXECUTE IMMEDIATE in sqlplus it works but of cours that's not a solution for me.

I have no idea what's going on here but it's really annoying thing, any help is appreciated.

here's an exact copy of the ddl I generate so this gets executed in a string with the EXECUTE IMMEDIATE command in a pl/sql package:


CREATE OR REPLACE PROCEDURE Rt_Cd_Stg_Cd (pin_sequence IN RT_MESSAGE_HISTORY.SEQUENCE%TYPE)
IS
/**********************************************************
/* PROCEDURE NAME: RT_CD_STG_CD
/* GENERATED: 10-JAN-2008 14:46:19
/* ++ MAPPING BETWEEN: ++
/* CD version: 1
/* and STG_CD
/**********************************************************/
BEGIN
INSERT INTO STG_CD
( title
, artist
, website
, Description
)
SELECT
Title
, Artist
, Website
, UPPER(description) Description
FROM RT_MESSAGE_HISTORY Q,
XMLTABLE ( '/CD' PASSING Q.MESSAGE_BODY
COLUMNS
Title VARCHAR2(50) PATH '/CD/Title'
, Artist VARCHAR2(50) PATH '/CD/Artist'
, Website VARCHAR2(50) PATH '/CD/Website'
, Description VARCHAR2(50) PATH '/CD/Description'
) xml
WHERE Q.SEQUENCE = pin_sequence ;

END;