ITPub博客

使用UTL_HTTP Package来调用Webservice的样例代码。Sample Code:DECLARE L_PARAM_LIST VARCHAR2(512); L_HTTP_REQUEST UTL_HTTP.REQ; L_HTTP_RESPONSE UTL_HTTP.RESP; L_RESPONSE_TEXT VARCHAR2(32767); BEGIN -- service\'s input parameters L_PARAM_LIST := \'FromCurrency=EUR&ToCurrency=USD\'; -- preparing Request... L_HTTP_REQUEST := UTL_HTTP.BEGIN_REQUEST(\'http://www.webservicex.net/currencyconvertor.asmx/ConversionRate\', \'POST\',

Package中私有的Procedure/Function(没在Package Specification声明)需要注意放置的位置比如下面的Package，Procedure A 想要调用 Procedure B，CREATE OR REPLACE PACKAGE AAAAA AUTHID CURRENT_USER AS PROCEDURE A; END AAAAA;CREATE OR REPLACE PACKAGE BODY AAAAA AS PROCEDURE A IS BEGIN B; END; PROCEDURE B IS BEGIN dbms_output.put_line(\'In PROCEDURE B\'); END ; END AAAAA;会报错：PLS-00313: \'B\' not declared in this scope原因: B不是Public的Procedure

Join (SQL) :http://en.wikipedia.org/wiki/Join_%28SQL%29

通过PL/SQL Developer把Excel导入到数据库表中的方法：1.首先在创建要把Excel导入表，字段名称最好和Excel保持一致。2.Tools > ODBC Importer，User/System DSN选择Excel Files。3.切换到Data to Oracle页签,选择Owner及第一步创建的导入表。默认的字段匹配没有问题的话，就可以Import了。

如何用一张表中的数据Update另外一张表的数据。Sample：UPDATE T1 SET (C1, C2, C3) = (SELECT C1, C2, C3 FROM T2 WHERE T2.USER_ID = T1.USER_ID) WHERE EXISTS (SELECT * FROM T2 WHERE T2.USER_ID = T1.USER_ID);如果没有"Where Exists"部分，则可能会出现的问题：在表T1中有的行，但如果在表T2中没有对应的行，那么值会被更新为NULL。加上"Where Exists"部分,确保只更新两表相匹配部分的数据，不匹配

在性能可以接收的前提下，不要为了重构而牺牲代码的阅读性，这会让后期的维护越来越难。尤其是PL/SQL这样面向过程的语言，宁肯代码存在冗余，也要保证逻辑的清晰。重构是为了让逻辑更简单和清晰，而不是为了抽象让逻辑变得混淆和复杂。为了重构而重构，那是炫技，企业级软件工程需要更多地是稳定，清晰，而不是花架子。--------------------------------------------------------

Oracle EBS中，如果获取Item，Organization等基础数据的信息。你可以写SQL来查，这种做法的不好处就是，在不同Procedure或者Function中，你可能需要写多次类似的SQL来取值，造成代码的冗余。EBS标准的做法是，通过Cache模式来时缓存需要的信息。以获取Organization为例，系统中标准的做法是: l_return_value := INV_CACHE.set_org_rec(p_organization_id); IF NOT l_return_value THEN RAISE fnd_api.g_exc_unexpected

1.要想从DB Server发邮件，首先得确保DB Server已经启用了SMTP(Simple Mail Transfer Protocol)服务。检查是否启动SMTP的方法telnet server.domain.com 25(25是SMTP的默认端口)2.PL/SQL to send email sample code:DECLARE lc_from_email_id VARCHAR2 (100); lc_mailhost VARCHAR2 (100) := \'abc.com\'; lc_connection UTL_SMTP.connection; v_mail_sub VARCHAR2 (200) := \'Hello\'; v_mail_body VARCHAR2 (1000) := \'This is the mail bo

1.LoopDECLARE emp_rec emp%rowtype; CURSOR emp_cur IS SELECT * FROM emp; BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO emp_rec; EXIT WHEN emp_cur%NOTFOUND;--这个必须有,否则buffer overflow dbms_output.put_line (emp_rec.ename || \' \' ||emp_rec.sal ); END LOOP; CLOSE emp_cur; END; 注：emp_rec必须和emp_cur保持结构一致2.For LoopDECLARE CURSOR emp_cur IS SELECT * FROM emp; BEGIN FOR emp_rec in emp_cur LOOP dbms_output.pu

Non-Across Sessionscreate or replace package test_pkg as global_var number :=1; constant_var CONSTANT NUMBER := 2; end;Across Sessionscreate table global_value ( x int ); insert into global_value values ( 0 ); create or replace package get_global as function val return number; procedure set_val( p_x in number ); end; / create or replace package body get_global as function val return number as l_x number; begin select x into l_x from global; return l_x; end; procedure se

普通的数组语法：TYPE year_type IS TABLE OF number INDEX BY BINARY_INTEGER;The above single statement contains a lot of meaning.  First of all, we are simply defining our own data type, “year_type.” Make sure that “year_type” is not a  variable.  It is a user defined data type.  According to the above statement, “year_type” is a data type which can hold a set (or table) of values (typically of type “number”), organized with a

触发器执行顺序：1．  当打开FORM时：（1）       PRE-FORM（2）       PRE-BLOCK（BLOCK级）（3）       WHEN-NEW-FORM-INSTANCE（4）       WHEN-NEW-BLOCK-INSTANCE（5）       WHEN-NEW-RECORD-INSTANCE（6）       WHEN-NEW-ITEM-INSTANCE2．  当填

Syntax of the Create Package CREATE OR REPLACE PACKAGE [body] packagename IS END;Sample: CREATE OR REPLACE PACKAGE overloaded IS PROCEDURE insby(namein VARCHAR2); PROCEDURE insby(numbin PLS_INTEGER); END overloaded; / CREATE OR REPLACE PACKAGE BODY overloaded IS --========================================== PROCEDURE local(namein VARCHAR2, nameout OUT VARCHAR2) IS BEGIN nameout := TRANSLATE(namein,\'AEIOUaeiou\',\'EIOUAeioua\'); END local; --======================================

Oracle DB中SQL Script注释的语法 REM Single line comment -- Single line comment /* Single line comment */ /* Multi Line comment */ 补充：REM是REMARK的缩写 那么REM和--的区别是什么？ --或者/* */可以用于PL/SQL,但REM[ark]不可以。 下边这段可以在SQL*PLUS中执行成功 REM comment -- comment /* comment */ begin DBMS_OUTPUT.PUT_LINE(\'Test\'); --comment DBMS_OUTPUT.PUT_LINE(\'Test\'); /* comment */ end; /但下

TRUNC(number[,decimals]) number 待做截取处理的数值 decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分(默认为0) select TRUNC(5.75) from dual; --Output:5 select TRUNC(-5.75) from dual; --Output:-5 SELECT TRUNC(109.29, 1) truncated FROM dual; --Output:109.2 SELECT TRUNC(109.29, -1) truncated FROM dual; --Output:100 SELECT TRUNC(1234.5678, -3) FROM DUAL; --Output:1000 TRUNC(date[,fmt]) date 一个日期值 fm

全文阅读见PL/SQL FAQ How can one see if somebody modified any code? The source code for stored procedures, functions and packages are stored in the Oracle Data Dictionary. One can detect code changes by looking at the TIMESTAMP and LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example: SELECT OBJECT_NAME, TO_CHAR(CREATED, \'DD-Mon-RR HH24:MI\') CREATE_TIME, TO_CHAR(LAST_DDL_TIME, \'DD-Mon-RR HH24:MI\') MOD_TIME, STATUS FROM USER_OBJECTS WHERE L

Cursor Loop   Explicitly declared cursor and record CREATE OR REPLACE PROCEDURE <procedure_name> IS CURSOR <cursor_name> IS <SQL statement> <record_name> <cursor_name>%ROWTYPE; BEGIN   OPEN <cursor_name>   LOOP     FETCH <cursor_name> INTO <record_name>;     EXIT WHEN <cursor_name>%NOTFOUND;     <other code>   END LOOP;  

Oracle提供三种样式的Loop结构 1. The Basic Loop The BASIC loop repeats until a condition is met. Because the condition is tested at the end of the loop, the BASIC loop will always execute at least once. A simple loop runs until you explicitly end the loop. The syntax for a simple loop is as follows: LOOP [Sequence of statements] EXIT [condition] END LOOP; To end the loop, you use either an EXIT or EXIT WHEN statement. The EXIT statement ends a loop immediately.