Oracle Utilities - 随笔分类(第2页) - Coldest Winter

debugging Problem

摘要：参考网址: http://forums.allroundautomations.com/ubb/ubbthreads.php?ubb=showflat&Number=14648&PHPSESSID=2e3c1b028a66500ae8a1730abc88b993 在开发项目的过程中，遇到一个比较比较奇怪的问题，当在过程中声明一个varchar2的变量，将将它的长度设置为32767，在进行DEBUG调试的过程中，当他的长度大于1000的，在下方的变量位置查看不到他的信息，当小于1000的时候，他还是varchar2，比较奇怪。例如: 1 CREATE OR REPLACE PRO 阅读全文

posted @ 2012-05-19 22:39 Coldest Winter 阅读(229) 评论(0) 推荐(0)

Oracle 存储过程无法编译解决方法(转载)

摘要：声明:本文为转载,如果有侵犯知识版本，请通知本人，本人将即刻停止侵权行为:http://blog.csdn.net/tianlesoftware/article/details/7412555Oracle存储过程无法编译，在PL/SQL中编译，总是挂住了，这个原因可能是要编译的对象被会话给锁住了:1、查看无效对象:1 SELECT Object_Name, Object_Type, Status2 FROM All_Objects3 WHERE Status = 'INVALID'4 AND Owner = 'SCOTT';2、查看正在访问无效对象的会话，这里我阅读全文

posted @ 2012-05-19 16:23 Coldest Winter 阅读(4568) 评论(0) 推荐(0)

Oracle中去除重复记录的方法:

摘要：参考网址:http://pcedu.pconline.com.cn/empolder/db/sql/0508/693658_3.html1、使用DISTINCT关键字： 1 --建立临时表 2 CREATE TABLE t_1_temp AS SELECT DISTINCT * FROM t_1 t; 3 --截断表 4 TRUNCATE TABLE t_1; 5 --将临时表的数据插入到本表 6 INSERT INTO T_1 SELECT * FROM t_1_temp; 7 --查询数据 8 SELECT * FROM T_1; 9 --删除临时表10 DROP TABLE t_1_t. 阅读全文

posted @ 2012-05-16 08:12 Coldest Winter 阅读(611) 评论(0) 推荐(1)

sys.dbms_utility包的使用:

摘要：参考文档:http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_util.htm http://www.oratechinfo.co.uk/delimited_lists_to_collections.html http://www.dbforums.com/oracle/887432-dbms_utility-comma_to_table-giving-problem.html1、 (1)、Oralce中获取毫秒级别的时间:dbms_utility.get_time :可以用来估算一个函数的执行所花费的时间1 -- ... 阅读全文

posted @ 2012-05-16 07:46 Coldest Winter 阅读(3061) 评论(0) 推荐(0)

REPLACEF

摘要：参考网址:http://www.oracle-developer.net/content/utilities/replacef.sql A function to simplify string building and debugging by replacing multiple placeholders from a collection of inputs.定义集合的类型:1 CREATE OR REPLACE TYPE replacef_ntt AS TABLE OF VARCHAR2(4000);函数: 1 CREATE OR REPLACE FUNCTION replace... 阅读全文

posted @ 2012-05-05 10:09 Coldest Winter 阅读(286) 评论(0) 推荐(0)

Oracle session

摘要：SESSION 阅读全文

posted @ 2012-05-04 07:59 Coldest Winter 阅读(1502) 评论(0) 推荐(0)

Oracle随机数:

摘要：1、使用SYS_GUID(),获取32位的UUID.1 SELECT SYS_GUID() FROM DUAL2、当要将某数据插入到A表中，但是在B表中也要使用插入数据的ID，就可以提前声明一个变量，存放它的ID,而不需要通过INSERT语句中使用RETURNING语句返回ID。1 DECLARE2 V_ID VARCHAR2(100) := SYS_GUID();3 BEGIN4 DBMS_OUTPUT.PUT_LINE(V_ID);5 END;3、其他使用的随机数是使用SYS.dbms_random包中提供的函数和过程: 1 --Obsolete(过时), get integer ... 阅读全文

posted @ 2012-05-03 20:27 Coldest Winter 阅读(2437) 评论(0) 推荐(0)

Oracle 11G行转列

摘要：http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:766825833740 阅读全文

posted @ 2012-04-30 12:14 Coldest Winter 阅读(164) 评论(0) 推荐(0)

String Aggregation Techniques---字符串连接技巧

摘要：参考网址:http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php#listagghttp://docs.oracle.com/cd/B14117_01/appdev.101/b10800/dciaggref.htmhttp://www.cxy.me/BBS/view26-22712-1.htmhttp://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2196162600402LISTAGG Analystic Function 阅读全文

posted @ 2012-04-27 22:09 Coldest Winter 阅读(501) 评论(0) 推荐(0)

Oracle常用技巧及一些函数的总结

摘要：1、将字符串转换为日期:可以不用TO_DATE1 SELECT * FROM EMP E WHERE E.HIREDATE < DATE '2012-08-09'2、从子查询中删除数据1 DELETE FROM (SELECT * FROM DUAL)3、查询所有的系统默认参数1 SELECT * FROM NLS_DATABASE_PARAMETERS;2 SELECT * FROM NLS_SESSION_PARAMETERS;4、查询数据库的信息1 SELECT * FROM v$database;4、查询实例的相关信息:包括主机名，启动时间，当前的状态，版本信息1 阅读全文

posted @ 2012-04-26 07:37 Coldest Winter 阅读(489) 评论(0) 推荐(0)

Oracle date

摘要：date 阅读全文

posted @ 2012-04-22 07:15 Coldest Winter 阅读(1211) 评论(0) 推荐(0)

Oracle JDBC

摘要：1、CONNECT1 Class.forName("org.postgresql.Driver");2 Connection connection = null;3 connection = DriverManager.getConnection(4 "jdbc:oracle:thin:@localhost:1521:mkyong","username","password");5 connection.close();JDBC & Statement2、STATAMENTThe “Statement” i 阅读全文

posted @ 2012-04-15 18:04 Coldest Winter 阅读(596) 评论(0) 推荐(0)

PL/SQL 命名规则

摘要：------------------------------------标识符命名规则实例程序变量 V_name V_name程序常量 C_Name C_company_name游标变量 Name_cursor Emp_cursor异常标识 E_name E_too_many表类型 Name_table_type Emp_record_type表 Name_table Emp记录类型 Name_record Emp_recordSQL*Plus 替代变量 P_name P_sal绑定变量 G_name G_year_sal---------------------------------- 阅读全文

posted @ 2012-03-18 20:54 Coldest Winter 阅读(340) 评论(0) 推荐(0)

Oracle Function-NEXT_DAY,TRUNC,ROUND,CEIL,SIGN

摘要：注意:国际通用的日期中；周末才是一个周的第一天ROUND and TRUNC Date Functionsnext lists the format models you can use with the ROUND and TRUNC date functions and the units to which they round and truncate dates. The default model, 'DD', returns the date rounded or truncated to the day with a time of midnight.Date F 阅读全文

posted @ 2012-03-10 11:55 Coldest Winter 阅读(653) 评论(0) 推荐(0)

Oracle Function: Case and Decode

摘要：CASE expression perform if-then-else logic in SQL without having to use PL/SQL.CASE works in a similar manner to DECODE().CASE is ANSI-compliant.There are two types of CASE expressions: Simple case expressions use expressions to determine the returned value. Searched case expressions use condi... 阅读全文

posted @ 2012-03-10 09:44 Coldest Winter 阅读(868) 评论(0) 推荐(2)

table join(left,right,inner)

摘要：Three different types of joins(三种不同方式的连接)inner joins - Return a row only when the columns in the join containvalues that satisfy the join condition.This means that if a row has a null value in one of the columns in thejoin condition, that row isn't returned.Outer joins - Can return a row even wh 阅读全文

posted @ 2012-02-11 17:56 Coldest Winter 阅读(428) 评论(0) 推荐(0)

一个 SQL 同时验证帐号是否存在、密码是否正确

摘要：user login 阅读全文

posted @ 2012-02-10 20:52 Coldest Winter 阅读(377) 评论(0) 推荐(0)

使用存储过程进行分页：page by Procedure

摘要：包头: 1 CREATE OR REPLACE PACKAGE EMP_SIMPLE_DEMO IS 2 3 --定义一个游标 4 TYPE EMP_REF_CURSOR IS REF CURSOR; 5 6 /** 7 *分页存储过程 8 *要求可以输入表名,每页显示记录数、当前页。返回总记录数,总页数，和返回的结果集。 9 */10 PROCEDURE FENYE(I_TABLENAME IN VARCHAR2, --表名11 I_PAGESIZE IN PLS_INTEGER, --页大小12 ... 阅读全文

posted @ 2012-02-07 22:50 Coldest Winter 阅读(282) 评论(0) 推荐(0)

使用自制事务进行异常信息的记录：Autonomous transactions-log

摘要：建立错误信息表：1 CREATE TABLE error_logs ( 2 id NUMBER(10) NOT NULL, 3 log_timestamp TIMESTAMP NOT NULL, 4 error_message VARCHAR2(4000)... 阅读全文

posted @ 2012-01-14 22:17 Coldest Winter 阅读(259) 评论(0) 推荐(0)

Using Flashback Queries(使用闪回操作)

摘要：SCN的概念:http://www.itpub.net/thread-1601493-1-1.html查询当前的系统的时间戳:1 select SysTimeStamp from DUAL;NOTE--Oracle uses undo to roll back transactions and support flashbackqueries. Oracle uses redo (captured in the online redo log files) toapply transactions during database recoveries.NOTE--To use some fea 阅读全文

posted @ 2012-01-11 16:25 Coldest Winter 阅读(617) 评论(0) 推荐(0)

随笔分类 - Oracle Utilities