CODEWITHSUNDEEP
plsqloracle11g
user1990383
user1990383

Reputation: 125

PLS-00103: Encountered the symbol ","

This procedure is getting following error.

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;
/

gives:

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

Can any one say what is wrong in this?

Thanks.

Upvotes: 1

Views: 16803

Answers (3)

Aspirant
Aspirant

Reputation: 2278

You can't use single quotes directly in select statement of Execute Immediate it need to be coded using 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;

Upvotes: -1

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

Another way (in Oracle 10g and later) is to use the alternative string literal notation - this means you don't need to worry about correctly escaping all the single quotes in the string, e.g. 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;
/

Upvotes: 3

Sean Landsman
Sean Landsman

Reputation: 7179

The problem I think is you have single quotes within single quotes. I cant test this at the moment, but I'd suggest you try the following (note the inner quotes are double quotes '', which escapes them:

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; 
/

I'd also try the create table part of the code standalone first just to make sure its valid before wrapping it in a proc.

Upvotes: 1

Related Questions