PLS-00103:遇到符号"，“

Question

此过程出现以下错误。

CREATE OR REPLACE PROCEDURE SAMPLE
IS
BEGIN
EXECUTE IMMEDIATE
    'CREATE TABLE COLUMN_NAMES AS (
     SELECT LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_NAME) AS STUDENTS
     FROM   
         (SELECT DISTINCT COLUMN_NAME
          FROM BW_COLUMN_ROW_CELL_JOIN)
     )';
END;
/

提供：

PLS-00103: Encountered the symbol "," when expecting one of the following:     
* & = - + ; < / > at in is mod remainder not rem return    
returning <an exponent (**)> <> or != or ~= >= <= <> and or   
like like2 like4 likec between into using || multiset bulk    member submultiset 

有人能说出这里面出了什么问题吗？

谢谢。

Answer 1

另一种方法(在Oracle10g和更高版本中)是使用替代的字符串文字表示法-这意味着您不需要担心正确转义字符串中的所有单引号，例如q'{my string's got embedded quotes}'

CREATE OR REPLACE PROCEDURE SAMPLE
IS
BEGIN
EXECUTE IMMEDIATE q'[
     CREATE TABLE COLUMN_NAMES AS (
     SELECT LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_NAME) AS STUDENTS
     FROM   
         (SELECT DISTINCT COLUMN_NAME
          FROM BW_COLUMN_ROW_CELL_JOIN)
     )]';
END;
/

Answer 2

我认为问题是你在单引号中有单引号。我现在不能测试这一点，但我建议您尝试以下方法(请注意，内部引号是双引号''，它对它们进行转义：

CREATE OR REPLACE PROCEDURE SAMPLE 
IS
BEGIN 
    EXECUTE IMMEDIATE 'CREATE TABLE COLUMN_NAMES AS ( SELECT LISTAGG(COLUMN_NAME, '','') WITHIN GROUP (ORDER BY COLUMN_NAME) AS STUDENTS FROM (SELECT DISTINCT COLUMN_NAME FROM BW_COLUMN_ROW_CELL_JOIN) )'; 
END; 
/

我还会先尝试代码的create table部分，以确保在将其包装到proc之前是有效的。

Answer 3

您不能在Execute Immediate的select statement中直接使用single quotes，需要使用CHR(39)进行编码

CREATE OR REPLACE PROCEDURE SAMPLE
IS
BEGIN
   EXECUTE IMMEDIATE
     'CREATE TABLE COLUMN_NAMES AS (
             SELECT LISTAGG(COLUMN_NAME,'||chr(39)||','||chr(39)||') WITHIN GROUP (ORDER BY COLUMN_NAME) AS STUDENTS
             FROM   
            (SELECT DISTINCT COLUMN_NAME FROM BW_COLUMN_ROW_CELL_JOIN))';
END;