3 Replies Latest reply: Mar 28, 2013 6:52 AM by BluShadow RSS

    EXECUTE IMMEDIATE ERROR ..

    user3558544
      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
          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
            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
              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}