Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    8

    Unanswered: Decode in Stored Procedure

    Hi

    I have decode statement in a query. When the query
    is executed its works fine. If its put in a stored procedure
    it gives the following error :

    5/17 PLS-00103: Encountered the symbol "SELECT" when expecting one of
    the following:
    ( - + mod not null others <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists max min prior sql stddev sum variance
    execute forall time timestamp interval date
    <a string literal with character set specification>
    <a number> <a single-quoted SQL string>

    Any idea what the problem could be.

    Query:
    =====

    select decode((select 1 from dual),1,sysdate,sysdate -1) from dual;

    Procedure:
    ========

    create or replace procedure abcd
    as
    a date;
    begin
    select decode((select 1 from dual),1,sysdate,sysdate -1) into a from dual;
    end;

    Regards
    Ram

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The problem is not the DECODE, it is the "scalar subquery" (select 1 from dual) which is not allowed by your PL/SQL engine.

    The PL/SQL engine's SQL syntax has always lagged behind the SQL engine's syntax, and it happens that in your Oracle version scalar subqueries have been added to the SQL engine but are not recognized by the PL/SQL engine.

    Assuming that an upgrade to 9i or 10G is not an option, you have various options:
    1) rewrite without the scalar subquery
    2) put the query into a view
    3) use dynamic SQL

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    Andrew is correct. But why do you even have the subquery at all? Just get rid of it, since it shuldn't be there to begin with (if it's not needed, then don't do it)

    select decode((select 1 from dual),1,sysdate,sysdate -1) into a from dual;

    becomes

    select decode(1,1,sysdate,sysdate -1) into a from dual;

    And, since 1 will match 1, why the decode? Is something in there a variable?

    And, since you're using pl/sql, just do an if statement - that way the flow stays in pl/sql and doesn't require an extra call to the db engine to resolve the query.

Posting Permissions

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