OraFAQ Forum: Forms » Associative Array in Oracle Forms 6i

Home » Developer & Programmer » Forms » Associative Array in Oracle Forms 6i (Oracle Forms 6i, Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Associative Array in Oracle Forms 6i [message #577582]

Mon, 18 February 2013 16:43

infosuresh2k
Messages: 77
Registered: September 2009
Location: CHENNAI, INDIA

Member

I have created below package in oracle database 10g, i am able to execute/call this in one sql editor/developer.
If i call this in Oracle forms 6i like below then forms shows compile time error as "no function with name 'L_security_matrix' exists in this scope"

Can anybody have idea on this issue.

-------------------------------Oracle form call---------------
declare
L_security_matrix BBY_FLD_LVL_SECURITY.security_tabletype;
begin
L_security_matrix := BBY_FLD_LVL_SECURITY.GET_SECURITY_LVL('FM_BBYTIMEPHASEFIND');
if L_security_matrix('FIELD_A').form_name = 'EMP' then
INSERT INTO MENU VALUES('X');
end if;
end;
-------------------------------Oracle form call---------------

Database procedure :
-------------------------------Package Spec---------------
create or replace PACKAGE FLD_LVL_SECURITY
AS
TYPE security_rectype IS RECORD (form_name bby_security_matrix.form_name%TYPE,
field_name bby_security_matrix.field_name%TYPE,
enable_yn bby_security_matrix.enable_yn%TYPE);
TYPE security_tabletype IS TABLE OF security_rectype INDEX BY VARCHAR2(64);

FUNCTION GET_SECURITY_LVL(I_form_name IN VARCHAR2)
RETURN security_tabletype;
END FLD_LVL_SECURITY;
-------------------------------Package Spec---------------
-------------------------------Package Body---------------
create or replace PACKAGE BODY FLD_LVL_SECURITY
AS
FUNCTION GET_SECURITY_LVL(I_form_name IN VARCHAR2)
RETURN security_tabletype
AS
L_security_tab security_tabletype;
L_where_in VARCHAR2(100) := I_form_name;
BEGIN

FOR R IN (SELECT sm.form_name,
sm.field_name,
sm.enable_yn
FROM security_matrix sm)
LOOP
L_security_tab(R.field_name).form_name := R.form_name;
L_security_tab(R.field_name).field_name := R.field_name;
L_security_tab(R.field_name).enable_yn := R.enable_yn;
END LOOP;
dbms_output.put_line(L_security_tab('FIELD_Y').form_name);

return L_security_tab;
END GET_SECURITY_LVL;
END FLD_LVL_SECURITY;
-------------------------------Package Body---------------

Regards,
Suresh V

Report message to a moderator

Re: Associative Array in Oracle Forms 6i [message #577593 is a reply to message #577582]

Tue, 19 February 2013 00:17

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Older (such as 6i) Forms PL/SQL engine didn't keep up with the database PL/SQL engine, so - what could have been done in the database, Forms rejected it. A usual workaround - if possible - is to create a stored procedure and call it from a form.

Report message to a moderator

Re: Associative Array in Oracle Forms 6i [message #577669 is a reply to message #577593]

Tue, 19 February 2013 08:55

infosuresh2k
Messages: 77
Registered: September 2009
Location: CHENNAI, INDIA

Member

Hi,
What i want to do is, i want to create packaged procedure/function with out parameter as associative array.
I want to populate this associative array into form package variable.
Once populated then i want to use like below to get the value from associative array without querying from database table for each value. Bcz table has more than 200 value's, each field i want to check the status(enabled_yn). So i dont want to query 200 times for 200 fields.

L_field_a := L_security_matrix('FORM_A').field_name;
L_enable_b_yn := L_security_matrix('FORM_A').enabled_yn;

L_field_b := L_security_matrix('FORM_B').field_name;
L_enable_b_yn := L_security_matrix('FORM_B').enabled_yn;

Report message to a moderator

Previous Topic:	insert by cursor
Next Topic:	Changing current visual attribute property for the entire application

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 03:26:16 CDT 2024