第25個冬天

Information silo/ Information island 記得之前在公司通靈鼎新Workflow GP過程也是十分艱難，畢竟無對外程式介面可以介接。 使用SQL Profiler來側錄鼎新ERP 底層SQL_通靈的過程 T-SQL筆記36_如何側錄軟體背後執行的SQL 不像 Tiptop 可能還多少有提供程式介面能做二次開發。 最近在研究Oracle EBS如何得知某一個介面底層查詢邏輯、介面程式檔案位置諸如此類問題。 話說Oracle EBS 一整包68G下載解壓到安裝真的有夠久.... Oracle ENS中有所謂「列表值」 (LOV, List of values) Step1.Help  --> About Oracle Applications. 可確認Form Name、Form Path、登入使用者名稱 Step2.對於UI 輸入篩選條件並點擊“查找”按鈕 Step3.打開連接到EBS資料庫模式的資料庫Session後，執行下面的SQL。 檢索由特定用戶名稱在module名稱包含 特定表單名稱的會話中執行的上一個SQL語句。 SELECT ( SELECT to_char(sql_fulltext) FROM v $ sqlarea WHERE sql_id = ses.prev_sql_id) FROM v $ session ses, v $ sqlarea sq WHERE ses.module LIKE '%&form_name%' AND client_identifier = '&user_name' AND sq.sql_id(+) = ses.sql_id; 這部分子查詢 SELECT (SELECT to_char(sql_fulltext) FROM v$sqlarea WHERE sql_id = ses.prev_sql_id) 從v$sqlarea視圖中檢索sql_fulltext，並將其轉換為字符串，對應於ses.prev_sql_id的SQL語句。 sql_fulltext包含SQL語句的全文。 sql_id是SQL語句的標識符。 v$session是一個包含當前會話信息的視圖。 v$sqlarea是一個性能視圖，提供當前...

PL-SQL筆記1_OracleOracle Database 21c Express Edition下載安裝筆記 https://coolmandiary.blogspot.com/2023/04/oracle-database-21c-express-edition.html 在個人電腦上練習Oracle時候 發覺太久沒登入忘記密碼多次輸入後被鎖住了QQ Step1.以管理員身份連接到數據庫 sqlplus / as sysdba Step2.確查目前有哪些帳號被鎖(或指定該帳號名稱)確認當前帳戶的LOCK狀態 SELECT username, account_status FROM dba_users WHERE username = 'username' ; Step3.解鎖用戶帳戶 ALTER USER 用戶名 ACCOUNT UNLOCK; Step4.忘記了密碼，修改用戶密碼 ALTER USER username IDENTIFIED BY new_password; Step5.重登一次試試看就皆大歡喜。

此篇參照  Oracle Database Free Release 23c https://container-registry.oracle.com/ords/f?p=113:4:106243230296983:::4:P4_REPOSITORY,AI_REPOSITORY,AI_REPOSITORY_NAME,P4_REPOSITORY_NAME,P4_EULA_ID,P4_BUSINESS_AREA_ID:1863,1863,Oracle%20Database%20Free,Oracle%20Database%20Free,1,0&cs=3KInUGzFRioTBPWzyCnF11sBPjJgt5JogHOMQfx8UOqSYV_zIKl1H9VvVNvNQyvevJzm49C21fiJk58GUpwDTGw https://container-registry.oracle.com/ords/f?p=113:1:106243230296983:::1:P1_BUSINESS_AREA:3&cs=3BUsuvUmAbuUfPcmKNZ5PLWrQq_X-HM3XGxC5bVcMLbHsQrjsJGuMtRnWJLSqo6gTxEATm9CZMVlsGuo4xgt-Dg 記得oracle官方網站帳戶先註冊準備好 官方網站連結 https://container-registry.oracle.com/ords/f?p=113:10:275482672962::::: container-registry.oracle.com/database/free 是位於 Oracle 的容器註冊處的 Docker 儲存庫。 23.3.0.0 是映像檔的標籤，通常表示軟體的版本 (在此案例中為 Oracle Database 版本 23.3.0.0) 本次是在win11 採用docker 25的版本 Step1.先去登入oracle官方registry docker login container - registry . oracle . com Step2.PULL 特定版本的oracle free docker pull container - registry . oracle . com / database / free: 23.3 ...

方法1.(最全面) select * FROM all_VIEWS where VIEW_NAME = '<view_name>' ; 方法2. select DBMS_METADATA.GET_DDL ( 'VIEW' , 'view_name' , 'owner' ) from dual; 方法3.某view有reference到哪些table跟view呢? select owner as view_schema, name as view_name, referenced_owner as referenced_schema, referenced_name as referenced_name, referenced_type from sys.all_dependencies where type = 'VIEW' and referenced_type in ( 'TABLE' , 'VIEW' ) -- and owner = 'SCHEMA_NAME' -- put schema name here -- and name = 'VIEW NAME' -- put view name here order by owner , view_name; Ref: Get Oracle View Definition with limited control https://stackoverflow.com/questions/31104012/get-oracle-view-definition-with-limited-control Find tables and views used by specific view in Oracle database https://dataedo.com/kb/query/oracle/find-tables-used-by-specific-view

  方法1.藉由V$VERSION 的View SELECT BANNER, BANNER_FULL FROM v $ version ; 方法2.藉由 PRODUCT_COMPONENT_VERSION 的View SELECT VERSION , VERSION_FULL FROM PRODUCT_COMPONENT_VERSION; Ref: https://www.beekeeperstudio.io/blog/how-to-check-oracle-version-and-edition https://database.guide/7-ways-to-check-your-oracle-version/

Table:  Employee +--------------+---------+ | Column Name | Type | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ id is the primary key column for this table. departmentId is a foreign key of the ID from the Department table. Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.  Table:  Department +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ id is the primary key column for this table. It is guaranteed that department name is not NULL. Each row of this table indicates the ID of a department and its name.  Write an SQL query to find employees who have the highest salary in each of the departments. Return the result table in  any order . The query result format is in the f...

Table:  Employee +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ id is the primary key column for this table. Each row of this table contains information about the salary of an employee.  Write an SQL query to report the  n th  highest salary from the  Employee  table. If there is no  n th  highest salary, the query should report  null . The query result format is in the following example. Input: Employee table: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ n = 2 Output: +------------------------+ | getNthHighestSalary(2) | +------------------------+ | 200 | +------------------------+ CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS result NUMBER; BEGIN     /* Write your PL/SQL query statement below */     WITH  real_query...

Table:  Logs +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ id is the primary key for this table. id is an autoincrement column.  Write an SQL query to find all numbers that appear at least three times consecutively. Return the result table in  any order . The query result format is in the following example. Input: Logs table: +----+-----+ | id | num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+ Output: +-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+ Explanation: 1 is the only number that appears consecutively for at least three times.  SELECT DISTINCT num as ConsecutiveNums FROM (      SELECT id, num,      LAG(num) OVER(ORDER BY id) as prev_num,     LEAD(num) OVER(ORDER BY id) as next_num ...

Table:  Scores +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | score | decimal | +-------------+---------+ id is the primary key for this table. Each row of this table contains the score of a game. Score is a floating point value with two decimal places. Write an SQL query to rank the scores. The ranking should be calculated according to the following rules: The scores should be ranked from the highest to the lowest. If there is a tie between two scores, both should have the same ranking. After a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no holes between ranks. Return the result table ordered by  score  in descending order. The query result format is in the following example. Input: Scores table: +----+-------+ | id | score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+...

Table:  Employee +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ id is the primary key column for this table. Each row of this table contains information about the salary of an employee. Write an SQL query to report the second highest salary from the  Employee  table. If there is no second highest salary, the query should report  null .The query result format is in the following example. Input: Employee table: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ Output: +---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+ select salary as SecondHighestSalary from (   select salary, RANK () OVER (ORDER BY salary DESC) RANK from Employee ) where RANK=2 select MAX(salary) "SecondHighestSalary" from employee  where salary < (select max(salary...

Table:  Tree +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | p_id | int | +-------------+------+ id is the primary key column for this table. Each row of this table contains information about the id of a node and the id of its parent node in a tree. The given structure is always a valid tree.   Each node in the tree can be one of three types: "Leaf" : if the node is a leaf node. "Root" : if the node is the root of the tree. "Inner" : If the node is neither a leaf node nor a root node. Write an SQL query to report the type of each node in the tree. Return the result table in  any order . The query result format is in the following example. /* Write your PL/SQL query statement below */ select id,'Root' as type from tree t where p_id is null union select id,'Inner' as type from tree t1 where exists(select 1 from tree where p_id = t1.id ) and p_id is not null union select id,'Leaf' a...