OLE error 800A03EC

今天跑程式，產生報表在 EXCEL ，
五分鐘前還好好的，結果突然就跑出一個錯誤訊息
【OLE error 800A03EC】？
不知道為什麼，一開始還以為 Oracle 的權限又跑掉了，
跑了權限的 SQL 結果還是一樣，
跑了別張報表發現也一樣？
把程式關掉重開還是一樣？
只好拜訪一下估狗大神了。

【OLE error 800A03EC - Toolbox for IT Groups】

隨便看了一下，
感覺應該是要利用 OLE 轉資料到 EXCEL 的時候出現錯誤，
恩~~~看到這邊，決定把 EXCEL 關起來再跑報表看看，
果然就可以了，筆記一下。

實驗證明，應該是 EXCEL 再輸入的狀態下是沒辦法轉出 EXCEL 的，
所以只要不是在輸入狀態下就可以轉出了，不用關閉所有的 EXCEL ，以上。

Oracle - 找出壞掉的物件重新 compile

最近在交易檔加了一個欄位，然後一干人等全部都壞掉了，
因為壞掉的有一狗票，所以不太可能一個、一個慢慢去把它修好，
所以找了一下，寫成以下偷懶寫法：

SELECT 'ALTER ' || DECODE(SUBSTR(OBJECT_TYPE, 1, 7), 'PACKAGE', ' PACKAGE', OBJECT_TYPE) 
       || ' ' || OBJECT_NAME || ' COMPILE' 
       || DECODE(SUBSTR(OBJECT_TYPE, 1, 7), 'PACKAGE', ' PACKAGE;', ';')
FROM USER_OBJECTS
WHERE OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY')
      AND STATUS = 'INVALID';

簡單的說就是透過 USER_OBJECTS 這個資料表的 STATUS 來找出壞掉的物件，

要注意的是 PACKAGE 的 OBJECT_TYPE 會分成 PACKAGE 跟 PACKAGE BODY 這兩個，

所以要特別處理一下。

參考一下 ALTER PACKAGE

可以針對 PACKAGE BODY Recompile ，可能有省一點工吧？

ALTER PACKAGE hr.emp_mgmt COMPILE BODY; 

不過我想我還是直接對整個 PACKAGE Compile 吧！

ALTER PACKAGE emp_mgmt COMPILE PACKAGE;

我想等等阿輝應該會解釋一下兩者的差異，

我是看了半天看不出來，不知道是英文不好？還是他沒解釋？

阿輝換你了！(啪)

Oracle - 如何在 CURSOR 裡面直接更新該筆資料

正所謂一懶還有一懶懶，
每次看到同事寫的程式裡面有一點是我覺得很奇怪的，
明明我就是抓某個 TABLE 裡面的某些資料出來，
為什麼在 FOR LOOP 裡面要更新那一筆資料的時候，
卻還是下 UPDATE 該 TABLE 然後一堆條件呢？

所以今天在寫個 FUNC 的時候就想說來找一下，
應該會有比較偷懶聰明的寫法吧！
找到了下面這個說明
【[Chapter 6] 6.11 SELECT FOR UPDATE in Cursors】
看起來應該可以，試了一下，
挑了一種我覺得比較偷懶簡潔的寫法，如下：

   CURSOR CUR_TEST IS
      SELECT *
      FROM TB_TEST
      FOR UPDATE;

只要在 CURSOR 最後面加上 FOR UPDATE 就可以了，很簡單。

然後 FOR LOOP 裡面的寫法也很簡單，如下：

   FOR REC_TEST IN CUR_TEST

   LOOP

      

      UPDATE TB_TEST

      SET CALC_AMT = 10000000 

      WHERE CURRENT OF CUR_TEST;

   END LOOP;

UPDATE 甚麼不是重點，
重點是 WHERE 條件的寫法 CURRENT OF CUR_TEST 就可以了，
夠簡單，讚！

進步始終來自於惰性！共勉之~~~

Oracle - Right Str ？

我真是個不認真記語法的人，
每次都很自然地寫出  RIGHT('ABCD', 2) 這種神人級的語法，
就我要從右邊取出 CD 咩！

剛剛找了一下，發現有個寫法還不錯，

SUBSTR('ABCD', -2, 2)

第二個參數可以應該可以解讀為從右邊數來第二個開始。
(自我解讀 XD)

所以如果我想取得 BC 可以用下面的寫法

SUBSTR('ABCD', -3, 2)

不過這樣的需求應該是比較少才對。
(自己為 :P)

T-SQL - 自定義資料表型態三兩事

最近忙著用 SQL 寫法規，需要將資料表傳來傳去的，
用 temp table 是另外一件事情，這邊不討論。
由於一開始也沒資料可以測試，
所以都是先閉著眼睛寫 UDFs, SP ...
因為簡化程式，所以開始使用 UDTs

CREATE TYPE SAMPLE_TABLE_TP AS TABLE (
   ACC_DT DATETIME,
   FUND_ID NVARCHAR(20));

以便在程式裡面可以縮減程式以

DECLARE @SAMPLE_TABLE AS SAMPLE_TABLE_TP;

去取代

DECLARE SAMPLE_TABLE TABLE (
   ACC_DT DATETIME,
   FUND_ID NVARCHAR(20));

當然啦！正所謂一懶還有一懶懶！
所以就開始在 UDFs 跟 SP 裡面將 TABLE TYPE 傳來傳去的，
等到正式開始有資料測試的時候發現幾項限制：

UDFs 可以接收的參數可以是 TABLE TYPE，如下：

CREATE FUNCTION FN_TEST (
   @SAMPLE_TABLE AS SAMPLE_TABLE_TP READONLY,

也可以 RETURNS TABLE，但是不能 RETURNS TABLE TYPE
所以可以這樣寫

RETURNS @RTN_TABLE TABLE (  
   ACC_DT DATETIME,
   FUND_ID NVARCHAR(20))

但是沒有辦法寫成這樣

RETURNS SAMPLE_TABLE_TP

如果寫成這樣，會有錯誤訊息，會說要回傳的是一個純量的變數。
但是可以變通的在 Func 裡面利用

DECLARE @SAMPLE_TABLE AS SAMPLE_TABLE_TP;

INSERT INTO @RTN_TABLE

   SELECT * FROM @SAMPLE_TABLE

這樣中間就可以搞出很多變化。

另外上次說可以用

INSERT INTO @SAMPLE_TABLE
   EXEC SP_OOXX

但是等到開始測試的時候跳出了錯誤訊息，
意思是說這種方式不可以巢狀呼叫！(攤)
上網查了一下可以用 Link Server 的方式去避免
Ref：INSERT EXEC 陳述式不可以是巢狀的
就是建立一個自己連到自己的 Link Server ，
霸特...(所有事情遇到霸特都是個轉則啊！)
Link Server 不支援資料表參數！(死棋)(攤)(攤)(攤)

最後只好摸摸鼻子調整程式，將 SP 改回去 UDFs.....
對，是改回去！之前因為 UDFs 不支援回傳資料表型態而改成 SP，
現在再度因為 SP 不能巢狀呼叫再度改回去！
這就是衰咖人篸啊~~~

T-SQL - 字串轉換小技巧，剔除小數位數

昨天貴公司嘆氣工程師問我一個字串轉換的問題，
因為如果將一個 DECIMAL(20, 5) 的數字轉換成 VARCHAR(20) 的話，
會出現 7500.0000 之類的，但是他只要整數的部分？
因為之前日期轉換的時候可以指定格式，
他問我這個格式要用甚麼？
說真的，我並不是那麼愛寫 SQL ，
更不會去記甚麼指定格式的參數是啥，
然後印象中.....應該只有日期才有吧？
然後就想說是不是該用 POS 之類的函數？
然後嘆氣工程師就說可是 Oracle 都有 ╮(╯3╰)╭
後來就在那邊開玩笑地說，不然用 LEFT(X, 4) 好了 XD
然後靈光乍現想說先轉成 INT 再轉成 VARCHAR 看看，果然就 ok 了。

CONVERT(VARCHAR(20), CONVERT(INT, @NUM))

T-SQL - Stored Procedure 回傳 Table 怎麼接？

一直以為回傳 Table 是 UDFs 的專利，
最近因為很不幸的接了一坨【屎】，又重拾 T-SQL 寫東西，
然後因為太久沒寫 T-SQL，又沒有資料可以看，
所以就感覺好像在寫 pseudo code 一般，
先避著眼睛寫，感覺先對了，先不管語法對不對的問題。

等到要把 pseudo code 包成 UDFs 的時候才發現.....
忘了 UDFs 不可以下 UPDATE 了 .....又中招了！
所以只好切一切，把 INSERT, UPDATE 包在 SP 裡面，
不過我還是要回傳 @資料表 啊！
不想再回實體資料表去查，這樣效能應該會比較差，<--非常心虛的說應該
雖然這邊應該是沒有在管的，不過個人是做口碑的.....<--口碑很重要，但是做死自己不應該
再又一陣亂寫的狀況下，居然也沒錯誤訊息！
很心虛的又測試了一下，發現好像真的可以這樣用，
特寫此篇以茲紀念~~~

簡單的說就是
1.宣告一個 @資料表 去接 SP 回傳的資料表。
2.@資料表的 Schema 要跟 SP 回傳的資料表一模模一樣樣。
3.用 INSERT INTO @資料表 EXEC 的方式去呼叫 SP。
4.SP 回傳的可以直接用 SELECT 出來就可以。

--1+2
DECLARE @TMP_LIST TABLE (
   order_dt DATETIME,
   product_name NVARCHAR(20));

--3 重點是這個
INSERT INTO @TMP_LIST
EXEC SP_GET_TMP_LIST @set_dt , @user_id;

--4

CREATE PROC SP_GET_TMP_LIST

   @set_dt DATETIME,

   @user_id NVARCHAR(20)

AS

   SELECT order_dt, product_name FROM ORD 

      WHERE SET_DT = @set_dt AND USER_ID = @user_id;

GO

就這樣~

btw 為什麼一堆人聽不懂 schema ？到底......

Oracle - Tablespace 空間不足

今天打開資料庫查一個 table 的時候發現沒資料？
重新用 plsql 再倒還是沒資料？
看了一下錯誤訊息是

ORA-01658: 無法建立表格空間 (TABLESPACE NAME) 中區段的 INITIAL 擴充區塊

應該是 TABLESPACE 空間不夠的關係，

用下面這個 SQL 查一下目前 TABLESPACE ，

SELECT A.TABLESPACE_NAME, A.BYTES / 1024 / 1024 "Sum MB",
       (A.BYTES - B.BYTES) / 1024 / 1024 "used MB", B.BYTES / 1024 / 1024 "free MB",
       ROUND(((A.BYTES - B.BYTES) / A.BYTES) * 100, 2) "percent_used"
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
       FROM DBA_DATA_FILES
       GROUP BY TABLESPACE_NAME) A,
     (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, MAX(BYTES) LARGEST
       FROM DBA_FREE_SPACE
       GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY ((A.BYTES - B.BYTES) / A.BYTES) DESC

果然有一個滿了！

用下面這個 SQL 可以查到資料庫的實體檔案，

SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, INCREMENT_BY
FROM DBA_DATA_FILES
ORDER BY FILE_ID DESC;

查到之後，就可以用下面的語法去增加檔案的大小。

ALTER DATABASE  
DATAFILE 'C:\APP\FRANKIE\ORADATA\XXXXXXXX.DBF'  
RESIZE 400M;

調整一下 RESIZE 後面的數字就可以了，就這樣~~~