Submitted by admin on Wed, 2004-08-04 13:49
DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
o CREATE - to create objects in the database
o ALTER - alters the structure of the database
o DROP - delete objects from the database
o TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
o COMMENT - add comments to the data dictionary
o RENAME - rename an object
DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
o SELECT - retrieve data from the a database
o INSERT - insert data into a table
o UPDATE - updates existing data within a table
o DELETE - deletes all records from a table, the space for the records remain
o MERGE - UPSERT operation (insert or update)
o CALL - call a PL/SQL or Java subprogram
o EXPLAIN PLAN - explain access path to data
o LOCK TABLE - control concurrency
DCL
Data Control Language (DCL) statements. Some examples:
o GRANT - gives user's access privileges to database
o REVOKE - withdraw access privileges given with the GRANT command
TCL
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
o COMMIT - save work done
o SAVEPOINT - identify a point in a transaction to which you can later roll back
o ROLLBACK - restore database to original since the last COMMIT
o SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
Difference between TRUNCATE, DELETE and DROP commands
Submitted by admin on Sat, 2006-02-11 02:07
DELETE
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
SQL> SELECT COUNT(*) FROM emp;
COUNT(*)
----------
14
SQL> DELETE FROM emp WHERE job = 'CLERK';
4 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> SELECT COUNT(*) FROM emp;
COUNT(*)
----------
10
TRUNCATE
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
SQL> TRUNCATE TABLE emp;
Table truncated.
SQL> SELECT COUNT(*) FROM emp;
COUNT(*)
----------
0
DROP
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
SQL> DROP TABLE emp;
Table dropped.
SQL> SELECT * FROM emp;
SELECT * FROM emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
From Oracle 10g a table can be "undropped". Example:
SQL> FLASHBACK TABLE emp TO BEFORE DROP;
Flashback complete.
PS: DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. As such, DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
How does one escape special characters when writing SQL queries?
Submitted by admin on Wed, 2004-08-04 13:53
Escape quotes
Use two quotes for every one displayed. Examples:
SQL> SELECT 'Frank''s Oracle site' AS text FROM DUAL;
TEXT
--------------------
Franks's Oracle site
SQL> SELECT 'A ''quoted'' word.' AS text FROM DUAL;
TEXT
----------------
A 'quoted' word.
SQL> SELECT 'A ''''double quoted'''' word.' AS text FROM DUAL;
TEXT
-------------------------
A ''double quoted'' word.
Escape wildcard characters
The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, while '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Examples:
SELECT name FROM emp
WHERE id LIKE '%/_%' ESCAPE '/';
SELECT name FROM emp
WHERE id LIKE '%\%%' ESCAPE '\';
Escape ampersand (&) characters in SQL*Plus
When using SQL*Plus, the DEFINE setting can be changed to allow &'s (ampersands) to be used in text:
SET DEFINE ~
SELECT 'Lorel & Hardy' FROM dual;
Other methods:
Define an escape character:
SET ESCAPE '\'
SELECT '\&abc' FROM dual;
Don't scan for substitution variables:
SET SCAN OFF
SELECT '&ABC' x FROM dual;
How does one eliminate duplicates rows from a table?
Submitted by admin on Wed, 2004-08-04 14:06
Choose one of the following queries to identify or remove duplicate rows from a table leaving only unique records in the table:
Method 1:
SQL> DELETE FROM table_name A WHERE ROWID > (
2 SELECT min(rowid) FROM table_name B
3 WHERE A.key_values = B.key_values);
Delete all rowids that is BIGGER than the SMALLEST rowid value (for a given key).
Method 2:
SQL> create table table_name2 as select distinct * from table_name1;
SQL> drop table table_name1;
SQL> rename table_name2 to table_name1;
This method is usually faster. However, remember to recreate all indexes, constraints, triggers, etc. on the table when done.
Method 3:
(contributed by Dennis Gurnick)
SQL> delete from my_table t1
SQL> where exists (select 'x' from my_table t2
SQL> where t2.key_value1 = t1.key_value1
SQL> and t2.key_value2 = t1.key_value2
SQL> and t2.rowid > t1.rowid);
Note: One can eliminate N^2 unnecessary operations by creating an index on the joined fields in the inner loop (no need to loop through the entire table on each pass by a record). This will speed-up the deletion process.
Note 2: If you are comparing NOT-NULL columns, use the NVL function. Remember that NULL is not equal to NULL. This should not be a problem as all key columns should be NOT NULL by definition.
‹ How does one escape special characters when writing SQL queries? up How does one get the time difference between two date columns? ›
»
• Login to post comments
Submitted by William Robertson (not verified) on Mon, 2005-02-07 19:15.
What about:
DELETE table_name
WHERE rowid IN
( SELECT LEAD(rowid) OVER
(PARTITION BY key_values ORDER BY NULL)
FROM table_name );
»
• Login to post comments
Remove duplicate rows
Submitted by srikanth M S (not verified) on Thu, 2006-02-23 13:16.
delete from
( select min(rowid)
from exp group by column1..,column2,...column3..);
»
• Login to post comments
Remove duplicate Rows (modified)
Submitted by Srikanth M S (not verified) on Fri, 2006-02-24 05:57.
This query removes rows based on the column names specified in the GROUP BY clause. If you specify only one column name it will remove all duplicate records for that column. If you want to delete exact replica's of the same row - use all the column names in the GROUP BY.
delete from
where rowid not in ( select min(rowid)
from
group by column1..,column2,...column3..)
»
• Login to post comments
Using dense_rank()
Submitted by Seshagiri Vaddadi (not verified) on Sun, 2006-03-19 01:08.
Another example using the dense_rank() function:
delete from table_name
where rowid in
(select rn from
(select rowid rn, dense_rank() over (partition by col1, col2, ..order by rowid) from table_name ) where rn <> 1
)
»
• Login to post comments
Using dense_rank()
Submitted by Srinivasan (not verified) on Thu, 2006-08-17 23:21.
Col1 and col2 are the primary key columns. To provide the correct SQL to use alias name "ln" for f dense_rank in the statement:
delete from
where rowid in (select rn from (Select rowid rn,
dense_rank() over (partition by col1,col2.. order by rowid) ln
from
where ln <> 1)
»
• Login to post comments
deleting duplicate rows from a table
Submitted by sriram (not verified) on Fri, 2006-05-19 01:24.
This statement deletes rows from the emp table where duplicate values of last_name appear.
delete from emp e
where empno in(select empno from emp d
where d.last_name=e.last_name
minus
select empno from emp f
where f.last_name=e.last_name
and rownum=1)
»
• Login to post comments
This Command will delete all duplicate rows
Submitted by varmas424 on Fri, 2010-03-05 23:05.
delete from emp where ('Delete',sal) in (
select case when count(*)>1 then 'Delete' else 'No' end DeleteFlag ,sal from emp group by sal having count(*)>1)
»
• Login to post comments
How does one get the time difference between two date columns?
Submitted by admin on Wed, 2004-08-04 14:13
Oracle allows two date values to be subtracted from each other returning a numeric value indicating the number of days between the two dates (may be a fraction). This example will show how to relate it back to a time value.
Let's investigate some solutions. Test data:
SQL> CREATE TABLE dates (date1 DATE, date2 DATE);
Table created.
SQL>
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1);
1 row created.
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/24);
1 row created.
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/60/24);
1 row created.
SQL> SELECT (date1 - date2) FROM dates;
DATE1-DATE2
-----------
1
.041666667
.000694444
Solution 1
SQL> SELECT floor(((date1-date2)*24*60*60)/3600)
2
' HOURS '
3 floor((((date1-date2)*24*60*60) -
4 floor(((date1-date2)*24*60*60)/3600)*3600)/60)
5
' MINUTES '
6 round((((date1-date2)*24*60*60) -
7 floor(((date1-date2)*24*60*60)/3600)*3600 -
8 (floor((((date1-date2)*24*60*60) -
9 floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) ))
10
' SECS ' time_difference
11 FROM dates;
TIME_DIFFERENCE
--------------------------------------------------------------------------------
24 HOURS 0 MINUTES 0 SECS
1 HOURS 0 MINUTES 0 SECS
0 HOURS 1 MINUTES 0 SECS
Solution 2
If you don't want to go through the floor and ceiling math, try this method (contributed by Erik Wile):
SQL> SELECT to_number( to_char(to_date('1','J') +
2 (date1 - date2), 'J') - 1) days,
3 to_char(to_date('00:00:00','HH24:MI:SS') +
4 (date1 - date2), 'HH24:MI:SS') time
5 FROM dates;
DAYS TIME
---------- --------
1 00:00:00
0 01:00:00
0 00:01:00
‹ How does one eliminate duplicates rows from a table? up How does one add a day/hour/minute/second to a date value? ›
»
• Login to post comments
Solution 3: If u want an easier method, use numtodsinterval()
Submitted by Shilpa Petrim (not verified) on Wed, 2006-05-03 17:09.
NUMTODSINTERVAL: This function is new to Oracle 9i. It takes two arguments numtodsinterval(x,c) where x is a number and c is a character string denoting the units of x. Valid units are 'DAY', 'HOUR', 'MINUTE' and 'SECOND'.
This function converts the number x into an INTERVAL DAY TO SECOND datatype.
SQL> select numtodsinterval(date1-date2,'day') time_difference from dates;
TIME_DIFFERENCE
----------------------------------------------------------------
+000000001 00:00:00.000000000
+000000000 01:00:00.000000000
+000000000 00:01:00.000000000
»
• Login to post comments
Very good solution
Submitted by James Peterson (not verified) on Thu, 2006-05-04 12:54.
Thanks you very much Shilpa Petrim. An awesome solution. Good job in suggesting an efficient solution of the latest Oracle 9i.
»
• Login to post comments
An enhancement to solution 1
Submitted by Hasan Amer (not verified) on Tue, 2006-06-06 00:56.
SQL> SELECT floor((date1-date2)*24)
2
' HOURS '
3 mod(floor((date1-date2)*24*60),60)
5
' MINUTES '
6 mod(floor((date1-date2)*24*60*60),60)
10
' SECS ' time_difference
11 FROM dates;
TIME_DIFFERENCE
--------------------------------------------------------------------------------
24 HOURS 0 MINUTES 0 SECS
1 HOURS 0 MINUTES 0 SECS
0 HOURS 1 MINUTES 0 SECS
»
• Login to post comments
difference in Second ( Will Work for both timestamp and Date).
Submitted by saha.sandipan on Tue, 2009-12-22 06:35.
CREATE OR REPLACE FUNCTION datediff
(
time1 TIMESTAMP
, time2 TIMESTAMP
)
-- RETURN NUMBER
RETURN number
AS sec NUMBER;
BEGIN
SELECT (extract(DAY FROM time2-time1)*24*60*60)+
(extract(HOUR FROM time2-time1)*60*60)+
(extract(MINUTE FROM time2-time1)*60)+
extract(SECOND FROM time2-time1)
into sec FROM dual;
RETURN sec;
END;
How does one add a day/hour/minute/second to a date value?
Submitted by admin on Wed, 2004-08-04 14:16
The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:
SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;
SYSDATE SYSDATE+1/24 SYSDATE+1/1440 SYSDATE+1/86400
-------------------- -------------------- -------------------- --------------------
03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13
The following format is frequently used with Oracle Replication:
select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;
NOW NOW_PLUS_30_SECS
-------------------- --------------------
03-JUL-2005 16:47:23 03-JUL-2005 16:47:53
Here are a couple of examples:
Description Date Expression
Now SYSDATE
Tomorow/ next day SYSDATE + 1
Seven days from now SYSDATE + 7
One hour from now SYSDATE + 1/24
Three hours from now SYSDATE + 3/24
An half hour from now SYSDATE + 1/48
10 minutes from now SYSDATE + 10/1440
30 seconds from now SYSDATE + 30/86400
Tomorrow at 12 midnight TRUNC(SYSDATE + 1)
Tomorrow at 8 AM TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of the month at 12 midnight TRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.m TRUNC(LEAST(NEXT_DAY(sysdate,''MONDAY' ' ),NEXT_DAY(sysdate,''WEDNESDAY''), NEXT_DAY(sysdate,''FRIDAY'' ))) + (9/24)
How does one implement IF-THEN-ELSE logic in a SELECT statement?
Submitted by admin on Sat, 2005-11-12 06:38
Oracle SQL supports several methods of coding conditional IF-THEN-ELSE logic in SQL statements. Here are some:
CASE Expressions
From Oracle 8i one can use CASE statements in SQL. Look at this example:
SELECT ename, CASE WHEN sal = 1000 THEN 'Minimum wage'
WHEN sal > 1000 THEN 'Over paid'
ELSE 'Under paid'
END AS "Salary Status"
FROM emp;
DECODE() Function
The Oracle decode function acts like a procedural statement inside an
SQL statement to return different values or columns based on the values of
other columns in the select statement. Examples:
select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')
from employees;
select a, b, decode( abs(a-b), a-b, 'a > b',
0, 'a = b',
'a < b') from tableX;
Note: The decode function is not ANSI SQL and is rarely implemented
in other RDBMS offerings. It is one of the good things about Oracle,
but use it sparingly if portability is required.
GREATEST() and LEAST() Functions
select decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B',
'B is greater than A')...
select decode( GREATEST(A,B),
A, decode(A, B, 'A NOT GREATER THAN B', 'A GREATER THAN B'),
'A NOT GREATER THAN B')...
NVL() and NVL2() Functions
NVL and NVL2 can be used to test for NULL values.
NVL(a,b) == if 'a' is null then return 'b'.
SELECT nvl(ename, 'No Name')
FROM emp;
NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.
SELECT nvl2(ename, 'Do have a name', 'No Name')
FROM emp;
COALESCE() Function
COALESCE() returns the first expression that is not null. Example:
SELECT 'Dear '
COALESCE(preferred_name, first_name, 'Sir or Madam')
FROM emp2;
NULLIF() Function
NULLIF() returns a NULL value if both parameters are equal in value. The following query would return NULL:
SELECT NULLIF(ename, ename)
FROM emp;
How does one drop/ rename a columns in a table?
Submitted by admin on Wed, 2005-12-14 23:46
Drop a column
From Oracle8i one can DROP a column from a table. Look at this sample script, demonstrating the ALTER TABLE table_name DROP COLUMN column_name; command.
Workarounds for older releases:
SQL> update t1 set column_to_drop = NULL;
SQL> rename t1 to t1_base;
SQL> create view t1 as select >specific columns> from t1_base;
SQL> create table t2 as select >specific columns> from t1;
SQL> drop table t1;
SQL> rename t2 to t1;
Rename a column
From Oracle9i one can RENAME a column from a table. Look at this example:
ALTER TABLE tablename RENAME COLUMN oldcolumn TO newcolumn;
Workarounds for older releases:
Use a view with correct column names:
rename t1 to t1_base;
create view t1 >column list with new name> as select * from t1_base;
Recreate the table with correct column names:
create table t2 >column list with new name> as select * from t1;
drop table t1;
rename t2 to t1;
Add a column with a new name and drop an old column:
alter table t1 add ( newcolame datatype );
update t1 set newcolname=oldcolname;
alter table t1 drop column oldcolname;
How does one add a column to the middle of a table?
Submitted by admin on Sat, 2005-12-03 00:53
Oracle only allows columns to be added to the end of an existing table. Example:
SQL> CREATE TABLE tab1 ( col1 NUMBER );
Table created.
SQL> ALTER TABLE tab1 ADD (col2 DATE);
Table altered.
SQL> DESC tab1
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
COL2 DATE
Nevertheless, some databases also allow columns to be added to an existing table after a particular column (i.e. in the middle of the table). For example, in MySQL the following syntax is valid:
ALTER TABLE tablename ADD columnname AFTER columnname;
Oracle does not support this syntax. However, it doesn't mean that it cannot be done.
Workarounds:
1. Create a new table and copy the data across.
SQL> RENAME tab1 TO tab1_old;
Table renamed.
SQL> CREATE TABLE tab1 AS SELECT 0 AS col1, col1 AS col2 FROM tab1_old;
Table created.
2. Use the DBMS_REDEFINITION package to change the structure on-line while users are workining.
How does one select EVERY Nth row from a table?
Submitted by admin on Wed, 2005-12-14 23:36
One can easily select all even, odd, or Nth rows from a table using SQL queries like this:
Method 1: Using a subquery
SELECT *
FROM emp
WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
FROM emp);
Method 2: Use dynamic views (available from Oracle7.2):
SELECT *
FROM ( SELECT rownum rn, empno, ename
FROM emp
) temp
WHERE MOD(temp.ROWNUM,4) = 0;
Method 3: Using GROUP BY and HAVING - provided by Ravi Pachalla
SELECT rownum, f1
FROM t1
GROUP BY rownum, f1 HAVING MOD(rownum,n) = 0 OR rownum = 2-n
Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may even help in the odd situation.
What is the difference between VARCHAR, VARCHAR2 and CHAR data types?
Submitted by admin on Sat, 2005-11-26 08:30
Both CHAR and VARCHAR2 types are used to store character string values, however, they behave very differently. The VARCHAR type should not be used:
CHAR
CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store varibale length strings, it will waste a lot of disk space.
SQL> CREATE TABLE char_test (col1 CHAR(10));
Table created.
SQL> INSERT INTO char_test VALUES ('qwerty');
1 row created.
SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM char_test;
COL1 LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty 10 Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32
Note: ASCII character 32 is a blank space.
VARCHAR
Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.
SQL> CREATE TABLE varchar_test (col1 VARCHAR2(10));
Table created.
SQL> INSERT INTO varchar_test VALUES ('qwerty');
1 row created.
SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar_test;
COL1 LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121
VARCHAR2
VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.
SQL> CREATE TABLE varchar2_test (col1 VARCHAR2(10));
Table created.
SQL> INSERT INTO varchar2_test VALUES ('qwerty');
1 row created.
SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar2_test;
COL1 LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121
‹ How does one select the TOP N rows from a table? up PL/SQL ›
»
• Login to post comments
VARCHAR vs. VARCHAR2
Submitted by Awadhesh Kumar Singh 'aks' (not verified) on Thu, 2006-06-15 06:58.
1. VARCHAR is going to be replaced by VARCHAR2 in next version. So, Oracle suggests the use VARCHAR2 instead of VARCHAR while declaring datatype.
2. VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters.
3. If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space.
»
• Login to post comments
CHAR vs. VARCHAR
Submitted by Yogesh Kumar (not verified) on Mon, 2006-11-20 23:38.
VARCHAR is used to store variable length character strings up to 4000 characters. But, remember CHAR is faster than VARCHAR - some times up to 50% faster.
»
• Login to post comments
Re: VARCHAR vs VARCHAR2
Submitted by William Robertson on Tue, 2007-07-31 16:17.
1. VARCHAR is NOT going to be replaced by VARCHAR2 in the next version. It is reserved for redefinition in some future version, probably well beyond 11g, if ever. This warning has been around since the two types were first introduced in Oracle 6 around 1989. It is there to allow Oracle some flexibility should the need arise.
2. The two types are currently synonymous, and so the storage limitations are identical.
3. As for #2, there is no difference between VARCHAR and VARCHAR2 in handling of NULL values because they are currently the same type.
While I'm at it, VARCHAR2 does NOT contain an additional length indicator (as the DUMP output clearly shows), CHAR is NOT faster than VARCHAR2, and VARCHAR does NOT use space any differently to VARCHAR2 and never has done. There is no good reason to use CHAR for anything, even fixed-length keys, and there never has been. I've never seen so much complete rubbish on one page in my life.
»
• Login to post comments
Differences between varchar and varchar2:
Submitted by Kalyani P. Banerjee (not verified) on Tue, 2006-07-25 00:42.
[i].
CHAR(5) is fixed length, right padded with spaces.
VARCHAR(5) is fixed length, right padded with null
VARCHAR2(5) is variable length.
Thus the difference between VARCHAR and VARCHAR2 is that VARCHAR is ANSI standard but takes up space whereas VARCHAR2 is Oracle-only but makes more efficient use of space.
But as someone has already pointed out, Oracle have resolved this (in 9.2, maybe earlier) by casting VARCHAR to VARCHAR2.
[ii].
Varchar and Varchar2 both are of variable character. Varchar can have MAximum 2000 character while Varchar can contain maximum 4000 character.
[iii]
Varchar is of ANSI SQL standart while Varchar2 is of Oracle standard.
»
• Login to post comments
Varchar vs Char
Submitted by Princevel Pereira (not verified) on Thu, 2006-12-07 01:35.
Searching is faster in CHAR as all the strings are stored at a specified position from the each other, the system doesnot have to search for the end of string.
Whereas in VARCHAR the system has to first find the end of string and then go for searching.
»
• Login to post comments
Varchar v/s Varchar2
Submitted by Adilz (not verified) on Mon, 2007-01-01 22:59.
VARCHAR was previosuly of fixed length and right padded with NULL spaces to fill up the total space. However, it seems that Oracle has resolved this issue and has made VARCHAR the same as VARCHAR2. Now, only CHAR is able of fixed length storage.
VARCHAR2 is variable length - according to the values that will be stored in the database.
Thanks
Adilz
»
• Login to post comments
varchar v/s Varchar2 v/s char
Submitted by Andrew York (not verified) on Tue, 2007-01-16 16:02.
Varchar and varchar2 are synonyms (now at least) varchar is supposed to be removed from oracle sql, but that will probably never happen. They both use 0 to N characters up on the disk. Depending on your NLS_CHARACTER_FORMAT (its called something like that) each character can take more than one byte on disk. ASCII uses one byte per character. UTF8 I think uses one byte unless the first bit is 1 then it may add another byte (and keep doing so until the first bit of the new byte is not 1) Most other forms of unicode default to two bytes. They can all grow I believe
char() should be thought of as fixd length. It will always take up the same amount of space no matter what is really stored in the column. It is a good idea to use them on fixed length things like MD5 signatures.
Varchars should fragment the datafile if their contents change. If they don't fragment the datafile then Oracle is taking extra steps to prevent this. So IF you are working with a string that will always be the same length (and that is less than 255) the go with char(). Where your string is < 4000 use Varchar2. Anything else go with CLOB.
»
• Login to post comments
length of long data type
Submitted by singhabm on Thu, 2009-02-05 13:49.
Hi,
Can you help me out in this...........
I want to find the maximum length of a long data type..
Suppose there is a table called xyz and its column are a, b and c
b is long data type..
and i want to see the largest data in b column..
Please help me out
Thanks in advance
»
• Login to post comments
how to find the average of a salary if all r in a row
Submitted by Mukul Mittal on Wed, 2009-03-04 11:07.
i have a table where employee is having salries for 12 months but the data is like say
jan feb mar apr .....dec
2000 1000 500 300 250
I have to use a single sql query to get the average salry excluding sum function.
can you guys pls help me out?
»
• Login to post comments
how to find avg
Submitted by vnomat on Mon, 2009-03-09 09:09.
Can you clarify if your data looks like
a)
emp_id jan_sal feb_sal... dec sal
321 2000 1000 250
322 1000 1500 1500
or
b)
emp_id mth sal
321 1 2000
321 2 1000
...
321 12 250
322 1 1000
322 2 1500
...
»
• Login to post comments
re:how to find the average of a salary if all r in a row
Submitted by rummy123 on Mon, 2009-03-30 13:16.
SQL> ed
Wrote file afiedt.buf
1 create table scott.salary
2 (empname varchar2(10),
3 jan number,
4 feb number,
5 mar number,
6* apr number)
SQL> /
Table created.
SQL> insert into scott.salary values ('Gairik',100,200,300,400);
1 row created.
Elapsed: 00:00:00.01
SQL> insert into scott.salary values ('ahmad',200,50,400,900);
1 row created.
Elapsed: 00:00:00.00
SQL> commit;
Commit complete.
SQL> select empname,jan,feb,mar,apr from scott.salary;
EMPNAME JAN FEB MAR APR
---------- ---------- ---------- ---------- ----------
Gairik 100 200 300 400
ahmad 200 50 400 900
SQL> select empname, (jan+feb+mar+apr)/4 avg_salary from scott.salary;
EMPNAME AVG_SALARY
---------- ----------
Gairik 250
ahmad 387.5
Elapsed: 00:00:00.00
SQL>
»
• Login to post comments
char and varchar2
Submitted by vnomat on Mon, 2009-03-09 09:05.
I don't feel compelled to comment on varchar. Never used it in Oracle and probably never will.
However I am compelled to comment about char and varchar because of some unhelpful comments here. First of all it is easy enough to verify stuff in Oracle so it comes as a surprise at some of the comments here.
char and varchar properties and length restrictions are widely available, however what is not available widely is perhaps the performance issue. I decided to put it to rest by creating a table of a million rows with essentially two columns (c char(6), vc varchar2(10)) and filled each with same values of 6 characters long (comparing 'apples' to 'apples') from a random table in my app.
I issued a search: select count(*) from mytable where c = 'values' and alternatively vc = 'values'
Invariably the char (c) search performed almost half as fast as the varchar2 (vc) search.
Varchar2 performed much better. Don't take my word for it, try it for yourself.
Vinny
How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Submitted by admin on Sat, 2004-08-07 05:47
Contrary to popular belief, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the undo/ rollback segments will be cleared for new transactions, causing ORA-1555 errors.
To fix this problem one can easily rewrite code like this:
FOR records IN my_cursor LOOP
...do some stuff...
COMMIT;
END LOOP;
COMMIT;
... to ...
FOR records IN my_cursor LOOP
...do some stuff...
i := i+1;
IF mod(i, 10000) = 0 THEN -- Commit every 10000 records
COMMIT;
END IF;
END LOOP;
COMMIT;
If you still get ORA-1555 errors, contact your DBA to increase the undo/ rollback
segments.
NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.
‹ PL/SQL up RDBMS Server ›
»
• Login to post comments
one can also use objects as an alternative
Submitted by Kevin Meade on Fri, 2006-09-29 14:55.
create or replace type o_rec is object (dummy varchar2(1))
/
create or replace type c_rec is table of o_rec
/
declare
c_rec_v c_rec;
begin
-- get our rows into a collection (or whatever)
select cast(multiset(select * from (
select *
from dual
)) as c_rec)
into c_rec_v
from dual
;
-- drive the loop from the collection
-- instead of from a cursor
for i in 1..nvl(c_rec_v.count,0) loop
-- stuff happens
commit;
end loop;
end;
/
Here we use whole object assignment to select our rows into an object collection and then use the collection as the driver of the loop rather than a cursor. This avoids fetch across commit altogether. You could just as easily have used other array structures if you don't like objects, but you should like objects in oracle, its the next step to better more intelligent and capable pl/sql code.
Of course there are some caveats like with everything. You must remember that collections are basically memory structures and thus consume memory. There is a practical limit to how large they can get before they cause problems. And then there is the whole philosophical discussion of "does this change the basic nature of your transaction". But all in all it works pretty good for most cases.
Lastly one must point out, if you have to use tricks like this to get around problems like snapshot too old, then maybe the basic algorithm you are using to get the work done needs rethinking. But most people don't want to hear that.
»
• Login to post comments
of course, the best way may be using sql only
Submitted by Kevin Meade on Mon, 2007-07-02 08:15.
Consider this sequence of code that does no looping in plsql at all. In it we see the update join syntax as a means of doing what used to be written commonly as a plsql loop. 9i and 10g have new features we should try to exploit because they are much more efficient. If you get an undo error doing this then the reason is your undo is juts too small, not because there is some fetch across commits happening.
This sample code just shows one update. Of coures there can be any amount of sql going on here. Join Update, Merge, Oracle Analytics, ... you name it. Its like Tom Kyte always said, sql solution first, then pl/sql.
SQL>
SQL> drop table t1
2 /
Table dropped.
SQL>
SQL> drop table t2
2 /
Table dropped.
SQL> create table t1
2 (
3 a number not null
4 ,b number not null
5 )
6 /
Table created.
SQL>
SQL> create table t2
2 (
3 a number not null
4 ,b number not null
5 )
6 /
Table created.
SQL>
SQL> alter table t1 add primary key (a)
2 /
Table altered.
SQL> alter table t2 add primary key (a)
2 /
Table altered.
SQL>
SQL> update (
2 select t1.*
3 ,t2.a na
4 ,t2.b nb
5 from t1
6 ,t2
7 where t1.a = t2.a
8 )
9 set b = nb
10 /
0 rows updated.
SQL>
SQL> insert into t1 values (1,1);
1 row created.
SQL> insert into t2 values (1,2);
1 row created.
SQL>
SQL> select * from t1;
A B
---------- ----------
1 1
SQL> select * from t2;
A B
---------- ----------
1 2
SQL> update (
2 select t1.*
3 ,t2.a na
4 ,t2.b nb
5 from t1
6 ,t2
7 where t1.a = t2.a
8 )
9 set b = nb
10 /
1 row updated.
SQL>
SQL> select * from t1;
A B
---------- ----------
1 2
SQL> select * from t2;
A B
---------- ----------
1 2
SQL>
SQL> alter table t2 drop primary key
2 /
Table altered.
SQL>
SQL> insert into t2 values (1,3);
1 row created.
SQL>
SQL> select * from t1;
A B
---------- ----------
1 2
SQL> select * from t2;
A B
---------- ----------
1 2
1 3
SQL>
SQL> update (
2 select t1.*
3 ,t2.a na
4 ,t2.b nb
5 from t1
6 ,t2
7 where t1.a = t2.a
8 )
9 set b = nb
10 /
set b = nb
*
ERROR at line 9:
ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL>
A confusing error with Join Update, till you understand what happened. Basically you must make sure the driving query brings back only one row as defined by the primary key of the table being updated. Otherwise you will attempt to update the same row twice which does not make sense.
And don't think you can fool Oracle into doing it just by supplying data that does not do multiple updates to the same row even though the key structures are not in place.
SQL> delete from t2 where b = 2;
1 row deleted.
SQL> select * from t1;
A B
---------- ----------
1 2
SQL> select * from t2;
A B
---------- ----------
1 3
SQL> update (
2 select t1.*
3 ,t2.a na
4 ,t2.b nb
5 from t1
6 ,t2
7 where t1.a = t2.a
8 )
9 set b = nb
10 /
set b = nb
*
ERROR at line 9:
ORA-01779: cannot modify a column which maps to a non key-preserved table
We saw before that this data like this updated just fine. Problem is, there is no primary key on T2 now so Oracle knows there is potential for more than one version of rows to materialize for the table targeted for update.
Putting the PK back on T2 fixing things.
SQL> alter table t2 add primary key (a)
2 /
Table altered.
SQL>
SQL> update (
2 select t1.*
3 ,t2.a na
4 ,t2.b nb
5 from t1
6 ,t2
7 where t1.a = t2.a
8 )
9 set b = nb
10 /
1 row updated.
SQL> select * from t1;
A B
---------- ----------
1 3
SQL> select * from t2;
A B
---------- ----------
1 3
SQL>
Good luck, Kevin
No comments:
Post a Comment