Mohamed Houri’s Oracle Notes

September 17, 2013

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

Filed under: PL/SQL — hourim @ 3:18 pm

Have you ever been faced to the above error?  I mean this bizarre Bulk Bind: Truncated Bind?  This is a strange and unclear error message. Let me put you in the context that gave me this error. I am trying to load data into an 11gR2 (11.2.0.3.0) database from data coming via a db link from a 10gR2 (10.2.0.5.0). This is not more as what I have simplified to the maximum here below

11gR2> begin
2   for x in (
3    select v2_col
4    from  distant_table
5    )
6   loop
7     null;
8  end loop;
9  end;
10  /

begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 2

And if instead I issue a straightforward select from a SQL windows

11gR2>
select v2_col
from  distant_table;

I have no errors.

I was struggling with this issue until one of my smart colleagues puts me into the right direction: there is a difference in the character set between the 11g (multi-byte) and the 10g database (single byte) as shown below

11gR2> select * from nls_database_parameters;
PARAMETER                      VALUE
------------------------------ ----------------------------
NLS_CHARACTERSET               AL32UTF8    --> here multibyte
NLS_LENGTH_SEMANTICS           CHAR        --> here
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.3.0

10gR2> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ------------------------------------
NLS_CHARACTERSET               WE8ISO8859P1  --> here single byte
NLS_LENGTH_SEMANTICS           BYTE          --> here
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.5.0

I was thinking that the 11gR2 PL/SQL engine (because still I am not fetching anything explicitly, I am just selecting) could recognize this character set difference between the local and the distant database and implicitly declare its local variable so that it could have enough space to contain incoming column when this one will have special characters .

Having smart colleagues is always a lucky situation, because not only my colleague pointed me to the right direction but gave me a workaround that I am pleased to reproduce here below :

11gR2> begin
2   for x in (
3    select v2_col ||''
4    from  distant_table
5    )
6   loop
7     null;
8  end loop;
9  end;
10  /

PL/SQL procedure successfully completed.

The work around consists of concatenating the distant column v2_col with a null string. This magically overcome the Bulk Bind: Truncated Bind error (don’t tell me why the PL/SQL engine can overcome this error when I have used such a concatenation).

If you have already been faced to this error then I will be pleased to know how you managed to solved it

March 21, 2012

ORA-00979: not a GROUP BY expression

Filed under: PL/SQL — hourim @ 4:17 pm

Yesterday I found something interesting about the GROUP BY clause. The model (borrowed from Jonathan Lewis as always) is

mhouri.world> create table t1
  2  as
  3  with generator as (
  4      select  --+ materialize
  5          rownum id
  6      from dual
  7      connect by
  8          level <= 10000)
  9  select
 10      rownum                  id,
 11      trunc(dbms_random.value(1,1000))    n1,
 12      lpad(rownum,10,'0') vc2,
 13      rpad('x',100)       vc3
 14  from
 15      generator   v1,
 16      generator   v2
 17  where
 18      rownum <= 1000000;

Table created.

mhouri.world> create index t1_n1 on t1(id, n1);
Index created.

mhouri.world> create table t2
  2  as
  3  with generator as (
  4      select  --+ materialize
  5          rownum id
  6      from dual
  7      connect by
  8          level <= 10000)
  9  select
 10      rownum                  id,
 11      trunc(dbms_random.value(10001,20001))   x1,
 12      lpad(rownum,10,'0') vc2,
 13      rpad('x',100)       vc3
 14  from
 15      generator   v1,
 16      generator   v2
 17  where
 18      rownum <= 1000000;

Table created.

mhouri.world> create index t2_i1 on t2(x1);

Index created.

mhouri.world> exec dbms_stats.gather_table_stats(user, 't2', method_opt => 'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

mhouri.world> exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

mhouri.world> select
  2       t1.id
  3      ,sum(t1.n1)
  4      ,(sum(t1.n1) +
  5             (select
  6                   sum(t2.x1) x
  7              from  t2
  8              where t2.id  = t1.id
  9              and   t2.x1  = t1.n1
 10              )
 11        ) as alias1
 12  from   t1
 13   where t1.id   = 11
 14   and   t1.n1   = 707
 15   group by
 16         t1.id
 17        ;
            and   t2.x1  = t1.n1
                           *
ERROR at line 9:
ORA-00979: not a GROUP BY expression 

Why do I need to group by t1.n1 also?

mhouri.world> select
  2       t1.id
  3      ,sum(t1.n1)
  4      ,(sum(t1.n1) +
  5             (select
  6                   sum(t2.x1) x
  7              from  t2
  8              where t2.id  = t1.id
  9              and   t2.x1  = t1.n1
 10              )
 11        ) as alias1
 12  from   t1
 13   where t1.id   = 11
 14   and   t1.n1   = 707
 15   group by
 16         t1.id
 17        ,t1.n1
 18        ;

        ID SUM(T1.N1)     ALIAS1                                                
---------- ---------- ----------                                                
        11        707                                                           

If I slightly change the above query by adding an extra where clause in the sub select (and t2.vc2 = t1.vc2)

mhouri.world> select
  2       t1.id
  3      ,sum(t1.n1)
  4      ,(sum(t1.n1) +
  5             (select
  6                   sum(t2.x1) x
  7              from  t2
  8              where t2.id  = t1.id
  9              and   t2.x1  = t1.n1
 10              and   t2.vc2 = t1.vc2
 11              )
 12        ) as alias1
 13  from   t1
 14   where t1.id   = 11
 15   and   t1.n1   = 707
 16   group by
 17         t1.id
 18        ,t1.n1
 19        ;
            and   t2.vc2 = t1.vc2
                           *
ERROR at line 10:
ORA-00979: not a GROUP BY expression 

The same error is raised again meaning that I need to group by this extra column (vc2) added into the where clause


mhouri.world> select
  2       t1.id
  3      ,sum(t1.n1)
  4      ,(sum(t1.n1) +
  5             (select
  6                   sum(t2.x1) x
  7              from  t2
  8              where t2.id  = t1.id
  9              and   t2.x1  = t1.n1
 10              and   t2.vc2 = t1.vc2
 11              )
 12        ) as alias1
 13  from   t1
 14   where t1.id   = 11
 15   and   t1.n1   = 707
 16   group by
 17         t1.id
 18        ,t1.n1
 19        ,t1.vc2
 20        ;

        ID SUM(T1.N1)     ALIAS1                                                
---------- ---------- ----------                                                
        11        707                                                           

And so on. Each time a column from table t1 (other than id1) is added to the sub select we need to group by that column

Any explanation?

May be when observing the execution plan


mhouri.world> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------

SQL_ID  5mzq0bmusjvtg, child number 0
-------------------------------------
select      t1.id     ,sum(t1.n1)     ,(sum(t1.n1) +            (select
           sum(t2.x1) x             from  t2             where t2.id  = t1.id
            and   t2.x1  = t1.n1             )       ) as alias1 from   t1
where t1.id   = 11  and   t1.n1   = 707  group by        t1.id       ,t1.n1

Plan hash value: 508850092

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE              |       |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |    10 |   103   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T2_I1 |   100 |       |     3   (0)| 00:00:01 |
|   4 |  SORT GROUP BY NOSORT        |       |     1 |     9 |     3   (0)| 00:00:01 |
|*  5 |   INDEX RANGE SCAN           | T1_N1 |     1 |     9 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T2"."ID"=:B1)
   3 - access("T2"."X1"=:B1)
   5 - access("T1"."ID"=11 AND "T1"."N1"=707)
                                                                                

…..

March 16, 2012

Auditing Updates and Deletes

Filed under: Oracle,PL/SQL — hourim @ 2:26 pm

I found myself very often answering question like “I want to track any update or delete done on my table. I want to store the complete record before modification and want also to know who modified this record, when it has been modified and by what program”. I decided finally to write this blog article in order to use it as an answer to this kind of question.

So let’s say we have an employee table defined as follows

mhouri.world> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

When I want to audit modifications on this table, the first thing to do is to create a resembling historical table


mhouri.world> create table emp_history as select * from emp where 1 = 2;

Table created.

mhouri.world> alter table emp_history add( dml_usr varchar2(48)
  2                                       ,dml_pgm  varchar2(48)
  3                                       ,dml_dat  date
  4                                       ,dml_operation varchar2(1)
  5                                     );

Table altered.

Then I will create a special trigger on this historical table

mhouri.world> create or replace trigger emp_his_b_i_r_trg
  2  before insert on emp_history
  3  for each row
  4  declare
  5     lv_module VARCHAR2(48);
  6     lv_action VARCHAR2(48);
  7     ld_current_date DATE := SYSDATE;
  8     lv_current_user VARCHAR2(48) := USER;
  9  begin
 10     dbms_application_info.read_module(lv_module, lv_action);
 11  
 12     :new.dml_usr  := lv_current_user;
 13     :new.dml_dat  := ld_current_date;
 14     :new.dml_pgm  := lv_module;
 15  end emp_his_b_i_r_trg;
 16  /

Trigger created.

And finally, I will create a trigger on the target table i.e. the table I want to audit

mhouri.world> create or replace trigger emp_b_u_d_r_trg
  2  before update or delete on emp
  3  for each row
  4  declare
  5   lv_dml_operation emp_history.dml_operation%type;
  6  begin
  7  if updating then
  8    lv_dml_operation := 'U';
  9  elsif deleting then
 10    lv_dml_operation := 'D';
 11  end if;
 12  
 13     insert into emp_history(empno
 14                            ,ename
 15                            ,job
 16                            ,mgr
 17                            ,hiredate
 18                            ,sal
 19                            ,comm
 20                            ,deptno
 21                            ,dml_operation
 22                            )
 23                      values(:old.empno
 24                            ,:old.ename
 25                            ,:old.job
 26                            ,:old.mgr
 27                            ,:old.hiredate
 28                            ,:old.sal
 29                            ,:old.comm
 30                            ,:old.deptno
 31                            ,lv_dml_operation
 32                            );
 33  end emp_b_u_d_r_trg;
 34  /

Trigger created.

Ok, we are done now. Our new historical table is empty for the moment

mhouri.world> select * from emp_history;
no rows selected

Let’s update and delete from target table

mhouri.world> update emp set sal = 4500 where empno = 7369;
1 row updated.                                                    
mhouri.world> delete from emp where empno = 9999;
1 row deleted.
mhouri.world> commit;
Commit complete.

What will look like our emp_history table?

d102@o102d00d.world> select  empno
  2        , ename
  3        , job
  4        , sal
  5        , substr(dml_usr,1,10) usr
  6        , substr(dml_pgm,1,10) pgm
  7        , substr(dml_dat,1,12) dat
  8        , dml_operation        dml
  9  from emp_history
 10  ;

     EMPNO ENAME      JOB              SAL USR        PGM        DAT       D
---------- ---------- --------- ---------- ---------- ---------- --------- -
      7369 allen      clerk             35 SX2_Z      SQL*Plus   16-MAR-12 U
      9999 Miloud     Expert          5600 SX2_Z      SQL*Plus   16-MAR-12 D

Where we can see that we have already done two DML operations on our emp table an update followed by a delete. For that we used SQL*Plus and the user who did the operation was SX2_Z.

July 15, 2011

Extracting separate information from a character string

Filed under: PL/SQL — hourim @ 9:01 am

I have been recently asked to develop a pl/sql stored procedure in order to dequeue a payload message from an Oracle Advanced queue. It was a text message. Don’t ask me why they didn’t foresee an object type for that purpose, I did asked the same question and I have been told that this is the standard they have been successfully using since several years and they do not have the intention to change it.

The goal is to dequeue that text message and extract from it several fields that are separated by a given delimiter.  I decided to reproduce this development in a blog article so that I can found it easily and also redirect to it when similar request will be done in Oracle forums.

mhouri.world> create or replace package split_text
3  IS
4   TYPE t_msg_array IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
7    FUNCTION f_extract_message_info (piv_message IN VARCHAR2
8                                    ,piv_delim   IN VARCHAR2)
9       RETURN t_msg_array;
11  END split_text;
12  /

Package created.

 

mhouri.world> create or replace package body split_text
2  IS
3  FUNCTION f_extract_message_info (piv_message IN VARCHAR2
5                                   ,piv_delim   IN VARCHAR2)
6     RETURN t_msg_array
7  IS
8     l_t_msg_array   t_msg_array;
9     ln_pos          NUMBER          := 0;
10     li              INTEGER         := 0;
11     lv_str          VARCHAR2 (4000) := piv_message;
12
13  BEGIN
14     -- Get the position fo the first delimitor
15     ln_pos := instr(lv_str,piv_delim,1,1);
16
17     -- While there is still delimitors loop
18     WHILE ( ln_pos != 0)
19     LOOP
20
21       -- Remove the first delimitor from the message
22       lv_str := substr(lv_str,ln_pos + length(piv_delim),length(lv_str));
23
24       -- Determine now the new position of the first delimiter
25       ln_pos := instr(lv_str,piv_delim,1,1);
26
27       -- Fill each information of the message in an array
28       li := li + 1;
29       IF ln_pos != 0
30       THEN
31         l_t_msg_array(li) := substr(lv_str,1, ln_pos-1);
32       ELSE
33         l_t_msg_array(li) := lv_str;
34       END IF;
35
36     END LOOP;
37
38     -- return the array
39      RETURN l_t_msg_array;
41  END f_extract_message_info;
44  END split_text;
45  /

Package body created.

Few points should be taken into account

  1. piv_delim represents the fields delimiter
  2. the message text should starts by the delimiter
And here below is a simple test of  this function:
mhouri.world> set serveroutput on

mhouri.world> declare
2   piv_txt            varchar2(4000);
3   l_t_jus_msg_array  split_text.t_msg_array;
4
5  BEGIN
6
7   piv_txt := '#@test01#@test02#@test03';
8
9   l_t_jus_msg_array     := split_text.f_extract_message_info(piv_txt,'#@');
10
11   dbms_output.put_line(' item1 := '||l_t_jus_msg_array(1) );
12   dbms_output.put_line(' item2 := '||l_t_jus_msg_array(2));
13   dbms_output.put_line(' item3 := '||l_t_jus_msg_array(3));
14
15
16  END;
17  /

item1 := test01
item2 := test02
item3 := test03

PL/SQL procedure successfully completed.

			

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)