顯示具有 Oracle 標籤的文章。 顯示所有文章
顯示具有 Oracle 標籤的文章。 顯示所有文章

2014年12月4日 星期四

刪除或修改暫存表格方法 (GLOBAL TEMPORARY TABLE)


新增欄位

TRUNCATE TABLE TPMPTABLE;
ALTER TABLE TPMPTABLE ADD (F1 VARCHAR2(6));

刪除表格TPMPTABLE

DROP TABLE TPMPTABLE;

刪除表格若出現以下錯誤

錯誤報告 - SQL 錯誤: ORA-14452: 嘗試建立, 更改或捨棄已在使用中之暫時表格的索引 14452. 00000 - "attempt to create, alter or drop an index on temporary table already in use" *Cause: An attempt was made to create, alter or drop an index on temporary table which is already in use. *Action: All the sessions using the session-specific temporary table have to truncate table and all the transactions using transaction specific temporary table have to end their transactions.


1.確定使用此表格的SID

SELECT SID, SERIAL# FROM V$SESSION V 
WHERE SID IN (SELECT SID FROM V$LOCK  L, DBA_OBJECTS O 
WHERE L.ID1 = O.OBJECT_ID AND O.OBJECT_NAME =UPPER('TPMPTABLE'));

2.刪除SESSION,成功後再做一次刪除表格動作(刪除SESSION的ORACLE帳號權限必須夠大)

ALTER SYSTEM KILL SESSION 'SID, SERIAL#';



2014年12月3日 星期三

Join至其他table並抓到最大的日期

取得table最大日期

SELECT K1,K2,D FROM T1
WHERE D = (SELECT MAX(D) FROM T1)

錯誤寫法:join至其他table並抓到最大的日期,此寫法會出現
ORA-01799: 資料欄不可以外部結合的方式與一個子查詢結合

SELECT K,D,D2 FROM T1 LEFT JOIN T2 ON T2.K2 = T1.K
AND T2.D2 = (SELECT MAX(D2) FROM T2)

成功寫法:join至其他table並抓到最大的日期

SELECT K,D,D2 FROM T1 LEFT JOIN 
(
  SELECT K2,D2 FROM T2 AA WHERE AA.D2 IN
   (
     SELECT MAX(D2) FROM T2
     WHERE T2.K2 = AA.K2
   )
)C ON C.K2 = T1.K

2014年11月28日 星期五

ORACLE 預存程序回傳錯誤

ORACLE 預存程序回傳錯誤

CREATE OR REPLACE PROCEDURE SPTEST(USERID IN VARCHAR2,RTN OUT VARCHAR2)
IS
  n NUMBER;
BEGIN
  n:=4/0;  
  RTN:= 'OK';
EXCEPTION
  WHEN OTHERS THEN
  RTN:= SQLERRM;
END SPTEST;

2014年9月19日 星期五

Oracle 修改 SEQUENCE

修改INCREMENT(可以為負),再執行NEXTVAL

ALTER SEQUENCE seq INCREMENT BY 100;
SELECT seq.NEXTVAL FROM DUAL;

執行完記得改回1

ALTER SEQUENCE seq INCREMENT BY 1;

2014年7月11日 星期五

Oracle 動態塞值到 IN Clause

解決下方情況

SELECT * FROM TableName
WHERE TUSER IN ('A','B','C')
--'A','B','C'為動態產生

1)新增類型

create or replace 
type ttype is table of varchar2(255)

2)新增函數

create or replace 
Function FindRLP RETURN ttype
 IS
   rtn ttype := ttype();
   idx integer := 0;
 BEGIN
  for r in (SELECT TUSER FROM UR_UIR WHERE RLNO='G05')
   loop
      rtn.extend;
      idx := idx+1;
      rtn(idx) := r.TUSER;
   end loop;
   return rtn;
 END;

使用方式

SELECT * FROM TableName
WHERE TUSER IN (SELECT * FROM TABLE(FindRLP))

2014年7月5日 星期六

.NET存取Oracle資料庫相關Provider說明,ODBC、OleDb、OracleClient、ODAC

使用時確認各Provider支援的Oracle版本


1).NET Framework Data Provider for ODBC:

(Open Database Connectivity,開放資料庫互連)提供了標準的API方法來存取資料庫管理系統。尚未有ODBC之前,若要開發資料庫應用程式,必須使用資料庫廠商隨資料庫產品一同發行的一些工具集來存取資料庫。微軟於1992年發表了ODBC。
using System.Data;
using Microsoft.Data.Odbc;

     {
       OdbcConnection cn;
       OdbcCommand cmd;
       string MyString;

       MyString="Select * from Customers";

       cn= new OdbcConnection("Driver={Microsoft ODBC for Oracle};Server=myOracleServer;UID=spring;PWD=spring;");

       cmd=new OdbcCommand(MyString,cn);
       cn.Open();

       MessageBox.Show("Connected");

       cn.Close();
     }

2).NET Framework Data Provider for OLE DB:

是微軟為以統一方式存取不同型別的資料儲存設計的一種應用程式介面,是一組用元件物件模型(COM)實作的介面。OLE DB Provider for Oracle Client,呼叫 Oracle 資料庫的 OLE DB 資料提供者,通常由 Oracle 原廠提供。
using System.Data;
using System.Data.OleDb;

        {
            OleDbConnection cn;
            OleDbCommand cmd;
            string MyString;

            MyString = "Select * from Customers";

            cn = new OleDbConnection("Data Source=myOracleServer;Provider=OraOLEDB.Oracle.1;User Id=spring;Password=spring");

            cmd= new OleDbCommand(MyString,cn);
            cn.Open();

            MessageBox.Show("Connected");

            cn.Close();
        }

3).NET Framework Data Provider for Oracle:

支援Oracle用戶端軟體8.1.7(含)以後版本。Visual Studio 2010後建議使用ODP.NET。
(Microsoft:The types in System.Data.OracleClient are deprecated)
using System.Data;
using System.Data.OracleClient;

         {

            OracleConnection cn;
            OracleCommand cmd;
            string MyString;

            MyString = "Select * from Customers";

            cn = new OracleConnection("Data Source=myOracleServer;User Id=spring;Password=spring");

            cmd = new OracleCommand(MyString, cn);
            cn.Open();

            MessageBox.Show("Connected");

            cn.Close();

         }

4)ODAC:

(ODP.NET:Oracle Data Provider for .NET)不同版本 Visual Studio 須搭配不同ODAC(Oracle Data Access Components)

Web.config
<add name="Default" connectionString="Data Source=myOracleServer;User Id=spring;Password=spring;providerName="Oracle.DataAccess.Client"/>
using Oracle.DataAccess.Client;

         using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
         {
            OracleCommand cmd;
            try
            {
              conn.Open();
              cmd = new OracleCommand();
              cmd.Connection = conn;
              cmd.CommandText = "";

              cmd.ExecuteNonQuery();
             }
             catch (Exception ex)
             {}
             //使用using不需要close與dispose
             //finally
             //{
             //    cmd.Dispose();
             //    conn.Close();
             //    conn.Dispose();
             //}    
          }

2014年4月3日 星期四

ASP.NET 使用 Oracle Store Procedure 分頁

INPUT
  PAGE_1:目前頁數
  START_1:開始筆數
  LIMIT_1:每頁幾筆資料
  SORT_1:排序欄位

OUTPUT
  RC_1:總筆數
  TP1025_RTN:回傳資料集

C#
Oracle Store Procedure
結果

2009年9月21日 星期一

Oracle 日期欄位轉字串的方法

TO_CHAR(, '')
範例:
SELECT TO_CHAR(b, 'YYYY/MM/DD') AS b FROM x;
Oracle 字串轉日期欄位的方法 ( Oracle 寫入日期的方法 )
TO_DATE(, '')
範例:
TO_DATE('2006/8/29', 'YYYY/MM/DD')

轉自
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html

Oracle 設定流水號

進入SQL-Plus後
--------------宣告concession
CREATE SEQUENCE concession_sequence
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;
--------------使用
concession_sequence.nextval;
--------------刪除sequence
DROP SEQUENCE concession_sequence;

Oracle資料庫問題,無法從Client連到Server的Oracle Client

Client端電腦透過公司的Server端電腦連至SAP資料庫
怎麼樣都不行連(重裝Oracle Client也不行)
後來改了一下Server端Oracle的權限設定
在Oracle的安裝目錄下 C:\oracle\ora92
將ora92的權限中的Authenticated Users加入完全控制(預設只有讀取及執行、清單資料夾內容、讀取)
即可解決

Excel檔匯入Oracle

1. 將excel轉存為.csv
2. 建立.ctl檔
檔案內容----
load data
infile "c:\SPC_Table.csv"(資料檔名稱與路徑)
badfile "bad.log"
discardfile "discard.log"
into table SPC_TABLE(Oracle裡的Table)
fields terminated by ',' optionally enclosed by '"'(原始資料以,區格)
trailing nullcols (欄位內容)
( 零件件號
, OP
, 工令
, 日期
, 序號
, 圖號
, 值
, 操作人員
)
3. 在命令提示字元下> sqlldr userid=帳號/密碼 control=C:\SPC_Table.ctl