Hello Sir, I would like to implement the following. The user has the possibility to select a responsible person with a pop-up LOV, the responsible person can be either a staff member or a manager. Both are stored in different tables, but should be displayed to the user. I use the following sql query for the list of value: SELECT u.Lastname || ', ' || u.Firstname AS displayed, s.STID AS ID from STAFF s left outer join USERS u on s.UUID=u.UUID union SELECT u2.Lastname || ', ' || u2.Firstname AS displayed, m.MAID AS ID FROM MANAGER m left outer join USERS u2 on m.UUID=u2.UUID Depending on what the user selects for this column I want set the Foreign Key either to the table STAFF or to the table MANAGER. This implementation is desirable because the user has the possibility to see all possible responsible persons he/she chooses at once, without the need to specify (Is it a staff member or manager?). So in general, the question arises, can you automatically set a foreign key to the corresponding table based on a lov choise of the user? Another possibility: create two hidden page items, each has a foreign key constraint to the corresponding page. Depending on the return value of the list of value, ...
Discy Latest Questions
Hello Sir, I have the following issue. In a form, the user has the option to enter contact details for a user, but these are not stored in any table, as the user has to register in the system for the first time and give their consent for the first time. As soon as the user submits the forms, a summary of their data is displayed to them, I retrieve this from the tables. However, I cannot retrieve the data for the contact details because they are not stored anywhere. It doesn't work with Collections as it is deleted with each new session. Another idea would be to work with flags, for example, i.e. to first save the contact details and store them in the corresponding tables, but if nothing happens or there is no consent, the user should be deleted better say the data should be deleted. Have you already encountered such an issue and if so, how did you deal with it?
Hello Sir, I have 3 types of users. These perform different actions in a process. Depending on the action, I want to record the pages in a seperate table (table2) (STATE=REQUESTED, PAGE=80,TBL1ID=1). Depending on user logging in, he can read only edit or read only some fields. I created a trigger which creates a new row in the table (table2) when an insertion is made in the other table(table1). However, as soon as an update is made to this table(table1), the row of the other table (table2) should be updated, not whole row columns of the table. So I would like to create a trigger which updates a row depending on value of column of the other table(table1). I tried something like this. create or replace TRIGGER INSERT_PAGE BEFORE update or insert on TABLE1 for each ROW begin if INSERTING THEN INSERT INTO TABLE2 (TBL1ID,STEP,PAGE) VALUES (:NEW.TBL1ID,:NEW.STEP,15); elsif UPDATING and :NEW.STATE='APPROVED' THEN update TABLE2 set PAGE=16 AND STEP1='TEXT123' where TBLID1 =TABLE1.TBL1ID; end if; end; But I am not sure whether a trigger should be the solution here and how in general a trigger with an update statement can be created depending on column value of other table. Can you give me suggestions here?:)
Hello Sir! I would like to insert an entry into a column depending on the column of the same table. For that I thought of creating a trigger. This trigger checks which entry the certain column has in the table and then decides what to enter in another in the same table and in the same row. create or replace TRIGGER INSERT_COLUMN2_TABLE1 BEFORE INSERT OR UPDATE ON TABLE1 FOR EACH ROW begin -- IF :NEW.COLUMN ='REQUESTED' THEN :NEW.COLUMN2 := 'REQUEST_PENDING'; ELSEIF :NEW.COLUMN ='NOT_REQUESTED' THEN :NEW.COLUMN2:='TEXT123' END IF; END; but I get following errors when trying to execute the trigger: PLS-00103: Encountered the symbol "" when expecting one of the following: := . ( @ % ; PLS-00103: Encountered the symbol ";" when expecting one of the following: if as well I think this would be an after insert or update trigger? For the reason that it should be fired when an entry is already done in the column.. But how can I in general reference column in a trigger from the same table where I try to fire a trigger on? Maybe the better question is, how can one column value be populated based on another column in the same table. should I really use a trigger for that? --Used a virtual column for that ALTER table1 ADD ( column2 as ( case when column1 ...
Hello Sir, I have a general question. I would like to create a functiion to insert a value in a table depending on the action of the logged in user. This means when the user clicks on accept, then an entry should be made in the column like 'ACCEPTED'. But when the user clicks on the button denied, then another entry should be made like 'DENIED' in the column. I want then create processes in the page which call the different functions and are executed when a button is clicked. So for example for the button submit I call the function which makes an insert 'ACCEPTED' in the column of the table. For the button denied I call another function, which inserts another entry in the table etc. My general question is: how can different insertions be made in the table depending on the button actions? is the presented approach useful for such an issue?
Hello Sir:) I want to create read only form, which retrieves data from the table. For that I created a region of type form and used SQL-Query as source. The page is rendered as read only always. But now I get displayed empty items, do I have to insert for each page item a default value type for example (sql-query) in order to set the item? or is there a better solution for this?
Hello Sir, to retrieve the status of a form from the logged in user, I did the following: - created application item 'STATE_OF_FORM'. - created application process to set the value into the application item this takes the status from a table and should set it in the application_item. Unfortunately it doesn't work as hoped, I think the error is in the syntax: --this is the code to set stored table status into the status application item declare v_state varchar2(50); begin select fp.STATE into v_state FROM PROCESSSTEPS_FORM fp left outer join USERS u on fp.UUID=u.UUID where u.username=:APP_USER; Apex_Util.Set_Session_State('STATE_OF_FORM', v_state); --exception handling needed otherwise the error 'ORA-01403: no data found' will exception be raised when no_data_found then NULL; end; What do I do wrong?
Hello 🙂 I would like to create a history table in my application, which should indicate when which user has done what. For now, I have created an audit table and a trigger that enters the necessary data into this table after an UPDATE, DELETE or INSERT on the specific table. In the history table, however, I want to log every process step and fill it with the information WHO did WHAT WHEN and display it. This table should be updated in the course of the process, so that one can see which steps have already been taken and by whom. But now I am not sure how to create such a history table database-wise, because I want to log every process step executed by any user and display it in the history tab. Should I create a trigger for this as well and a table for each process step (although that would probably be a bad database design). I'm not asking for any code here, just a hint if it's possible and in best case some keywords so I can search for ...
Hello:) I have one very general question. Since APEX it not like an usual development process, I asking myself how for example the implemented Pages with their regions and page items, the processes and dynamic actions are documented. So that another developer knows exactly what has been done. Is there a standard for how an application should be documented in apex or does anyone know how it is done in practice?
Hello 🙂 I have created forms in which the user can enter data. With collections the information is saved and will be inserted in the corresponding tables after the forms are submitted. Now one column in the table has remained empty and I am not sure how to solve it in APEX. Namely, the table has a foreign key to another table. But the ID of this table is generated only after submitting the forms. Can I solve it, for example, with a trigger that then enters the foreign key into the table after the forms are submitted? Would it be an after insert trigger like this: CREATE OR REPLACE TRIGGER INSERT_FK AFTER INSERT ON TBL1 FOR EACH ROW begin INSERT INTO TBL2 VALUES (:NEW.ID); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line (TO_CHAR (SQLERRM (-20299))); WHEN OTHERS THEN DBMS_OUTPUT.put_line (TO_CHAR (SQLERRM (-20298))); end; or is there another better solution for this?