SQL 日期的應用 (轉)

  oracle SQL裡常用的時間函數,經典推薦
相信很多人都有過統計某些數據的經歷，比如，要統計財務的情況，可能要按每年，每季度，每月，甚至每個星期來分別統計。 那在oracle中應該怎麼來寫sql語句呢，這個時候Oracle的日期函數會給我們很多幫助。
常用日期型函數
1。  Sysdate當前日期和時間
 SQL> Select sysdate from dual;
SYSDATE
----------
 21-6月-05
2。  Last_day本月最後一天
 SQL> Select last_day(sysdate) from dual;
LAST_DAY(S
----------
 30-6月-05
3。  Add_months(d,n)當前日期d後推n個月用於從一個日期值增加或減少一些月份
date_value:=add_months(date_value,number_of_months)
 SQL> Select add_months(sysdate,2) from dual;
ADD_MONTHS
----------
 21-8月-05
4。  Months_between(f,s)日期f和s間相差月數
 SQL> select months_between(sysdate,to_date('2005-11-12','yyyy-mm-dd'))from dual;
MONTHS_BETWEEN(SYSDATE,TO_DATE('2005-11-12','YYYY-MM-DD'))
 -------------------------------------------------- --------
-4.6966741
5。  NEXT_DAY(d, day_of_week)
返回由"day_of_week"命名的，在變量"d"指定的日期之後的第一個工作日的日期。 參數"day_of_week"必須為該星期中的某一天。
 SQL> SELECT next_day(to_date('20050620','YYYYMMDD'),1) FROM dual;
NEXT_DAY(T
----------
 26-6月-05
6。  current_date()返回當前會話時區中的當前日期
date_value:=current_date
 SQL> column sessiontimezone for a15
 SQL> select sessiontimezone,current_date from dual;
 SESSIONTIMEZONE CURRENT_DA
 --------------- ----------
 +08:00 13-11月-03

 SQL> alter session set time_zone='-11:00' 2 /
會話已更改。
 SQL> select sessiontimezone,current_timestamp from dual;
 SESSIONTIMEZONE CURRENT_TIMESTAMP
 --------------- ----------------------------------- -
 -11:00 12-11月-03 04.59.13.668000下午-11:00
7。  current_timestamp()以timestamp with time zone數據類型返回當前會話時區中的當前日期
 SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
 -------------------------------------------------- -------------------------
 21-6月-05 10.13.08.220589上午+08:00
8。  dbtimezone()返回時區
 SQL> select dbtimezone from dual;
DBTIME
------
-08:00
9。  extract()找出日期或間隔值的字段值
 date_value:=extract(date_field from [datetime_value|interval_value])
 SQL> select extract(month from sysdate) "This Month" from dual;
 This Month
----------
6
 SQL> select extract(year from add_months(sysdate,36)) " Years" from dual;
Years
----------
2008
10。  localtimestamp()返回會話中的日期和時間
 SQL> select localtimestamp from dual;
LOCALTIMESTAMP
 -------------------------------------------------- -------------------------
 21-6月-05 10.18.15.855652上午
11。取得week number
TO_CHAR(TO_DATE('2007/05/16','YYYY/MM/DD'),'IW') AS WEEK




常用日期數據格式（該段為摘抄）
 Y或YY或YYY年的最後一位，兩位或三位Select to_char(sysdate,’YYY’) from dual； 002表示2002年
 SYEAR或YEAR SYEAR使公元前的年份前加一負號Select to_char(sysdate,’SYEAR’) from dual； -1112表示公元前111 2年
 Q季度，1～3月為第一季度Select to_char(sysdate,’Q’) from dual； 2表示第二季度①
 MM月份數Select to_char(sysdate,’MM’) from dual； 12表示12月
 RM月份的羅馬錶示Select to_char(sysdate,’RM’) from dual； IV表示4月
 Month用9個字符長度表示的月份名Select to_char(sysdate,’Month’) from dual； May後跟6個空格表示5月
 WW當年第幾週Select to_char(sysdate,’WW’) from dual； 24表示2002年6月13日為第24週
 W本月第幾週Select to_char(sysdate,’W’) from dual； 2002年10月1日為第1週
 DDD當年第幾, 1月1日為001，2月1日為032 Select to_char(sysdate,’DDD’) from dual； 363 2002年1 2月2 9日為第363天
 DD當月第幾天Select to_char(sysdate,’DD’) from dual； 04 10月4日為第4天
 D週內第幾天Select to_char(sysdate,’D’) from dual； 5 2002年3月14日為星期一
 DY週內第幾天縮寫Select to_char(sysdate,’DY’) from dual； SUN 2002年3月24日為星期天
 HH或HH12 12進制小時數Select to_char(sysdate,’HH’) from dual； 02午夜2點過8分為02
 HH24 24小時制Select to_char(sysdate,’HH24’) from dual； 14下午2點08分為14
 MI分鐘數(0～59) Select to_char(sysdate,’MI’) from dual； 17下午4點17分
 SS秒數(0～59) Select to_char(sysdate,’SS’) from dual； 22 11點3分22秒提示注意不要將MM格式用於分鐘(分鐘應該使用MI)。  MM是用於月份的格式，將它用於分鐘也能工作，但結果是錯誤的。

現在給出一些實踐後的用法：
1。 上月末天：
 SQL> select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from dual;
LASTDAY
----------
2005-05-31
2。 上月今天
 SQL> select to_char(add_months(sysdate,-1),'yyyy-MM-dd') PreToday from dual;

PRETODAY
----------
2005-05-21
 3.上月首天
 SQL> select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual;
FIRSTDAY
----------
2005-05-01
 4.按照每週進行統計
 SQL> select to_char(sysdate,'ww') from dual group by to_char(sysdate,'ww');
TO
--
25
5。 按照每月進行統計
 SQL> select to_char(sysdate,'mm') from dual group by to_char(sysdate,'mm');
TO
--
06
6。 按照每季度進行統計
 SQL> select to_char(sysdate,'q') from dual group by to_char(sysdate,'q');
T
-
2
7。 按照每年進行統計
 SQL> select to_char(sysdate,'yyyy') from dual group by to_char(sysdate,'yyyy');
TO_C
----
2005
 8.要找到某月中所有周五的具體日期
 select to_char(td,'YY-MM-DD') from (
 select trunc(sysdate, 'MM')+rownum-1 as d
 from dba_objects
 where rownum < 32) t
 where to_char(td, 'MM') = to_char(sysdate, 'MM') --找出當前月份的周五的日期
 and trim(to_char(td, 'Day')) = '星期五'
--------
03-05-02
03-05-09
03-05-16
03-05-23
03-05-30
如果把where to_char(td, 'MM') = to_char(sysdate, 'MM')改成sysdate-90，即為查找當前月份的前三個月中的每週五的日期。
 9.oracle中時間運算
內容如下：
 1、oracle支持對日期進行運算
 2、日期運算時是以天為單位進行的
 3、當需要以分秒等更小的單位算值時，按時間進制進行轉換即可
 4、進行時間進制轉換時注意加括號，否則會出問題
 SQL> alter session set nls_date_format='yyyy-mm-dd hh:mi:ss';
會話已更改。
 SQL> set serverout on
 SQL> declare
 2 DateValue date;
 3 begin
 4 select sysdate into DateValue from dual;
 5 dbms_output.put_line('原時間:'||to_char(DateValue));
 6 dbms_output.put_line('原時間減1天:'||to_char(DateValue-1));
 7 dbms_output.put_line('原時間減1天1小時:'||to_char(DateValue-1-1/24));
 8 dbms_output.put_line('原時間減1天1小時1分:'||to_char(DateValue-1-1/24-1/(24*60)));
 9 dbms_output.put_line('原時間減1天1小時1分1秒:'||to_char(DateValue-1-1/24-1/(24*60)-1/(24*60*60))) ;
 10 end;
 11 /
原時間:2003-12-29 11:53:41
原時間減1天:2003-12-28 11:53:41
原時間減1天1小時:2003-12-28 10:53:41
原時間減1天1小時1分:2003-12-28 10:52:41
原時間減1天1小時1分1秒:2003-12-28 10:52:40
 PL/SQL過程已成功完成。

在Oracle中實現時間相加處理
 --名稱：Add_Times
 --功能：返回d1與NewTime相加以後的結果，實現時間的相加
 --說明：對於NewTime中的日期不予考慮
 --日期：2004-12-07
 --版本：1.0
 --作者：Kevin

 create or replace function Add_Times(d1 in date,NewTime in date) return date
is
 hh number;
 mm number;
 ss number;
 hours number;
 dResult date;
begin
 --下面依次取出時、分、秒
 select to_number(to_char(NewTime,'HH24')) into hh from dual;
 select to_number(to_char(NewTime,'MI')) into mm from dual;
 select to_number(to_char(NewTime,'SS')) into ss from dual;
 --換算出NewTime中小時總和，在一天的百分幾
 hours := (hh + (mm / 60) + (ss / 3600))/ 24;
 --得出時間相加後的結果
 select d1 + hours into dResult from dual;
return(dResult);
 end Add_Times;

 --測試用例
 -- select Add_Times(sysdate,to_date('2004-12-06 03:23:00','YYYY-MM-DD HH24:MI:SS')) from dual

在Oracle9i中計算時間差計算時間差是Oracle DATA數據類型的一個常見問題。  Oracle支持日期計算，你可以創建諸如“日期1－日期2”這樣的表達式來計算這兩個日期之間的時間差。

一旦你發現了時間差異，你可以使用簡單的技巧來以天、小時、分鐘或者秒為單位來計算時間差。 為了得到數據差，你必須選擇合適的時間度量單位，這樣就可以進行數據格式隱藏。
使用完善複雜的轉換函數來轉換日期是一個誘惑，但是你會發現這不是最好的解決方法。
 round(to_number(end-date-start_date))-消逝的時間（以天為單位）
 round(to_number(end-date-start_date)*24)-消逝的時間（以小時為單位）
 round(to_number(end-date-start_date)*1440)-消逝的時間（以分鐘為單位）
顯示時間差的默認模式是什麼？ 為了找到這個問題的答案，讓我們進行一個簡單的SQL *Plus查詢。
 SQL> select sysdate-(sysdate-3) from dual;
SYSDATE-(SYSDATE-3)
-------------------
3
這裡，我們看到了Oracle使用天來作為消逝時間的單位，所以我們可以很容易的使用轉換函數來把它轉換成小時或者分鐘。 然而，當分鐘數不是一個整數時，我們就會遇到放置小數點的問題。
Select
(sysdate-(sysdate-3.111))*1440
from
dual;
(SYSDATE-(SYSDATE-3.111))*1440
------------------------------
4479.83333
當然，我們可以用ROUND函數（即取整函數）來解決這個問題，但是要記住我們必須首先把DATE數據類型轉換成NUMBER數據類型。
Select
round(to_number(sysdate-(sysdate-3.111))*1440)
from
dual;
ROUND(TO_NUMBER(SYSDATE-(SYSDATE-3.111))*1440)
----------------------------------------------
4480
我們可以用這些函數把一個消逝時間近似轉換成分鐘並把這個值寫入Oracle表格中。 在這個例子裡，我們有一個離線（logoff）系統級觸發機制來計算已經開始的會話時間並把它放入一個Oracle STATSPACK USER_LOG擴展表格之中。
Update
perfstat.stats$user_log
set
 elapsed_minutes =
round(to_number(logoff_time-logon_time)*1440)
where
 user = user_id
and
 elapsed_minutes is NULL;
查出任一年月所含的工作日
 CREATE OR REPLACE FUNCTION Get_WorkingDays(
 ny IN VARCHAR2
 ) RETURN INTEGER IS
 /*------------------------------------------------ ------------------------------------------
函數名稱：Get_WorkingDays
中文名稱：求某一年月中共有多少工作日作者姓名: XINGPING
編寫時間: 2004-05-22
輸入參數：NY:所求包含工作日數的年月，格式為yyyymm,如200405
返回值：整型值，包含的工作日數目。
算法描述：
 1).列舉出參數給出的年月中的每一天。 這裡使用了一個表（ljrq是我的庫中的一張表。這個表可以是有權訪問的、記錄條數至少為31的任意一張表或視圖）來構造出某年月的每一天。
 2).用這些日期和一個已知星期幾的日期相減(2001-12-30是星期天)，所得的差再對7求模。 如果所求年月在2001-12-30以前，那麼所得的差既是負數，求模後所得值範圍為大於-6,小於0，如-1表示星期六,故先將求模的結果加7，再求7的模.
 3).過濾掉結果集中值為0和6的元素，然後求count,所得即為工作日數目。
 -------------------------------------------------- -----------------------------------------------*/
 Result INTEGER;
BEGIN
 SELECT COUNT(*) INTO Result
 FROM (SELECT MOD(MOD(q.rq-to_date('2001-12-30','yyyy-mm-dd'),7),7) weekday
 FROM ( SELECT to_date(ny||t.dd,'yyyymmdd') rq
 FROM (SELECT substr(100+ROWNUM,2,2) dd
 FROM ljrq z WHERE Rownum<=31
 ) t
 WHERE to_date(ny||t.dd,'yyyymmdd')
 BETWEEN to_date(ny,'yyyymm')
 AND last_day(to_date(ny,'yyyymm'))
)q
 ) a
 WHERE a.weekday NOT IN(0,6);
 RETURN Result;
 END Get_WorkingDays;
______________________________________
還有一個版本
 CREATE OR REPLACE FUNCTION Get_WorkingDays(
 ny IN VARCHAR2
 ) RETURN INTEGER IS
 /*------------------------------------------------ -----------------------------------------
函數名稱：Get_WorkingDays
中文名稱：求某一年月中共有多少工作日作者姓名: XINGPING
編寫時間: 2004-05-23
輸入參數：NY:所求包含工作日數的年月，格式為yyyymm,如200405
返回值：整型值，包含的工作日數目。
算 法描述：使用Last_day函數計算出參數所給年月共包含多少天，根據這個值來構造一個循環。 在這個循環中先求這個月的每一天與一個已知是星期天的日期(2001-12-30是星期天)的差，所得的差再對7求模。 如果所求日期在2001-12-30以前，那麼所得的差既是負數，求模後所得值範圍為大於-6,小於0，如-1表示星期六,故先將求模的結果加7，再求7 的模.如過所得值不等於0和6（即不是星期六和星期天），則算一個工作日。
 -------------------------------------------------- --------------------------------------*/
 Result INTEGER := 0;
 myts INTEGER; --所給年月的天數
 scts INTEGER; --某天距2001-12-30所差的天數
 rq DATE;
 djt INTEGER := 1; --
BEGIN
 myts := to_char(last_day(to_date(ny,'yyyymm')),'dd');
LOOP
 rq := TO_date(ny||substr(100+djt,2),'yyyymmdd');
 scts := rq - to_date('2001-12-30','yyyy-mm-dd');
 IF MOD(MOD(scts,7)+7,7) NOT IN(0,6) THEN
 Result := Result + 1;
 END IF;
 djt := djt + 1;
 EXIT WHEN djt>myts;
 END LOOP;
 RETURN Result;
 END Get_WorkingDays;
以上兩個版本的比較
第一個版本一條SQL語句就可以得出結果，不需要編程就可以達到目的。 但需要使用任意一張有權訪問的、記錄條數至少為31的一張表或視圖。
第二個版本需要編程，但不需要表或者視圖。
這兩個版本都還存在需要完善的地方，即沒有考慮節日，如五一、十一、元旦、春節這些節假期都沒有去除。 這些節假日應該維護成一張表，然後通過查表來去除這些節假日。

求一段連續日期，但排除六日
自已標記一下，方便以後查尋
select allday from (
select to_date('2007-02-15','yyyy-mm-dd')+rownum allday from
(select to_date('2007-02-15','yyyy-mm-dd'),to_date('2007-02-28','yyyy-mm-dd') from dual)
connect by rownum < to_date('2007-02-28','yyyy-mm-dd')-to_date('2007-02-15','yyyy-mm-dd')
) where to_char(allday,'D') >1 and to_char(allday,'D') <7 

轉自 http://oracled2k.pixnet.net/blog/post/21677874-若有侵權請告知

巧妙的運用 AfterPositionChange 事件 來達到透過上一筆、下一筆來瀏覽RDB資料。

以下範例，是透過LSXODBC 將RDB 資料顯示在表單的表格中，且巧妙的使用AfterPositionChange 事件 來達到與一般設計介面一樣，在表單上使用「下一筆」、「上一筆」按鈕來輕鬆瀏覽RDB後端資料 ；可達到將AP資料完全存放在Oraclec或是其它可與Domino一起應用的資料庫軟體上，目前我是將他與Oracle一起應用。

Uselsx "*LSXODBC"
Dim con As ODBCConnection
Dim qry As ODBCQuery
Dim result As ODBCResultSet
Sub Postopen(Source As Notesuidocument)
  Set con = New ODBCConnection
  Set qry = New ODBCQuery
  Set result = New ODBCResultSet
  Set qry.Connection = con
  Set result.Query = qry
  On Event AfterFirstRow From result Call
  AfterPositionChange
  On Event AfterLastRow From result Call AfterPositionChange
  On Event AfterNextRow From result Call AfterPositionChange
  On Event AfterPrevRow From result Call AfterPositionChange
  con.ConnectTo("ATDB")
  qry.SQL = "SELECT * FROM STUDENTS ORDER BY LASTNAME"
  result.Execute
  If Not source.EditMode Then
    source.EditMode = True
  End If
  result.FirstRow
  Call source.FieldSetText("Student_No", _
  Cstr(result.GetValue("STUDENT_NO")))
  Call source.FieldSetText("FirstName", _
  result.GetValue("FIRSTNAME"))
  Call source.FieldSetText("LastName", _
  result.GetValue("LASTNAME"))
  Call source.FieldSetText("Address", _
  result.GetValue("ADDRESS"))
  Call source.FieldSetText("City", _
  result.GetValue("CITY"))
  Call source.FieldSetText("State", _
  result.GetValue("STATE"))
  Call source.FieldSetText("Zip", _
  result.GetValue("ZIP"))
  Call source.FieldSetText("Phone", _
  result.GetValue("PHONE"))
  Call source.FieldSetText("Cr_to_date", _
  Cstr(result.GetValue("CR_TO_DATE")))
End Sub
Sub Click(Source As Button)
REM Action to get the next row
  Dim workspace As New NotesUIWorkspace
  Dim uidoc As NotesUIDocument
  Set uidoc = workspace.CurrentDocument
  If Not result.IsEndOfData Then result.NextRow
  Call uidoc.FieldSetText("Student_No", _
  Cstr(result.GetValue("STUDENT_NO")))
  Call uidoc.FieldSetText("FirstName", _
  result.GetValue("FIRSTNAME"))
  Call uidoc.FieldSetText("LastName", _
  result.GetValue("LASTNAME"))
  Call uidoc.FieldSetText("Address", _
  result.GetValue("ADDRESS"))
  Call uidoc.FieldSetText("City", result.GetValue("CITY"))
  Call uidoc.FieldSetText("State", result.GetValue("STATE"))
  Call uidoc.FieldSetText("Zip", result.GetValue("ZIP"))
  Call uidoc.FieldSetText("Phone", result.GetValue("PHONE"))
  Call uidoc.FieldSetText("Cr_to_date", _
  Cstr(result.GetValue("CR_TO_DATE")))
End Sub
Sub Click(Source As Button)
REM Action to get the previous row
  Dim workspace As New NotesUIWorkspace
  Dim uidoc As NotesUIDocument
  Set uidoc = workspace.CurrentDocument
  If Not result.IsBeginOfData Then result.PrevRow
  Call uidoc.FieldSetText("Student_No", _
  Cstr(result.GetValue("STUDENT_NO")))
  Call uidoc.FieldSetText("FirstName", _
  result.GetValue("FIRSTNAME"))
  Call uidoc.FieldSetText("LastName", _
  result.GetValue("LASTNAME"))
  Call uidoc.FieldSetText("Address", _
  result.GetValue("ADDRESS"))
  Call uidoc.FieldSetText("City", result.GetValue("CITY"))
  Call uidoc.FieldSetText("State", result.GetValue("STATE"))
  Call uidoc.FieldSetText("Zip", result.GetValue("ZIP"))
  Call uidoc.FieldSetText("Phone", result.GetValue("PHONE"))
  Call uidoc.FieldSetText("Cr_to_date", _
  Cstr(result.GetValue("CR_TO_DATE")))
End Sub
Sub Queryclose(Source As Notesuidocument, Continue As Variant)
  result.Close(DB_CLOSE)
  con.Disconnect
End Sub
Sub AfterPositionChange(res As ODBCResultSet)
  Dim ws As New NotesUIWorkspace
  Dim source As NotesUIDocument
  Set source = ws.CurrentDocument
  Call source.FieldSetText("RowNumber", Cstr(res.CurrentRow))
End Sub

引用自Domino Designer 6.5 Help

unixODBC

unixODBC

Oracle Open Source ODBC Driver Project Page

Oracle Open Source ODBC Driver Project Page

Lotus Notes 與 iPhone 的資料同步 解決方案[ iPhone solution for Lotus Notes sync]

一、需具備軟體 Microsoft Office Outlook 、IBM Lotus Domino Access for Microsoft Outlook（詳細安裝過程請參照 管理員說明中IBM Lotus Domino Access for Microsoft Outlook此章節）。
二、以上軟體安裝完畢 即可將 Lotus Notes 上的 Mail、行事曆、代辦事項 等三項 直接透過 iTunes同步至IPhone手機上 。
三、至於Lotus Notes 本區的 通訊錄也同步 到 Iphone 上也可以 步驟如下：
     3.1 先將 Lotus Notes 本區的 通訊錄匯出成 *.CSV 格式。
     3.2 將*.CSV 檔案匯入 Outlook Express 通訊錄，再由Outlook Express 通訊錄匯出 其他格式中的「文字檔案（逗號分開）」，再將此檔案匯入「Microsoft Office Outlook」即大功告成；為什麼要這麼作呢？因為Microsoft Office Outlook 無法正確的將 *.CSV 檔案 正確的匯入該 聯絡人中。（PS.當然你也可以直接使用 Outlook Express 通訊錄 同步也可以）。

我的測試環境為：Microsoft Office Outlook2003、Lotus Notes6.5.6、iphone 3GS

Oracle/PLSQL: Sequences (Autonumber)

In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.
The syntax for a sequence is:

CREATE SEQUENCE sequence_name
    MINVALUE value
    MAXVALUE value
    START WITH value
    INCREMENT BY value
    CACHE value;

For example:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}. It will cache up to 20 values for performance.
If you omit the MAXVALUE option, your sequence will automatically default to:

MAXVALUE 999999999999999999999999999

So you can simplify your CREATE SEQUENCE command as follows:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

Now that you've created a sequence object to simulate an autonumber field, we'll cover how to retrieve a value from this sequence object. To retrieve the next value in the sequence order, you need to use nextval.
For example:

supplier_seq.nextval

This would retrieve the next value from supplier_seq. The nextval statement needs to be used in an SQL statement. For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.nextval, 'Kraft Foods');

This insert statement would insert a new record into the suppliers table. The supplier_id field would be assigned the next number from the supplier_seq sequence. The supplier_name field would be set to Kraft Foods.

Frequently Asked Questions

---

One common question about sequences is:
Question:  While creating a sequence, what does cache and nocache options mean? For example, you could create a sequence with a cache of 20 as follows:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

Or you could create the same sequence with the nocache option:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    NOCACHE;

Answer:  With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.
The downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not be used, will be "lost". This results in a "gap" in the assigned sequence values. When the system comes back up, Oracle will cache new numbers from where it left off in the sequence, ignoring the so called "lost" sequence values.

Note: To recover the lost sequence values, you can always execute an ALTER SEQUENCE command to reset the counter to the correct value.

Nocache means that none of the sequence values are stored in memory. This option may sacrifice some performance, however, you should not encounter a gap in the assigned sequence values.

---

Question:  How do we set the LASTVALUE value in an Oracle Sequence?
Answer:  You can change the LASTVALUE for an Oracle sequence, by executing an ALTER SEQUENCE command.
For example, if the last value used by the Oracle sequence was 100 and you would like to reset the sequence to serve 225 as the next value. You would execute the following commands.

alter sequence seq_name
increment by 124;
select seq_name.nextval from dual;
alter sequence seq_name
increment by 1;

Now, the next value to be served by the sequence will be 225.

轉自：http://www.techonthenet.com/oracle/sequences.php

Oracle日期轉換技巧

The TO_CHAR function converts a date value to character representations. You can use it within SQL statements or PL/SQL modules to present date values in more useful formats. The syntax of the TO_CHAR function used to format date values is shown here:

TO_CHAR(date_value, 'format mask')

The format mask specifies the format of the date when it is returned by the function. You can specify the output format any way you want by using valid date format models.

EX：
轉成何種格式的日期以下列是用TO_CHAR的使用方法：

TO_CHAR(欄位名稱,'YYYY/MM/DD')

使用公式取限定的小數位數

公式方法使用 ：使用公式 @text 並配合使用參數  F  固定格式（在小數點右邊第一個數字的位置設定數值）。
EX： tmp 欄位值 為 "1.23198765" 使用公式後取得值會變為"1.23"。
          @textToNumber(@text(tmp;"F,2"))

轉帖：Lotus Notes: JDBC connectivity with Oracle in Domino-Part I不錯的教學文件

Lotus Notes: JDBC connectivity with Oracle in Domino-Part I

Openoffice與Lotus Notes整合 表格欄寬調整技巧

//***********************************************************
此斷程式碼使用來調整表格的欄寬，而OpenOffice調整欄寬的方式
與MS Word調整方式不一樣；OpenOffice的基準點是以表格欄與欄之間
分隔線所在的定位點往前或往後加減來調整欄寬。
假設：第一欄[oTblColSepsM( 0 ).Position =6000]位置所在為6000那要將欄調寬，
則是由原來的位置6000+1000 那欄寬就會加寬1000各單位
變成[oTblColSepsM( 0 ).Position =7000]，第二欄由此類推。
//***********************************************************


Sub Click(Source As Button)
Dim objServiceManager As Variant
Dim objCoreReflection As Variant
Dim objDesktop As Variant
Dim objDocument As Variant
Dim objTable As Variant
Dim objCursor As Variant
Dim op As Variant
Dim objDispatch As Variant
Dim objRows As Variant
Dim objRow As Variant
Dim objCellCursor As Variant
Dim objCellText As Variant
Dim PageStyles As Variant
Dim StdPage As Variant
Dim sURL As String
Dim session As New NotesSession
Dim db As NotesDatabase
 
Dim args() As Variant
Dim argsEnd() As Variant
'The service manager is always the starting point
'If there is no office running then an office is started up

' Initialize the OpenOffice Environment
Set objServiceManager = CreateObject("com.sun.star.ServiceManager")
 
 Set objDesktop = objServiceManager.createInstance("com.sun.star.frame.Desktop")

' Initialize the Lotus Notes Environment
Set db = session.CurrentDatabase
' URL to create a new file

sUrl ="private:factory/swriter"
Set objDocument = objDesktop.loadComponentFromURL(sURL, "_blank", 0, args)

Set objDispatch = objServiceManager.createInstance("com.sun.star.frame.DispatchHelper")

Set PageStyles=objDocument.StyleFamilies.getByName("PageStyles")

Set objText= objDocument.getText()
Set objCursor= objText.createTextCursor()
' objCursor.gotoStart(False)
Set StdPage= PageStyles.getByName(objCursor.PageStyleName)
StdPage.IsLandscape=True
StdPage.Width=29700
StdPage.Height=21000
objCursor.CharFontName="Courier"
objCursor.CharHeight=10
objCursor.CharWeight=100
objCursor.CharHeight=10

Set objMTable= objDocument.createInstance( "com.sun.star.text.TextTable")


objMTable.initialize 1, 3
'Insert the table
objText.insertTextContent objCursor, objMTable, False
'Get first row
Set objRows= objMTable.getRows()
Set objRow= objRows.getByIndex(0)

Set objCellText= objMTable.getCellByName("A1")
Set objCellCursor= objCellText.createTextCursor()
' objCellCursor.setPropertyValue "CharColor",16777225
' objCellCursor.setPropertyValue "CharWeight", 75
objCellText.insertString objCellCursor, "第一欄",False


'-----------------------------------------
' Dim objCell As Variant
' Dim objCellCursorA As Variant
' Set objCell = objTable.getCellByName("A1")
' Set objCellCursorA = objTable.createCursorByCellName("A1")
objCellCursor.ParaAdjust = 2

'7.5 Setting Text Attributes
'http://api.openoffice.org/docs/common/ref/com/sun/star/awt/FontWeight.html#BOLD
' objCellCursor = objCellText.createTextCursorByRange()
' Dim objReformat
Set objReformat= objMTable.getCellRangeByName("A1:A1")
' Set objReformat= objTable.getCellByName("A1")
' objCellCursor.CharWeight =75
objReformat.CharWeightAsian=100
'-----------------------------------------------------

Dim oTblColSepsM As Variant
objMTable.LeftMargin = 100
objMTable.RightMargin = 100
Dim iWidth As Variant
iWidth=objMTable.Width
' Msgbox "iWidth: " & iWidth '115591 /11=9826.976
Dim objTableSum As Variant
objTableSum=objMTable.TableColumnRelativeSum
' Msgbox "objTableSum:="& objTableSum '10000
Dim dRatio As Double
dRatio=objTableSum/iWidth
' Msgbox "Ratio:"& dRatio '8.65111...
Dim dRelativeWidth As Double
dRelativeWidth=(1000*dRatio)/2 '約0.11公分
' Msgbox "dRelativeWidth:"&dRelativeWidth '173.02 約等於0.44公分
Dim dPosition As Double
dPosition=objTableSum-dRelativeWidth
' Msgbox "dPosition:&"& dPosition '9826.976
' Msgbox objTableSum '10000
'由此開始調整欄寬
oTblColSepsM = objMTable.TableColumnSeparators
oTblColSepsM( 0 ).Position =6000+1000
oTblColSepsM( 1 ).Position =7000+2000
objMTable.TableColumnSeparators = oTblColSepsM

'插入分頁
' objText.insertControlCharacter objCursor, 0 , False
' objCursor.BreakType=4 '//PAGE_BEFORE
objCursor.BreakType=5 '//PAGE_AFTER
objText.insertControlCharacter objCursor, 0 , False

End Sub


OpenOffice文件結構

這編文章主要在介紹OpenOffice Writer結構

API/Samples/Java/Writer/TextDocumentStructure

轉至：http://wiki.services.openoffice.org/wiki/API/Samples/Java/Writer/TextDocumentStructure
TextDocumentStructure
This example aims to demonstrate the basic structure of swriter text document and how iterating work.
The example has the following 4 aspects:

1.Initialize office and load bland document
2.Create example text
3.Iterating over text
4.Property State

---

Contents [hide] 1 ONE: Initialize office and load bland document 2 TWO: Create example text 3 THREE：Iterating over text 3.1 Step 1: create an enumeration access of all paragraphs of a document 3.2 Step 2: iterating over text's paragraphs，and create text portions for each paragraph 3.3 Step 3: through the Text portions Enumeration, get interface to each individual text portion. 4 FOUR：Property State 5 Example project download

ONE: Initialize office and load bland document

Just like other examples:
connect to Office ：declare Context -> through bootstrap to initialize the context -> get the Service manager of the context -> create Desktop instance ->querying XcomponentLoader interface -> load Component from URL (loadComponentFromURL method) -> then you'll have a document interface in Xcomponent type. All services and interface involved here has specific type:
Office Context : com.sun.star.uno.XComponentContext Service manager: com.sun.star.lang.XMultiComponentFactory Desktop: Object Component loader: com.sun.star.frame.XComponentLoader Document interface： com.sun.star.lang.XComponent

TWO: Create example text

At the first stage, we have got the document interface, thus the document is able to be manipulate via that interface.
Query the document for the XtextDocument interface (we are look at text document structure):

         // query the new document for the XTextDocument interface
          com.sun.star.text.XTextDocument xTextDocument =
              (com.sun.star.text.XTextDocument)UnoRuntime.queryInterface(
                  com.sun.star.text.XTextDocument.class, xComp);

Get document text interface(the interface to the all of document's text and its components)：

            com.sun.star.text.XText xText = xTextDocument.getText();

com.sun.star.text.XText is derived from com.sun.star.sun.XSimpleText，inherited methods of XsimpleText and XtextRange, in addition with ability of inserting and removing XtextComponent. You also can see this kind of usage in the example of GraphicsInserter. XtextContent is specifyed in OpenOffice.org as:
enables objects to be inserted into a text and to provide their location in a text once they are inserted into it.

Methods called in this example are from XSimpleText specification, via insertString method to insert a string into document/Text, and then using Cursor (XwordCursor in this case) to iterate over Text and configurate it's format. In the examples of HardFormatting has detail demonstration about foramting. Formating text could be considered as configuring the propertySet of a TextRange (via a Cursor).

          xText.setString( "This is an example sentence" );

          com.sun.star.text.XWordCursor xWordCursor =
              (com.sun.star.text.XWordCursor)UnoRuntime.queryInterface(
                  com.sun.star.text.XWordCursor.class, xText.getStart());

          xWordCursor.gotoNextWord(false);
          xWordCursor.gotoNextWord(false);
          xWordCursor.gotoEndOfWord(true);

          com.sun.star.beans.XPropertySet xPropertySet =
              (com.sun.star.beans.XPropertySet)UnoRuntime.queryInterface(
                  com.sun.star.beans.XPropertySet.class, xWordCursor );
          xPropertySet.setPropertyValue("CharWeight",
                           new Float( com.sun.star.awt.FontWeight.BOLD ));

com.sun.star.awt module is Java AWT-like user interface toolkit interface specifications for UNO.

THREE：Iterating over text

Text has XenumerationAccess interface (inherited from XelementAccess for accessing collection), it enumerates all paragraphs in a text and returns objects which support com.sun.star.text.Paragraph. This includes tables, because writer sees tables as specialized paragraphs that support the com.sun.star.text.TextTable service. For information of TextTable, please visit: TextTable.
Paragraphs also have a com.sun.star.container.XEnumerationAccess of their own. They can enumerate every single text portion that they contain. A text portion is a text range containing a uniform piece of information that appears within the text flow. An ordinary paragraph, formatted in a uniform manner and containing nothing but a string, enumerates just a single text portion. In a paragraph that has specially formatted words or other contents, the text portion enumeration returns one com.sun.star.text.TextPortion service for each differently formatted string, and for every other text content. Text portions include the service com.sun.star.text.TextRange.
In a TextDocument，the relationship maybe：

 Text (1 has n ) paragraph ( 1 contains n) text portion (1 is n) character.

Breaking down the text document structure：

Step 1: create an enumeration access of all paragraphs of a document

            // create an enumeration access of all paragraphs of a document
          com.sun.star.container.XEnumerationAccess xEnumerationAccess =
              (com.sun.star.container.XEnumerationAccess)
                  UnoRuntime.queryInterface(
                      com.sun.star.container.XEnumerationAccess.class, xText);
          xParagraphEnumeration = xEnumerationAccess.createEnumeration();

Step 2: iterating over text's paragraphs，and create text portions for each paragraph

      while ( xParagraphEnumeration.hasMoreElements() ) {
        xTextElement = (com.sun.star.text.XTextContent)
             UnoRuntime.queryInterface(
                  com.sun.star.text.XTextContent.class,
                  xParagraphEnumeration.nextElement());
              ...
              ...
        // create another enumeration to get all text portions of 
        //the paragraph
        xParaEnumerationAccess =
             (com.sun.star.container.XEnumerationAccess)
                   UnoRuntime.queryInterface(
                       com.sun.star.container.XEnumerationAccess.class,
                           xTextElement);
        xTextPortionEnum = xParaEnumerationAccess.createEnumeration();
              ...
        //step 3  Through the Text portions Enumeration, get interface to each individual text portion
              ...
         }

Step 3: through the Text portions Enumeration, get interface to each individual text portion.

From the XnumerationAccess specification (including it's parent – XelementAccess), this container interface provides to access enumeration but not objects within the enumeration, thus, querying interface are required in order to obtain the interface to access those objects. As previous metioned, Text portions include the service com.sun.star.text.TextRange (for text manipulation).

while ( xTextPortionEnum.hasMoreElements() ) {
                      com.sun.star.text.XTextRange xTextPortion =
                          (com.sun.star.text.XTextRange)UnoRuntime.queryInterface(
                              com.sun.star.text.XTextRange.class,
                              xTextPortionEnum.nextElement());
                       ...
                       ...
}

FOUR：Property State

PropertyState, an enumeration lists the states that a property value can have (it's used as a member of struct com.sun.star.beans.PropertyValue).
The state consists of two aspects:

1.whether a value is available or void,
2.whether the value is stored in the property set itself or is a default, or ambiguous.

it has 3 Status/Values：

    com.sun.star.beans.PropertyState.DIRECT_VALUE：
  The value of the property is stored in the PropertySet itself.

  com.sun.star.beans.PropertyState.DEFAULT_VALUE：
  The value of the property is available from a master (e.g., template).

  com.sun.star.beans.PropertyState.AMBIGUOUS_VALUE：
  The value of the property is only a recommendation because
  there are multiple values for this property (e.g., from a multi selection).

In this example, property state of “CharWeight” in the Text portion was checked: （pay attention to PropertyState.****_VALUE and its related System.out.println() information)

    if( xPropertyState.getPropertyState("CharWeight").equals(
       com.sun.star.beans.PropertyState.AMBIGUOUS_VALUE) )
         System.out.println( "-  The text range contains more than one different attributes" );

  if( xPropertyState.getPropertyState( "CharWeight" ).equals(
       com.sun.star.beans.PropertyState.DIRECT_VALUE ) )
          System.out.println( " - The text range contains hard formats" );

  if( xPropertyState.getPropertyState( "CharWeight" ).equals(
       com.sun.star.beans.PropertyState.DEFAULT_VALUE ) )
          System.out.println( " - The text range doesn't contains hard formats" );

For details, please visit API document

透過LotesNotes建立Openoffice空白文件上建立表格並且插入列，並且「合併儲存格」

在OpenOfficeg上使用Basic語法「合併儲存」，不像在MS Word上來的便利
試了一天半終於測試出來的..感動..希望可以提供大家參考。各位也可以直接參考
以下這篇文章：
http://codesnippets.services.openoffice.org/WriterWriter.MergeTableCells.snip

'/==================================
'透過LotesNotes建立Openoffice空白文件
'上建立表格並且插入列，並且「合併儲存格」
'/==================================

Sub Click(Source As Button)
Dim objServiceManager As Variant
Dim objDesktop As Variant
Dim objDocument As Variant
Dim objTable As Variant
Dim objCursor As Variant

Dim objRows As Variant
Dim objRow As Variant
Dim objCellCursor As Variant
Dim objCellText As Variant

Dim sURL As String
Dim session As New NotesSession
Dim db As NotesDatabase
Dim args() As Variant
Dim argsEnd() As Variant

' Initialize the OpenOffice Environment
Set objServiceManager = CreateObject("com.sun.star.ServiceManager")
Set objDesktop = objServiceManager.createInstance("com.sun.star.frame.Desktop")

' Initialize the Lotus Notes Environment
Set db = session.CurrentDatabase
' URL to create a new file
sUrl ="private:factory/swriter"
Set objDocument = objDesktop.loadComponentFromURL(sURL, "_blank", 0, args)

Set objText= objDocument.getText()
Set objCursor= objText.createTextCursor()

Set objTable= objDocument.createInstance( "com.sun.star.text.TextTable")

objTable.initialize 2, 4
'Insert the table
objText.insertTextContent objCursor, objTable, False
'Get first row
Set objRows= objTable.getRows()
Set objRow= objRows.getByIndex(0)

Set objCellText= objTable.getCellByName("A1")
Set objCellCursor= objCellText.createTextCursor()
objCellCursor.setPropertyValue "CharColor",255
objCellText.insertString objCellCursor, "第一欄", False

'-----------------------------------------
' xTextTableCursor=xTextTable.createCursorByCellName("A"row) '-- create cursor
' xTextTableCursor=gotoCellByName("B"row, .true) '-- select area up toand including this cell
' xTextTableCursor=mergeRange '-- merge selectedcells

' insertIntoCell "A1","第一欄", objTable
' insertIntoCell "B1","第二欄", objTable
' insertIntoCell "C1","第三欄", objTable
' insertIntoCell "D1","加總", objTable
' insertByIndex( [in] long nIndex, [in] long nCount );
' com.sun.star.text.XTableRows xRows = xTextTable.getRows();
' insertByIndex( 6,4 ),objTable
' xRows.insertByIndex(xRows.getCount()-1, 0);
' 加入動態插入列
objRows.insertByIndex objRows.getCount(), 6
'測試合併儲存格
Dim objTextTableCursor As Variant
Set objTextTableCursor=objTable.createCursorByCellName("A1")
objTextTableCursor.gotoCellByName("A4") ,True
objTextTableCursor.mergeRange

End Sub

與LotesNotes建立Openoffice空白文件「直向」轉換為「橫向」作法

//==============================
透過Lotus Notes 建立OpenOffice並將文件由
「直向」轉換為「橫向」。
//==============================

Sub Click(Source As Button)
Dim objServiceManager As Variant
Dim objDesktop As Variant
Dim objDocument As Variant

Dim sURL As String
Dim session As New NotesSession
Dim db As NotesDatabase
Dim args() As Variant
Dim argsEnd() As Variant

Dim objCursor As Variant
Dim objText As Variant
Dim PageStyles As Variant
Dim StdPage As Variant

' Initialize the OpenOffice Environment
Set objServiceManager = CreateObject("com.sun.star.ServiceManager")
Set objDesktop = objServiceManager.createInstance("com.sun.star.frame.Desktop")
' Initialize the Lotus Notes Environment
Set db = session.CurrentDatabase
' URL to create a new file
sUrl ="private:factory/swriter"
Set objDocument = objDesktop.loadComponentFromURL(sURL, "_blank", 0, args)

'切換頁面
Set PageStyles=objDocument.StyleFamilies.getByName("PageStyles")
Set objText = objDocument.getText()
Set objCursor = objText.createTextCursor()
objCursor.gotoStart(False)

Set StdPage= PageStyles.getByName(objCursor.PageStyleName)
StdPage.IsLandscape=True
StdPage.Width=29700
StdPage.Height=21000
End Sub

透過OpenOffice巨集將文件頁面由「直向」轉換為「橫向」的作法

//========================
以下巨集是在OpenOffice下執行可透過巨集控制直接，
將文件頁面由「直向」轉換為「橫向」。
//========================
sub nym1
Dim Doc As Object
Dim DateTimeField As Object
Dim PageStyles As Object
Dim StdPage As Object
Dim FooterCursor As Object
Dim PageNumber As Object
Doc = StarDesktop.CurrentComponent
PageNumber = Doc.createInstance("com.sun.star.text.textfield.PageNumber")
PageNumber.NumberingType = com.sun.star.style.NumberingType.ARABIC
PageStyles = Doc.StyleFamilies.getByName("PageStyles")
StdPage = PageStyles("Default")
StdPage.IsLandscape = True
StdPage.Width = 29700
StdPage.Height = 21000
end sub

Tables - OpenOffice.org 文件的表格架構觀念與閱讀方式

Tables - OpenOffice.org 文件表格架構概念與閱讀方式

透過LotesNotes建立Openoffice空白文件上建立表格並且插入列

//--------------------------------------------------------------
透過Lotus Notes按鈕建立一份空白文件，並且在文件上建立表格
建立表格後，在插入列，便於未來的表格應用。
//--------------------------------------------------------------

Sub Click(Source As Button)
Dim objServiceManager As Variant
Dim objDesktop As Variant
Dim objDocument As Variant
Dim objTable As Variant
Dim objCursor As Variant

Dim objRows As Variant
Dim objRow As Variant
Dim objCellCursor As Variant
Dim objCellText As Variant


Dim sURL As String
Dim session As New NotesSession
Dim db As NotesDatabase
Dim args() As Variant
Dim argsEnd() As Variant

' Initialize the OpenOffice Environment
Set objServiceManager = CreateObject("com.sun.star.ServiceManager")
Set objDesktop = objServiceManager.createInstance("com.sun.star.frame.Desktop")

' Initialize the Lotus Notes Environment
Set db = session.CurrentDatabase
' URL to create a new file
sUrl ="private:factory/swriter"
Set objDocument = objDesktop.loadComponentFromURL(sURL, "_blank", 0, args)

Set objText= objDocument.getText()
Set objCursor= objText.createTextCursor()
'建立表格
Set objTable= objDocument.createInstance( "com.sun.star.text.TextTable")
建立2列4欄的表格
objTable.initialize 2, 4
'Insert the table
objText.insertTextContent objCursor, objTable, False
'Get first row
Set objRows= objTable.getRows()
Set objRow= objRows.getByIndex(0)
'將第一欄填入名稱
Set objCellText= objTable.getCellByName("A1")
Set objCellCursor= objCellText.createTextCursor()
objCellCursor.setPropertyValue "CharColor",255
objCellText.insertString objCellCursor, "第一欄", False
'插入6列，使表格變成8列4欄
objRows.insertByIndex objRows.getCount(), 6
End Sub

參考網站：http://api.openoffice.org/docs/common/ref/com/sun/star/table/XTableRows.html#insertByIndex

透過LotesNotes建立Openoffice空白文件

Sub Click(Source As Button)
Dim objServiceManager As Variant
Dim objDesktop As Variant
Dim objDocument As Variant

Dim sURL As String
Dim session As New NotesSession
Dim db As NotesDatabase
Dim args() As Variant
Dim argsEnd() As Variant

' Initialize the OpenOffice Environment
Set objServiceManager = CreateObject("com.sun.star.ServiceManager")
Set objDesktop = objServiceManager.createInstance("com.sun.star.frame.Desktop")

' Initialize the Lotus Notes Environment
Set db = session.CurrentDatabase
' URL to create a new file
sUrl ="private:factory/swriter"

Set objDocument = objDesktop.loadComponentFromURL(sURL, "_blank", 0, args)

End Sub

Installing Lotus OEM ODBC on Unix

Installing Lotus OEM ODBC on Unix

Domino替代名稱

這是r5管理員內的說明(在系統維護之下), cut部分內容供你參考, 主要是用cert.id來認證cert.id來新增替代名稱, 之後再用此cert.id去註冊公司人員, 就可使用替代名稱了

新增替代名稱到認證者
在此程序中，您指派替代名稱及其相關的語言給組織認證者 ID 及其組織單位 (子項) 認證者。您先重新認證組織認證者，然後使用認證者來重新認證其組織單位認證者。
1. 對於您要新增替代名稱的認證者 ID，必須具有存取權。
2. 按一下「Domino 管理員」中的「設定」索引標籤。
3. 選擇「認證」，然後按一下「認證」。
4. 選取認證者 ID，然後輸入密碼。
5. 選取您要重新認證的 ID，然後輸入密碼。欲新增替代語言及名稱到組織 (根) 認證者，請選取步驟 4 及 5 中的同一 ID。
6. 按一下「新增」。
7. 在「語言」欄位中選擇替代語言。如果您要重新認證組織單位認證者，可供使用的語言包括所有與組織 (根) 認證者 ID 相關的語言。
8. (選擇性) 輸入組織的國碼。此選項僅供組織認證者 ID 使用。
9. 在「組織/組織單位」欄位中輸入組織/組織單位的名稱。
10. 按一下「確定」。
11. (選擇性) 欲新增另一個替代語言，請按一下「新增」按鈕，並重複步驟 7 到 10。
12. 按一下「確認」。

Lotus Notes/Domino 7 application performance: Part 2: Optimizing database views

Lotus Notes/Domino 7 application performance: Part 2: Optimizing database views

Lotus Notes/Domino 7 application performance: Part 1: Database properties and document collections

Lotus Notes/Domino 7 application performance: Part 1: Database properties and document collections

Code exports Lotus Notes data to an OpenOffice Calc spreadsheet

Code exports Lotus Notes data to an OpenOffice Calc spreadsheet

Debian Linux安裝記錄

作者：NoInfoNoFear

这几天安装了最新的debian，通过网络安装的。
贴出来共享，希望大家指教。


1.安装基本系统(debian-31r2-i386-netinst.iso)

(1)用cdrom启动，选择expert26，按照步骤安装，注意要选择2.6的内核，还有，一定
要设置好网络，设置的内容会直接存入配置文件，否则你要手工修改配置文件来设
置网络，因为此时我们只拥有基本系统。

(2)安装过程中重启后，按照步骤一步步设置，但要跳过“选择并安装软件包”这一项，
因为它总是出错（反正我安装的时候一直都出错）。
我使用的apt源是：
台湾：ftp://ftp.tku.edu.tw
日本：ftp://ftp2.jp.debian.org
实测这两个网站最快，我使用的是网通宽带。
尽量不要使用国内的apt源，不知为何，速度都很慢。

(3)手工重启，因为这是处在一个奇怪的状态中（安装程序的FrameBuffer模式，其实
可以不重启，直接进行下一步。不过不重启，zhcon可能无法正常工作）。

现在得到的是一个最基本的debian环境。准备在这个环境中安装其他软件。

2.安装基本工具

(1) 启动后，以root登录后，执行下面的命令，将字符集改为英文。这是因为此时没
有任何显示汉字的环境，如果默认字符集是中文，我们看到的提示信息是乱码。
# export LANG=C

(2) 刷新apt源。这一步不是必需的，因为安装基本系统时已经刷新了。
# apt-get update

(3) 保存内核配置
# cp /boot/config-2.6.8-2-386 ~/

(4) 安装zhcon字符界面中文环境
# apt-get install zhcon
安装时会弹出一个界面，要求修改locales，请设置好，最后将默认locale成zh_CN，
设置的结果在重新登录时才起作用。会出现警告信息，不用理会这些信息，直接回答Y安装。
因为升级glibc，安装过程会询问你是否删除当前运行的内核影像，你可以删除它，后面会
编译最新内核，它已经没必要存在了，但删除后千万不要重启机器。

(4) 进入字符界面中文环境
# logout
# 登录
# zhcon
重新登录是为了保证locale设置成zh_CN

(5) 安装编译内核的工具
# apt-get install kernel-package
# apt-get install libncurses5-dev
# apt-get install fakeroot
# apt-get install module-init-tools
# apt-get install initrd-tools
# apt-get install procps
# apt-get install wget tar bzip2 vim lftp


3. 安装新内核
(1) 下载内核 2.6.17.3
# cd /usr/src
# wget http://www.kernel.org/pub/linux/kern...6.17.3.tar.bz2
(2) 解包
# tar xvfj linux-2.6.16.3.tar.bz2
(3) 配置和编译
# ln -sv linux-2.6.16.3 linux
# cd linux
# make prproper
# cp ~/config-2.6.8-2-386 .config
# make menuconfig
这一步很麻烦，非常不好配，我的声卡现在还美哟配好，唉！慢慢诼磨吧。
# make
# make modules_install
(4) 保存内核及配置
# cp .config /boot/config-2.6.17.3
# cp System.map /boot/System.map-2.6.17.3
# cp arch/i386/boot/bzImage /boot/kernel-2.6.17.3
(5) 制作内存镜像
# cd /boot
# mkinitrd -o /boot/initrd.img-2.6.17.3 2.6.17.3
(5) 修改/boot/grub/menu.lst
将vmlinux-2.6.8-2-386 改为kernel-2.6.17.3
将initrd.img-2.6.8-2-386 改为initrd.img-2.6.17.3
(6) 重启，注意每次重启登录后，需要运行zhcon，进入汉字环境。

4. 更新系统
# apg-get upgrade

5. 安装基本图形界面
# apt-get install x-window-system-core
安装后会出现配置界面，按照提示配置。安装好后，启动试试。
# startx
如果不成功，可以用下面的命令重新配置：
# dpkg-reconfigure xserver-xorg

6. 安装中文字体
# cd /usr/share/fonts
# mkdir zh_CN
# cd zh_CN
将字体文件拷贝到此目录下
# mkfontscale
# mkfontdir
修改/etc/X11/xorg.cong，找到FontPath行，增加一行：
FontPath "/usr/share/fonts/zh_CN"

7. 安装gnome核心
# apt-get install gnome-core
安装后，直接启动
# startx
这时就进入gnome环境了，而且汉字也显示出来了。

8. 安装小企鹅中文输入法
# apt-get install fcitx
# cd /etc/X11/Xsession.d
增加一个文件：40xfcitx-startup，内容是
export XMODIFIERS="@im=fcitx"
fcitx
启动图形界面，输入法可以使用了。

9. 安装kde核心
# apt-get install kde-core
# apt-get install kde-i18n-zhcn
增加一个文件 ~/.xinitrc，内容是：
export XMODIFIERS="@im=fcitx"
fcitx &
exec startkde
执行startx就进入KDE的设置界面了，而且小企鹅输入法也配置好了。
如果想用startx启动gnome界面，将~/.xinitrc文件删除即可。

但是，安装可Kde后，gnome的“应用程序菜单”打开时一直闪烁，无法使用。这好像是一个Bug
大家的说法各不相同，详情请看： http://bbs.linuxsir.org/showthread.php?t=254773&page=2
我使用了上面的一个方法，就是退出图形界面，执行：
# apt-get install menu
问题解决了！

10.安装firefox浏览器
# apt-get install firefox
安装完后需要重新启动，否则出错
可惜firefox界面是英文的，我不知道怎么改成中文的。

11.安装完整版的gnome
# apt-get install gnome
很耗时，我没有测过。

12.安装完整版的kde
# apt-get install kde
很耗时，我也没有测过。
轉置：http://www.oklinux.cn/html/Basic/azpz/20070626/30497.html

Dlink USB dwa-110 無線網卡在Debian linux 上設定方法

---

Ralink RT2501USB/RT2571W (RT73) devices

This page describes how to enable support for WiFi devices based on the Ralink RT2501USB chipset on Debian systems.

Contents

1. Ralink RT2501USB/RT2571W (RT73) devices
   1. Drivers
   2. Installation
      1. rt73usb (next-generation driver)
         1. Lenny
         2. Etch with 2.6.18 kernel
         3. Etch with 2.6.24 kernel
      2. rt73 (enhanced legacy driver)
         1. Lenny
         2. Etch
   3. Supported Devices
   4. See Also
   5. External Links

Drivers

The Ralink RT2501USB wireless LAN chipset is supported by three native drivers:

• rt73 (Legacy driver)

  • Released by Ralink under the GPL.
  • Not generally recommended for use.

• rt73 (Enhanced legacy driver)

  • Source provided by the rt73-source package.

• rt73usb (Next-generation driver)

  • The recommended driver to use.
  • Included in the mainline kernel starting from 2.6.24.

  • Source previously packaged in rt2x00-source, removed from Debian to resolve bug 474189.

Both the enhanced legacy and next-generation drivers are maintained by the rt2x00 project. Supported devices are listed at the end of this page.

All drivers require non-free firmware for their operation. This can be provided by the firmware-ralink package.

The RT2501USB is not to be confused with the RT2500USB, which is supported by different drivers.

Installation

rt73usb (next-generation driver)

Lenny

Using Etch? Skip to the Etch section.

As drivers from the rt2x00 project are included in the mainline kernel, the rt73usb driver is present on your system. Firmware is required to be installed prior to driver operation:

1. Add a "non-free" component to /etc/apt/sources.list, for example:

   # Debian Lenny
   deb http://ftp.us.debian.org/debian lenny main contrib non-free

2. Update the list of available packages, then install the firmware-ralink and wireless-tools packages:

   aptitude update
   aptitude install firmware-ralink wireless-tools

3. The necessary kernel module should be automatically loaded. If necessary, it can be manually loaded via:

   modprobe rt73usb

   If not already performed, connect the device to your system.

4. Verify your device has an available interface:

   iwconfig

5. Raise the interface to activate the radio, for example:

   ifconfig wlan0 up

6. Configure your wireless interface as appropriate.

Troubleshooting

• Examine the kernel ring buffer (dmesg) to verify the required firmware files are being loaded by the driver. Lack of firmware is the usual reason for the "SIOCSIFFLAGS: No such file or directory" error message.

  • Ensure the firmware-ralink package is installed.

Etch with 2.6.18 kernel

" src="http://wiki.debian.org/htdocs/modern/img/attention.png" title="" width="15" height="15"> Although an etch/rt2x00-source package is available, this version is considered experimental and only supports a small number of devices. The devices which are supported are likely to encounter issues.

It is highly recommended to use an EtchAndAHalf kernel image, which will provide your system with an updated rt73usb driver included as part of the kernel. You will not need to use etch/rt2x00-source.

Install an EtchAndAHalf kernel image (apt-cache search linux-image-2.6.24-etchnhalf and install a package appropriate for your system), restart your system to boot using this kernel, then follow the Etch with 2.6.24 kernel procedure below.

Etch with 2.6.24 kernel

As drivers from the rt2x00 project are included in the mainline kernel, the rt73usb driver is present on your system. Firmware is required to be installed prior to driver operation:

1. Install the etch/wireless-tools package:

   aptitude update
   aptitude install wireless-tools

2. Add the Debian Backports repository to /etc/apt/sources.list, also including the "non-free" component. For example:

   # Backported packages for Debian Etch
   deb http://www.backports.org/debian etch-backports main contrib non-free

3. Update the list of available packages and install the etch-backports/debian-backports-keyring package:

   aptitude update
   aptitude -t etch-backports install debian-backports-keyring

   As your system does not yet have the GnuPG archive key of the backports.org repository installed, you will be asked to confirm installation of a package from an untrusted repository. Enter Yes when prompted.

4. Install the etch-backports/firmware-ralink package:

   aptitude update
   aptitude -t etch-backports install firmware-ralink

5. The necessary kernel module should be automatically loaded. If necessary, it can be manually loaded via:

   modprobe rt73usb

   If not already performed, connect the device to your system.

6. Verify your device has an available interface:

   iwconfig

7. Raise the interface to activate the radio, for example:

   ifconfig wlan0 up

8. Configure your wireless interface as appropriate.

Troubleshooting

• Examine the kernel ring buffer (dmesg) to verify the required firmware files are being loaded by the driver. Lack of firmware is the usual reason for the "SIOCSIFFLAGS: No such file or directory" error message.

  • Ensure the etch-backports/firmware-ralink package is installed.

rt73 (enhanced legacy driver)

Lenny

While use of the next-generation driver is preferred, the enhanced legacy driver can alternatively be used. This is available as a pre-built module packages made against Debian stock kernels, but can also be generated on your system via module-assistant.

Pre-Built Module

1. Add "contrib" and "non-free" components to /etc/apt/sources.list. For example:

   # Debian Lenny
   deb http://ftp.us.debian.org/debian lenny main contrib non-free

2. Update the list of available packages. Install the firmware-ralink, rt73-common, the relevant enhanced legacy module and wireless-tools packages:

   aptitude update
   aptitude install firmware-ralink rt73-common rt73-modules-$(uname -r) wireless-tools

3. Load the enhanced legacy driver:

   modprobe rt73

   This module will be automatically loaded when your system is restarted and the device is inserted. If not already performed, connect the device to your system.

4. Verify your device has an available interface:

   iwconfig

5. Raise the interface to activate the radio, for example:

   ifconfig wlan0 up

6. Configure your wireless interface as appropriate.

Module Assistant

1. Add "contrib" and "non-free" components to /etc/apt/sources.list. For example:

   # Debian Lenny
   deb http://ftp.us.debian.org/debian lenny main contrib non-free

2. Update the list of available packages. Install the firmware-ralink, module-assistant, rt73-common and wireless-tools packages:

   aptitude update
   aptitude install firmware-ralink module-assistant rt73-common wireless-tools

3. Build and install a rt73-modules-* package within your system:

   m-a prepare
   m-a a-i rt73

4. Load the enhanced legacy driver:

   modprobe rt73

   This module will be automatically loaded when your system is restarted and the device is inserted. If not already performed, connect the device to your system.

5. Verify your device has an available interface:

   iwconfig

6. Raise the interface to activate the radio, for example:

   ifconfig wlan0 up

7. Configure your wireless interface as appropriate.

Troubleshooting

• Examine the kernel ring buffer (dmesg) to verify the required firmware files are being loaded by the driver. Lack of firmware is the usual reason for the "SIOCSIFFLAGS: No such file or directory" error message.

  • Ensure the firmware-ralink package is installed.

Etch

You are required to acquire and compile the rt2x00 sources, as these are not packaged. This procedure is documented at the rt2x00 project's website: http://rt2x00.serialmonkey.com/wiki/index.php?title=Rt2x00_GIT_instructions

Supported Devices

The page HowToIdentifyADevice/USB explains how to identify a USB device.

The following list is based on the alias fields of modinfo rt73usb on Lenny.

• USB: 0411:00F4 MelCo., Inc. (Device name unknown)
  USB: 050D:7050 Belkin Components F5D7050 ver 1000 WiFi
  USB: 050D:705A Belkin Components F5D7050A Wireless Adapter
  USB: 050D:905B Belkin Components F5D9050 ver 3 Wireless Adapter
  USB: 050D:905C Belkin Components Wireless G Plus MIMO Network Adapter
  USB: 06F8:E010 Guillemot Corp. HWGUSB2-54-LB
  USB: 06F8:E020 Guillemot Corp. HWGUSB2-54V2-AP
  USB: 0769:31F3 Surecom Technology Corp. RT2573
  USB: 07AA:002E Corega K.K. (Device name unknown)
  USB: 07B8:B21D D-Link Corp. RT2573
  USB: 07D1:3C03 D-Link System DWL-G122 802.11g Adapter [ralink rt73]
  USB: 07D1:3C04 D-Link System WUA-1340
  USB: 07D1:3C06 D-Link System (Device name unknown)
  USB: 07D1:3C07 D-Link System Wireless G DWA-110 Adapter
  USB: 0B05:1723 ASUSTek Computer, Inc. WL-167G v2 802.11g Adapter [ralink]
  USB: 0B05:1724 ASUSTek Computer, Inc. RT2573
  USB: 0DB0:6874 Micro Star International RT2573
  USB: 0DB0:6877 Micro Star International RT2573
  USB: 0DB0:A861 Micro Star International RT2573
  USB: 0DB0:A874 Micro Star International RT2573
  USB: 0DF6:90AC Sitecom Europe B.V. WL-172
  USB: 0DF6:9712 Sitecom Europe B.V. WL-113 rev 2
  USB: 1044:8008 Chu Yuen Enterprise Co., Ltd GN-WB01GS
  USB: 1044:800A Chu Yuen Enterprise Co., Ltd GN-WI05GS
  USB: 1371:9022 Dick Smith Electronics RT2573
  USB: 1371:9032 Dick Smith Electronics C-Net CWD-854 rev F
  USB: 13B1:0020 Linksys WUSB54GC 802.11g Adapter [ralink rt73]
  USB: 13B1:0023 Linksys WUSB54GR
  USB: 1472:0009 Huawei-3Com Aolynk WUB320g
  USB: 148F:2573 Ralink Technology, Corp. RT2501USB Wireless Adapter
  USB: 148F:2671 Ralink Technology, Corp. RT2601USB Wireless Adapter
  USB: 14B2:3C22 Atheros Communications Inc C54RU
  USB: 15A9:0004 (Vendor name unknown) (Device name unknown)
  USB: 1631:C019 Good Way Technology RT2573
  USB: 1690:0722 Askey Computer Corp. [hex] RT2573
  USB: 1740:7100 Senao (Device name unknown)
  USB: 18E8:6196 Qcom RT2573
  USB: 18E8:6229 Qcom RT2573
  USB: 18E8:6238 Qcom (Device name unknown)
  USB: 2019:AB01 PLANEX GW-US54HP
  USB: 2019:AB50 PLANEX GW-US54Mini2

See Also

• rt2500usb - Help page for Ralink RT2500USB devices

• How To use Wifi network adapter

External Links

• http://rt2x00.serialmonkey.com/ - enhanced legacy and next-generation driver homepage.