Re: DBMS_SQL and VARIABLE_VALUE - So Convoluted I Want To Scream!

Thanks for all your help! It's nice to know there is such a great resource out there.

Anyway, after FORCING myself to read through the Oracle documentation again (believe me, it took some doing ...) I realized I was using the wrong procedure! As your code illustrates, I don't need the VARIABLE_VALUE procedure at all, but rather the COLUMN_VALUE procedure! Once I figured that out, (and got rid of some other moronic errors which were causing me to believe there was a problem with my COLUMN_VALUE code ...) all was happy!

Thanks again for your quick and helpful responses!

Cheers.
John

> DECLARE
>
> FUNCTION count_em (
> tablename VARCHAR2
> )
> RETURN NUMBER
> IS
> cursor_handle NUMBER(6);
> counter NUMBER(6);
> statement VARCHAR2(256);
> res NUMBER(6);
> BEGIN
> statement := 'SELECT count(*) from ' || tablename;
> cursor_handle := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cursor_handle, statement, DBMS_SQL.NATIVE);
> DBMS_SQL.DEFINE_COLUMN(cursor_handle, 1, counter);
> res := DBMS_SQL.EXECUTE(cursor_handle);
> res := DBMS_SQL.FETCH_ROWS(cursor_handle);
> DBMS_SQL.COLUMN_VALUE(cursor_handle, 1, counter);
> DBMS_SQL.CLOSE_CURSOR(cursor_handle);
> RETURN counter;
> END count_em;
>
> BEGIN
> DBMS_OUTPUT.ENABLE(1000000);
> DBMS_OUTPUT.PUT_LINE('count is ' || count_em('all_users'));
> END;
> /

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 07 1999 - 05:43:16 CDT