cursor variables

[Quoted] I 've used cursor variables for store procedures on the server. Now, I am [Quoted] considering using cursor variables on the client. So first question is -can [Quoted] I do it? (I am using Forms 4.5, by the way.)

In a program unit, say function My_Function_A returns the cursor variable and My_Procedure_ B fetches the data and inserts data to another table. (Of [Quoted] course, you can use one procedure instead.) The reason for doing it is that [Quoted] there are six input combinations on the user input block. Can someone review [Quoted] the code and give me some hint on syntax so that it can work on the client [Quoted] application as well. I hope to call this procedure in when-new-block-instance trigger.

Any help will be greatly appreciated!

With PL/SQL (2.3 and higher) I would write something like this (all in one.)

[Quoted] CREATE OR REPLACE PROCEDURE Show_Charges   (p_Input IN number) AS

  TYPE t_Records IS REF CURSOR;

  v_CursorVar t_Records;

  /* Variables to hold the output. Just show one in here. /   v_Amount tbraccd.tbraccd_amount%TYPE;

BEGIN

• Based on the input parameters, open the cursor variable.

IF p_Input = 1 THEN

    OPEN v_CursorVar FOR
      SELECT statement

ELSIF p_Input = 2 THEN

    OPEN v_CursorVar FOR
      SELECT statement
.. .. ..

ELSE
    /* Wrong value passed as input - raise an error */   RAISE_APPLICATION_ERROR(-20000, 'Input must be 1 - 6'); END IF; /* Fetch loop. */
  LOOP

      FETCH v_CursorVar INTO
        v_Amount;
      EXIT WHEN v_CursorVar%NOTFOUND;

      INSERT INTO table_1 (amount_paid)
        VALUES (v_Amount);

      END IF;

  COMMIT;
END Show_Charges;

Kevin Received on Tue Jun 27 2000 - 00:00:00 CEST