Translate

Donnerstag, 4. Januar 2018

maintenance information for application down-time

Environment APEX 5.1 (but will work for older versions as well)

In reply to APEX community forum question 

"show a different page for maintenance purposes"

I like to explain my maintenance solution.

I created a "maintenance Info"-Region on the Login page, which is shown during maintenance time (and the app/server is not down) and can be maintained by the Admin/developer without using APEX Developer but from the UI itself.

I experienced, that "system parameters" are quite useful, if the Admin can change them without using the APEX Developer.

6 Steps and you are set:

1. create a table named SYSTEM_PARAMETERS for the SYSTEM_PARAMETERS with a structure like:

ID, name(varchar 50), active (varchar 10 for: yes/no), from (datetime), to (datetime), Info_text_for_users (varchar 500), remarks (varchar 500)

2. create two pages with type form and report to maintain the data

3. On the Login-page 101 create a new region called: "maintenance_region"

4. for the region "maintenance region" create a "server-side-conditon of type "Rows returned" and SQL:

select 1 from SYSTEM_PARAMETERS
WHERE id = '1' --- maintenance works
and active  = 'Yes'

So, when there is a maintenance period switch your system parameter to YES and this region is shown.

5. For the existing "Login_region" create the same as above, but ofcourse set 

... and active = 'No'

6. create a field  P101_INFO_TEXT_FOR_USERS in the "maintenance_region" with type text and 
Source: "Sql Query rerturing single row"  with 
SQL:
select text from SYSTEM_PARAMETERS where id = '1'

In addition (optionally) it proofs well to have "General Info" (like News) region on page No. 1 of the application for announcements such as maintenance and other news. A few days before maintenance starts I usually do announce that. This region can be maintained by the Admins as well and will work similar as the above (table with text/content and valid from/to columns).

Now, as maintenance is carried out (and the application actually is not down), the Admin excluded himself from the application, as the Login region with username/password is not shown anymore...
Workaround: copy page 101 to 102 (before you do the above 6 steps) and when you like to enter (e.g. for testing) your app, you can use page 102 by manipulating the login_url.

Hope that finds your interest.


Dienstag, 7. November 2017

Change APEX_USER password for end users

Environment APEX 5.1.3

Business case: How can the end user change his password?
(in reply to Dennis question in the APEX forum)

Prerequisite: APEX_AUTHENTICATION (default) = APEX_USER created in the workspace

The documention is straight forward:

https://docs.oracle.com/cd/E71588_01/AEAPI/CHANGE_CURRENT_USER_PW-Procedure.htm#AEAPI102


The procedure example code is:

BEGIN
    APEX_UTIL.CHANGE_CURRENT_USER_PW ('secret99'); 
END;
Step by step:

1. create a new page - type "blank" - let's say: Page 9
2. create a region named: Password change region
3. create an item named: P9_NEW_PWD of type "text field"
4. create a button named: "change Password"
5. in Page Processing - Processing - create a procedure named "change_password" of type pl/sql
6. in "PL/SQL Code" enter the above PL/SQL code
7. replace 'secret99' by :P9_NEW_PWD so it looks:

BEGIN
    APEX_UTIL.CHANGE_CURRENT_USER_PW (:P9_NEW_PWD);
END;
8. and last steps:
- the PL/SQL procedure "change_password" selected
- right hand in region "Success message" enter in: "Password changed successfully (:-)"
- in region "error message" key in "nothing done - error"
- in region "Server side condition" and "When Button pressed" select: "change Password"

SAVE everything - Test

That's it.

Result:


What does the procedure do?
It just uses your username (:APP_USER or &APP_USER. ) and replaces the corresponding password with the new Password.

I hope that helps.

BTW: there es another procedure to change the password:
https://docs.oracle.com/cd/E71588_01/AEAPI/RESET_PASSWORD-Procedure.htm#AEAPI29920

There, the user must key in his present password and can enter a new password.

The example code of the procedure:

apex_util.reset_password (
    p_old_password => :P111_OLD_PASSWORD,
    p_new_password => :P111_NEW_PASSWORD );



Donnerstag, 2. November 2017

APEX 5 - Interactive Report - Delete Row feature

In the last post I focussed on Insert and Set value features within the IR.

Now I came across this very straight forward approach on "Delete" ROW... from Farzad.
Thanks so much for your Input!

http://farzadsoltani.com/2017/03/14/checkboxes-interactive-reports-ajax-apex/

Crossposting - sorry - but in short:

Farzard makes use of the  APEX_ITEM.CHECKBOX2

Code:

select 
  CHECKBOX_TABLE.SRL as SRL, 
  CHECKBOX_TABLE.NAME as NAME, 
  APEX_ITEM.CHECKBOX2(p_idx => 1, 
                  p_value => SRL, 
                  p_attributes => 'class="boxes"') AS SELECTOR 
from CHECKBOX_TABLE

Then he creates a pl/sql Process like:

BEGIN 
 FOR I IN 1..APEX_APPLICATION.G_F01.COUNT 
 LOOP DELETE CHECKBOX_TABLE 
 WHERE SRL = APEX_APPLICATION.G_F01(I); 
 END LOOP; 
 htp.p('{ "message": "' || APEX_APPLICATION.G_F01.COUNT || ' rows deleted" }'); 
END;

Thirdly he adds a AJAX Callback... - pls see his posting above.
Sorry for x-posting, but I found this as a need solution and completes the added IR features.

Bernhard


Freitag, 27. Oktober 2017

IR with Checkbox select/unselect to A) set values or B) copy specific records

My environment: APEX 5.1.2.00.09 - DB 11.2

My Business case:
Select records in the IR based on the checkbox and copy these - and assign some new values
(here: Date :P107_GUELTIG_AB,  - means: valid from...)

First build the checkbox for the IR based on Jeffs input:


Jeff uses a combination of DA, JQuery and Javascript and the u.m. pl/sql to set a value

Thanks to Jeff!

A) Process type pl/sql code - to set a value within the report/table. :

DECLARE
  MELD_ID WWV_FLOW_GLOBAL.VC_ARR2;
BEGIN
  MELD_ID := APEX_APPLICATION.G_F01;
  FOR IDX IN 1 .. MELD_ID.COUNT
  LOOP
    IF MELD_ID(IDX) IS NOT NULL THEN
      UPDATE MELDUNGEN
      SET ABGERECHNET='Ja'
      WHERE MELD_ID =MELD_ID(IDX);
        END IF;
  END LOOP;
END;

----
Then to my business case:

Select records in the IR based on the checkbox and copy these - and assign some new values (here: Date :P107_GUELTIG_AB,  - means: valid from...)

B) So have modified the above to copy the selected records/values to another (or the same) table:

Process: type pl/sql code

Syntax:

DECLARE
  LST_DEF_ID WWV_FLOW_GLOBAL.VC_ARR2;
BEGIN
  LST_DEF_ID := APEX_APPLICATION.G_F01;
  FOR IDX IN 1 .. LST_DEF_ID.COUNT
  LOOP
    IF LST_DEF_ID(IDX) IS NOT NULL THEN
 --- here insert starts...
insert into LEISTUNGEN_DEFAULT DEF
(
DEF."LST_DEF_ID",
DEF.KD_FK,
DEF.KD_GR_FK, 
DEF."KD_LST_FK", 
DEF."DEF_BETRAG", 
DEF."DEF_ZEIT", 
DEF."DEF_BEZ_KD", 
DEF."DEF_UMFASST_BEMERKUNGEN", 
DEF."DEF_BEMERKUNGEN", 
DEF."DEF_GUELTIG_VON", 
DEF."DEF_AKTIV"  
)
select
LEISTUNGEN_DEFAULT_SEQ.nextval,
--- some ID's to set
:P107_AK_KD_ID,
:P107_KD_GR_ID,
DEF."KD_LST_FK", 
DEF."DEF_BETRAG", 
DEF."DEF_ZEIT", 
DEF."DEF_BEZ_KD", 
DEF."DEF_UMFASST_BEMERKUNGEN", 
DEF."DEF_BEMERKUNGEN", 
---- Date Picker field on the page
:P107_GUELTIG_AB, 
DEF.DEF_AKTIV  
from LEISTUNGEN_DEFAULT DEF
where  DEF."LST_DEF_ID" = LST_DEF_ID(IDX);
    END IF;
  END LOOP;
END;

To make it working you have to add a Date Picker (or other) field on your page

Freitag, 29. September 2017

APEX 5.1 - Interactive Grid - Edit - two tables

I like to share the following based on explanations in the APEX forum of Sven Weller
(Thanks so much to Sven !)


We can use the Interactive Grid (IG) Edit function also for Views... or for more than one table....

We have to consider, that only columns of the "driving" table can be edited. Otherwise you will get Javascript errors like:

Ajax-Call has Servererror ORA-20987: APEX - ORA-01779: cannot modify a column which maps to a non key-preserved table for submit.

The "key-preserved-table" is in other words the "driving" table.

In a master/detail scenario the the driving table" is the "detail table";
more than one "detail" record may occur for one "master " record 

(e.g. 1 order may have several detail items).
So in an IG one can only edit the columns of the detail items. 


The setup of the attributes of the respective column(s) of the driving (detail-) table is as follows:

First:
Set all relevant columns to: 


Query only: Yes - Primary Key: No


















Those columns you like to edit: set "Query Only: NO"

Second:
The Primary Key columns of the tables must be set to: YES -
(ofcourse for all other columns to: No)


Thats it. Good luck!

Field with textarea only 1 line high

Sorry for mixing german and english in this blog....

I found this very helpful:

Business case:
Input field with character counter needed - but one line high only:
(the character counter is only available in field type: textarea - not: text-field)








Tom W. found out the following :

it seems to me that the css attribute min-height overwrites the textarea-attribute rows (the value you enter in the property for the item).

By resetting the css the textareas height works like you would expect, e.g.

define in page properties -> css -> inline

textarea.textarea { min-height: 0px !important; }

Thanks Tom W. !

Good luck!

BTW: I use APEX 5.1

Mittwoch, 27. September 2017

NULL-WERTE - NULL-VALUES in Interactive Reports, Classic Reports

Altes Thema: NULL-VALUES

Nicht alle Spalten der Tabellen werden im täglichen Gebrauch mit Werten, speziell: "Zahlen" gefüllt.

Will man nun im Interactive Report "Berechnen" (über das Aktionen-Menü), kann es zu falschen oder gar keinen Ergebnissen führen, wenn das System "Null-Werte" (= "weiß nicht genau..") vorfindet. 

Workaround: Eine "0" explizit dort setzen, wo ein Null-Wert ist:

Befehl im Taschenrechner: NVL( AB,0) + NVL(AD,0) oder im

SQL-Report:

select NVL(PREIS,0) as Preis

from TABELLE