Przejdź do głównej zawartości

How to Oracle - en

Szukaj na tym blogu

How to...

Pobierz link
Facebook
Twitter
Pinterest
E-mail
Inne aplikacje

How to index null.
How to load data using SQL
How to load data - SQL: is there any error? [GTT, tab]
Ho to load data- SQL: is there any error? [var]
How to make bubbles or homemade ORDER BY

How to convert a list to a table?

How to measure time - the simplest method!

It won't be faster, how (not) to use bulk collect.

Pobierz link
Facebook
Twitter
Pinterest
E-mail
Inne aplikacje

Komentarze

Prześlij komentarz

Popularne posty

How to check if a row is a continuation of the previous one - part 2.

Some time ago, in 2019, I wrote a post with a query that solved a quite common problem - how to recognize in an SQL query whether rows in a table are a continuation of the previous row. What is it actually about? For example, let's look at an employee with employee_id = 101 and check his employment history (all examples use the HR scheme). SELECT * FROM hr.job_history WHERE employee_id in (101) ; EMPLOYEE_ID START_DA END_DATE JOB_ID DEPARTMENT_ID ----------- -------- -------- ---------- ------------- 101 97/09/21 01/10/27 AC_ACCOUNT 110 101 01/10/28 05/03/15 AC_MGR 110 In 2001 Ms. Kochhar changed her position from AC_ACCOUNT to AC_MGR. Nevertheless, she was still employed. We would like to find the dates of employment of employees from - to, regardless of whether they changed positions or departments in the meantime or not. In 2019, I proposed a way, on an older version of Oracle, how to solve this problem using analytical

How to unlock HR schema? [XE]

Unlocking sample HR scheme in versions older than Oracle 12c was very easy. Just execute from the SYS user: alter user hr identified by hr account unlock; And done! - HR scheme ready to go. In 12c, when the containers came in, things got complicated: alter user hr identified by hr account unlock; Error report - ORA-01918: user 'HR' does not exist 01918. 00000 - "user '%s' does not exist" *Cause: User does not exist in the system. *Action: Verify the user name is correct. But I know, that HR is out there somewhere... When we log as SYS user, we log into the CDB database. And there is actually no HR schema. The HR schema has been hidden in the pluggable database. To be able to unblock the HR user, we need to connect to the PDB (pluggable) base. How to do that? 1. Find PDB name as a SYS user execute the SQL SELECT name, con_id, open_mode FROM v$pdbs; Out PDB base is XEPDB1 . 2. Update tnsnames.ora file We need to add our

How to check if a row is a continuation of the previous one - part 1

In database systems (although probably not only in databases) we often specify the beginning and end of something - record validity, employment start and end dates, invoice validity period. Examples could be multiplied. However, even though we specify the start_date and end_date of a single record, it may turn out that one record is a continuation of another. For example, an employed employee worked in one team for 3 years and then, without a break in employment, he change the team. Despite the change of the team, he did not stop being an employee of the company. An example of such data can be found in the HR schema in the HR.JOB_HISTORY table. For the sake of clarity of the example, I will display records for only two employees - 101 and 200. SELECT * FROM hr.job_history where employee_id in (101, 200) ; (I've added a third record for employee 101 with an employment date of 19-DEC-19 to make the example clearer) INSERT INTO job_history VALUES (101, to_date('1

Obsługiwane przez usługę Blogger