Home » SQL & PL/SQL » SQL & PL/SQL » How does FORMS_DLL work?
How does FORMS_DLL work? [message #1044] Mon, 25 March 2002 06:03 Go to next message
Greg Horton
Messages: 37
Registered: February 2002
Member
I've written the following code in a WHEN_BUTTON_PRESSED trigger using forms 6i:

DECLARE
total_record NUMBER(6) := 0;
v_item NUMBER(6) := 0;
v_countstar NUMBER(6) := 0;
--
CURSOR c_frequent_items IS
SELECT distinct item, count(*) count_star
from table1
group by item
having count(*) > :mine_data.support_value_text;
--
BEGIN
-- create table table2
FORMS_DDL('
CREATE OR REPLACE TABLE table2
(
TID NUMBER(6)
,ITEM NUMBER(2)
');
-- populate table2 with cursor c_frequent_items
OPEN c_frequent_items;
LOOP
/* Retrieve each row of the result of the above query into PL/SQL variables: */
FETCH c_frequent_items INTO v_item, v_countstar;
/* If there are no more rows to fetch, exit the loop: */
EXIT WHEN c_frequent_items%NOTFOUND;
-- insert into table2 cursor values for that tuple
FORMS_DDL('
INSERT INTO table2
VALUES(v_item, v_countstar)
');
END LOOP;
/* Free cursor used by the query. */
CLOSE c_frequent_items;
-- populate block frequent_items with values from table2
.
.
.

The code populates a cursor with some values, creates a table, and then populates the table with the cursor. The code compiles and the cursor gets populated, but for some reason the table insn't getting created and the cursor is therefore not populating it. Can you help? Do you know if I'm executing the FORMS_DDL incorrectly?

Thanks.
Re: How does FORMS_DLL work? [message #1045 is a reply to message #1044] Mon, 25 March 2002 06:37 Go to previous messageGo to next message
sfhollands
Messages: 15
Registered: October 2001
Junior Member
In the table creation remove the 'replace' option. forms_ddl stt should read as follows

FORMS_DDL('
CREATE table2
(
TID NUMBER(6)
,ITEM NUMBER(2)
');
Re: How does FORMS_DLL work? [message #1047 is a reply to message #1044] Mon, 25 March 2002 06:57 Go to previous message
Greg Horton
Messages: 37
Registered: February 2002
Member
Thankyou for your reply. I have managed to get it working now.. I also had a bracket missing which didn't help!!

Is there a way to overwrite an existing table if it already exists, as the OR REPLACE statement would allow?

Thankyou.
Previous Topic: How to import data from a text file in comma delimeter form?
Next Topic: Package owner.
Goto Forum:
  


Current Time: Fri Mar 29 09:58:13 CDT 2024