Re: PL/SQL: dbms_sql.execute error

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 09 Aug 1999 12:21:43 GMT
Message-ID: <37b0c783.1370911@newshost.us.oracle.com>

A copy of this was sent to "Paul Chu" <chupaul_at_earthlink.net> (if that email address didn't require changing) On Sun, 8 Aug 1999 18:36:53 -0700, you wrote:

>Hi all,
>
>Help, I'm running 7.3.4 and sql*plus 3.3 and testing a script to
>dynamically create select 'table_name', rowcount(*) from table_name stmts
>and execute them.
>
>Problem is that sql plus returns an error on execute method :
>If I comment out the line then the script runs fine.

[snip]

> 9 for csr_alteruser_rec in csr_alteruser loop
> 10 dbms_output.put_line ( csr_alteruser_rec.ssql);
> 11 dbms_sql.parse(cid, csr_alteruser_rec.ssql, dbms_sql.v7);
> 12 dbms_sql.execute( cid );
> 13
> 13 end loop;

[snip]

>declare
>*
>ERROR at line 1:
>ORA-06550: line 12, column 2:
>PLS-00221: 'EXECUTE' is not a procedure or is undefined
>ORA-06550: line 12, column 2:
>PL/SQL: Statement ignored
>

It says "execute is not a procedure or is undefined". you are getting this because execute is not a procedure -- its a function and you are calling it as though it were a procedure. Here is an example that does what you are trying to do:

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;
/

begin

    for x in ( select owner || '.' || table_name tname

                 from all_tables 
                where owner = 'SCOTT' )
    loop
        dbms_output.put_line( x.tname || ' ' || countem( x.tname ) );

    end loop;
end;
/

>???Not sure why in line1: declare is causing an error also
>Appreciate a direct email as well as a newsgroup post.
>
>Thanks, Paul
>
>
>

--
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 Mon Aug 09 1999 - 07:21:43 CDT