Hi Sir, I have a question about a function that I call in a before-header process to redirect to the appropriate page. create or replace function redirect_page(p_user_name in varchar2) return varchar2 as v_redirect_url varchar2(100); branch_page table_1.PAGE%type; v_state table_1.STATE%type; v_finalpaper_id table_1.fpid%type; begin begin select fp.state, fp.page,fp.id into v_state, branch_page,v_id from table_1 fp left outer join users u on u.uuid=fp.uuid where lower(u.username)=lower(p_user_name); if v_state ='REQUESTED'then v_redirect_url:='f?p=100:95:&APP_SESSION.::NO::P95_ID:'||v_id; elsif v_state ='APPROVED'then v_redirect_url:='f?p=100:92:&APP_SESSION.::NO::P92_ID:'||v_id; end if; exception when no_data_found then v_redirect_url:='f?p=100:1:&APP_SESSION.::NO::'; end; return v_redirect_url; end; Now, when I call the function in the page, I get the 500 Internal Server Error DECLARE v_redirect_url VARCHAR2(100); BEGIN v_redirect_url:=redirect_page_finalpapers(:session_user_name); htp.init(); owa_util.redirect_url(v_redirect_url); apex_application.g_unrecoverable_error := true; END; I don't quite understand why this happening and would appreciate some help on here?
Discy Latest Questions
Hello Sir, I created an interactive report based on a SQL-Query, I then wanted to insert another value from the table into this SQL query. However, when I tried to do this I got the following error: ORA-00904: "D". "SPID": invalid ID. I have already googled it and it says it could be because the wrong column name was entered or it would be solved if an alias was used, which I have already tried but still get the same error. I have also run the SQL query in the SQL command and it shows me the correct result. So what could be the cause of the error?
Hello Sir, I need some help with an interactive report: Here is the use case: I have a table form create table form (FORMID, COLUMN1 varchar2(50), MID NUMBER,MANID NUMBER); MID and MANID are foreign keys to the table MANAGER create table manager (MID number, COLUMN1 varchar2(30)); The manager can have different roles in the form. Depending on the role the id of the manager will be set in different columns of the table. However, all forms for which the manager has been entered,regardless of the role in the form, should still be displayed. Here is the table with some sample data: In the interactive report the manager with the id 15, should see both entries like this: I used the following sql query for the interactive report: SELECT * FROM FORM f LEFT OUTER JOIN MANAGER m ON m.MID = f.MID LEFT OUTER JOIN USERS u ON u.ID = m.ID LEFT OUTER JOIN MANAGER m2 ON m2.MID = f.MANID LEFT OUTER JOIN USERS u2 ON u2.ID = c.ID WHERE lower(u.username) = lower(:session_user_name) OR lower(u2.username) = lower(:session_user_name); and I also tried to use a union select * FROM FORM f LEFT OUTER ...
Hello Sir, I recently read your blog about creating a row button for an interactive report. Create Delete Row Button for Interactive Report in Oracle Apex It is about showing the user all persons who have a similar name to a specified person. This allows the user to decide whether he or she wants to add the new specified user to the database or whether the person is a person who already exists in the system. When clicking on the Row button, an update should take place and the ID of the selected row should be set in the page element. I proceeded as in the tutorial: added dummy column in interactive report select (u.LASTNAME||','||u.FIRSTNAME) as Name, u.EMAIL, u.ID,m.ID 'Update' upd from users u right outer join manager on m.ID=u.MID where utl_match.jaro_winkler_similarity(:P8_LASTNAME,u.LASTNAME)>80; 2. Set Properties for the del column Type: Link Heading: Delete Target > Type: URL URL: javascript:void(null); Link Text: <span class="t-Icon fa fa-user-check update-irrow" aria-hidden="true"></span> Link Attributes: data-id=#MID# 3 Created a page item to hold pk , key column here is MID Name: P8_MID 4.Created Dynamic Action Name: UPD_row Event: Click Selection Type: jQuery Selector jQuery Selector: .update-irrow Event Scope: Dynamic 5. Created ...
Hello Sir, I am trying to set the value in a page item after an insert on a table when clicking a button. For this I created a process with the following pl/sql code, this code is executed when clicking the button add: declare l_user_id number; l_manager_id NUMBER; begin INSERT INTO USERS(ID,FIRSTNAME,LASTNAME,EMAIL) VALUES (USER_ID_SEQ.NEXTVAL,:P1_FIRSTNAME,:P1_LASTNAME,:P1_EMAIL) returning ID into l_user_id; INSERT INTO MANAGER(USERID,MID) VALUES (l_user_id,MANAGER_ID_SEQ.nextval) RETURNING MID into :P1_MID; end; as you can see, I have the primary key of the users table stored in a variable and use it for the manager table, as i need it here as a foreign key. The insert works, and when I check the session state, I also see that the value for the page element P1_MID is set to the correct value, but the field P1_MID remains empty. However, I want the value to be set here, as I want to use it for the rest of the process. Can you help me with this?
Hello Sir, I am sorry to bothering you with this again.. But I'm just not getting anywhere with the following. The issue is that a user logs in and should be redirected to the corresponding page when clicking the 'Go to last status' button. The corresponding page is stored in a table together with the ID and is always updated when the status changes. In order to know which page should be redirected to for this form ID, I use a function that outputs the page as a return value. I use this result to build the URL and save it in a page element. I use URL Identified by Item as the type for the branching process. Unfortunately I always get an error no data found. Here I am not sure how to pass on the ID of the form. I tried these both methods: Building own url DECLARE l_branch_page number; l_id number; begin select ID into l_id from TABLE1 where ID=:P8_ID; l_branch_page:= FORWARDING_TO_PAGE(:P8_ID); :P8_URL :='f?p=&APP_ID.:l_branch_page'|| ':&APP_SESSION.::NO::l_id'|| ':'||:P8_ID; END; 2. apex_page.get_url DECLARE l_branch_page number; l_id number; begin select ID into l_id from TABLE1 where ID=:P8_ID; l_branch_page:= FORWARDING_TO_PAGE(:P8_ID); :P8_URL :=apex_page.get_url(p_page => l_branch_page,p_items=>l_id)||:P8_ID; end; Perhaps an important note, the :P8_ID is set, so the value is known here. Can you please help me here?
Hello Sir, I have the following issue. I have an interactive grid, when clicking on the columns the user is directed to the form. There he can edit the form, e.g. accept or reject it. Depending on this, the user is redirected to another page. I would now like that the next time the person clicks on a column in the interactive grid, this person is redirected to the corresponding page. That is, if he has already edited the form, he should be redirected to the page to which he was redirected when he accepted or rejected the form. I have read your blog on a similar topic https://www.foxinfotech.in/2019/10/create-delete-row-button-for-interactive-report-in-oracle-apex.html and wonder if it would also work for this case or what I would have to change here. I proceeded as described in this tutorial. I use the value of the status from interactive report to redirect to the corresponding page. I use this code snippet from the blog in the interactive report. 5 case when nvl(STATUS,'APPROVED')='APPROVED' then 6 'f?p=&APP_ID.:100:8:&APP_SESSION.::::P8_ID'||ID 7 elsif nvl(STATUS,'REQUESTED')='REQUESTED' then 8 'f?p=&APP_ID.:100:9:&APP_SESSION.::::P9_ID'||ID 9 else 10 '#' 11 end LINK But I always get the error: ORA-00905: missing keyword in line 6. Is something wrong with the URL?
Hello Sir, I am trying to insert data into a read only form using following sql query: begin select s.ID, s.SPID, s.PHONE_NUMBER INTO :P9_ID,:P9_SPID,:P9_PHONE_NUMBER FROM TABLE1 s left outer join TABLE2 f on f.ID=s.ID where f.ID=:P9_TBL2_ID; end; However I always get the error no data found when using the where clause, although the page element has an explicit value (here number 5). When I change the where clause to where f.ID=5; I get the right data inserted into the read only form. Can you please help here?
Hello Sir, I am in the process of implementing a trigger, which is triggered as soon as a change occurs in table 1. The first user in this case is a student, who can edit this table using a form. The trigger then records in a so-called AUDIT table who has done what and when in the form intended for them. However, I have the following problems: - A person can be selected as responsible for several forms - However, if this person edits the table or the form intended for him, the action carried out should only be recorded for this form. At the moment, however, the trigger looks to see in which forms this person is entered as the responsible person and logs it for all the rows it finds. create or replace TRIGGER TRG_AUDIT_TBL1 -- starts on every update or insert command AFTER INSERT OR UPDATE ON TBL1 FOR EACH ROW DECLARE v_user varchar2(30); v_userid USERS.UUID%TYPE; v_done_action varchar2(50); v_stud_id STUDENTS.ID%TYPE; v_resp RESPONSIBLE.ID%TYPE; v_form_id form.id%TYPE; does_exist number; BEGIN v_user := SYS_CONTEXT('APEX$SESSION','APP_USER'); select UUID into v_userid from users where lower(username)=lower(v_user); select count(*) into does_exist from STUDENTS where uuid in (select UUID from users where uuid=v_userid); if (does_exist>0) then select STUID into v_stud_id from students where uuid = v_userid; end if; select count(*) into does_exist from respsonsible where uuid in (select UUID from users where uuid=v_userid); if (does_exist>0) then select r.ID, ...
Hello:) I am trying to update a table with values from a form that is submitted. For this I use the following page process fired when the page is submitted: begin UPDATE TABLE SET COLUMN1 =:P8_COLUMN1, COLUMN2 =:P8_COLUMN2 WHERE TBL1ID =:P8_TBL1ID; end; When submitting the page I'll get the error ORA-06502: PL/SQL: numeric or value error. I already checked if some values do not correspond to the table data type. But I don't know at which line the error occurs, because it is not explicitly shown in the debug information. I only receive information about which error is involved in which process. But not which value triggers this error - component: APEX_APPLICATION_PAGE_PROCESS Insert into DB_Tables (22664009596071946) Can you tell me how I can see which value triggers this error or how I can avoid this error otherwise? I would really appreciate any help:)