Oracle Rows Generate from Column Value

Example

 Select COLUMN1,COLUMN2,
               From (
               select COLUMN1,COLUMN2,
                   regexp_substr(STRING_COLUMN,'[^,]+',1,column_value) as STR
                  from SAMPLE_TABLE
                        TABLE(cast(multiset(select level
                       from dual
                        connect by level <= length(DAYS) -
                                length(replace(DAYS,','))+1)
                               as sys.odcinumberlist))               
               )  Where ,COLUMN2=P_COLUMN2 and STR Is Not Null Order By 1,3

Oracle: Make Oracle Password Unlimited

Change the profile limit to unlimited.

SQL> alter profile DEFAULT limit PASSWORD_REUSE_TIME unlimited;

SQL> alter profile DEFAULT limit PASSWORD_LIFE_TIME  unlimited;

PHP: Remove all characters from a string except numerical digit

You can use following example for removing character leaving all the numerical digit

$nano_number=preg_replace('/[^0-9.]/', '', $nano_string);

Oracle: ORA-12638-Credential Retrieval Failed

You will find original entry in $ORACLE_HOME\Network\Admin\sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)

You have to Change the

SQLNET.AUTHENTICATION_SERVICES= (NONE)


Your problem will be solved.

Oracle: Skipped locked rows in select query

Example

select * from hrm for update skip locked

By using this query, locked rows will not shown in query.

Oracle : Import from one user to another user

Example

imp scott/tiger@example file=<file>.dmp fromuser=<source> touser=<dest>

imp nanotouser/nanotouserpass@nanodb  file=/soft/nanodb.dmp log=/soft/nanodb.log fromuser=nanouser touser=nanotouser

Oracle: Delete all constraint from multiple table

If you want o delete all constraint from your required tables , you can use following PL/SQL program:

begin
    for r in ( select table_name, constraint_name
               from user_constraints
               where TABLE_NAME in ('TABLE1', 
                                    'TABLE2',
                                    'TABLE3') )
    loop
        execute immediate 'alter table '||r.table_name
                          ||' drop constraint '||r.constraint_name;
    end loop;
end loop;