Thanks for the question, Kevin.

Asked: July 05, 2000 - 10:15 pm UTC

Last updated: September 08, 2005 - 4:26 pm UTC

Version: 1.0

Viewed 1000+ times

You Asked

I knew I could use cursor variables for store procedures on the server (Oracle 8/8i). Now, I am
considering using cursor variables on the client. So first question is - can
I do it using Forms 4.5?

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
course, you can use one procedure instead.) The reason for doing it is that
there are six input combinations on the user input block. Can someone review
the code and give me some hint on syntax so that it can work on the client
application as well. I hope to call this procedure in
when-new-block-instance trigger. If I am using Forms 4.5 and I can't cursor
varialbes, is there work-arround? -- better than creating six cursors for six
input combinations. Also, I like to get some hints to start with.
e.g. if the detail block should be a base table.

Any help will be greatly appreciated!

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

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;
END LOOP;

CLOSE v_CursorVar;

COMMIT;
END Show_Charges;

Thanks and regards,

Kevin

and Tom said...

You cannot use cursor variables in forms 4.5. You can call stored procedures that use them (stored in the server) but not on the client. That was added in forms 5.0.

There is no way I'd want to do this processing on the client anyway -- even in 5.0 and up -- i would want a stored procedure to fetch and insert. I would not want to bring each row back to the client and iterate row by row over it just to send them back.

What I would do is use either dbms_sql (in 8.0 or before or if I did not know the number of bind variables) or execute immediate and do a simple "INSERT INTO .... SELECT ... FROM WHERE ....". If I had no need for bind variables -- I would just use forms_ddl. For example:

...
begin
if p_input = 1 then
theQuery := 'insert into T select * from T2 where ...';
elsif p_input = 2 then
theQuery := 'insert into T select * from T3 where ...';
....

end if;

forms_ddl( theQuery );
commit;
end;

That is much more efficient then single row fetching/inserting.

If you really believe you must fetch the rows back to the form for whatever reason, use record groups. See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:270615625768 <code>for an example.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

cursor variables, what are they?

A reader, July 07, 2004 - 2:59 pm UTC

Hi

I understand ref cursors are cursor variables but are %TYPE and %ROWTYPE also cursor variables?

July 07, 2004 - 3:33 pm UTC

no, they are "types"....

a cursor variable is a variable of type ref cursor.

x c%rowtype;

there X is a record whose structure matches whatever C is defined as.

x y%type;

there X is a variable that happens to have the same datatype as Y, whatever that is.

cursor variables and type

A reader, July 08, 2004 - 2:07 am UTC

Hi

Thanks for your prompt reply, that's what we thought that type and rowtype has nothing to do with cursor variables however some Metalink analysts insist that type and rowtype and cursor variables. How can we convince them these are not cursor variables? We point them (yea them because they are 3) to the documentation but they dont seem to know how to read! Or doesnt want to understand what they read whatever....

Thank you very much, at least someone from Oracle share our same technical opinion :-S

Great site keep it up!

July 08, 2004 - 8:18 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96624/01_oview.htm#777 <code>

sort of clearly points out what is what, with no ambiguity.

tells you exactly what a cursor variable is:

Cursor Variables

Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. But, unlike a cursor, a cursor variable can be opened for any type-compatible query. It is not tied to a specific query. Cursor variables are true PL/SQL variables, to which you can assign new values and which you can pass to subprograms stored in an Oracle database.

%Type is:

%TYPE

The %TYPE attribute provides the datatype of a variable or database column. This is particularly useful when declaring variables that will hold database values. For example, assume there is a column named title in a table named books. To declare a variable named my_title that has the same datatype as column title, use dot notation and the %TYPE attribute, as follows:

my_title books.title%TYPE;

and %ROWTYPE:

%ROWTYPE

In PL/SQL, records are used to group data. A record consists of a number of related fields in which data values can be stored. The %ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable.

it clearly says "cursor variables are variables" and type/rowtype are "types".

since types are not variables, and a cursor variable is a variable -- type/rowtype cannot possible be considered cursor variables in any sense

You can post this link in your metalink thread if you like to help clear it up. Just use the url you get after clicking on the "bookmark this page" link.

that is the link

A reader, July 08, 2004 - 4:06 pm UTC

That is the link we have been point them to read and asking them where does it says cursor variables are type and rowtype. Obviously they cannot and cannot provide any proof. No ofense but I find many support guys's knowledge level are really low, many times I find myself feeling like I am the support guy and they are the customer....

Not mentioning last time I believed (after insisting a lot that it was not working) an analysts regarding an AIO issue on RHEL 3 who was insisting if you see asynch event in system event then aio is working. After 3 weeks when my TAR was hard closed I found out that there is a bug when relink Oracle in order for AIO to work. Literally he said:

"I can confirm from Oracle Corp that AIO is working"

:-S

Again, great site keep it up!

THX

cursor variables

A reader, September 08, 2005 - 1:00 pm UTC

Hi tom,
while reading PL/SQL Book about cursor variables, i did not understand the following line. Can you please explain it.

-----------------------------------------------------------
If you pass a cursor variable as a bind variable or host variable to PL/SQL, you will not be able to fetch from it from within the server unless you also open it in that same server call.
-----------------------------------------------------------

This is from oracle 9i book.

Thanks in advance.

September 08, 2005 - 4:26 pm UTC

means you cannot pass an opened ref cursor to plsql from a client application.


ops$tkyte@ORA9IR2> variable x refcursor;
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
  2          open :x for select * from dual;
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_dummy dual.dummy%type;
  3  begin
  4          fetch :x into l_dummy;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 4
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_dummy dual.dummy%type;
  3  begin
  4          open :x for select * from dual;
  5          fetch :x into l_dummy;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

cursor variables, what are they?

cursor variables and type

that is the link

cursor variables

More to Explore

PL/SQL demos

PL/SQL docs

use of cursor variables in Forms 4.5

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

cursor variables, what are they?

cursor variables and type

that is the link

cursor variables

More to Explore

PL/SQL demos

PL/SQL docs