Hi I have a table in Oracle APEX to store documents as well as their revisions:

CREATE TABLE M_DOCUMENTS
(
    DOCUMENT_GUID                   VARCHAR2(38) DEFAULT ON NULL sys_guid(),

    DOCUMENT_TITLE                  VARCHAR2(100) NOT NULL,
    DOCUMENT_DESC                   VARCHAR2(2000),
    LATEST_REVISION_GUID            VARCHAR2(38),
    
    constraint M_DOCUMENTS_PK primary key (DOCUMENT_GUID)
)
;

and a table to store revisions of said documents (as well as fk constraint on M_DOCUMENTS for the subject of this question):

CREATE TABLE M_DOCUMENT_REVISIONS
(
    REVISION_GUID           VARCHAR2(38) DEFAULT ON NULL sys_guid(), 

    DOCUMENT_GUID           VARCHAR2(38) NOT NULL,
    REVISION_TITLE          VARCHAR2(100) NOT NULL,
    REVISION_DESC           VARCHAR2(2000),
    REVISION_DOWNLOAD_LINK  VARCHAR2(2048),
    
    constraint M_DOCUMENT_REVISIONS_PK primary key (REVISION_GUID),
    constraint M_DOCUMENT_REVISIONS_FK_DOCUMENT_GUID foreign key (DOCUMENT_GUID) references M_DOCUMENTS (DOCUMENT_GUID)
)
;

ALTER TABLE M_DOCUMENTS ADD CONSTRAINT M_DOCUMENTS_FK_LATEST_REVISION_GUID foreign key (LATEST_REVISION_GUID) references M_DOCUMENT_REVISIONS (REVISION_GUID);

CREATE OR REPLACE EDITIONABLE TRIGGER AI_M_DOCUMENT_REVISIONS
AFTER INSERT ON M_DOCUMENT_REVISIONS
FOR EACH ROW
DECLARE
BEGIN            
    UPDATE M_DOCUMENTS
    SET LATEST_REVISION_GUID = :new.revision_guid
    WHERE DOCUMENT_GUID = :new.DOCUMENT_GUID;
END
;
/

ALTER TRIGGER AI_M_DOCUMENT_REVISIONS ENABLE
;

As you can see the latest revision is stored as a field in the M_DOCUMENTS table and the M_DOCUMENT_REVISIONS table has an after insert trigger that updates the parent documents latest revision. When thinking of handling deletes of revisions and realizing I need to create a before delete trigger to remove the latest revision relation if it exists and update the latest revision again, I began to wonder if this is the right way to approach this. Is there any better ways of doing this? Some things off the top of my head are: 1. Manually calculating the latest revision from its create date (not shown in the tables above) 2. Materialized view of 1. 3. Creating another table to store latest revision for each document and adding a unique constraint on it on the parent guid. 4. Letting the revision itself store whether or not its the latest revision. 5. Letting the revision store what revision order it is (the number revision it is. e.g. 1st revision, 2nd revision) My ideal method should be quick (in run time and in development time), have an easy way to delete revisions and retrieve the new revision, and should also be good to use with Oracle APEX development.

I'm trying to deploy Oracle APEX 18 on Windows 8.1 PC using Oracle REST Data Services and Oracle Database 18c XE following this guide . But when I'm trying to run @apex_rest_config.sql I get this error - Enter: GetConsoleMode failed, LastError=|6| at Drive_letter:/DB_install_folder/dbhomeXE/perl/site/lib/Term/ReadKey.pm line 334. In CMD it looks like this: Every apex_rest_config#.log file looks similar to this: What could be causing this problem? Why @apex_rest_config.sql doesn't ask new passwords for APEX_LISTENER and APEX_REST_PUBLIC_USER, as it does in the guide? Does it have something to do with this ORA-03113: end of file on communication channel error? Is there any fix or workaround? **Update 1:** trying to implement this solution.

i'm trying to hash or encrypt the password provided by the user in my apex oracle before store it in the DB table How can i do it ? i tried using DBMS_CRYPTO but it always generate an error saying identifier 'DBMS_CRYPTO' must be declared i have tried to grant execute privilege to PUBLIC and Administrator but no luck there

What could I change to make this insert work and give a new ID value on each row I am inserting? Right now it's giving all the inserted rows the same ID number. Sadly, I can't use triggers and sequences so that's not an option.

INSERT INTO shema1.W_ITEM_TABLE("C_ID", "KID", "Number", "LastStatusTime", "AddTime", "Status")
select (select max("C_ID")+1 from shema1.W_ITEM_TABLE), KID, TALRUNIS, NULL,NULL,NULL
from ROBO_SYSTEM;

TABLES:

shema1.W_ITEM_TABLE("C_ID", "KID", "Number", "LastStatusTime", "AddTime", "Status")

ROBO_SYSTEM(KID, TALRUNIS,)

1 I have installed Oracle Database 21c Enterprise Edition on Oracle windows 10 I am now trying to install Apex 22.2 . Got to the chapter "5.4.2.3 Running apxchpwd.sql" to set the instance administrator password. Executed the @apxchpwd.sql command, it asks me for user name, e-mail and password but then it gives an error as quoted string not properly ended, also Tried various other passwords, nothing seems to be acceptable. What can be the problem?

I have a Trigger and Function which sends mail with "updated" column(s), when i am updating in my back-end (sql developer), i receive a mail with that particular column. But when i do UPDATE directly through Apex Page i get mail listing all columns names indicating all are updated. **Function Code:** create or replace function SNAP_FUN(inTableName in varchar2) return varchar2 is result varchar2(4000); sep varchar2(2) := null; begin for c in (select column_name from user_tab_columns where table_name = inTableName) loop if updating(c.column_name) then result := result || sep || c.column_name; sep := ', '; end if; end loop; return result; end; / **Trigger** create or replace trigger SNAP_TRIG after update on dw.W_SUPPL_SNAPSHOT_CONTROL_G for each row DECLARE X VARCHAR2(4000); begin begin X:=X||('Updated:' || snap_fun('W_SUPPL_SNAPSHOT_CONTROL_G')); end; dlr_send_mail('***.com', 'SUPPLEMENTAL_SNAPSHOT', 'These are the column changes observed' || X , '****', 'host') end;

I have installed Oracle Database 19c Enterprise Edition on Oracle Linux 7.8.I am now trying to install Apex 20.1 . Got to the chapter "5.4.2.3 Running apxchpwd.sql" to set the instance administrator password. Executed the @apxchpwd.sql command, it asks me for user name, e-mail and password but then it gives an error as if the password does not have enough complexity, although the password I provide is literally H29dN8%Ih^^IV$Y3$j4Mx. Tried various other passwords, nothing seems to be acceptable. What can be the problem? Here is how I got to the error:

[oracle@oraclemachine apex]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 25 09:50:03 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> connect sys as sysdba
Enter password:
Connected.
SQL>
SQL> @apxchpwd.sql
...set_appun.sql
================================================================================
This script can be used to change the password of an Application Express
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN] apexadmin
User "apexadmin" does not yet exist and will be created.
Enter apexadmin's email [apexadmin] a@b.c
Enter apexadmin's password []
--------------------------------------------------------------------------------
PASSWORD_COMPLEXITY_ERROR
* PASSWORD_ONE_ALPHA_ERR
* PASSWORD_ONE_PUNCTUATION_ERR
* PASSWORD_ONE_UPPER_ERR
* PASSWORD_ONE_LOWER_ERR
--------------------------------------------------------------------------------
declare
*
ERROR at line 1:
ORA-20001: Password validation failed.
ORA-06512: at line 30
ORA-06512: at "APEX_200100.WWV_FLOW_FND_USER_INT", line 3744
ORA-06512: at line 20

I am trying to install APEX 19.1 on ORACLE XE 18c but the problem is that APEX_LISTENER and APEX_REST_PUBLIC_USER are not being created. While I execute apex_rest_config.sql script via sqlplus, an error message appears saying: **GetConsoleMode failed, Last Error=|6|** Then I check the list of users and I see that these two users are not created. Is this a bug of APEX installation? Please can anybody help?

If you create an application with "Theme Style Selection" option and "Enable End Users to choose Theme Style" in User Interface Attributes, there's a "Customize" link in the footer, which is referenced by #CUSTOMIZE# substitution placeholder in the template, and the target is javascript:apex.theme.openCustomizeDialog(title, language);. This function then opens a modal dialog (apex.navigation.dialog) with a wwv_flow_customize.show page using these parameters: * p_flow - Application ID, returned by $v('pFlowId') * p_page - Page ID, returned by $v('pFlowStepId') * p_session - Session ID, returned by $v('pInstance') * p_lang - Language two-letter code, from openCustomizeDialog argument > This page could also be opened as standalone if you replace /f?p=... part with /wwv_flow_customize.show?p_flow=... This page contains a form with radio group name="p_themestyle". Labels of the options match with public Theme Styles, but the numeric multiple digit values don't match with Theme Style IDs from wwv_flow_theme_styles table. What I need is a custom theme style switcher frontend. But I can't find the exact JS calls that set certain Theme Style IDs as selected for the current user. "Apply Changes" button just does onclick="javascript:apex.submit('DOIT');". And the desktop_all.min.js file is very minified and obfuscated to properly debug. I also wasn't able to track network activity in debugger as it always resets records after "Apply Changes". * Is there an option somewhere to edit how the "Customize" page looks like? * Is there full documentation for this feature somewhere? * Where and how does APEX store which theme the current user has selected? * What are the options to debug this request and find this particular API call in network activity?

I've installed a plugin in APEX, but when I've tried to use it on a page, I've got some nasty errors and page not working properly. The files are present in the plugin settings: But, as you can see below, they are not being loaded for some reason. I get 404 errors instead. I have ORDS running and APEX 19 installed on DB 19c XE. What could be causing this? Wrong ORDS configuration maybe? How to fix it?

I am creating an Oracle ORDS API using APEX_JSON. I recently started using bind variables instead of string concatenation using

||

. I am trying to use an

clause in my

condition. The problems begin here. The field I need to have on the left side of

is a

and the parameter to my stored procedure needs to be

as it is a comma seperated list of numbers. **Example (edited for brevity)**

CREATE OR REPLACE PROCEDURE GET_CATEGORYPRODS (
    PCATEGORYID IN NUMBER,
    COMMASEPPRODUCTIDS IN VARCHAR2
) AS

l_cursor               SYS_REFCURSOR;
v_stmt_str             STRING(5000);
v_name                 NUMBER; --PRODUCT.NAME%TYPE;
v_displayorder         NUMBER; --PRODUCTCATEGORY%TYPE;
BEGIN
 v_stmt_str := 'SELECT 
    P.NAME, 
    PC.DISPLAYORDER
FROM 
    PRODUCT P
INNER JOIN
    PRODUCTCATEGORY PC
ON P.PRODUCTID = PC.PRODUCTID
WHERE 
   PC.CATEGORYID := :CATEGORYID
AND
   (P.PRODUCTID IN (SELECT * FROM TABLE(STRING_TO_TABLE_NUM(:COMMASEPPRODUCTIDS))) -- PREVIOUSLY WHERE || OCCURRED
        OR (:COMMASEPPRODUCTIDS IS NULL))';

s_counter := 0;

OPEN l_cursor FOR v_stmt_str
        USING pcategoryid, commasepproductids, commasepproductids;

FETCH l_cursor INTO
    v_productid,
    v_displayorder;

APEX_JSON.OPEN_ARRAY;
LOOP
    EXIT WHEN l_cursor%notfound;
    apex_json.open_object;
    apex_json.write('ProductID', v_productid);
    apex_json.write('DisplayOrder', v_displayorder);
    apex_json.close_object;
END LOOP;
apex_json.close_all;

END GET_CATEGORYPRODS;

**Sample of parameters**

'97187,142555,142568,48418,43957,44060,45160,45171,333889,333898'

To handle this problem, I created an aggregate function that takes in a string, splits on the commas, and pipes the row to a custom type. **Custom Type**

create or replace type tab_number is table of number;

**Aggregate Function**

create or replace FUNCTION string_to_table_num (
    p VARCHAR2
)

   RETURN tab_number
   PIPELINED IS
BEGIN
   FOR cc IN (SELECT rtrim(regexp_substr(str, '[^,]*,', 1, level), ',') res
                FROM (SELECT p || ',' str FROM dual)
              CONNECT BY level <= length(str) 
                                  - length(replace(str, ',', ''))) LOOP
      PIPE ROW(lower(cc.res));
   END LOOP;
    
END;

The query slowed down significantly. I figured some optimization was needed but I had never done any sort of optimization before. After some research, I found

PLAN

and ran it on the orginal query. I couldn't get a good answer because of the bind variables, so I decided to run it on the aggregate function. **EXPLAIN PLAN QUERIES**

explain plan for select * from TABLE(string_to_table_num('97187,142555,142568,48418,43957,44060,45160,45171,333889,333898'));

SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY);

When I ran

PLAN

for the aggregate function the results were:

Plan hash value: 127161297
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                     |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| STRING_TO_TABLE_NUM |  8168 | 16336 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

As I stated before, I am a noob to analyzing and optimizing queries, but 8168 Rows and 16336 bytes seems to be a lot for such a simple function. I looked into it, and found that the problem may be the lack of indexing of the pipelined table. I tried to add an index to the type

but it turned it into a PL/SQL object that needed to be declared in a query, not a function. I am pretty lost with this one. If you have any suggestions for any of the scenarios I mentioned, I am all ears. Thanks in advance. **EDIT** After following the steps in Balazs's answer below, I ran

PLAN

for both the aggregate function and the

from my procedure. **Aggregate Function**

Plan hash value: 229973419
 
------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |            |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| F_CONVERT2 |  8168 | 16336 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

**Select Function**

Plan hash value: 1690769838
 
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                                | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                                     |     6 |  2790 |   972   (0)| 00:00:01 |
|*  1 |  FILTER                                 |                                     |       |       |            |          |
|   2 |   NESTED LOOPS OUTER                    |                                     |    65 | 30225 |   972   (0)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER                   |                                     |    65 | 27950 |   842   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                        |                                     |    65 | 27365 |   777   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTCATEGORY                     |    65 |   845 |   712   (0)| 00:00:01 |
|*  6 |       INDEX SKIP SCAN                   | SYS_C0012982                        |    65 |       |   709   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS BY INDEX ROWID        | PRODUCTNEW                          |     1 |   408 |     1   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN                 | SYS_C0013161                        |     1 |       |     0   (0)| 00:00:01 |
|*  9 |     INDEX RANGE SCAN                    | SYS_C0012993                        |     1 |     9 |     1   (0)| 00:00:01 |
|* 10 |    INDEX RANGE SCAN                     | IDX_URLMAPPER_PRODUCTID_FRIENDLYURL |     1 |    35 |     2   (0)| 00:00:01 |
|* 11 |   COLLECTION ITERATOR PICKLER FETCH     | F_CONVERT2                          |     1 |     2 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(:COMMASEPPRODUCTIDS IS NULL OR  EXISTS (SELECT 0 FROM TABLE() "KOKBF$0" WHERE VALUE(KOKBF$)=:B1))
   6 - access("pc"."CATEGORYID"=TO_NUMBER(:PCATEGORYID))
       filter("pc"."CATEGORYID"=TO_NUMBER(:PCATEGORYID))
   7 - filter("p"."PUBLISHED"=1 AND "p"."DELETED"=0)
   8 - access("p"."PRODUCTID"="pc"."PRODUCTID")
   9 - access("p"."PRODUCTID"="pm"."PRODUCTID"(+))
  10 - access("p"."PRODUCTID"="um"."PRODUCTID"(+))
  11 - filter(VALUE(KOKBF$)=:B1)

Do these numbers seem to be within a reasonable range? **EDIT 2**

create or replace PROCEDURE GET_CATEGORYPRODS2 
(
  COMMASEPPRODUCTIDS IN VARCHAR2 
, COMMASEPPRODUCTSKUS IN VARCHAR2 
, PCATEGORYID IN NUMBER 
, SORTBY IN VARCHAR2 
)

....

IF sortby IS NULL OR sortby = 'null' OR sortby = '' THEN
        v_sortby := '"p".Discontinued, "pc".DisplayOrder ';
    ELSIF sortby = 'PriceAscending' THEN
        v_sortby := '"p".discontinued, "p".price ';
    ELSIF sortby = 'PriceDescending' THEN
        v_sortby := '"p".discontinued, "p".price DESC ';
    ELSIF sortby = 'Name' THEN
        v_sortby := '"p".discontinued, "p".name ';
    ELSE
        v_sortby := '"p".discontinued, "pc".displayorder ';
    END IF;
....

I was also using a bind variable to add this to the end of the query.

I'm mocking up some options for a simple, internal reporting front-end for an Oracle database. I have Oracle XE 11R2 on a separate testing machine, and am going through a tutorial on making a simple report in Application Express. To my surprise, Application Express has magically connected to the XE database. I'm guessing this was so easy because the database and Application Express are on the same machine. However, I'm wondering how this would work in a production/network environment. My former solution was an MS Access front-end, which used an ODBC connection to connect to the database. But I don't imagine Application Express would use an ODBC connection. How does Oracle Application Express connect to the database in a production/network environment? Would an equivalent to an ODBC connection need to be deployed on each machine that uses the Application Express report? ----------------------- *Note: I'm well aware that I have no idea what I'm doing. I'm just trying to get a grasp on how this all works, so I can make an informed pitch to my I.T. department. Corrections are welcome.*

I have a requirement in Oracle apex to implement dynamic approval workflow in issue tracker. I am trying to write a trigger for the same as below. I need to further modify it so that it runs sequentially. i.e. When an issue is raised in related dept, it passes through multiple approval levels. Trigger 1 sends email to first approver for approval. When the approver logs in to approve, set p_it_issues.APPROVE_THIS='Y1', the next trigger sets p_it_issues.approved=1 to show it has passed first level. The second trigger sends email notification to second approver as well. (all code mentioned below for reference). But due to varying levels of approval this would means i will have to create more and more triggers. Is there a way to combine this so that it would keep incrementing and sending approvals from p_it_people.approver='Approver 1'.to..'Ápprover n' based on approval_level per department? HR department has n approval levels (p_it_departments.approval_level=n , so it would only send it to the n approvers in p_it_people table with Approver column set as Approver 1 .. n respectively? I need to make this more dynamic, so that in approve_this we have only Y and N. And both Approvers with Approve 1 select Y in approve_This and then it would move to Approver 2 Looking forward to your help.

I work for a small company that currently use Apex to run reports on our accounts system. Oracle is installed on our local server and part of the data is gained via a database link to an access database. We are changing our server and I was interested in trying out the Always Free cloud service to recreate the above. Can anyone give me any pointers on if/how you could connect the cloud service to an access database held on of local server? Please let me know what crucial information is missing from my question - I am sure there is some! Any advice would be gratefully received. Cheers Andrew

I have a problem with with latest version of APEX, I have installed it on my pc, i logged in as and admin created workspace with schema, but when I log in to that workspace as a user with admin's privileges or developer's privileges and try to use SQL Workshop I get an error: > User has no privileges on the schema. Contact your application administrator. When I try create a table with SQL command I get > Schema "DB" does not exist in the database. Please contact your workspace admin to set correct default schema for you. even though user has been assigned that schema and it does exist. When I try to create another schema for the workspace I can't and I get an error > ORA-65096: invalid common user or role name Even when I try create new workspace create workspace I get an error > Error provisioning . ORA-20001: Request 1246482779115523 could not be processed. PROVISION_COMPANY but when I press return and press create workspace again it succeeds for some reason. I have no idea what to do, how to solve this problem, I tried everything in settings: granting privileges, setting default schemas, creating new users, setting role as developers, yet the issues do not go away. Any help is appreciated.

I'm talking about schemas like APEX_040200, APEX_050100, APEX_180300, APEX_190100 etc. I'm trying to install an APEX Plug-in that is being installed from a script, where developper commented Assumes you are running the script connected to SQL*Plus as the Oracle user APEX_040200 or as the owner (parsing schema) of the application. I have APEX 19 installed, so it's APEX_190100 then. Empty password didn't do the trick, default password I don't know. Is there a way to change it's password harmlessly? Or are there any ways to install this plugin to the whole APEX instaince, not only to a certain workspace?

What I'm trying to do is to extend authorisation upon APEX basic users to use it as conditions of pages rendering. I would like to create additional tables to store some workspace specific info about users. Is it possible to reference wwv_flow_fnd_user.user_name or apex_workspace_apex_users.user_name as a foreign key constraint? If yes, how to? If no, what's the alternative to bind APEX user info with my own additional info on database level? I've tried: - CONSTRAINT FK_USER_NAME FOREIGN KEY (USER_NAME) REFERENCES apex_workspace_apex_users (USER_NAME) ON DELETE CASCADE - CONSTRAINT FK_USER_NAME FOREIGN KEY (USER_NAME) REFERENCES wwv_flow_fnd_user (USER_NAME) ON DELETE CASCADE - CONSTRAINT FK_USER_NAME FOREIGN KEY (USER_NAME) REFERENCES APEX_190100.wwv_flow_fnd_user (USER_NAME) ON DELETE CASCADE All of this both as SYSDBA and as an owning schema of an APEX workspace. All I've got so far is a bunch of errors like insufficient priviliges and table or view does not exist.

I'll generate in APEX 5 (Universal Theme) a large table with PL/SQL Dynamic Content. To scroll down, there is a scrollbar on the page. To scroll to the right, there is only a scrollbar in the region/div. So if user want to see the values in the most right column in the first rows, they have to scroll all down, move to the right and scoll all up. Thats very anoying. Any suggestions?

Trying to learn sql queries recently and as the title says I stumbled upon some "behavior" i didn't understand. This query returns the managers from the database. SELECT * FROM employees e WHERE EXISTS (SELECT * FROM employees f WHERE e.employee_id = f.manager_id) However if I switch the aliases in the equality it returns all the employees. SELECT * FROM employees e WHERE EXISTS (SELECT * FROM employees f WHERE f.employee_id = e.manager_id) Isn't employees e and employees f the same table but with different names? If so, why is then the alias order important?

I have configured apex instance to use smtp email, and I can send email with IR when using download option from action menu. But the IR subscription is not sending emails. I have checked there is no record on APEX_MAIL_LOG and APEX_MAIL_QUEUE. Any idea how to troubleshoot problem?