Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2008
    Posts
    8

    Unanswered: Need help on "execute immediate"

    Hi,

    I am getting below error while doing "execute immediate".Can any one help me with correct syntax for the below query?

    SQL> begin
    sqlText := 'SELECT event_id FROM fr_event where EVENT_DATE < '1-Oct-2008' and rownum < 10';
    execute immediate sqltext;
    end;
    2 3 4 5 /


    sqlText := 'SELECT event_id FROM fr_event where EVENT_DATE < '1-Oct-2008' and rownum < 10';
    *
    ERROR at line 2:
    ORA-06550: line 2, column 65:
    PLS-00103: Encountered the symbol "1" 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 "1" to continue.
    ORA-06550: line 2, column 75:
    PLS-00103: Encountered the symbol " and rownum < 10" when expecting one of the
    following:
    * & = - + ; < / > at in is mod remainder not rem
    <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
    LIKE4_ LIKEC_ between || me

    thanks in advance.......

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If/when the SQL is invalid, then Oracle complains & spews error(s).
    Such as your statement.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    Quote Originally Posted by tucs_123
    Hi,

    I am getting below error while doing "execute immediate".Can any one help me with correct syntax for the below query?

    SQL> begin
    sqlText := 'SELECT event_id FROM fr_event where EVENT_DATE < '1-Oct-2008' and rownum < 10';
    execute immediate sqltext;
    end;
    2 3 4 5 /


    sqlText := 'SELECT event_id FROM fr_event where EVENT_DATE < '1-Oct-2008' and rownum < 10';
    *
    ERROR at line 2:
    ORA-06550: line 2, column 65:
    PLS-00103: Encountered the symbol "1" 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 "1" to continue.
    ORA-06550: line 2, column 75:
    PLS-00103: Encountered the symbol " and rownum < 10" when expecting one of the
    following:
    * & = - + ; < / > at in is mod remainder not rem
    <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
    LIKE4_ LIKEC_ between || me

    thanks in advance.......

    try this

    sql > set serveroutput on;

    SQL>
    declare
    sql_txt varchar2(300);
    begin
    sql_txt:= 'SELECT event_id FROM fr_event where EVENT_DATE < TO_DATE(''01-Oct-2008'',''DD-MON-YYYY'') and rownum < 10';
    execute immediate sql_txt;
    end;
    /
    Last edited by hasan_uiu; 02-18-09 at 05:17.
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Code:
    sqlText := 'SELECT event_id FROM fr_event where EVENT_DATE < '1-Oct-2008' and rownum < 10';
    *
    ERROR at line 2:
    ORA-06550: line 2, column 65:
    PLS-00103: Encountered the symbol "1" when expecting one of the following:
    ...
    When using quotes in string, you shall treat it specially, as already documented in SQL Reference. It is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/.
    Much better (from transparency and performance perspective) is binding instead of using literals in dynamic queries. Have a look into PL/SQL User's Guide and Reference for more details.
    By the way, the query you posted may be run statically, which would be the best option.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •