How to view the database nls parameters?

SQL> select * from nls_database_parameters;

How to edit the schedule jobs in unix?

Use the following command to edit the schedule jobs in unix.

$crontab -e

How to see the schedule jobs in unix?

Use the following command to see the list of schedule jobs.

$crontab -l

While importing the dump facing ORA-22993 error

This is due to characterset problem. This is because of CLOB datatype in the table.

Change the target characterset to AL32UTF8. It will solve the problem.

OR

Recreate the target database with the AL32UTF8 characterset.

Sending mail from unix

The following command is used to send e-mail from unix box.

mailx -s "subject" abcd@abcd.com < message

Oracle Converting delimited value in a row to collection of values

Using a Table Function  
A nice approach for converting a delimited list of a collection is via a custom  PL/SQL Table Function which you can then SELECT from via the TABLE operator,  i.e. 


/* Create the output TYPE, here using a VARCHAR2(100) nested table type */   
SQL> CREATE TYPE test_type AS TABLE OF VARCHAR2(100);    
2  /   
Type created.   


/* Now, create the function.*/   
SQL> CREATE OR REPLACE FUNCTION f_convert(p_list IN VARCHAR2)    
2    RETURN test_type    
3  AS    
4    l_string       VARCHAR2(32767) := p_list || ',';    
5    l_comma_index  PLS_INTEGER;   
6    l_index        PLS_INTEGER := 1;    
7    l_tab          test_type := test_type();    
8  BEGIN    
9    LOOP   
10      l_comma_index := INSTR(l_string, ',', l_index);   
11      EXIT WHEN l_comma_index = 0;   
12      l_tab.EXTEND;   
13      l_tab(l_tab.COUNT) := SUBSTR(l_string, l_index, l_comma_index - l_index);   
14      l_index := l_comma_index + 1;   
15    END LOOP;   
16    RETURN l_tab;   
17  END f_convert;   
18  /  
Function created.    


/* Prove it works */    
SQL> SELECT * FROM TABLE(f_convert('AAA,BBB,CCC,D'));   
COLUMN_VALUE  
--------------------------------------------------------------------------------  
AAA  
BBB  
CCC  
D    
4 rows selected. 

oracle connect by level

Generation of sequence numbers using connect by stmt.

Here is the SQL.

select level from dual connect by level < 10;

Output

=====

1

2

3

4

5

6

7

8

9

oracle connect by rownum

Generation of sequence numbers using connect by stmt.

Here is the SQL.

select rownum from dual connect by rownum < 10;

Output

======

1

2

3

4

5

6

7

8

9