Re: PL/SQL EXECUTE IMMEDIATE won't parse...help

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 28 Jul 1999 11:52:57 GMT
Message-ID: <37a0ee8c.3100428@newshost.us.oracle.com>

A copy of this was sent to Mark Brunkhart <markhart_at_ricochet.net> (if that email address didn't require changing) On Tue, 27 Jul 1999 13:42:18 -0700, you wrote:

>I'm having trouble getting EXECUTE IMMEDIATE to parse in Oracle 8. The
>following shows a SQL*PLUS session and the resultant error. The command
>is straight out of the PL/SQL User's Guide and Reference. I'm guessing
>this may have to do with the fact that the EXECUTE command is getting
>converted into a BEGIN..END pair. Anyone know what's up?
>
>SQL> CREATE FUNCTION row_count (tab_name CHAR) RETURN INT AS
> 2 rows INT;
> 3 BEGIN
> 4 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name INTO rows;
> 5 RETURN rows;
> 6 END;
> 7 /
>
>Warning: Function created with compilation errors.
>
>SQL> show errors function row_count;
>Errors for FUNCTION ROW_COUNT:
>
>LINE/COL ERROR
>--------
>-----------------------------------------------------------------
>4/11 PLS-00103: Encountered the symbol "IMMEDIATE" when expecting
>one
> of the following:
> := . ( @ % ;

Sounds like you have the Oracle8i release 8.1 documentation but you have the Oracle8.0 database installed. EXECUTE IMMEDIATE is a new feature for 8.1.

---

Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production With the Partitioning and Objects options PL/SQL Release 8.0.3.0.0 - Production

SQL> @test

Warning: Function created with compilation errors.

SQL> show err
Errors for FUNCTION ROW_COUNT:

LINE/COL ERROR

-------- -----------------------------------------------------------------
4/13     PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one
         of the following:
         := . ( @ % ;
-----------------------------------------------

Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

SQL> @test

Procedure created.

SQL>

---

the procedure creates in 8.1. In 8.0 and before, the code would look like this:

create or replace function countem( p_tname in varchar2 ) return number is

    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   number default NULL;
    l_status        integer;

begin

    dbms_sql.parse( l_theCursor,

                    'select count(*) from ' || p_tname,
                     dbms_sql.native );

    dbms_sql.define_column( l_theCursor, 1, l_columnValue );

    l_status := dbms_sql.execute(l_theCursor);

    if ( dbms_sql.fetch_rows(l_theCursor) > 0 )

     then
        dbms_sql.column_value( l_theCursor, 1, l_columnValue );

    end if;

    dbms_sql.close_cursor(l_theCursor);

    return l_columnValue;
end countem;
/

you have to use DBMS_SQL in those releases.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jul 28 1999 - 06:52:57 CDT