Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_SQL.DEFINE_COLUMN problem
DBMS_SQL.DEFINE_COLUMN problem [message #18415] Wed, 30 January 2002 08:03 Go to next message
Anthony
Messages: 48
Registered: September 2000
Member
Hi all,

I am using oracle 7.3 and I have to write a dynamic procedure gets a total of records from the database and puts it into a local variable.

so far I thought I would have to use the DBMS_SQL.define_column package in the following way

dbms_sql.define_column(v_count,1,'count(*)',1);
where v_count is the local var which will receive the total record count.

Then using the following lines:
v_STR_SQL := 'select v_count from tableX';
i_CURSOR := DBMS_SQL.open_cursor;
DBMS_SQL.parse(i_CURSOR, v_STR_SQL, DBMS_SQL.v7);
i_ROWS_PROCESSED := DBMS_SQL.execute(i_CURSOR);
DBMS_SQL.close_cursor(i_CURSOR);

To execute the procedure (I hard-coded the select statement to make it simpler to explain)

Unfortunately its falling over, with the typical exceptions. Any help, anybody?

T.I.A

Anthony
Re: DBMS_SQL.DEFINE_COLUMN problem [message #18427 is a reply to message #18415] Wed, 30 January 2002 19:56 Go to previous messageGo to next message
dinakar shetty
Messages: 29
Registered: January 2002
Junior Member
hiow can u use that V_count in the query
use count(*) then give a alias and
then define a column to take that value
declare
aa number
lv_select := select count(*) x from table
begin
lc_cursor := dbms_sql.open_cursor ;
dbms_sql.parse(lc_cursor,lv_select , 1);
dbms_sql.define_column(lc_cursor,2,aa ) ;
this should help
the whole process i have not written
Re: DBMS_SQL.DEFINE_COLUMN problem [message #18431 is a reply to message #18427] Wed, 30 January 2002 23:53 Go to previous message
Anthony
Messages: 48
Registered: September 2000
Member
Thanks dinakar,

Your comments were helpful, at least the code compiles now, it still doesn´t return any value (I am inserting the value into a table and nothing shows up). The code now looks like the following.

v_STR_SQL := 'select count(*) XXX from emp';
i_CURSOR := DBMS_SQL.open_cursor;
DBMS_SQL.parse(i_CURSOR, v_STR_SQL, DBMS_SQL.v7);
DBMS_SQL.define_column(i_CURSOR,1,v_count);
i_ROWS_PROCESSED := DBMS_SQL.execute(i_CURSOR);
DBMS_SQL.close_cursor(i_CURSOR);
insert into ants values ('v_count is',v_count);

Any further advice?

Regards
Previous Topic: evaluation v literal
Next Topic: Collecting records from 2 tables - please help
Goto Forum:
  


Current Time: Thu Mar 28 05:00:07 CDT 2024