Archives de l’auteur :  CHERIF BEN HENDA

ORA-12638: Extraction des préférences utilisateur impossible

Dans SQLNET.ORA si le paramètre SQLNET.AUTHENTICATION_SERVICES a comme valeur NTS alors les connexions Oracle échouent dès lors qu »on est connecté avec un compte Windows qui est sur un autre domaine que celui du serveur Oracle.
Vous pouvez résoudre le problème en commentant cette ligne dans SQLNET.ORA (en mettant devant la ligne un #).

Oracle Tip: Create functions to join and split strings in SQL

Takeaway: Learn how to take a comma delimited list of values in a single string and use it as a table of values.
A common task when selecting data from a database is to take a set of values a query returns and format it as a comma delimited list. Another task that’s almost as common is the need to do the reverse: Take a comma delimited list of values in a single string and use it as a table of values.

SQL> select join(cursor(select ename from emp)) from dual;

SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,
JAMES,FORD,MILLER

The following code will perform this function:


create or replace function join 
      (
       p_cursor sys_refcursor,
       p_del varchar2 := ','
       ) return varchar2
is
  l_value varchar2(32767);
  l_result varchar2(32767);
begin
  loop
    fetch p_cursor
      into l_value;
    exit when p_cursor%notfound;
    if l_result is not null then
      l_result := l_result || p_del;
    end if;
    l_result := l_result || l_value;
  end loop;
  return l_result;

end join;





SQL> select join(cursor(select trunc(hiredate,'month') from emp),'|') from
dual;

01-DEC-80|01-FEB-81|01-FEB-81|01-APR-81|01-SEP-81|01-MAY-81|01-JUN-81|01-APR-87|01-NOV-81|01-SEP-81|01-MAY-87|01-DEC-81|
01-DEC-81|01-JAN-82

We’d also like the reverse functionality: to have the ability to take a single comma-delimited value and treat it as if it were a column in a table. We can take advantage of the TABLE SQL function and PL/SQL function tables to do this quite easily, but first, we must define the result type to be a TABLE type of the largest possible string.

create or replace type split_tbl as table of varchar2(32767);
/
show errors;

create or replace function split
(
p_list varchar2,
p_del varchar2 := ','
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
AA
l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));

else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;
/
show errors;

With this function, I can run a query like this:

SQL> select * from table(split(‘one,two,three’));

one
two
three

The PL/SQL procedure will parse its argument and return each part through a PIPELINE; the TABLE function allows it to be used in the FROM statement, so it appears to SQL as if it is a table with one column and three rows. (Remember that the column being returned is named COLUMN_VALUE if you want to use the value elsewhere.)