How to retrieve values from a column in comma seperated

login to sqlplus

sqlplus username/password@host:port/sid





SQL> create table emp(

2 emp_id number(10),

3 emp_name varchar2(10)

4 );

create table emp(

*

ERROR at line 1:

ORA-00955: name is already used by an existing object



SQL> drop table emp;



Table dropped.



SQL> create table emp(

2 emp_id number(10),

3 emp_name varchar2(10)

4 );



Table created.



SQL> insert into emp(emp_id,emp_name) values (1,'Tom');



1 row created.



SQL> insert into emp(emp_id,emp_name) values (2,'Dick');



1 row created.



SQL> insert into emp(emp_id,emp_name) values (3,'Harry');



1 row created.



SQL> commit;



Commit complete.

SQL> select * from emp;



EMP_ID EMP_NAME

---------- ----------

1 Tom

2 Dick

3 Harry



SQL> select rtrim(xmlagg(xmlelement (e, emp_name ',')).extract ('//text()'), ',') emp_name

2 from emp ;



EMP_NAME

--------------------------------------------------------------------------------

Tom,Dick,Harry



SQL> drop table emp;



Table dropped.

****Happy CoDiNg!!****

ORA-01830 date format picture ends before converting entire input string

Error Description:

I have executed one select query which compares date column for which I recieved the below error



"ORA-01830 date format picture ends before converting entire input string"



Query:



select * from temp where to_date(substr(tokenvalue,1,10),'dd/mm/yyyy')>=

to_date('25/05/2011','dd/mm/yyyy')



Analysis:

Here the token value was having date data in different formats like

'08/25/2011',

'8/25/2011|'

which when passed to the to_date function results to this error as the second value doesnt satisfies correct format.



to_date(tokenvalue,'dd/mm/yyyy')



Solution:



Remove those bad data ('8/25/2011|')and run

this query against valid token values. You will get the correct output.

****Happy CoDiNg!!****

How to display records between two ranges in Oracle Database

Requirement:

Say for example the employee table has 100 records. We need to get the records in the range 40 to 50. We know how to use rownum and rowid in a select query. A simple query using rownum, rowid and minus helps us to get the data we required.



Solution:

select rownum, empno, ename from emp where rowid in

(select rowid from emp where rownum <=&upperlimit

minus

select rowid from emp where rownum<&lowerlimit);




Enter value for upperlimit: 60

Enter value for lowerlimit: 40




This query will give you rows between 40 & 60

****Happy CoDiNg!!****

How to use sqlloader to load a csv file into Oracle database table

Scenario:
Employee table is created to store employee details. Load the employee details from the given csv(comma seperated values) file into the employee table using sqlloader.


Create the table to store employee details:
create table emp
(emp_id number(10),
emp_name varchar2(25),
emp_dept_id number(10),
constraint emp primary key (emp_id));


Prerequisites to load csv file into the given table:
1. Table to be loaded.
2. Control file to load the data into employee table. Sample control file is given below.
3. CSV file.
4. SQL script to trigger the data load.

Control file specifies:
csv file name and path
Table to be loaded
Options to SKIP the header line
Option to truncate the table before loading.
Column Names to be loaded.

Sample control file defined:
Sample control file used here is named as EmployeeDetails.ctl
SKIP=1 will skip the heard in the file. Header info is nothing but column names. If column names are not there in the csv file then we can skip this line

Table to be loaded has to be truncated before loading the file (this method is to flush and load the table), else will through error saying "Table is not empty". Truncate option will truncate the table before loading the data. Alternative is to truncate the table manually before triggering the sqlloader.

Values in csv can be seperated by characters like ', ;, | etc. This has to be mentioned in the control like Terminated by ";" (if the column values are seperated by ',' then repalce this with ",")

Column names need to mentioned in the control file


Sample control file:
OPTIONS (SKIP=1)load data infile 'c:\shiyas\EmployeeDetails.csv'
TRUNCATE into table emp fields
terminated by ";" optionally enclosed by '"'
TRAILING NULLCOLS
(emp_id,emp_name,emp_dept_id)

Use the below script and execute from sqlplus to load the csv file:
Give the control file name as mentioned above
sqlldr username/password@host:port/servicename control=EmployeeDetails.ctl log=EmployeeDetails.log;
This will load the csv file to the employee table


Report on records inserted:
Refer EmployeeDetails.log to view the report on data load. It gives you information like how many records are inserted succesfully and how many records are rejected.

The log file will be flushed and loaded with new data evey time

Alert: If you really dont want to flush the table before loading data into the table please use APPEND instead of TRUNCATE. Else you will lose the data. Append act as a normal insert, whereas when you mention TRUNCATE, the table will be truncated before laoding.

****Happy CoDiNg!!****

How to display records between two ranges

Requirement: I need to get rows from a table within a given range

Solution:
select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum <=&upto
minus
select rowid from emp where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7

This query will give you rows between 7 & 10

ORA-00054: resource busy and acquire with NOWAIT specified

Problem Description

In my production database Oracle 10.2g while I was adding column to one of my transaction table it fails with ORA-54 error as below.



SQL> alter table student add b number;

alter table student add b number

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

ORA-00054: resource busy and acquire with NOWAIT specified while executing procedure

Problem Description:



While executing procedure from sql developer I am getting error "resource busy and acquire with NOWAIT specified"



Error:

exec mtep_miscellaneous_pkg.mtep_delete_delta_proc;Error report:ORA-00054: resource busy and acquire with NOWAIT specifiedORA-06512:



Solution of the Problem

1. select * from v$locked_object

This view lists all locks acquired by every transaction on the system.

In order to see locked object query,



select oracle_username || ' (' || s.osuser || ')' username, s.sid || ',' || s.serial# sess_id, owner || '.' || object_name object, object_type, decode( l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') status, decode(v.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lmode)) mode_held from v$locked_object v, dba_objects d, v$lock l, v$session s where v.object_id = d.object_id and v.object_id = l.id1 and v.session_id = s.sid order by oracle_username, session_id



Cause of the Problem

There was another one session opened and the person was trying to update the table and havent done the commit. Hence throwing error.



SQL> alter system kill session '142, 232';



Now it works.



Now if the person who opened the second session trying execute some query error will be generated saying that session has been killed.

****Happy CoDiNg!!****

How can I execute DDL statement from PL/SQL block

Scenario:
What happens when following code is executed?
BEGIN

CREATE TABLE EMPLOYEE
(EMP_NO NUMBER(10),
EMP_NAME VARCHAR2(30));
END;

Result:
A parsing error will occur. The above block is trying to execute a DDL statement which is not allowed in PL/SQL.

Solution:

Example 1:

BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE EMPLOYEE
(EMP_NO NUMBER(10),
EMP_NAME VARCHAR2(30))';
END;

anonymous block completed.

>>Here table is succesfully created.

Example 2:

BEGIN
EXECUTE IMMEDIATE 'truncate table tablename';
END;
anonymous block completed.

>>table is successfully truncated.