Oracle eBS經驗分享

Using Message Dictionary in PL/SQL

Document reference ..http://oraclemaniac.com/2012/03/12/using-fnd_message-to-show-formatted-text/

Using Regular Expressions in Oracle

If you want to send the output report when you submit concurrent program.

You can use the standard function.

If we need write Oracle PL/SQL Program for concurrent program using.

We need create procedure (ex. xx_procedure)

This xx_procedure need has base two parameter.(errbuf out varchar2,retcode out number).

• Errbuf : Errbuf is one the paramter which will define in pl/sql to store procedure to get error messges into log file.
• Retcode: this parameter will be used to get the status of the concurrent program. It will give the values
  - 0 for sucess
  - 1 for warning
  - 2 for errors

1.FND_GLOBAL.USERID --Returns userid

2.FND_GLOBAL.APPS_INTIALIZE procedure

•    APPS_INITIALIZE(user_id in number,resp_id in number,resp_appl_id in number);
     This is used to set the values userid and responsibilityid for a session
  3.FND_GLOBAL.LOGIN_ID -Gives login id
  4.FND_GLOBAL.CONC_LOGIN_ID--Not sure how to use this
  5.FND_GLOBAL.PROG_APPL_ID--Concurrent program application id
  6.FND_GLOBAL.CONC_PROGRAM_ID--Concurrent program id
  7.FND_GLOBAL.CONC_REQUEST_ID (Server)This will give the concurrent request id of the program which is calling the plsql package..

ln_Request_id:=FND_GLOBAL.CONC_REQUEST_ID;

連接方式可以分為透明閘道和通用連接兩種。
透明閘道（Transparent Gateways） 透明閘道使用Oracle提供的特定閘道程式來設置代理，例如連接SQL Server則必須要有SQL Transparent Gateway for SQL Server。
通用連接（Generic Connectivity） 通用連接又分為ODBC連接和OLE DB連接兩種，其連接方法和透明閘道沒有本質區別，只不過通用連接是和資料庫一起提供的功能，你不需要向Oracle購買相關的透明閘道程式

使用fnd_file Package

Collection Type 有3種

1. PL/SQL Table : 只存在Memory 裡，效能較好

2. Nested Table：可以存在DB中，可以被DB Table 拿來宣告成DB column

3. VARRAYs ：可以存在DB中，可以被DB Table 拿來宣告成DB column

=Case=<PL/SQL Table>==

DECLARE
  TYPE para_tab IS TABLE OF fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE   INDEX BY BINARY_INTEGER;
  para_list   para_tab;
BEGIN
   FOR ass_r IN (SELECT RANK () OVER (ORDER BY column_seq_num) seq  -- , form_left_prompt
                ,      UPPER ('P_' || REPLACE (form_left_prompt, ' ', '_') ) parameter
                   FROM fnd_descr_flex_col_usage_vl
                  WHERE descriptive_flexfield_name = '$SRS$.DITAR_PR2009005' AND application_id = 222)
   LOOP
      para_list (ass_r.seq) := ass_r.parameter;
   END LOOP;

   FOR x IN para_list.FIRST .. para_list.LAST
   LOOP
      DBMS_OUTPUT.put_line (para_list (x) );
   END LOOP;
END;

 Insert by Using RECORD Type Variable

Doc ID:106513.1     Type: BULLETIN

Overview
--------

The UTL_SMTP package is a new package included in the Oracle8i Release 2
(8.1.6).  This package allows PL/SQL to generate e-mail messages using the 
UTL_SMTP package.  UTL_SMTP is discussed in the documentation 
'PL/SQL Packages and Types Reference'. 

When using Oracle 8i (8.1.5 to 8.1.7) it is required to install the Oracle JVM 
inside the database, because the UTL_SMTP is implemented by usage of mapped 
JVM libraries - i.e. JAVA classes. See Note 132936.1

Beginning with Oracle 9i (9.0.1) and higher, the UTL_SMTP is using library 
functions implemented in C for gaining performance and enabling databases 
to use these packages without installed JVM.

To install the UTL_SMTP package, use SQL*Plus and run as user "SYS" 
the following files located in ORACLE_HOME/rdbms/admin

utlsmtp.sql
prvtsmtp.plb

additionally in 10g it is possible to use UTL_MAIL 

utlmail.sql
prvtmail.plb

See also Note 369777.1


Generating E-mail Using UTL_SMTP
--------------------------------

Perform the following steps to generate e-mail messages using the UTL_SMTP 
package:

  1) Establish a connection with the SMTP server (usually port 25).  This is
     done through a UTL_SMTP.OPEN_CONNECTION() function call.  This function
     returns the connection record to be used in subsequent calls.

  2) Perform the initial handshake with the SMTP server.  This is done through
     a UTL_SMTP.HELO() call, or optionally through the UTL_SMTP.EHLO() call.

  3) Start the mail message by specifying the 'From' mail ID.  This is done
     through a UTL_SMTP.MAIL() call.

  4) Specify the recipients for the e-mail message.  This can be done through
     the UTL_SMTP.RCPT() call.

  5) Assemble the body of the e-mail message with the redundant recipient list.
     If the body of the e-mail does not contain this information, it will not
     be received in the resulting e-mail.  

     When assembling the body, RFC 821 requires that the lines to be terminated
     by <CR><LF> which is a character 13 and character 10 (accomplished in 
     PL/SQL by using: CHR(13)||CHAR(10)).

  6) Pass the body of the message into the UTL_SMTP buffer by calling the
     UTL_SMTP.DATA().  

     Note:  This function performs the RFC specified termination of 
            <CR><LF>.<CR><LF> to denote the end of the data.

  7) Close the SMTP connection through the UTL_SMTP.QUIT() call.

Subject:  Simple Example of Sending Attachments Using UTL_SMTP

Doc ID:  414062.1

Modified Date: 15-MAY-2008

Applies to:

PL/SQL - Version: 9.2.0.1 to 10.2.0.1
Information in this document applies to any platform.

Goal

How to send attachments using the PL/SQL package UTL_SMTP.  The sample code uses the DBMS_LOB package to open and read the external file.

Solution

/*  設定SQL Session的Client Org Information */
begin
  dbms_application_info.set_client_info(88);
end;

今天請教天翔Oracle ERP中有無查看SOB、ORG關係的View？

厲害的天翔告訴我就是這一個=>  ORG_ORGANIZATION_DEFINITIONS

提供給大家參考，為了方便串所有的Org releationship 我改寫如下：

utl_file 寫入檔案的過程 : [utl_file.put]資料寫入檔案-->先寫入Buffer-->Buffer滿了,utl_file.fflush執行了才會將Buffer資料寫入檔案

• 善心人士:天翔  提供
  • 程式功能: 使用一段程式，包含多個SQL Query ，利用UTL_FILE 將資料輸出到 Excel File
  • 使用方式: 請參考 "天翔的說明文件"
  • 程式取得:
    • Intall Package
    • 範例程式 (要注意Excel_Path的預設值,如果你沒權限設定Directory,你就先指定成'usr/tmp'目錄吧! 我就是這樣測的喔~~)

特別感謝天翔的技術分享

感謝大家的技術分享與研究
前些日子有人提供了由Oracle ERP view output 直接產出Excel File 的參考連結（如下網址）... 
昨天終於測出來了…
參考文章： http://garethroberts.blogspot.com/2007/10/excel-file-output-from-oracle.html

步驟如下：
1. 編譯共用程式庫：owa_sylk_apps  => 我這個版本是可以輸出中文的
2. Update PCL mime Type value => 請先確認你們的PCL (Printer Control Language是沒有在用的)
3. 編譯測試範例： Concurrent Package XXXV8_USERS_SYLK_PKG
4. 註冊你的concurrent program ,please reference this website here
5. Submit your request and view output

2007/09/12

你想使用Oracle Procedure幫你產生Excel Report嗎?

況且產生一個具有Summary功能的Excel檔案嗎?

這個Procedure你一定要看一下唷!!

我試過了，但是這個Procedure沒有辦法產生正常的中文文字唷，如果你Select的欄位是中文的，是會無法產出了。

有空的人可以在幫忙試試怎麼改讓它可以呈現中文的?

Reference Web Site

Function: 

1. To_Char:to_char(effective_date,'yyyy') => 傳出為文字型態
2. EXTRACT:EXTRACT (year  FROM effective_date) => 傳出為數字型態

1. Rownum:rownum 是一個虛擬欄位,但你不知道，Oracle的rownum是怎麼排序的，無法控制它的排序規則。
2. row_number() over( order by 字段名1,字段名2,...字段名n ):依據你Order by的column排序
3. row_number() over(PARTITION BY deptno order by sal ):在每一個小分區內部取序號

資訊來源:藍色小舖