ORA-12638: credential retrieval failed

The SQLNET.ora file is a configuration file available in ORACLE_HOME\NETWORK\ADMIN location. The parameter "SQLNET.AUTHENTICATION_SERVICES" available in the SQLNET.ora file is used to specify the authentication services.

If you are facing error ORA-12638 it may be due to the wrong authentication service mentioned in the parameter. Change the parameter value from "nts" to "none" and try.

 sqlnet.authentication_services= (nts)  
 Change the above to  
 sqlnet.authentication_services= (none)  

Here the "nts" specifies the windows native authentication, which is used in the windows. with this parameter and ORA_DBA role a windows user can login into the database without database username and password.

Concatenate Rows

In this post we will discuss how we can concat rows in different ways.

 -- Objects used in this Example  
 SQL> select * from v$version;  
 BANNER  
 --------------------------------------------------------------------------------  
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
 PL/SQL Release 11.2.0.1.0 - Production  
 CORE     11.2.0.1.0     Production  
 TNS for Linux: Version 11.2.0.1.0 - Production  
 NLSRTL Version 11.2.0.1.0 - Production  
 SQL> select * from emp;  
  EMPID FNAME        LNAME             SEX SSN     SALARY DEPTNO  
 ------ -------------------- ------------------------------ --- --------- ---------   
   501 JOHN         DOE              M  500000001   45000  4001  
   502 JOHN         SMITH             M  500000002   60000  4001  
   503 SEAN         LEE              M  500000003   45000  4001  
   504 EVAN         SEAN              M  500000004   50000  4002  
   505 REBECCA       SEAN              F  500000005   30000  4002  
   506 TIM         DUNCAN             M  500000006   30000  4002  
   507 ROBERT        DUVAL             M  500000007   30000  4002  
   508 CLINT        JOHNSON            M  500000008   30000  4002  
   516 George        BUSH              M  500000016   30000  4004  
   517 SAM         MALONE             M         30000  4004  
   518 NANCY        REAGAN             F  500000018   30000  4004  
   519 HILLARY       CLINTON            F  500000019   30000  4004  
   520 MARRY        GEORGIA    cat        F  500000020   30000  4004  
   600 SOON         CHUN              f  500000029   50000  4002  
 14 rows selected  
  SQL> select * from dept;  
 DEPTNO NAME         BUILDING  
 ------ -------------------- --------------------  
  4002 WOMAN CLOTHING    BUILDING II  
  4003 MEN CLOTHING     BUILDING II  
  4004 KITCHEN APPLIANCES  MAIN BUILDING  
  4001 SHOES        BUILDING I  
  4006 FINANCE       FOX BUILDING  

Using WM_CONCAT

WM_CONCAT is an undocumented oracle aggregate function, which will concatenate the rows (delimited by comma) and returns a single value. WMSYS schema owns this function. As WM_CONCAT is undocumented, Oracle holds the support for this, means, we should not use this function in production ideally.

 SQL> SELECT d.name dept_name, wm_concat(fname || ' ' || lname) employee  
           FROM emp e, dept d  
           WHERE e.deptno = d.deptno  
       GROUP BY d.name;  
 DEPT_NAME           EMPLOYEE  
 -------------------- --------------------------------------------------------------------------------  
 KITCHEN APPLIANCES  SAM MALONE,George BUSH,MARRY GEORGIA,HILLARY CLINTON,NANCY REAGAN  
 SHOES               JOHN SMITH,SEAN LEE,JOHN DOE  
 WOMAN CLOTHING      SOON CHUN,ROBERT DUVAL,EVAN SEAN,REBECCA SEAN,TIM DUNCAN,CLINT JOHNSON  

if we are in need of other delimiter instead of "," we can use the translate function.

Using STRAGG

STRAGG is an another undocumented oracle aggregate function similar to WM_CONCAT, except the owner and delimiter. Here there will be no delimiter and the owner of this function is SYS.

 SQL> SELECT d.name dept_name,  
     listagg(fname || ' ' || lname, ',') within GROUP(ORDER BY fname, lname) employee  
    FROM emp e, dept d  
    WHERE e.deptno = d.deptno  
    GROUP BY d.name;  
 DEPT_NAME      EMPLOYEE  
 -------------------- --------------------------------------------------------------------------------  
 KITCHEN APPLIANCES  George BUSH,HILLARY CLINTON,MARRY GEORGIA,NANCY REAGAN,SAM MALONE  
 SHOES               JOHN DOE,JOHN SMITH,SEAN LEE  
 WOMAN CLOTHING      CLINT JOHNSON,EVAN SEAN,REBECCA SEAN,ROBERT DUVAL,SOON CHUN,TIM DUNCAN  

Using LISTAGG

LISTAGG aggregate function is a well documented function released along with oracle 11g database. the advantage of this function is, we can specify the delimited as a parameter to this function.

 SQL> SELECT d.name dept_name,  
     listagg(fname || ' ' || lname, ',') within GROUP(ORDER BY fname, lname) employee  
    FROM emp e, dept d  
    WHERE e.deptno = d.deptno  
    GROUP BY d.name;  
 DEPT_NAME      EMPLOYEE  
 -------------------- --------------------------------------------------------------------------------  
 KITCHEN APPLIANCES  George BUSH,HILLARY CLINTON,MARRY GEORGIA,NANCY REAGAN,SAM MALONE  
 SHOES               JOHN DOE,JOHN SMITH,SEAN LEE  
 WOMAN CLOTHING      CLINT JOHNSON,EVAN SEAN,REBECCA SEAN,ROBERT DUVAL,SOON CHUN,TIM DUNCAN  

Using sys_connect_by_path

 SQL> SELECT NAME,  
  2     ltrim(MAX(sys_connect_by_path(fname||' '||lname, ','))  
  3        keep(dense_rank LAST ORDER BY curr), ',') AS employees  
  4  FROM (SELECT NAME,  
  5         fname,lname,  
  6         row_number() over(PARTITION BY NAME ORDER BY fname,lname) AS curr,  
  7         row_number() over(PARTITION BY NAME ORDER BY fname,lname) - 1 AS prev  
  8      FROM emp e,dept d WHERE e.deptno = d.deptno)  
  9  GROUP BY NAME  
  10 CONNECT BY prev = PRIOR curr  
  11     AND NAME = PRIOR NAME  
  12  START WITH curr = 1;  
 NAME         EMPLOYEES  
 -------------------- --------------------------------------------------------------------------------  
 KITCHEN APPLIANCES  George BUSH,HILLARY CLINTON,MARRY GEORGIA,NANCY REAGAN,SAM MALONE  
 SHOES               JOHN DOE,JOHN SMITH,SEAN LEE  
 WOMAN CLOTHING      CLINT JOHNSON,EVAN SEAN,REBECCA SEAN,ROBERT DUVAL,SOON CHUN,TIM DUNCAN  

Age Calculation using “Interval Year to Month” data type.

Interval year to Month oracle data type is used to store the time period in years and months. EXTRACT function is used to extract the value from the interval data types (get the years, months, seconds .... etc.,).

SQL>SELECT  EXTRACT(YEAR FROM(TRUNC(SYSDATE) - :BIRTH_DATE) YEAR TO MONTH)  AGE FROM DUAL;

Clear SGA and Buffer Cache

The “Buffer Cache” has the recently used data blocks and the “SGA” has the SQL parse and the explain plan details. While executing SQL statements the details are stored in Buffer cache and the SGA, Hence the subsequent execution the SQL statement will be faster.

It is necessary to clear the above memories to find the exact execution time of the SQL statement (Mostly while working in SQL tuning...).

1:  SQL> alter system flush buffer_cache;  
2:  SQL> alter system flush shared_pool;  

ORA-12519: TNS:no appropriate service handler found.

This error is mainly due to the low "PROCESSES" initialization parameter value.
PROCESS - parameter describes the total number of operating system process allowed to access the database concurrently.

This total process is divided into user process and background processes, user processes are the processes allotted to the users connected to the database and the background processes are the processes utilized by the database.

Procedure to increase the PROCESSES parameter
Login as SYS user

 SQL>alter system set processes=200 scope=spfile;  

then stop and start the DB server.

 SQL> shutdown immediate;  
 SQL> startup;  

Variable declarartion in Package specification

Variables / Collections / Cursors declared in the specification of the package are global to the session.

We are going to create a sample package to check the scope of the variable defined in the package specification.

Package

 CREATE OR REPLACE PACKAGE test_scope IS  
  v_global_char VARCHAR2(300);  
  FUNCTION test_func(v_char VARCHAR2) RETURN BOOLEAN;  
 END test_scope;  
 CREATE OR REPLACE PACKAGE BODY test_scope IS  
  FUNCTION test_func(v_char VARCHAR2) RETURN BOOLEAN IS  
  BEGIN  
   v_global_char := v_char;  
   RETURN TRUE;  
  END;  
 END test_scope;  

Assigning and checking the value in the same session

 SQL>   
 SQL> SET serveroutput ON;  
 SQL> DECLARE  
  2 v_test BOOLEAN;  
  3 BEGIN  
  4 v_test :=test_scope.test_func('Oracle 11g PL/SQL Programming');  
  5 END;  
  6 /  
 PL/SQL procedure successfully completed  
 SQL> BEGIN  
  2 dbms_output.put_line(test_scope.v_global_char);  
  3 END;  
  4 /  
 Oracle 11g PL/SQL Programming  
 PL/SQL procedure successfully completed  
 SQL>   

Checking the Assigned Value in different session

 SQL> set serveroutput on;  
 SQL>   
 SQL> BEGIN  
  2 dbms_output.put_line(test_scope.v_global_char);  
  3 END;  
  4 /  
 PL/SQL procedure successfully completed  
 SQL>   

So whatever the value stored in the global variable of package specification exist until the session expires.