Oracle Applications Tips

2011-06-02  来源:本站原创  分类:Database  人气:49 

(1) oracle database up to 1000 elements in the limit:
select * from a where id in (1,2 ,..... 1001); will get an error reported in the elements can not exceed 1000.
select * from a where id in (select id from b where rownum <6002); the statement does not complain normal operation.
Of the principle, I do not understand.

(2) a statement to remove duplicate records (one or more columns repeat):
delete from TABLENAME where rowid not in
(Select max (rowid) from TABLENAME group by (COL1, COL2, COL3 .... COLn);

(3) conditions of the query by date record of the day:
select * from TABLENMAE
where createTime> = to_date (: date, 'YYYY-MM-DD')
and createTime <to_date (: date, 'YYYY-MM-DD') + 1

(4) must remember that according to the query page the only key to sort, otherwise inexplicable occurrence of certain record series of the page:
select *
from (select a. *, rownum row_num from tableName a order by a.uniqueColumn)
where row_num <= 100
and row_num> 80

相关文章
  • Oracle Applications Tips 2011-06-02

    (1) oracle database up to 1000 elements in the limit: select * from a where id in (1,2 ,..... 1001); will get an error reported in the elements can not exceed 1000. select * from a where id in (select id from b where rownum <6002); the statement does

  • Successful development of the first chapter of the Oracle Applications 2011-08-09

    Successful development of the first chapter of the Oracle Applications 1.3 How to develop database applications 1.3.1 understand the Oracle architecture 1) Do not run under MTS long transaction MTS (Multi Threaded Server) architecture, that is, a con

  • "Ruby on Rails for Oracle Applications" Frequently Asked Questions 2010-02-16

    Directory Why RUBY ON RAILS? Definition What is Ruby? What is Rails? Model - View - Controller pattern (MVC) is what? RubyGems What is this? What is OCI8? ORACLE database connection To use the Oracle database to run Ruby on Rails, what needs to be in

  • ORACLE Applications fool common question asked 1000 (2) 2010-06-29

    In the applications we would perhaps be ORACLE's encounter many seem not difficult Wen Ti, especially for novice Lai Yue, today I briefly summarize what the Ta, published for everyone, Hope on help! And we Yiqitantao, Gongtongjinbu! ORACLE is not on

  • ORACLE Applications fool common asked questions 1000 (1) 2010-06-29

    When you apply ORACLE may experience many of the problems seem difficult, especially for novice, and today I briefly summarize it, released to us, we want to help! And everyone of progress together! ORACLE is not on the master to see. 1. Oracle insta

  • ORACLE applications commonly ask fool questions 1000 (1) 2010-06-29

    When you apply ORACLE may experience many of the problems seem difficult, especially for novice, and today I briefly summarize it, released to us, we want to help! And everyone of progress together! ORACLE is not on the master to see. 1. Oracle insta

  • ORACLE Applications fool common questions asked 1000 (3) 2010-06-29

    When you apply ORACLE may experience a lot of the problems seem difficult, especially for novice, and today I briefly summarize it, release to you, we want to help! And everyone of progress together! See before content: ORACLE Application Frequently

  • Summary of Oracle Applications in dblink 2010-11-26

    1. View the database global_name SELECT * FROM GLOBAL_NAME; 2. View global_name argument is true or false show parameter global_name If true, the local establishment of the dblink name must be consistent with the remote global_name Caixing. 3. Create

  • Oracle Maintenance Tips 2010-12-04

    1. Recompiled with the error functions, stored procedures, packages, SQL>SET PAGESIZE 0 SQL>SET FEEDBACK OFF SQL>SET VERIFY OFF SQL>SPOOL TEMP.SQL SQL>SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '. ' || OBJECT_NAME || ' COMPILE; ' FRO

  • win 7 under, oracle startup tips: Unable to initialize oracle call interface 2011-08-06

    Today in win7 Ultimate 32-bit operating system to install Oracle 10201_database_win32, but after installation, with sqlplus login error: C: \ Users \ sanling> sqlplus SP2-1503: Unable to initialize Oracle call interface SP2-0152: ORACLE does not work

  • oracle simple tips summary 2010-09-30

    to_date (): Oracle in a specific format to insert date value, Oracle default system format dd-mm-yy month Usage: to_date ('1985 / 3 / 12 ',' yyyy / mm / dd ') or ('1982-3-12', 'yyyy-mm-dd') Insert data using a subquery: Features: a large amount of da

  • WINDOWS Install Oracle 10g tips for: 5.0,5.1,5.2,6.0 one of the solutions 2011-08-12

    Install oracle 10g in WIN7, the following message: Checking operating system requirements ... Request result : 5.0,5.1,5.2,6.0 One of the Actual results : 6.1 Check completed. overall results of this check : Failed <<<< Problem : Oracle Databa

  • Oracle Applications comes with connection pool 2011-08-31

    Ojdbc14.jar package dependencies OraclePooled.java code: import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import javax.sql.PooledConnection; import oracle.jdbc.pool.OracleConnectionPoolDataSource; /** * Oracle

  • Oracle Applications to modify the log mode and sqlplus 2011-10-10

    9i version (above) using 1 connection oracle sqlplus setup 1: sqlplus /nolog setup 2: conn username/[email protected] as sysdba Or with sqlplus system/[email protected] as sysdba If the system can not be used sys user CONN sys / Oracle @ xhBBSQYL as SYSDB

  • Senior Oracle Applications 2010-03-12

    / / Recall the table from DB by mistake. SELECT * FROM RECYCLEBIN ORDER BY DROPTIME DESC FLASHBACK TABLE table_name TO BEFORE DROP; COMMIT; SQL standard analytical order: (1). FROM clause, and assembly of data from different data sources (2). WHERE c

  • A function of Oracle Applications 2010-09-10

    nvl (column, the result is empty the default value) decode (column, value, match the statement is executed, does not match the implementation of the statement) / / equivalent of three-eyed expression

  • oracle Optimization Tips 2010-10-17

    I think the main aspects should be adjusted from 5: 1. Remove unnecessary large-table full table scan 2. Cached small table full-table scan 3. Testing the use of optimization index 4. Testing the connection optimization technology 5. Cost to minimize

  • oracle applications (2) 2011-02-13

    - Create a temporary table in two ways --- Create an identical table with the same original data and the structure of the temporary table create table newitemfile as select * from itemfile; --- Create an original table with the same structure the sam

  • Oracle Database Tips 2011-08-01

    Query the database table creation time DBA privileges: select object_name, created from dba _objects where object_type = 'TABLE' and object_name = 'your_tab'; Ordinary users check the user table: select object_name, created from user _objects where o

  • ORACLE JAVA stored procedure using BLOB image export 2010-11-17

    A few days ago a friend to solve the problem: a XX acquiring system transactions need another set of statistics from the system to image storage and export the view mode. Figure Type of direct film to exist in the database BLOB. Would have thought th