Re: ORA-01007 when executing DBMS_SQL.COLUMN

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/10/30
Message-ID: <3459b4ea.65624042@newshost>#1/1

I don't see the dbms_sql.define_column call. Without it, you'll get the 1007. To do dynamic sql in pl/sql you

parse a query
bind all inputs
define all outputs
execute the query
loop
- fetch row
  - get outputs
end loop

below is a routine that does what you need for the most part:

create or replace procedure countem( p_tname in varchar2 ) is

    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   number;
    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 );
        dbms_output.put_line( l_columnValue );

end if;

dbms_sql.close_cursor(l_theCursor);
end countem;
/

On 30 Oct 1997 00:30:48 GMT, "Haresh Assumal" <assumal_at_sprynet.com> wrote:

>Hi,
>I am trying to get a count(*) of rows in using the following query in a
>PL/SQL function:
>
>DBMS_SQL.PARSE
> (cursor_handle,
> 'SELECT count(*) FROM ' || table_name ||
> ' WHERE ID = ' || object_id ,
> DBMS_SQL.V7);
>execute_feedback := DBMS_SQL.EXECUTE(cursor_handle);
>
>Note: table_name and object_id are PL/SQL variables.
>I get an error here when I try and do:
>
>DBMS_SQL.COLUMN_VALUE(cursor_handle,1,num_ids);
>
>Where num_ids is declared as number. The error is:
>
>ORA-01007: variable not in select list
>
>Why do I get this? Any help would be much appreciated.
>Thanks,
>Haresh
>E-mail:assumal_at_sprynet.com
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

NOTICE: In order to cut down on the amount of time I spend applying silly logic to email addresses like "In order to cut down on spam, remove THIS from my email" or "In order to cut down on spam, reverse my email address", etc, I will simply hit delete when that mail bounces back to me, which is what I do with spam myself.

I haven't figured out whats more time consuming for me, deleting spam or resending emails after re-reading the whole thing to figure out the algorithm to apply. Received on Thu Oct 30 1997 - 00:00:00 CST

Re: ORA-01007 when executing DBMS_SQL.COLUMN_VALUE