This discussion is archived
3 Replies Latest reply: Mar 28, 2013 4:52 AM by BluShadow RSS

EXECUTE IMMEDIATE ERROR ..

user3558544 Newbie
Currently Being Moderated
Kidnly somebody suggest me how to wirte below query in execute immediate in pl/sql block as it throwing below error

CREATE OR REPLACE PROCEDURE LBA_NORTH
AS
BEGIN
execute immediate ( 'CREATE TABLE LBA_ALERT(DATE_CRT,DIST_MSISDN,FOS_MSISDN,RT_COUNT,RT_SERIES)TABLESPACE PRTP_INDX1 AS
SELECT TO_CHAR(LMA.DATE_CRT,'DD-MON-YY'), LMP.DIST_MSISDN,LMA.FOS_MSISDN,COUNT(LMA.RT_MSISDN)RT_COUNT,
RTRIM (xmlagg (xmlelement (e, LMA.RT_MSISDN || ',')).extract ('//text()'), ',')AS RT_SERIES
FROM LBA_Mapping LMP,LBA_Master LMA
WHERE LMP.DIST_MSISDN=LMA.DIST_MSISDN
GROUP BY LMP.DIST_MSISDN,LMA.FOS_MSISDN,TO_CHAR(LMA.DATE_CRT,'DD-MON-YY')');

END;
/


LINE/COL ERROR
-------- -----------------------------------------------------------------
5/30 PLS-00103: Encountered the symbol "DD" when expecting one of the
following:
) , * & | = - + < / > at in is mod remainder not rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
The symbol "," was substituted for "DD" to continue.

5/39 PLS-00103: Encountered the symbol "),
LMP.DIST_MSISDN,LMA.FOS_MSISDN,COUNT(LMA.RT_MSISDN)RT_COUNT,
" when expecting one of the following:
. ( ) , * @ % & | = - + < / > at in is mod remainder not rem

LINE/COL ERROR
-------- -----------------------------------------------------------------
=> .. <an exponent (**)> <> or != or ~= >= <= <> and or like
LIKE2_ LIKE4_ LIKEC_ between || member SUBMULTISET_
The symbol "(" was substituted for "),
LMP.DIST_MSISDN,LMA.FOS_MSISDN,COUNT(LMA.RT_MSISDN)RT_COUNT,
" to continue.

6/65 PLS-00103: Encountered the symbol "/" when expecting one of the
following:
( - + case mod new null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current max min prior sql stddev sum variance execute

LINE/COL ERROR
-------- -----------------------------------------------------------------
forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set
specification>
<an alternatively-quoted SQL string>

6/72 PLS-00103: Encountered the symbol "), " when expecting one of the
following:
. ( ) , * % & | = - + < / > at in is mod remainder not rem =>
.. <an exponent (**)> <> or != or ~= >= <= <> and or like

LINE/COL ERROR
-------- -----------------------------------------------------------------
LIKE2_ LIKE4_ LIKEC_ between || member SUBMULTISET_

Edited by: user3558544 on Mar 28, 2013 4:52 AM
  • 1. Re: EXECUTE IMMEDIATE ERROR ..
    Paul Horth Expert
    Currently Being Moderated
    Why are you creating a table in a procedure?

    I do not see anything that would require dynamic SQL and hence an execute immediate.

    In general it is not a good idea to create tables dynamically.

    Why not just create it in a SQL script?
  • 2. Re: EXECUTE IMMEDIATE ERROR ..
    BluShadow Guru Moderator
    Currently Being Moderated
    You are not escaping the quotes in your string, so when it gets to the date format:

    'DD...

    the opening quote of that format string is actually acting as a closing quote for the SQL string you are building.

    There are ways to escape the quotes in the string so that it works, but more importantly... why on Earth are you creating a table at runtime? That is not good design and is certainly not recommended.
  • 3. Re: EXECUTE IMMEDIATE ERROR ..
    BluShadow Guru Moderator
    Currently Being Moderated
    And by the way...
    user3558544 wrote:
    Kidnly suggest immediately
    This is a forum of volunteers who have their own jobs to do. Suggesting they respond immediately or ugently is considered downright rude, not just to those volunteers but also to all the other people who would like a quick answer to their questions (but who haven't been rude).

    Read: {message:id=9360002}

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points