-
编辑 删除
How to get server IP address
How to get server IP address...UTL_INADDR useful package in oracle 9i&10g;declare v_ip_address varchar2(50); v_host_name v$instance.host_name%type; /*UTL_INADDR useful package added in oracle 9i and...
-
编辑 删除
PLS-00231 private function is not in scope in SQL
SQL> declare 2 lv_test number; 3 function fn_test return number 4 is 5 begin 6 return 1; 7 end fn_test; 8 begi...
-
编辑 删除
Fetch limit and %NOTFOUND attribute
SQL> create table t_test as select * from user_objects where rownum < 1;Table createdSQL> SET serverout on;SQL> SQL> CREATE OR REPLACE PROCEDURE p_limit_test(p_array_size IN NUMBER DEFAULT 20) IS&nb...
-
编辑 删除
About Merge statement
Merge 语句就是依据条件update或者insert数据到目的表中,语句结构如下:MERGE [hint] INTO [schema .] table [t_alias] USING [schema .] { table | view | subquery } [t_alias] ON ( condition ) WHEN MATCHED TH...
-
编辑 删除
Oracle提供的密码复杂度函数
Oracle公司提供的验证密码复杂度的函数......CREATE OR REPLACE FUNCTION verify_function(username varchar2, password varchar2, old_password varchar2) RETURN boolean IS n boolean; m integer; diff...
-
编辑 删除
Interactions with PL/SQL(2) How to read multiple lines from the text file using
读取TXT文件并显示......declare f utl_file.file_type; s varchar2(200);begin f := utl_file.fopen('e:test','sample.txt','R'); loop utl_file.get_line(f,s); dbms_output.put_line(s...
-
编辑 删除
using index
创建primary or unique constraint时可以使用using index来对相应的index定义......SQL> create table t1(c1 number primary key 2 using index tablespace users initrans 40 pctfree 10 3 storage(initial 10k...
-
编辑 删除
Create logic constraints
Table t1(c1,c2), c1 not null; if c2 is null then c1 c2 can be identical,if c2 not null then c1,c2 must be unique;SQL> create table t1 (c1 varchar2(10),c2 varchar2(10));Table created.SQL> alter table t...
-
编辑 删除
DISTINCT statement with Order by clause
SQL> desc test;Name Type Nullable Default Comments ---------- ------------ -------- ------- -------- LAST_NAME ...
-
编辑 删除
Pragma RESTRICT_REFERENCES
To be callable from SQL statements, a stored function must obey the following "purity" rules, which are meant to control side effects:When called from a SELECT statement or a parallelized INSERT, UPDA...
-
编辑 删除
ORA-04021
ORA-04021 timeout occurred while waiting to lock object stringstringstringstringstringCause: While waiting to lock a library object, a timeout is occurred.Action: Retry the operation later.来自: Oracl...
-
编辑 删除
Deferrable constraints
对deferrable constraints可定义其检查为immediate or deferred......SQL> create table t1(c1 number);Table createdSQL> alter table t1 add constraint chk_t1 check(c1>5) not deferrable;Table alteredSQL> cre...
-
编辑 删除
ASCII Character Set
ASCII Character SetASCII Character SetSymbolDecimal valueSymbolDecimal valueblank32;59!33<60"34=61#35>62$36?63%37@64&38A-Z65-90'39[91(4092)41]93*42^94+43_95,44'96-45a-z97-122.46{123/47|1240-948-57}125...
-
编辑 删除
An example of ref cursor(1)
An example of ref cursorSQL> create table t1(c1 varchar2(20),c2 date);Table created.SQL> create or replace package pkg_refcursor_example is2 type refcursortype is ref cursor;3 procedure p_refcur (ps i...
-
编辑 删除
Exception into table
打开constraints时将exception into table......SQL> create table exceptions(row_id rowid, 2 owner varchar2(30), 3 table_name varchar2(30), 4 constraint ...
-
编辑 删除
Out join
a example for out join.........SQL> select * from t1;EMPLOYEE_ID ENAME----------- -------------------- 1 aaa 2 bbb 3 cccSQL> select * from t2;EMPLOYEE_ID EE_START_DATE ...
-
编辑 删除
Advanced queuing test (3)
7. Queue PropagationSQL> BEGIN 2 dbms_aqadm.create_queue_table( 3 queue_table => 'bzcardorders_prpqt' 4 ...
-
编辑 删除
引用系统保留字引起的PLS-00222
在表的定义中引用保留字引起的PLS-00222错误......SQL> desc t1; Name Null? Type ----------------------------------------- -------- ---------------------------- C1 ...
-
编辑 删除
Global variable
对于global variable,只是在session初次调用时加载,所以要注意“重置”，不能“信手拈来”......SQL> select id,sal,status 2 from test; ID SAL S---------- ---------- - 1 3000 ...
-
编辑 删除
不能相信你的眼睛
SQL> create table test(c1 varchar2(10));Table createdSQL> insert into test 2 values(chr(77)||chr(205)||chr(113)||chr(241));1 row insertedSQL> select c1 from test;C1----------MMqqSQL...
