最近踩了一個坑，主要是一個預儲程序利用給定的參數來做查詢，再將查詢到的資料寫入變數，原本都還很正常，但是突然失效，偏偏單獨的查詢都很正常，遍尋不著，後來同事協助之後終於脫坑。
create or replace procedure test_proc(p_a varchar2) is
begin
select column into v_column from table where column_a = p_a;
end
上面是一個示意的範例，test_proc這個預儲程序在得到p_a之後就會去資料表查詢，然後將資料寫入v_column，但不知道什麼時候，資料表中被加入一個同名欄位，也就是p_a，這導致了參數中的p_a就失效了。
這可能就代表著在oracle的預儲程序中，整個上下文還是會以查詢資料表為主，查詢資料表沒有對應欄位名稱的時候才會以變數做為條件？
這很頭痛的是，這是無聲異常，而且還可能造成大量異常，不得不防，以後預儲函數的參數必需要設置的非常怪異，才不會有機會跟未來人命名欄位名稱相同。
oracle materialized view最近一個Materialized View(後以mv簡稱)在排程更新的時候發出欄位長度不足的異常訊息，一直到現在我才知道，Oracle mv的欄位也會有長度不足的問題。
主要是，系統會參照你在執行mv的當下的該欄位長度，上stackoverflow找到了相關的訊息。
範例取自上面stackoverflow
--Create simple table and materialized view
create table test1(a varchar2(1 char));
create materialized view mv_test1 as select a from test1;
--Increase column width of column in the table
alter table test1 modify (a varchar2(2 char));
--Insert new value that uses full size
insert into test1 values('12');
--Try to compile and refresh the materialized view
alter materialized view mv_test1 compile;
begin
dbms_mview.refresh(user||'.MV_TEST1');
end;
/
ORA-12008: error in materialized view refresh path
ORA-12899: value too large for column "JHELLER"."MV_TEST1"."A" (actual: 2, maximum: 1)
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745
ORA-06512: at line 3
--Increase column width of column in the materialized view and refresh
alter materialized view mv_test1 modify (a varchar2(2 char));
begin
dbms_mview.refresh(user||'.MV_TEST1');
end;
/
select * from mv_test1;
A
--
12
範例中可以看的到：
後續跟著範例調整欄位長度，也總算是正常了，雖然在執行perl的時候還是有出現異常訊息DBD::Oracle::st execute failed: ORA-00911: invalid character，不過這只要拿掉字串內的結尾符號『;』就正常了。
oracle 欄位拆解| column_A | column_B |
|---|---|
| A | 1,2,3,4,5,6,7 |
| column_A | column_B |
|---|---|
| A | 1 |
| A | 2 |
| A | 3 |
| A | 4 |
| A | 5 |
| A | 6 |
| A | 7 |
select 'A' column_a, '1,2,3,4,5,6,7' column_b from dual
,，並計算內含的資料筆數：
select column_a,
',' || column_b || ',' column_b, -- 頭尾加上,
length(column_b || ',') - length(replace(column_b, ',')) dc-- 計算裡面的資料筆數
from (
select 'A' column_a, '1,2,3,4,5,6,7' column_b from dual
)a
connect by level設置一個範圍內的長度欄位：select *
from (
select column_a,
',' || column_b || ',' column_b, -- 頭尾加上,
length(column_b || ',') - length(replace(column_b, ',')) dc -- 計算裡面的資料筆數
from (
select 'A' column_a, '1,2,3,4,5,6,7' column_b from dual
)a
)b,
-- 設置一個長度區間<=20的欄位，這部份是依實際需求所設置的長度
(select level lv from dual connect by level <=20) c
where c.lv <= b.dc -- 要注意，是<=，這樣就能一口氣串出所有的資料
| COLUMN_A | COLUMN_B | DC | LV |
|---|---|---|---|
| A | ,1,2,3,4,5,6,7, | 7 | 1 |
| A | ,1,2,3,4,5,6,7, | 7 | 2 |
| A | ,1,2,3,4,5,6,7, | 7 | 3 |
| A | ,1,2,3,4,5,6,7, | 7 | 4 |
| A | ,1,2,3,4,5,6,7, | 7 | 5 |
| A | ,1,2,3,4,5,6,7, | 7 | 6 |
| A | ,1,2,3,4,5,6,7, | 7 | 7 |
substr與instr搭配：select b.*,
substr(b.column_b, -- 取值的字串來源
instr(b.column_b, ',', 1, c.lv) + 1, -- 從那開始，instr是取索引值所在位置，意思就是從b.column_b的第1個字元開始找','，然後取第c.lv個
instr(b.column_b, ',', 1, c.lv + 1) - (instr(b.column_b, ',', 1, c.lv) + 1)) split_data -- 到那裡
from (
select column_a,
',' || column_b || ',' column_b, -- 頭尾加上,
length(column_b || ',') - length(replace(column_b, ',')) dc -- 計算裡面的資料筆數
from (
select 'A' column_a, '1,2,3,4,5,6,7' column_b from dual
)a
)b,
-- 設置一個長度區間<=20的欄位，這部份是依實際需求所設置的長度，以這次的案例設置10也可以
(select level lv from dual connect by level <=20) c
where c.lv <= b.dc -- 要注意，是<=，這樣就能一口氣串出所有的資料
| COLUMN_A | COLUMN_B | DC | SPLIT_DATA |
|---|---|---|---|
| A | ,1,2,3,4,5,6,7, | 7 | 1 |
| A | ,1,2,3,4,5,6,7, | 7 | 2 |
| A | ,1,2,3,4,5,6,7, | 7 | 3 |
| A | ,1,2,3,4,5,6,7, | 7 | 4 |
| A | ,1,2,3,4,5,6,7, | 7 | 5 |
| A | ,1,2,3,4,5,6,7, | 7 | 6 |
| A | ,1,2,3,4,5,6,7, | 7 | 7 |
oracle MODEL ORA-25137 Data value out of rangeMODEL是oracle中非常好用的一個分析函數，部份應用可見個人另篇說明。
這次在設置measures的時候，有部份欄位需要以文字來處理，但執行的時候卻因此造成ORA-25137 Data value out of range的錯誤訊息。這似乎是因為限制上其欄位長度不能超過dimension的欄位長度，但這很不能滿足實務上的需求。
幾次測試發現，在measures定義欄位的時候，直接利用cast來定義欄位長度，舉例cast('a' as varchar2(100))，那就可以一次給定長度100的varchar2欄位，避免Data value out of range。
如下詳細範例：
return updated rows
partition by (A, B, C)
dimension by (D)
measures(
cast('a' as varchar2(100)) E,
cast('a' as varchar2(100)) F
)
rules upsert(
write your code here
)
這種方式設置，就可以讓E、F兩個欄位長度都預設為100。
oracle ORA-01830ORA-01830:在轉換整個輸入字串前,日期格式圖片就結束了
利用Model執行欄位相減取日期區間的時候出現該錯誤訊息，欄位資料如下：
索引值相減僅第一個錯誤，因此直覺想法就是將文字轉換日期格式失敗。
將to_date(column, 'yyyy-mm-dd')調整為to_date(substr(column, 1, 10), 'yyyy-mm-dd')之後正常。
除此之外，也可以調整後面的日期格式，如下：
select to_date('2019/7/23 11:40','yyyy-mm-dd hh24:mi') from dual
oracle MODEL
MODEL [RETURN [UPDATED | ALL] ROWS]
[reference models]
[PARTITION BY (<cols>)]
DIMENSION BY (<cols>)
MEASURES (<cols>) [IGNORE NAV] | [KEEP NAV]
[RULES
[UPSERT | UPDATE]
[AUTOMATIC ORDER | SEQUENTIAL ORDER]
MODEL:是一個宣告的關鍵字
PARTITION BY:以XX欄位為分組
DIMENSION BY:MODEL維度設定，看成INDEX，可以是複合PK
MEASURES:指定資料欄位，可自行定義
RULES:規則，你怎麼去操作它，如any,cv…
CREATE TABLE A AS
SELECT 'lottu' AS vname,
1 AS vals FROM dual;
SELECT vname,vals FROM A
MODEL
--partition by ()可以忽略
DIMENSION BY(vals)
MEASURES(vname)
RULES (vname[1]='0924');
結果如下：
VNAME VALS
0924 1
執行結果會發現，vname的部份被指定為0924，因為只有一筆資料，所以index(VALS)=1的部份即為該資料，而且被指定為0924!
如果調整一下RULES!
SELECT vname,vals FROM A
MODEL
--partition by ()可以忽略
DIMENSION BY(vals)
MEASURES(vname)
RULES (vname[0]='0924');
結果如下：
VNAME VALS
1 lottu 1
2 0924 0
這時候會發現，多了一筆資料了，並且VALS為0!
我們再插入一筆資料，(‘LI’,2)
INSERT INTO A VALUES ('LI',2);
COMMIT;
接著執行!
SELECT vname,vals FROM A
MODEL
DIMENSION BY(vals)
MEASURES(vname)
RULES (vname[2]='0924');
結果如下：
VNAME VALS
1 lottu 1
2 0924 2
跟剛才一樣，RULES將LI調整為0924了!
當然也可以跟剛才不一樣，用不存在的INDEX去做設置。
SELECT vname,vals FROM A
MODEL
DIMENSION BY(vals)
MEASURES(vname)
RULES (vname[5]='0924',vname[0]='99');
這次我們加了兩筆記錄進去。
MODEL後面如果加上RETURN UPDATED ROWS即代表，有被RULES更新或者插入的資料才會顯示。
SELECT vname,vals FROM A
MODEL RETURN UPDATED ROWS
DIMENSION BY(vals)
MEASURES(vname)
RULES (vname[0]='0924');
結果如下：
VNAME VALS
1 0924 0
我們有兩筆資料，按上面的練習應該是會出現三筆才對!
但這次的SELECT卻只出現一筆，這就是加入RETURN UPDATED ROWS的用途!
用另一個例子來說明!
建立另一個新的table，並加入數據!
我們建立了2011年到2014年的資料，希望預測2015年!
CREATE TABLE B(p_id NUMBER,p_year Varchar2(5),p_val NUMBER);
INSERT INTO B VALUES (1001,'2011',25);
INSERT INTO B VALUES (1001,'2012',35);
INSERT INTO B VALUES (1001,'2013',65);
INSERT INTO B VALUES (1001,'2014',95);
INSERT INTO B VALUES (1002,'2011',25);
INSERT INTO B VALUES (1002,'2012',55);
INSERT INTO B VALUES (1002,'2013',75);
INSERT INTO B VALUES (1002,'2014',95);
接著先以不加入RETURN的方式呈現比較清楚整個資料結構。
SELECT * FROM B
MODEL
PARTITION BY (p_id)
DIMENSION BY (p_year)
MEASURES (p_val)
RULES (p_val['2015']=p_val['2014']+p_val['2013']);
結果如下：
P_ID P_YEAR P_VAL
1 1001 2011 25
2 1001 2012 35
3 1001 2013 65
4 1001 2014 95
5 1002 2011 25
6 1002 2012 55
7 1002 2013 75
8 1002 2014 95
9 1001 2015 160
10 1002 2015 170
我們以P_ID為分組依據，以P_YEAR為維度，設定P_VAL為呈現的數據，然後設置2015年的值=2013年加上2014年!
接著我們加入RETURN UPDATED ROWS
SELECT * FROM B
MODEL RETURN UPDATED ROWS
PARTITION BY (p_id)
DIMENSION BY (p_year)
MEASURES (p_val)
RULES (p_val['2015']=p_val['2014']+p_val['2013']);
結果如下：
P_ID P_YEAR P_VAL
1 1001 2015 160
2 1002 2015 170
只回傳異動的資料，所以只會有2015年的資料呈現。
資料集的部份一樣是剛才建置的TABLE B
2015年的1001是前兩年的總合，而1002是上年度的2倍。
SELECT * FROM B
MODEL RETURN UPDATED ROWS
DIMENSION BY (p_id,p_year)
MEASURES (p_val)
RULES (p_val[1001,'2015']=p_val[1001,'2013']+p_val[1001,'2014'],
P_val[1002,'2015']=2 * p_val[1002,'2014']);
結果如下：
P_ID P_YEAR P_VAL
1 1002 2015 190
2 1001 2015 160
這次不設置PARTITION
將P_ID加入維度內(DIMENSION)
並且搜尋數據組一樣為P_VAL
就可以個別的處理兩個P_ID的2015年的銷售計算了。
語法：SUM(MEASURES)[DIMENSION BETWEEN CON1 AND CON2]
SELECT * FROM B
MODEL RETURN UPDATED ROWS
PARTITION BY (p_id)
DIMENSION BY (p_year)
MEASURES (p_val)
RULES (p_val['2015']=sum(p_val)[p_year BETWEEN '2013' AND '2014']);
結果如下：
P_ID P_YEAR P_VAL
1 1001 2015 160
2 1002 2015 170
透過SUM(MEASURES)[DIMENSION BETWEEN CON1 AND CON2]
可以像在操作標準SQL語法一樣設置條件。
此例加總了2013年至2014年的P_VAL
語法：SUM(MEASURES)[FOR DIMENSION IN (CONDITION)]
SELECT * FROM B
MODEL RETURN UPDATED ROWS
PARTITION BY (p_id)
DIMENSION BY (p_year)
MEASURES (p_val)
RULES (p_val['2015']=sum(p_val)[for p_year in ('2014','2013')]);
結果如下：
P_ID P_YEAR P_VAL
1 1001 2015 160
2 1002 2015 170
這個案例我們用了FOR + IN的語法
FOR DIMENSION IN 條件
所以一樣求得了2013年與2014年的加總
如果P_YEAR本身是數值的話，可以利用表達式
FOR DIMENSION FROM INT1 TO INT2 INCREMENT N
此例來說，如果P_YEAR為數值，那我們的表達式可以以這樣子來表示
for year from 2013 to 2014 increment 1
代表從2013年到2014年，迭代部份一次增加1
最後提到
FOR DIMENSION IN (SELECT 子句)
在IN的部份是可以利用SELECT子句來處理
ANY>位置標記使用
IS ANY>符號標記使用
SELECT * FROM B
MODEL RETURN UPDATED ROWS
PARTITION BY (p_id)
DIMENSION BY (p_year)
MEASURES (p_val)
RULES (p_val['2017']=SUM(p_val)[ANY]);
結果如下：
P_ID P_YEAR P_VAL
1 1001 2017 220
2 1002 2017 250
我們假設2017年的預測為前面幾年的部份，這時候可以利用ANY表達式!
SELECT * FROM B
MODEL RETURN UPDATED ROWS
PARTITION BY (p_id)
DIMENSION BY (p_year)
MEASURES (p_val)
RULES (p_val['2017']=SUM(p_val)[P_YEAR IS ANY]);
也可以得到一樣的結果
SELECT * FROM B
MODEL RETURN UPDATED ROWS
DIMENSION BY (p_id,p_year)
MEASURES (p_val)
RULES (p_val[1001,'2015']=p_val[currentv(),'2013']+p_val[currentv(),'2014'],
P_val[1002,'2015']=2 * p_val[currentv(),'2014']);
結果如下：
P_ID P_YEAR P_VAL
1 1002 2015 190
2 1001 2015 160
CURRENTV主要用來取得某個DIMENSION目前的值!
對比上面的範例
SELECT * FROM B
MODEL RETURN UPDATED ROWS
DIMENSION BY (p_id,p_year)
MEASURES (p_val)
RULES (p_val[1001,'2015']=p_val[1001,'2013']+p_val[1001,'2014'],
P_val[1002,'2015']=2 * p_val[1002,'2014']);
dll 找不到指定程序
過程一波三折，建議作法是，先不要聽網路上的八卦，把所有cx_Oracle移除，然後把依著網路八卦複製什麼檔到那裡的檔案通通砍了!
然後至官網下載對應的版本(作業系統、資料庫版本)之後，就可以直接安裝!!
是的，我弄了四個多小時就是這樣子做就好了!
不過對於oracle的設置確認還是要先處理好!
tnsping 可以測試是否設置正常
import cx_Oracle
沒有錯誤訊息的時候眼淚都流出來了!
MIS2000老師有教過，連接資料庫四步驟!
CONNECTION->SQLCOMMAND->SQLREADER->CLOSE(有借記得要有還)
db = cx_Oracle.connect(帳號, 密碼, 連接的資料庫)
db.version
眼淚再次的噴發!看到版本了!
一般來說，如果直接透過cx_Oracle.cursor.excute來取得資料集的話，這個回傳資料集會是一個物件!
cursor = db.Cursor()
sql = 'select 123 coluu from dual'
cursor.excute(sql)
當然這要看實際的應用是不是有要保留這個物件，有的話就可以配合pickle來將檔案序列化(我是這樣想@@)
不過如果要做直接使用的話，也可以搭配pandas
import pandas as pd
df = pd.read_sql(sql, db)
就可以開始做後續的利用了!
不過別忘了，有借有還!
db.close()
import cx_Oracle
db = cx_Oracle.connect(帳號, 密碼, 連接的資料庫)
sql = 'select 123 coluu from dual union select 456 coluu from dual'
cursor = db.Cursor()
每執行一次，指標就會往下一筆過去
cursor.execute(sql)
# 取回第一筆
row = cursor.fetchone()
print(row)
# 取回第二筆
row = cursor.fetchone()
print(row)
結果如下：
>>>cursor.fetchone()
(123,)
>>>cursor.fetchone()
(456,)
透過numRows指定一次取回的筆數
cursor.execute(sql)
res = cursor.fetchmany(numRows=3)
結果如下：
>>>res = cursor.fetchmany(numRows=2)
>>>res
[(123,), (456,)]
cursor.execute(sql)
all = cursor.fetchall()
結果如下：
>>>all
[(123,), (456,)]
for cur in cursor.execute(sql):
print(cur)
結果如下：
(123,)
(456,)
在執行一個動作之後確認影響筆數
在select的情況下，回傳的不是影響筆數，而是目標游標所在。
這部份很多人誤會了!
cursor.rowcount
結果如下：
2
在執行select的時候，帶參數是很正常的需求。
所以where條件上，需要可以依需求來取得資料。
str_sql = "select column from yourTable where col1=:col_a and col2=:col_b"
cursor.execute(str_sql, {col_a: 'your condition', col_b: 'your condition'})
或者也可以將參數先用dict格式裝起來
str_sql = "select column from yourTable where col1=:col_a and col2=:col_b"
dict_con = {'col_a': 'your condition', 'col_b': 'your condition'}
cursor.execute(str_sql, dict_con)
Oracle官方文件
不少情況在寫入資料庫的時候需求批量處理，簡單的作法上當然是透過迴圈來處理，但是這部份cx_Oracle與oracle都替我們想好了。
透過executemany搭配tuple就可以滿足了。
create_table = "
CREATE TABLE python_modules (
module_name VARCHAR2(50) NOT NULL,
file_path VARCHAR2(300) NOT NULL
)
"
from sys import modules
cursor.execute(create_table)
M = []
for m_name, m_info in modules.items():
try:
M.append((m_name, m_info.__file__))
except AttributeError:
pass
>>> len(M)
76
>>> cursor.prepare("INSERT INTO python_modules(module_name, file_path) VALUES (:1, :2)")
>>> cursor.executemany(None, M)
>>> db.commit()
>>> r = cursor.execute("SELECT COUNT(*) FROM python_modules")
>>> print cursor.fetchone()
(76,)
>>> cursor.execute("DROP TABLE python_modules PURGE")
重點的部份在於後段的cursor.prepare與cursor.executemany的搭配使用!
str_sql = "select column from yourTable where col1=:col_a and col2=:col_b"
cursor.prepare(str_sql)
dict_con = {'col_a': 'your condition', 'col_b': 'your condition'}
cursor.execute(None, dict_con)
如果有個TABLE格式一樣要直接複製的話，可以直接透過SELECT來處理!
不過用這方法的話，KEYS值不會過來，還有PRIVILEGES的授權也不會過來,記得再手工補上!
CREATE TABLE PE_TBL_C31R_TEMP AS
SELECT * FROM PE_TBL_C31R
在oracle中建置一個回傳select的function
tags:
oracle在mssql中，要弄一個回傳select的function或stored procedure好像是一件非常簡單的事，只要在文末下一個select就回傳了。
不過在oracle好像不是這麼一回事，需要不少步驟，這邊僅記錄個人能夠成功的步驟，但是已經有點忘了當初參考的出處，如有雷同部份再請告知，讓在下可以標註參考來源。
首先，建立一個參照的物件
-- 建立一個參照物件 CREATE OR REPLACE TYPE NOTES_OBJ_EOD_MLIST AS OBJECT ( MATNR VARCHAR2(54), MTIDX VARCHAR2(54), IDNRK VARCHAR2(54), FERTH_LBR VARCHAR2(54), FERTH_LED VARCHAR2(54) );接下來，建立一個暫存表，命名習慣上我會直接在參照物件後面加上一個TEMP，看個人喜好
CREATE TYPE NOTES_OBJ_EOD_MLIST_TEMP AS TABLE OF NOTES_OBJ_EOD_MLIST;然後建立你的function
-- 建立FUNCTION create or replace function NOTES_FUNC_EOD_MLIST(YOUR_PARAMETERS) return NOTES_OBJ_EOD_MLIST_TEMP pipelined AS BEGIN FOR cur IN ( SELECT MATNR, MTIDX, IDNRK, FERTH_LBR, FERTH_LED FROM YOUR_TABLE ) -- END cur LOOP -- return date PIPE ROW(NOTES_OBJ_EOD_MLIST(cur.MATNR, cur.MTIDX, cur.IDNRK, cur.FERTH_LBR, cur.FERTH_LED ) ); END LOOP; RETURN; END;第1行：要特別注意到，最後的return是你的暫存表，並且加上
pipelined第3行：把你的select用一個
for cur in包住，其中cur你可以自己用你自己的命名第6行：用
loop end loop包住預計回傳的資料第7行：用
pipe row(你建立的參照物件(欄位))來處理要回傳的資料最後只要再return，就可以成功弄一個可以回傳select的function
使用方法也很簡單，見下範例：
select * from table(your_function(parameter, if have))