Re: ORA-00905 error in PL/SQL code

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/30
Message-ID: <8h16pe$q90$1@nnrp1.deja.com>#1/1

In article <3934160a$0$32021_at_fountain.mindlink.net>,   "Gennady" <gennadyb_at_halcosoftware.com> wrote:
> Hello,
> I'm trying to execute the code:
> declare
> c2 number;
> d2 number;
> k number;
> vin_table_name varchar2(30):='MYTABLE';
> begin
> dbms_output.enable;
> c2 := dbms_sql.open_cursor;
> dbms_sql.parse(c2, 'select count(*) into '||k||' from
> '||vin_table_name||'',dbms_sql.native);
> d2:=dbms_sql.execute(c2);
> dbms_output.put_line(k);
> dbms_sql.close_cursor(c2);
> end;
>
> But I get error ORA-00905: missing keyword.
> Could you please give me a hint how to fix the problem?
> Thanks,
> Gennady
>
>

select .. into is ONLY used with implicit cursors. Never with DBMS_SQL like that.

Here is how:

create or replace function get_rows( p_tname in varchar2 ) return number as

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

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue May 30 2000 - 00:00:00 CDT