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: Stored Procedure

Re: Stored Procedure

From: Gary SMITH <Gary.Smith_at_cern.ch>
Date: 1997/11/18
Message-ID: <34716ADD.D5916C1F@cern.ch>#1/1

Something like this might do it:

CREATE OR REPLACE FUNCTION load_table_rows(load_table IN varchar,

batch_id IN integer)
RETURN integer
AS

    cur_id integer := DBMS_SQL.OPEN_CURSOR;
    rtn_code integer;
    num_rows integer;

BEGIN
    DBMS_SQL.PARSE(cur_id, 'select count(*) from '||load_table
                                        ||' where batch_id =
'||to_char(batch_id), DBMS_SQL.V7);

    DBMS_SQL.DEFINE_COLUMN(cur_id,1, num_rows);

    rtn_code := DBMS_SQL.EXECUTE(cur_id);

    IF DBMS_SQL.FETCH_ROWS(cur_id) = 0
    THEN

        /* Error */
        num_rows = -1;
     ELSE
        DBMS_SQL.COLUMN_VALUE(cur_id,1, num_rows);
    END IF;     return num_rows;
END; This maybe needs some extra error checking.

Regards

--

*************************
Gary Smith CERN, Geneva, Switzerland Email: Gary.Smith_at_cern.ch Tel: +41 22 7678944
*************************
Received on Tue Nov 18 1997 - 00:00:00 CST

Original text of this message

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