目前分類:PL/SQL (20)

瀏覽方式: 標題列表 簡短摘要

Using Message Dictionary in PL/SQL

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

文章標籤

SomebabyTina 發表在 痞客邦 留言(0) 人氣()

Using Regular Expressions in Oracle

SomebabyTina 發表在 痞客邦 留言(0) 人氣()

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

You can use the standard function.

SomebabyTina 發表在 痞客邦 留言(6) 人氣()

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

SomebabyTina 發表在 痞客邦 留言(1) 人氣()

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;

SomebabyTina 發表在 痞客邦 留言(0) 人氣()

文章標籤

SomebabyTina 發表在 痞客邦 留言(0) 人氣()

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

SomebabyTina 發表在 痞客邦 留言(3) 人氣()

使用fnd_file Package

SomebabyTina 發表在 痞客邦 留言(0) 人氣()

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;

SomebabyTina 發表在 痞客邦 留言(0) 人氣()

 Insert by Using RECORD Type Variable

SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(BYTE),
  3    First_Name         VARCHAR2(10 BYTE),
  4    City               VARCHAR2(10 BYTE)
  5  )
  6  /

Table created.

SQL>
SQL>
SQL> create or replace procedure p_create(i_deptNo VARCHAR, i_dName VARCHAR2, i_loc VARCHAR2is
  2  v_row employee%ROWTYPE;
  3  begin
  4      if length(i_dName)>10 then
  5          raise_application_error(-20999,'first name is too long');
  6      end if;
  7
  8      v_row.id:=i_deptNo;
  9      v_row.first_Name:=i_dName;
 10      v_row.city:=i_loc;
 11
 12      insert into employee values v_row;
 13  end;
 14  /

Procedure created.

SQL>
SQL> call p_create ('01','new','new');

Call completed.

SQL>
SQL> select from employee;

ID   FIRST_NAME CITY
---- ---------- ----------
01   new        new

SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SomebabyTina 發表在 痞客邦 留言(0) 人氣()

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.

SomebabyTina 發表在 痞客邦 留言(0) 人氣()

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

SomebabyTina 發表在 痞客邦 留言(1) 人氣()

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

 

SomebabyTina 發表在 痞客邦 留言(0) 人氣()

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

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

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

SomebabyTina 發表在 痞客邦 留言(1) 人氣()

From 天翔
utl_file 寫入檔案的過程 : [utl_file.put]資料寫入檔案-->先寫入Buffer-->Buffer滿了,utl_file.fflush執行了才會將Buffer資料寫入檔案
 
  若各位以後有用到 utl_file,
 
  將資料寫入到檔案時,
 
  建議寫入資料, 用 utl_file.fflush, 強制將 buffer 寫入到檔案,
 
  不然, 像我昨天, 檔案寫入失敗 String buffer too small,
 
  但看其寫入點, 是個長度為 1 的資料,
 
  怎麼看都不是錯誤發生點,
 
  後來才發現, 是後面的資料寫入有問題, 導致前面 buffer 沒有寫入檔案, 造成誤判.

SomebabyTina 發表在 痞客邦 留言(0) 人氣()

 關於我上一篇文章,天翔給了我些意見
他認為上一種使用輸出 SLK 方式,但是它有很多限制, 如 : 不能分 Worksheet, 不能合併儲存

如下是他提供的改善程式與方法:


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

 

特別感謝天翔的技術分享

SomebabyTina 發表在 痞客邦 留言(5) 人氣()

感謝大家的技術分享與研究
前些日子有人提供了由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

SomebabyTina 發表在 痞客邦 留言(10) 人氣()

2007/09/12

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

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

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

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

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

Reference Web Site

SomebabyTina 發表在 痞客邦 留言(1) 人氣()

Function: 

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

 

SomebabyTina 發表在 痞客邦 留言(0) 人氣()

  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 ):在每一個小分區內部取序號

資訊來源:藍色小舖

SomebabyTina 發表在 痞客邦 留言(0) 人氣()