Re: ORA-01007 when executing DBMS_SQL.COLUMN_VALUE

This is a multi-part message in MIME format.

--------------D554A7DE453EA690EF3B1B6C
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Haresh,

You need to do four things with DBMS_SQL:

1. Parse the statement
2. Define the columns using DBMS_SQL.DEFINE_COLUMN
3. Execute the query
4. Assignt the column to a variable using DBMS_SQL.COLUMN_VALUE

It looks like you are missing the define_column part

Hope this helps,

--
Tom Mettling
WT Chen & Company, Inc.
mettling_at_volpe.dot.gov

Haresh Assumal 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










--------------D554A7DE453EA690EF3B1B6C
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Tom  Mettling
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             Tom  Mettling
n:              Mettling;Tom 
org:            WT Chen & Company, Inc.l
email;internet: mettling_at_volpe.dot.gov
x-mozilla-cpt:  ;0
x-mozilla-html: TRUE
version:        2.1
end:            vcard


--------------D554A7DE453EA690EF3B1B6C--