DBMS_SQL.DEFINE_COLUMN problem [message #18415] |
Wed, 30 January 2002 08:03 |
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 |
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 |
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
|
|
|