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

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Tue, 27 Jul 1999 21:34:18 GMT
Message-ID: <37a1248b.120266834@inet16.us.oracle.com>

On Tue, 27 Jul 1999 13:42:18 -0700, Mark Brunkhart <markhart_at_ricochet.net> 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 /

This is Oracle8i ( 8.1.5 ) syntax. Verify that you are using that version of the database. If you are not then you will need to use the dbms_sql package to accomplish the same thing.

hope this helps.

chris.

>
>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:
> := . ( @ % ;

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jul 27 1999 - 16:34:18 CDT