-
编辑 删除
不同数据进制之间转化
Tom写的转化函数......SQL> create or replace function to_base( p_dec in number, p_base in number ) 2 return varchar2 3 is 4 l_str varchar2(255) default NULL; 5 l_num number default p_dec; 6...
-
编辑 删除
Create csv file from sql*plus
Create csv file from sql*plusSQL> SET UNDERLINE OFFSQL> SET COLSEP ','SQL> SET LINES 100 PAGES 100SQL> SET FEEDBACK offSQL> SET HEADING off SQL> Spool e:emp.csvSQL> select * from emp; 7369,SMITH ...
-
编辑 删除
Pipelined Table Functions(1)
对于 Pipelined Table Functions在调用时需要给出column_name而不是*:SQL> CREATE OR REPLACE PACKAGE pkg_with IS 2 3 TYPE type_cursor_variable IS REF CURSOR; 4 TYPE em...
-
编辑 删除
Setting up AUTOTRACE in SQL*PLUS
Setting up AUTOTRACE in SQL*PLUS• cd [ORACLE_HOME]/rdbms/admin• log into SQL*PLUS as SYSTEM• run @utlxplan• run CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;• run GRAN...
-
编辑 删除
Pipelined Table Functions(2)
对于Pipelined Table Function 在with语句中不可以多次调用,但如果函数使用常量除外：SQL> CREATE OR REPLACE PACKAGE BODY pkg_with IS 2 3 FUNCTION f_emp_info (p_deptno emp.deptno%T...
-
编辑 删除
Sequence in Merge statement
SQL> CREATE TABLE test(c1 int,c2 varchar2(35));Table createdSQL> SQL> CREATE SEQUENCE test_s 2 INCREMENT BY 1 3 &nb...
-
编辑 删除
查找丢失的日子
对于from date,to date这种形式的查找.......SQL> select * from test;FIRST_DATE LAST_DATE------------------- -------------------2007-04-10 00:00:00 2007-04-16 00:00:00SQL> select all_date 2 ...
-
编辑 删除
unhandled exception with NOCOPY
两个匿名块唯一的区别是:第一个没有使用NOCOPY hint,第二个使用了:SQL> set serverout on;SQL> declare 2 lv_int PLS_INTEGER := 1; 3 procedure p_test(p_int IN OUT PLS_INT...
-
编辑 删除
Interactions with PL/SQL(2) How to read multiple lines from the text file using PL/SQL
读取TXT文件并显示......[@more@]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); end loop;exception when NO_DATA_FOUND then utl_file.fclose(f);end;
-
编辑 删除
Deterministic function
当用户在FBI中使用自定义函数时须加关键字:deterministic,就是说必须使用deterministic function……SQL> create table test as select * from all_objects;Table created.SQL> create or replace function f_lower(...
-
编辑 删除
ORA-01743: only pure functions can be indexed
SQL> create table test(c1 int,c2 varchar2(10));Table created.SQL> create index test_idx_01 on test(to_date(c2,'YYMMDD'));create index test_idx_01 on test(to_date(c2,'YYMMDD'))  ...
-
编辑 删除
Convert long to char
将Long type data 转化为char type......sql>create table t1(c1 long);Table created.sql>insert into t1 values('aaaabbbcccc789');1 row created.sql>commit;Commit complete.sql>create table t2(c1 varchar2(30...
-
编辑 删除
An example of ref cursor(2)
An example of ref cursorSQL> create or replace function f_test return sys_refcursor is 2 v_cursor sys_refcursor; 3 begin 4 5 open v_cursor for 6 select * 7 from emp; 8 9 ret...
-
编辑 删除
PL/SQL集合类型
在pl/sql中，有三种集合类型：index-by tables(associative arrays,9i之前plsql table),nested table,arrays,它们都可以作为装载集合数据的载体，但有以下区别：1. Nested table 和arrays 可以作为column,但index-by...
