Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Code Review

Re: Code Review

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/05/14
Message-ID: <958338382.3230.0.pluto.d4ee154e@news.demon.nl>#1/1

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;

   rows_processed 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US