Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Code Review
Ethan Post <epost1_at_my-deja.com> schreef in berichtnieuws
8fkk11$m8h$1_at_nnrp1.deja.com...
> here is a function I wrote to take a sql string a return a single value
> from it, I hacked the code out from an example so I see that is there
> is more than one value it will return the last value in the set, how
> does it look and is this the easiest way to do this? I couldn't fing
> the docs for dbms_sql so I'm not sure if everything here is required
> for it to work.
>
> FUNCTION fetch_dynamic (
> p_sql IN VARCHAR2)
> RETURN NUMBER IS
>
> cursor1 INTEGER;
> lv_val NUMBER;
> rows_processed NUMBER;
>
> BEGIN
>
> cursor1 := dbms_sql.open_cursor;
> dbms_sql.parse(cursor1, p_sql, dbms_sql.v7);
> dbms_sql.define_column (cursor1, 1, lv_val);
> rows_processed := dbms_sql.execute (cursor1);
>
> LOOP
>
> IF dbms_sql.fetch_rows (cursor1) > 0 THEN
>
> dbms_sql.column_value (cursor1, 1, lv_val);
>
> ELSE
>
> EXIT;
>
> END IF;
>
> END LOOP;
>
> dbms_sql.close_cursor(cursor1);
>
> RETURN lv_val;
>
> END fetch_dynamic;
>
> --
> http://www.freetechnicaltraining.com/home/ethan
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Some problems rectified, comments in code
FUNCTION fetch_dynamic (
p_sql IN VARCHAR2)
RETURN NUMBER IS
cursor1 INTEGER; lv_val NUMBER;
BEGIN cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, p_sql, dbms_sql.native); -- please use native dbms_sql.define_column (cursor1, 1, lv_val); rows_processed := dbms_sql.execute (cursor1);
--turned into structured code
while rows_processed > 0 LOOP
dbms_sql.column_value (cursor1, 1, lv_val); rows_processed := dbms_sql.fetch_rows (cursor1);END LOOP; dbms_sql.close_cursor(cursor1);
RETURN lv_val;
END fetch_dynamic;
>
> --
Hth,
Sybrand Bakker, Oracle DBA Received on Sun May 14 2000 - 00:00:00 CDT