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 );

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

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:

https://jeffkemponoracle.com/2012/11/select-all-unselect-all-checkbox-in-interactive-report-header/ 

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

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 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

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

Dynamische Navigation - dynamic navigation

Business Case:
Bei einer größeren Anwendung mit mehreren Modulen/Abteilungen kommt es immer häufiger vor, dass aus MEHREREN Reports auf EINE Form (DML) verlinkt werden soll.
Der Benutzer möchte aber nach der Nutzung der Form auf "seine" Seite mit dem Report zurückkommen.
Mit Seiten im "Modal"-Modus geht das (eingeschränkt)..

Great Thanks to Roel Hartman for his Tipps in the APEX Forum concerning this!.

Hier nun eine "dynamische" Variante:

Auf der Seite mit der "Form" fügen wir ein neues Item ein (auf meiner Seite 43):
P43_PREV_PAGE vom Typ "hidden/ausgeblendet" - dieses Item soll die Seitennummer speichern, von der ich komme.

Wenn wir jetzt verlinken von einer Spalte im Report wählen wir für die Spalte:
bei "Identification", Type: "link" und bei dem Attribut "Link" sehen die Parameter dann so aus bei einer Verlinkung nach Seite 43:



Auf meiner Seite 43 passe ich das "Branching/Verlinken" an:

Im Detail: &P43_PREV_PAGE.

APEX 5.1 - Show Region - start hidden - upon change of value

APEX 5.1.1

Business Case: 
Upon selection of a specific service, the user is prompted to fill in 3 extra fields in a region...

Challenge: 
SHOW the "hidden" region upon change of a value

Solution:
Change template to hide a region by default - create Dynamic Action (DA) to unhide

----

As APEX does not dispose of a region, which is hidden by default, we have to "hide" a region first by changing the Region Template.

Thanks to Matt Mulvaney, who figured that out in his blog (also for buttons and labels).

Step 1: Change Template
In the Template section we have to add  style=”display: none;”  just before the last right-chevron character (>) on the first line.

So I chose a Region Template of template Class "Form Region" and copied it to read:

I added the   style=”display: none;”  to look as follows:

Step 2: Apply region template

We select the region on our page and apply this new template: "Form region start Hidden"

Step 3: Create a Dynamic Action

1. select the region to show (or un-hide...) containing the fields
2. in the left panel right click on the region and select "Create Dynamic Action"
3. select the properties in the right panel.... my case:

As you can see, when values 7 or 101 are entered in the item/field the DA shall fire....

4. Go back to left panel and select the DA - right click and "Create TRUE action"

This having achieved, we have to create the  "Hide" action, to hide the region again, when a different value is selected:

5. In the left panel select DA again and right mouse click on it and select:

"Create Opposite action"

That's it. I think it is a cool and useful feature. Have fun !!!

APEX 5 - Quickpicks for Date_Picker

APEX 5.1.1 - Universal Theme 4.2

When using the Quickpicks feature for a field - which is quite useful ! - I wonder which value to use to achieve SYSDATE -1 for a date_picker field...?

I did ask @ the apex discussion forum....

and myself solved it as follows:
1) I created a hidden Item "P1_DATE_DEFAULT_0" to hold the default value with source = pl/sql expression : TO_DATE(SYSDATE)


2) I created a hidden Item "P1_DATE_DEFAULT_1" to hold the default value with source = pl/sql expression : TO_DATE(SYSDATE) -1

3) I created a hidden Item "P1_DATE_DEFAULT_2" to hold the default value with source = pl/sql expression : TO_DATE(SYSDATE) -2

4) Quick Picks Label_1: Today - value: &P1_DATE_DEFAULT_0.

5) Quick Picks Label_2: Yesterday - value: &P1_DATE_DEFAULT_1.

6) Quick Picks Label_3: The day before yesterday - value: &P1_DATE_DEFAULT_2.

In my (german) page it looks like:

and works well.

APEX 5 - Dynamic Action - Refresh Classic Report

Für APEX 4 und die ersten Gehversuche von Dynamic Actions gibt es ja eine Vielzahl an How Tos und Documentation... - aber bei APEX 5 wurde ich nicht so richtig fündig und einiges sieht doch jetzt anders aus - speziell mit dem neuen UI Developer Designer.

Edited 13.5.2017 - Sehr hilfreich finde ich diesen Tipp (Grafik),den ich bei meinen Recherchen fand (Source kommt später..- sorry):

Dynamic Actions funktionieren immer in 3 Schritten:

Die Aufgabe/Frage: 
Wie definiere ich eine Dynamic Action (DA) für eine SelectList, damit ein Classic Report "refreshed"...?!

(Anmerkung: ich weiß, ich kann auch eine SelectList mit Page Action "Submit Page" definieren - aber DA finde ich smarter und cooler, zumal nicht die ganze Region "zuckt" und neu geladen wird...)

Es geht natürlich los mit einem Classic Report und einem SQL Query:

Schritt 01: (in unserem Beispiel: Seite 4)

select 
 id
, name
, bemerkungen
  from table 
    where id =:P4_BRANCHE

Unter dem SQL-Query (rechts) bei "Page-Items to submit" muß "P4_BRANCHE" ausgewählt bzw. eingetragen sein, sonst "funzt" es nicht...

Schritt 02:

Wir legen ein Item vom Typ  SelectList an und nennen ihn P4_BRANCHE mit einer LOV vom
Typ SQL-Query: 

select name as d, id as r from table order by id


Schritt 03:

Wir selektieren P4_BRANCHE und mit rechter Maustaste bekommen wir das Kontextmenü:


































Wir klicken: "Create Dynamic Action" - rechts bei "Identifikation" nennen "NEW" um in: "Report_Refresh".
Die "When" Parameter sollten dann so aussehen:


















Dieser Typ DA ist automatisch vom Typ "TRUE"





















Schritt 04:

Wir klicken links bei der DA unter TRUE auf "Show": 










Und gehen wieder rechts ins "Detail-Pane" - und 
ändern die vorbelegte Action von SHOW auf REFRESH.

Bei "Affected Elements"  wird "Region" ausgewählt (worin unser Report liegt)
und der Name des Reports: "Branchen-Report"

Bei "Event" verweisen wir auf den oben angelegt Event: "Report_Refresh" und 
"Fire when Event result" :True (sollte default-mäßig auf "True" stehen).

Dann noch: "Fire on Initialisation:" ->  "Yes"

Fertig sieht es dann so aus:

Mal speichern..... (;-) und testen....

Ergebnis: