Hello,
I am not a DBA, and I am encountering an issue with an Oracle APEX application that was previously hosted on a Windows server and has now been migrated to an Oracle instance on Linux PaaS.
Scenario: In the application, we have a tab called "History downloaded xls", where when clicking on an .xls file from the list, we can download the file. This functionality was working perfectly in the Windows environment, but in the new Linux PaaS environment, it is no longer working.
The query responsible for listing the files is as follows:
SELECT t1.column_value file_name, t2.constant_value, TO_TIMESTAMP(SUBSTR(t1.column_value,length(t3.constant_value)+1,length(to_char(sysdate,t2.constant_value))), t2.constant_value) file_date FROM table(sys.list_files('APPLICATION')) t1, application_defined_constants t2, application_defined_constants t3 WHERE t2.constant_name = 'generated_file_date_format' AND t3.constant_name = 'generated_file_prefix' AND t1.column_value LIKE t3.constant_value || '%';
Problem: When I try to run this query in production on the Linux PaaS environment, I receive the error:
ORA-00904: "SYS"."LIST_FILES": invalid identifier 00904. 00000 - "%s: invalid identifier"
This error also occurs when I run the query directly in SQL Developer, outside of APEX. However, in the production Windows environment, the query works fine both in SQL Developer and in APEX (SQL Workshop).
Investigation: In the production Windows environment, upon investigating the LIST_FILES function, I found the following code in DBA_SOURCE:
SELECT text FROM dba_source WHERE name = 'LIST_FILES' AND type = 'FUNCTION' ORDER BY line;
The body of the LIST_FILES function is as follows:
FUNCTION list_files (p_directory IN VARCHAR2)
RETURN file_array pipelined AS
path VARCHAR2(1024);
path_length number;
ns VARCHAR2(1024);
BEGIN
SELECT directory_path, length(directory_path) INTO path, path_length
FROM dba_directories
WHERE directory_name = p_directory;
SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(path, ns);
FOR file_list IN (SELECT substr(FNAME_KRBMSFT, path_length + 2) AS file_name FROM X$KRBMSFT) LOOP
PIPE ROW(file_list.file_name);
END LOOP;
END;
When I run the query SELECT * FROM X$KRBMSFT, the query returns empty, but does not throw an error.
In the new paas environment I tried running:
GRANT SELECT ON X$KRBMSFT TO APEXUSER;
But received the error:
ORA-00942: table or view does not exist
Environment Differences:
- In the production Windows environment, the files are stored in the path:
E:/eli/movel.
- In the Linux PaaS environment, I set the directory to
/tmp/, which I know exists on the database server, but the functionality still doesn't work.
Request: I would like to know if there is a solution that does not require involving the DBA. I need a solution that allows me to test and validate the functionality without requiring any changes at the database administration level. I have a user with good permissions (SQL Developer), but I do not have access to the server (SQL Plus).
Thank you in advance for your help!