Feed aggregator

I would like my schema to delete rows automatically after a certain number of days. Therefore, I was planning to use this statement for example: <code> ALTER TABLE MY_TABLE USING TTL 5 days </code> However, I'm unable to find specifics in the TTL documentation about how FK relationships are handled in this scenario. Will TTL automatically ignore these FK relationships when it reaches that date and expire these rows anyway? (OR) Will TTL fail, as a delete would fail, because these FK relationships exist and need to be removed first?

Hi, I would like to create initally deferred constraint for FK as below. The problem is, that the constraint cars_service_car_id_fk is validated after DELETE statement, not at the end of transaction(COMMIT). I don't understand this behavior... <code> DROP TABLE cars; CREATE TABLE cars( car_id INTEGER , name VARCHAR2(20) , CONSTRAINT cars_pk PRIMARY KEY(car_id) ); DROP TABLE cars_service; CREATE TABLE cars_service( service_id INTEGER , car_id INTEGER , CONSTRAINT cars_service_pk PRIMARY KEY(service_id) , CONSTRAINT cars_service_car_id_fk FOREIGN KEY (car_id) REFERENCES cars(car_id) <b>DEFERRABLE INITIALLY DEFERRED</b> ); INSERT INTO cars(car_id, name) VALUES(1, 'Volvo'); INSERT INTO cars_service(service_id, car_id) VALUES(1, 1); COMMIT; DELETE FROM cars WHERE car_id = 1; -- And now Iim getting error to early.. <b> DELETE FROM cars WHERE car_id = 1 Error report - ORA-02292:</b> </code>

I am facing issue of "Pooled connection request timed out" when I upgraded database to 19c and moved the application to cloud(TAS) from VM. There is no fixed scenario when this happens. No code changes has been done except replacing DataAccess.dll to ManagedDataAccess.dll. SGA is set to 2GB. TAS memory is 2GB. Could you suggest me what configurations has to be setup or checked to support pooling, as I am developer and can delegate the same to DBA to have such configurations on database

Hello Chris/Connor, I am trying to use the option MY_WALLET_DIRECTORY in TNSNAMES file in order to avoid having to specify WALLET_LOCATION in SQLNET.ORA file. However, it fails with ORA-01017: invalid username/password; logon denied error. Tracing the SQLNET (from client side) reveals that it never attempts to access wallet when specified as MY_WALLET_DIRECTORY but works fine when used conventionally (as WALLET_LOCATION in SQLNET.ORA file). Can you please help me figure out how to get this working? In MOS note <b>The Impact of the Sqlnet Settings on Database Security (sqlnet.ora Security Parameters and Wallet Location) (Doc ID 1240824.1)</b>, it appears to mention that using MY_WALLET_DIRECTORY is supported (excerpt below) <code>#8. Client settings. OCI Clients are using the same sqlnet.ora and wallet settings as the database server. However, a very good hint to use, when multiple wallets should be used by the same client and using multiple TNS_ADMIN environment variables can become a real pain (one example would be when using ODP.NET applications requiring multiple SSL certificates to logon to multiple services, especially on application servers), then this could be easily solved using a tnsnames.ora syntax similar to the one below: <tns_alias> = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = <machine_name>)(PORT = <port_number>)) (CONNECT_DATA = (SERVICE_NAME = <service_name>) ) (SECURITY = (MY_WALLET_DIRECTORY = <some_wallet_directory>)))" </code> Below is my attempt First, to prove that the wallet is set up correctly and WALLET_LOCATION mentioned in SQLNET.ORA. <code>C:\Users\JoeBlog\TNSHome\ScottWallet>copy ..\sqlnet.ora . 1 file(s) copied. C:\Users\JoeBlog\TNSHome\ScottWallet>set TNS_ADMIN=C:\Users\JoeBlog\TNSHome\ScottWallet C:\Users\JoeBlog\TNSHome\ScottWallet>tnsping DB_SCOTT TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 11-JUN-2021 15:02:41 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: C:\Users\JoeBlog\TNSHome\ScottWallet\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbscan.oracle.world)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbserv.oracle.world.com)) (SECURITY = (MY_WALLET_DIRECTORY = C:\Users\JoeBlog\TNSHome\ScottWallet))) OK (70 msec) C:\Users\JoeBlog\TNSHome\ScottWallet>sqlplus /@DB_SCOTT SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 11 15:02:51 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Fri Jun 11 2021 10:03:06 +01:00 Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.10.0.0.0 SQL> show user USER is "SCOTT" SQL> exit Disconnected from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.10.0.0.0 </code> Now, when I try to use only MY_WALLET_DI...

I will preface this with not having had tested SQL*Plus scripts on Windows utilizing script arguments in a number of years so I can't categorically state when this stopped working on said platform. That being said, the issue described does not occur on any *nix system. Additionally, this is running the SQL*Plus from the Instant Client not the Full Client. For Windows-based SQL*Plus scripting if you plan to use script arguments such as (being executed as "sqlplus <username>/<password>@<tns_alias> @test.sql 1"): <code> select to_number( '&1' ) from dual; </code> You get the following: <code> SQL> select to_number( '&1' ) 2 from dual; select to_number( '&1' ) * ERROR at line 1: ORA-01722: invalid number </code> as it is not translating the '&1' to mean the first script argument. If you add "set define '&'" to the options setting up the SQL*Plus environment then it runs as expected: <code> old 1: select to_number( '&1' ) new 1: select to_number( '1' ) TO_NUMBER('1') -------------- 1 1 row selected. </code> My question is why is it only mandatory on Windows to purposefully set DEFINE to its default value ('&')? I struggled with this for many weeks as there wasn't even a thought in my head I needed to set DEFINE to its default value as it makes no sense to do so. Only after re-reading the SQL*Plus User's Guide for the thousandth time did I say what the heck and as soon as I did it worked. Either this needs to be fixed or the documentation needs to address this because in theory SQL*Plus scripts, short of shelling out with HOST, should be portable.

Hola! en Apex 23.1 Estoy fallando al intentar ejecutar un metodo GET luego de crear desde Componentes Compartidos >> Origenes de Datos una conexion a un servicio web http (no seguro) publico. El metodo GET lo pruebo desde la opcion de edicion. Y lo he probado tambien desde un boton en mi pagina. Aclaro que los servicios https que he creado me funcionan correctamente. Necesito saber si hay que realizar alguna configuracion para estos casos.. si me pueden aportar alguna documentacion.. se los agradezco!! Estoy trabajando en https://apex.oracle.com/pls/apex/r/apex/workspace Dejo el error: is_internal_error: false ora_sqlcode: -403 ora_sqlerrm: Forbidden component.type: APEX_APPLICATION_PAGE_PROCESS component.id: 37623776800238671091 component.name: Nuevo error_backtrace: ----- PL/SQL Call Stack ----- object line object handle number name 0x3709919170 590 package body APEX_230100.WWV_FLOW_INVOKE_API_PROCESS.INVOKE_API_REST 0x3709919170 639 package body APEX_230100.WWV_FLOW_INVOKE_API_PROCESS.INVOKE_API_PROCESS 0x3256fedff0 1295 package body APEX_230100.WWV_FLOW_PROCESS_NATIVE.EXECUTE_PROCESS 0x35e89acdf8 3443 package body APEX_230100.WWV_FLOW_PLUGIN.EXECUTE_PROCESS 0x34524835b0 189 package body APEX_230100.WWV_FLOW_PROCESS.PERFORM_PROCESS 0x34524835b0 479 package body APEX_230100.WWV_FLOW_PROCESS.PERFORM 0x38de05fa70 4233 package body APEX_230100.WWV_FLOW.ACCEPT 0x38bdd2eaf0 2 anonymous block

I want to talk about the foibles of DIRECTORY creation in the Oracle database. This is not a 23c specific issue but one that goes back years. I want to understand what rights and objects are created when we make....[Read More]

Posted by Pete On 26/05/23 At 01:29 PM

Does oracle apex 23.1 data load wizard support excel files like the data loader in the SQL Workshop?

How to create a user for eBusiness DB account equivalent to APPS user but only with select and execute on FND, INV and APPLSYS objects?

I have the following query: select OPERATION_CODE, to_char(OPERATION_DATE, 'dd/mm/yyyy hh24:mi:ss') as OPERATION_DATE, EMP_CODE from HR_ORIGINAL_ATTENDANCE where EMP_CODE = 4415 and to_char(OPERATION_DATE, 'yyyymmdd') = 20230517 order by OPERATION_DATE; And I have the following output: OPERATION_CODE OPERATION_DATE EMP_CODE 1 17/05/2023 07:08:03 4415 1 17/05/2023 07:55:15 4415 2 17/05/2023 08:00:00 4415 1 17/05/2023 15:07:01 4415 2 17/05/2023 16:00:00 4415 2 17/05/2023 16:58:27 4415 2 17/05/2023 17:26:05 4415 1 17/05/2023 20:00:00 4415 The report query I want is as follow: Entrance Leave 17/05/2023 07:08:03 17/05/2023 08:00:00 17/05/2023 15:07:01 17/05/2023 17:26:05 17/05/2023 20:00:00 Witch meaning that, the first attendance period row is the min entrance and the max leave before the next entrance period start. Where the leave can be overrided by the user, but if the user entrance more than one time without leave, I want to select the min entrance. I hope it is clear. Thank you. The above was my question in an old thread, and Chris answer me with the following: select * from hr_original_attendance match_recognize ( order by operation_date measures first ( op1.operation_date ) st_dt, last ( op2.operation_date ) en_dt pattern ( op1+ op2+ ) define op1 as operation_code = first ( operation_code ), op2 as operation_code <> first ( operation_code ) ); The result was: ST_DT EN_DT -------- -------- 17/05/23 17/05/23 17/05/23 17/05/23 And Connor follow up, and answer me the following: with prep (operation_code, operation_date, emp_code) as ( select operation_code, operation_date, emp_code from hr_original_attendance where emp_code = 4415 and operation_date >= date '2023-05-17' and operation_date < date '2023-05-18' ) select emp_code, entrance, leave from prep match_recognize( order by operation_date measures first(emp_code) as emp_code, first(one.operation_date) as entrance, last (two.operation_date) as leave pattern ( one* two* ) define one as operation_code = 1, two as operation_code = 2 ); The result was: EMP_CODE ENTRANCE LEAVE ---------- ------------------- ------------------- 4415 17/05/2023 07:08:03 17/05/2023 08:00:00 4415 17/05/2023 15:07:01 17/05/2023 17:26:05 4415 17/05/2023 20:00:00 the exactly was I need. Thank you very much Chris and Connor. The new question is that: When I have oracle 11gr2 version, how to rewrite the query? Thank you.

We have been using an older version of Apex for about 10 years now and now want to set up email capability for sending reports as attachments. I have scoured the internet for the requirements to use but am getting confused with all the different answers. We need to connect to an external smtp server. What exactly do I need to get in place to get this to work on this older version of Apex? Please let me know if you need more info to better answer this question. Thank you.

Hi, I have column having data like below. 101010 10203040 2030405060 etc From each row above I have to break as below to check whether that number exist in a table or not, if yes skip and if no then insert. E.g. for first row 101010 First I have extract two digit ie 10 and check if exist in a table or not and if not then insert if yes then skip. Second time i have to take first 4 digital eg 1010 and verify whether exist in a table or not. Similarly I have to look 101010 last time for first row to verify if it exist or not. Can you please help how to achieve this. Thanks.

What do we mean by instance caging in context of oracle database? How it works? Kindly elaborate with examples?

Whilst the 23c version shipped by Oracle is a free developer release we should not complain as its free and we should also recognise that this is not production and this 23c version is not intended to be used in....[Read More]

Posted by Pete On 23/05/23 At 02:09 PM

Dear Sirs, I'm using database version 19c and trying to set a filter on schema level before inserting into UNIFIED_AUDIT_TRAIL view. I tried these example policies but without success: <code>CREATE AUDIT POLICY test1 ACTIONS UPDATE ON <schemaname>.* WHEN 'INSTR(UPPER(SYS_CONTEXT(''USERENV'',''CLIENT_PROGRAM_NAME'')), ''FRMWEB'') = 0' EVALUATE PER SESSION ONLY TOPLEVEL; CREATE AUDIT POLICY test2 ACTIONS UPDATE ON <schemaname> WHEN 'INSTR(UPPER(SYS_CONTEXT(''USERENV'',''CLIENT_PROGRAM_NAME'')), ''FRMWEB'') = 0' EVALUATE PER SESSION ONLY TOPLEVEL;</code> Also there is in my opinion no paramter, like obj_owner, in the namespace userenv in sys_context. Do you have a hint please or is logging on schema level not possible? Many thanks Juergen

Are you looking to take your PeopleSoft Development skills to the next level? Look no further than our All-access Learning Membership.

With this membership, you'll gain access to a wealth of resources to help you improve your PeopleSoft Development knowledge and skills. From video tutorials to code samples and webinars, you'll have everything you need to become a PeopleSoft Development pro.

The membership also includes access to PeopleSoft experts, where you can ask questions and get feedback.

But what sets the All-access Learning Membership apart from other online learning platforms is its focus on real-world applications. You'll learn how to use PeopleTools to build practical, functional applications that you can use in your own projects.

And with new content added regularly, you'll always have something new to learn and explore.

So if you're ready to take your PeopleTools skills to the next level, sign up for the All-access Learning Membership at https://www.jsmpros.com/groups/all-access/. Your future self will thank you!

Oracle GoldenGate Service is Oracle’s cloud offering  to quickly use GoldenGate to move data within OCI as well as other […]

The post Manually purging trail files from OCI GoldenGate Service appeared first on DBASolved.

This post is part of a series that starts with this post.

Having gone through much of what can be done with a basic REST-service earlier in this series, it is time to look at securing the service. When you can access a service with noting noire than just the URL, then so can anyone else that has access to sen toe ORDS-server the URL. Not only can they read, but if the view allows writing then they can do that too as an autoREST view has DML capability too in the generated API.

In the example used for this series it will not work as the view goes against a view Oracle has defined and that cannot be updated by you as a user. However, you will typically want to protect read as well as write for your services. That is what we’ll achieve with securing the service. In this post we’ll secure it from access and in the next we’ll look at accessing the secured service.

To secure a service we create a role, and a privilege and then define what it will protect, in our case we’re protecting a path in the URL.

Let’s start with creating a role. It is done by just giving it a name, nothing else is needed.

Remember, we protect the service in the source database/schema. That is rest_demo with the alias rest_demo_schema in the setup for this series.

exec ords.create_role(p_role_name => 'gnome_role');

Why gnome you ask? Well, there is a certain theme to the images in this series.

Now, the next step is to set up a privilege that is used to protect the service.

begin
  ords.create_privilege(
      p_name        => 'gnome_priv',
      p_role_name   => 'gnome_role',
      p_label       => 'Gnome Data',
      p_description => 'Give access till demo data.');
end;
/

With that all there is left is to define what it should protect. With an AutoREST-enabled view our only option is to define a path for the URL to protect.

begin
  ords.create_privilege_mapping(
      p_privilege_name => 'gnome_priv',
      p_pattern        => '/vw_rest_svc/*');
end;
/

With that the service on that path is now protected. Note that the pattern is within the schema-alias. It starts from that point in the URL so it does not work to have /ords/… That is good as it allows the alias for the schema to be changed without the security being side stepped.

All that is left now is to verify that the service is in deed not available anymore.

curl https://.../ords/rest_demo_schema/vw_rest_svc/ | python3 -m json.tool 

{
    "code": "Unauthorized",
    "message": "Unauthorized",
    "type": "tag:oracle.com,2020:error/Unauthorized",
    "instance": "tag:oracle.com,2020:ecid/039ed419abad226de418d37c6f146756"
}

Great, the service is now protected. In fact, it is so well protected that there is no way to access it. Setting up to allow access is where we pick up in the next post.