We help IT Professionals succeed at work.

oracle forms question

anumoses
anumoses asked
on
219 Views
Last Modified: 2020-03-02
Forms [32 Bit] Version 11.1.1.4.0 (Production)
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
     With the Partitioning, OLAP, Data Mining and Real Application Testing options
Oracle Toolkit Version 11.1.1.4.0 (Production)
PL/SQL Version 11.1.0.7.0 (Production)
Oracle Procedure Builder V11.1.1.4.0 - Production
PL/SQL Editor (c) WinMain Software (www.winmain.com), v1.0 (Production)
Oracle Query Builder 11.1.1.4.0 - Production
Oracle Virtual Graphics System Version 11.1.1.4.0 (Production)
Oracle Tools GUI Utilities Version 11.1.1.2.0 (Production)
Oracle Multimedia Version 11.1.1.4.0 (Production)
Oracle Tools Integration Version 11.1.1.2.0 (Production)
Oracle Tools Common Area Version 11.1.1.4.0
Oracle CORE      11.1.0.7.0      Production


I have a tab page form.

  User Access Tab
has 2 tabs. 1. division, 2. customer

We are breaking some rules hre to make the relation work. I have created relations on the block.
1st block - usrdiv
2nd block - usrcust

both these will have user roles. above 2 blocks sit on top of roles block

If user clicks on either div or cust we will display user roles but the roles displayed will be based on division key or customer key.
 
the roles block is udr.
I have a code in pre query of udr block

  IF( :SYSTEM.TAB_NEW_PAGE = 'USER_ACCESS' )THEN
  	IF( :SYSTEM.TAB_NEW_PAGE = 'DIV' )THEN
        :UDR.USRDIV_ID := :USRDIV.ID;
  	END IF;
  END IF;
  
  IF( :SYSTEM.TAB_NEW_PAGE = 'USER_ACCESS' )THEN      
  	IF( :SYSTEM.TAB_NEW_PAGE = 'CUST' )THEN
        :UDR.USRCUST_ID := :USRCUST.ID;  
    END IF;
  END IF;

Open in new window


on the block where clause I have this code

 
WHERE  (USRDIV_ID = :USRDIV.ID OR USRCUST_ID = :USRCUST.ID)

Open in new window


I am attaching screen prints. The div tab does not show roles, but customer tab shows. Its actually the other way around. The div tab has to show roles but not customer.

I need help in fixing and will appreciate the help.

table script

DROP TABLE DSS.TAB1 CASCADE CONSTRAINTS;

CREATE TABLE DSS.TAB1
(
  ID           NUMBER(38)                       NOT NULL,
  DEFAULT_DIV  VARCHAR2(1 BYTE)                 NOT NULL,
  DIV_NO       NUMBER(2)                        NOT NULL,
  PERS_ID      NUMBER(38)                       NOT NULL,
  ACTIVE       VARCHAR2(1 BYTE)                 NOT NULL,
  NOTES        VARCHAR2(240 BYTE)
)
TABLESPACE MISC
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

SET DEFINE OFF;
Insert into DSS.TAB1
   (ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE, 
    NOTES)
 Values
   (501903, 'N', 11, 1711, 'A', 
    'x');
Insert into DSS.TAB1
   (ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE, 
    NOTES)
 Values
   (501905, 'N', 50, 1711, 'I', 
    NULL);
Insert into DSS.TAB1
   (ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE, 
    NOTES)
 Values
   (11423931738, 'N', 71, 1711, 'A', 
    NULL);
Insert into DSS.TAB1
   (ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE, 
    NOTES)
 Values
   (11750159949, 'N', 55, 1711, 'A', 
    NULL);
Insert into DSS.TAB1
   (ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE, 
    NOTES)
 Values
   (20310150, 'Y', 1, 1711, 'A', 
    'x');
Insert into DSS.TAB1
   (ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE, 
    NOTES)
 Values
   (22645048, 'N', 5, 1711, 'A', 
    'x');
Insert into DSS.TAB1
   (ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE, 
    NOTES)
 Values
   (23978631, 'N', 6, 1711, 'A', 
    NULL);
Insert into DSS.TAB1
   (ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE, 
    NOTES)
 Values
   (1080171145, 'N', 91, 1711, 'A', 
    NULL);
Insert into DSS.TAB1
   (ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE, 
    NOTES)
 Values
   (1080171156, 'N', 97, 1711, 'A', 
    NULL);
Insert into DSS.TAB1
   (ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE, 
    NOTES)
 Values
   (3339590005, 'N', 31, 1711, 'A', 
    NULL);
Insert into DSS.TAB1
   (ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE, 
    NOTES)
 Values
   (4199747257, 'N', 81, 1711, 'A', 
    NULL);
Insert into DSS.TAB1
   (ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE, 
    NOTES)
 Values
   (5051070062, 'N', 7, 1711, 'A', 
    NULL);
Insert into DSS.TAB1
   (ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE, 
    NOTES)
 Values
   (5336771059, 'N', 59, 1711, 'A', 
    NULL);
Insert into DSS.TAB1
   (ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE, 
    NOTES)
 Values
   (8331564946, 'N', 65, 1711, 'A', 
    NULL);
Insert into DSS.TAB1
   (ID, DEFAULT_DIV, DIV_NO, PERS_ID, ACTIVE, 
    NOTES)
 Values
   (9844406287, 'N', 57, 1711, 'A', 
    NULL);
COMMIT;

Open in new window


DROP TABLE DSS.TAB2 CASCADE CONSTRAINTS;

CREATE TABLE DSS.TAB2
(
  ID            NUMBER(38)                      NOT NULL,
  DEFAULT_CUST  VARCHAR2(1 BYTE)                NOT NULL,
  CUST_ID       NUMBER(38)                      NOT NULL,
  PERS_ID       NUMBER(38)                      NOT NULL,
  ACTIVE        VARCHAR2(1 BYTE)                NOT NULL,
  NOTES         VARCHAR2(4000 BYTE)
)
TABLESPACE MISC
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

SET DEFINE OFF;
Insert into DSS.TAB2
   (ID, DEFAULT_CUST, CUST_ID, PERS_ID, ACTIVE, 
    NOTES)
 Values
   (12405285275, 'Y', 4851575509, 1711, 'A', 
    'x');
COMMIT;

Open in new window


DROP TABLE DSS.TAB3 CASCADE CONSTRAINTS;

CREATE TABLE DSS.TAB3
(
  ID               NUMBER(38)                   NOT NULL,
  ORACLE_ROLE      VARCHAR2(30 BYTE)            NOT NULL,
  USRDIV_ID        NUMBER(38),
  ACTIVE           VARCHAR2(1 BYTE)             NOT NULL,
  ROLE_SHORT_NAME  VARCHAR2(10 BYTE),
  NOTES            VARCHAR2(240 BYTE)
)
TABLESPACE MISC
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

SET DEFINE OFF;
Insert into DSS.TAB3
   (ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME, 
    NOTES)
 Values
   (1069946932, 'NORM_ALL', 20310150, 'I', 'ILS_RTBL', 
    NULL);
Insert into DSS.TAB3
   (ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME, 
    NOTES)
 Values
   (12399502686, 'NORM_ALL', 20310150, 'A', 'ILS_MNTVEN', 
    NULL);
Insert into DSS.TAB3
   (ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME, 
    NOTES)
 Values
   (12399502810, 'NORM_ALL', 20310150, 'A', 'ILS_QCSR', 
    NULL);
Insert into DSS.TAB3
   (ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME, 
    NOTES)
 Values
   (12399499473, 'NORM_ALL', 20310150, 'A', 'SECURITY', 
    NULL);
Insert into DSS.TAB3
   (ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME, 
    NOTES)
 Values
   (12399499475, 'NORM_ALL', 20310150, 'A', 'ACCTNG', 
    NULL);
Insert into DSS.TAB3
   (ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME, 
    NOTES)
 Values
   (12399499477, 'NORM_ALL', 20310150, 'A', 'CUST SRVC', 
    NULL);
Insert into DSS.TAB3
   (ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME, 
    NOTES)
 Values
   (12400771316, 'NORM_ALL', 20310150, 'A', 'ILS_RTBL', 
    NULL);
Insert into DSS.TAB3
   (ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME, 
    NOTES)
 Values
   (21492721, 'NORM_ALL', 20310150, 'A', 'IS_PRD_SUP', 
    'x');
Insert into DSS.TAB3
   (ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME, 
    NOTES)
 Values
   (20310151, 'NORM_ALL', 20310150, 'A', 'USR SECRTY', 
    NULL);
Insert into DSS.TAB3
   (ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME, 
    NOTES)
 Values
   (20310153, 'NORM_ALL', 20310150, 'A', 'INQ_ALL', 
    NULL);
Insert into DSS.TAB3
   (ID, ORACLE_ROLE, USRDIV_ID, ACTIVE, ROLE_SHORT_NAME, 
    NOTES)
 Values
   (20310154, 'NORM_ALL', 20310150, 'I', 'IS_DEVELOP', 
    'x');
COMMIT;

Open in new window



Help appreciated
Comment
Watch Question

flow01IT-specialist
CERTIFIED EXPERT

Commented:
And if you debug the prequery block by displaying the arguments ?

add as last line of the prequery block
message(''usrdiv.id=' ||  :USRDIV.ID || '< usrcust_id='  || USRCUST_ID || '<' , acknowledge);

check if it explains the behaviour and/or check for extra spaces ...

Author

Commented:
I have to get values on the division. But its not happening. When I click on customer tab then the values populate. Actually customer has no roles entered.

Author

Commented:
     --41178  Start
  IF( :SYSTEM.TAB_NEW_PAGE = 'USER_ACCESS' )THEN
  	IF( :SYSTEM.TAB_NEW_PAGE = 'DIV' )THEN
        :UDR.USRDIV_ID := :USRDIV.ID;
  	END IF;
  END IF;
  
  IF( :SYSTEM.TAB_NEW_PAGE = 'USER_ACCESS' )THEN      
  	IF( :SYSTEM.TAB_NEW_PAGE = 'CUST' )THEN
        :UDR.USRCUST_ID := :USRCUST.ID;  
    END IF;
  END IF;

Open in new window


Pre query is getting the id, but the focus is shifting and prequery fires when I click customer tab.
flow01IT-specialist
CERTIFIED EXPERT

Commented:
The message is meant to verify  if after firing of the requery trigger  the forms_items have the expected values.
And are both :USRDIV. ID   :USRCUST_ID defined as query_allowed ?

Now I see:
Your prequery code wil not work :  your are checking :SYSTEM.TAB_NEW_PAGE twice in the same condition for different values:
so both your combined conditions (line 2 and 3)  and (line 8 and 9)  will never be true and thus the  items will not be set.
The values before the pre_query determine the result.
(consider also if you need to initialise :UDR.USRDIV_ID and :UDR.USRCUST_ID)

Author

Commented:
(consider also if you need to initialise :UDR.USRDIV_ID and :UDR.USRCUST_ID)

Initialization has to be done before pre-query?

Author

Commented:
IF get_canvas_property('USER_ACCESS_TAB',topmost_tab_page) = 'DIV' THEN
        :UDR.USRDIV_ID := :USRDIV.ID;
END IF;

IF get_canvas_property('USER_ACCESS_TAB',topmost_tab_page) = 'CUST'  THEN
        :UDR.USRCUST_ID := :USRCUST.ID;  
END IF;

Open in new window


I have to use this code to display the top  tab. But when I query or do a tab change, the data does not display. I have to go to user roles block and to execute query. The data is correctly displaying. In customer no data, which is correct for roles.  Now I need to get data after I change tab.

Author

Commented:
IF get_canvas_property('USER_ACCESS_TAB',topmost_tab_page) = 'DIV' THEN
	GO_BLOCK( 'UDR' );
	Clear_Block(No_Validate);
  IF :UDR.USRDIV_ID IS NOT NULL THEN
		       execute_query;		  
  END IF;	
END IF;

IF get_canvas_property('USER_ACCESS_TAB',topmost_tab_page) = 'CUST' THEN
   GO_BLOCK( 'UDR' );
   Clear_Block(No_Validate);
   IF :UDR.USRCUST_ID IS NOT NULL THEN
		       execute_query;		  
   END IF;
END IF;

Open in new window


When I call when-tab-page_change, can I use this code?
Helena Markováprogrammer-analyst
CERTIFIED EXPERT

Commented:
Yes, you can.
flow01IT-specialist
CERTIFIED EXPERT

Commented:
And you can probably avoid the pre_query trigger bij changing the where clause for the next execution depending on the current tab
Set_Block_Property('UDR', ONETIME_WHERE, 'USRDIV_ID = :USRDIV.ID;')
Set_Block_Property('UDR', ONETIME_WHERE, 'USRCUST_ID = :USRCUST.ID;')

after the clear_block  of UDR :UDR.USRDIV_ID and :UDR.USRCUST_ID will always be null !!!

Author

Commented:
Should the code be

Set_Block_Property('UDR', ONETIME_WHERE, 'USRCUST_ID =' ||:USRCUST.ID);

Author

Commented:
Set_Block_Property('UDR', ONETIME_WHERE, 'USRDIV_ID = :USRDIV.ID;')
Set_Block_Property('UDR', ONETIME_WHERE, 'USRCUST_ID = :USRCUST.ID;')

I tried to use all help. But nothing is working. I dont get data for roles.
flow01IT-specialist
CERTIFIED EXPERT

Commented:
Apart from getting the rows (= execute_query) ,  you can also count the rows.   Maybe you have a menu icon or you know the keys to activate that feature.  At my work it's shift-F2.   Try it on the roles  : if there are rows counted > 0  then you have to check your  post-query trigger, if it fails rows are not shown,

Author

Commented:
declare
	
	v_count number;
begin
	
select count(*) into v_count from dss.USER_DIV_ROLES
where usrdiv_id = :usrdiv.id;

message('count for div from roles = ' || v_count);pause;
end;

Open in new window


But the data will not display automatically. On my relations I have Yes to query records.  But I have to press execute query.

Author

Commented:
For the 1st time when I query a person, I am getting the correct data in both DIV and Customer Tabs. ist time queryon tab change
So what needs to be done. In pre-query I have this code.

declare
   	v_where VARCHAR2(2000);    
begin
   	
IF get_canvas_property('USER_ACCESS_TAB',topmost_tab_page) = 'DIV' THEN
        :UDR.USRDIV_ID := :USRDIV.ID;
         V_Where := 'udr.usrdiv_id = '||:usrdiv.id;
         SET_BLOCK_PROPERTY ('UDR', DEFAULT_WHERE, V_Where);
ELSIF get_canvas_property('USER_ACCESS_TAB',topmost_tab_page) = 'CUST'  THEN
        :UDR.USRCUST_ID := :USRCUST.ID;  
        V_Where := 'udr.uscust_id = '||:usrcust.id;
         SET_BLOCK_PROPERTY ('UDR', DEFAULT_WHERE, V_Where);
END IF;
end;

Open in new window


But still does not work.Help is appreciated.
IT-specialist
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for all the options. One question - all options for when tab changed trigger? . Please confirm

Author

Commented:
Create a when-tab-page_changed  trigger

option 0  depend only on your default_where  (WHERE  (USRDIV_ID = :USRDIV.ID OR USRCUST_ID = :USRCUST.ID))
begin
GO_BLOCK( 'UDR' );
execute_query;
end;

This option worked the best.

Author

Commented:
IF( :SYSTEM.TAB_NEW_PAGE = 'DIV' )THEN
      SHOW_VIEW('USER_ACCESS_TAB');
      GO_BLOCK( 'USRDIV' );
      begin
         GO_BLOCK( 'UDR' );
         execute_query;
      end;

Sometimes when the user is in the divisions block, they go to top people block and execute query (blind query or put a user name and then query). At that point udr block is not querying. I know at this point when-tab-page-changed does not fire. What is the good way to handle this?

Author

Commented:
remove the pre-query trigger

Create a when-tab-page_changed  trigger

option 0  depend only on your default_where  (WHERE  (USRDIV_ID = :USRDIV.ID OR USRCUST_ID = :USRCUST.ID))
begin
GO_BLOCK( 'UDR' );
execute_query;
end;

default_where - what will this be set? (since I am removing all from prequery)

Author

Commented:
default_where - where will this be set? (since I am removing all from pre-query)
Helena Markováprogrammer-analyst
CERTIFIED EXPERT

Commented:
I would use some trigger on "top people block" for setting default where of 'UDR' block- maybe it can be "top people block" pre-query trigger.

Author

Commented:
thanks for all the help

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.