Archive

Archive for the ‘Development’ Category

ORA-31011: XML parsing failed LPX-00217: invalid character error can be a bug

January 27, 2012 1 comment

 

If you get the error below:

 ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00217: invalid character

from code running on Oracle 11.2.0.2 and 11.2.0.3 and it used to work on previous versions then stop and check MOS 1391688.1. It can save you a lot of time. The reason you get this error can be bug 11877267.

 

I hit this bug on Oracle 11.2.0.3 during investigation why a PL/SQL piece of code that parses XMLTYPE was not working. The reason is the new XML parser introduced with 11.2.0.2.

You can either apply patch 11877267 or use the workaround to set back the old XML parser as of version 11.2.0.1.

alter system set event=’31156 trace name context forever, level 0x400′ scope=spfile;

 

Cheers,
Maria

 

Categories: Development

How to recompile a PL/SQL package if it is used by another session?

April 8, 2011 7 comments

 

Imagine you have to recompile a PL/SQL package urgently. In case it is being used and other sessions are executing it , you will not be able to compile this package successfully.

 

What you get is :

ORA-04021: timeout occurred while waiting to lock object error

 

The solution is to find out the locking session and to kill it. Let me say you need to change PCK_TEST package, then:

select * from dba_lock_internal
where lock_id1 like ‘%PCK_TEST%’;

 

Get the session_id and if possible for the system kill that session in order to compile the package.

 

Regards,

Maria

 

Categories: DBA, Development Tags:

Load Time dimension in the data warehouse

November 30, 2010 5 comments

 

The Time dimension exists in every data warehouse system and usually it is the first dimension we start with.  A very common task is to populate TIME dimension target table with data which is usually one time job. I developed a PL/SQL procedure to do this.

 

1. Create TIME dimension table

 

DROP TABLE D_TIME CASCADE CONSTRAINTS;

 

CREATE TABLE D_TIME
(
TIME_ID INTEGER  NOT NULL ,
CALENDAR_DATE DATE ,
YEAR_NUMBER INTEGER,
QUARTER_NUMBER INTEGER,
MONTH_NUMBER INTEGER,
WEEK_NUMBER INTEGER,
DAY_NUMBER INTEGER,
DAY_WEEKNAME VARCHAR2(20 BYTE),
MONTH_NAME VARCHAR2(20 BYTE),
QUARTER_NAME VARCHAR2(30 BYTE),
HALF_YEAR VARCHAR2(30 BYTE),
WEEKDAY VARCHAR2(20 BYTE),
YEAR_Q_NUM VARCHAR2(30 BYTE),
IS_LAST_DAY VARCHAR2(1 BYTE),
IS_LAST_FRIDAY VARCHAR2(1 BYTE),
DAY_YYYYMM VARCHAR2(6 BYTE),
DAY_FORM_SLASH VARCHAR2(10 BYTE),
PREV_DATE DATE,
NEXT_DATE DATE
) ;

 

ALTER TABLE D_TIME ADD CONSTRAINT D_TIME_PK PRIMARY KEY ( TIME_ID ) ;

 

 

2. Load Time dimension table with a PL/SQL Procedure which populates the TIME data given from – to period

 

CREATE OR REPLACE PROCEDURE LOAD_TIME_DIM(p_start_date IN DATE, p_end_date IN DATE) AS

 

v_seq_id NUMBER:= 0;
v_load_date DATE := p_start_date;
v_qurtername VARCHAR2(30);
v_halfyear VARCHAR2(30);
v_last_day VARCHAR2(1);
v_last_friday VARCHAR2(1);
BEGIN

LOOP
v_seq_id :=  v_seq_id + 1;
v_load_date := v_load_date + 1;

 

BEGIN
SELECT
CASE
WHEN TO_CHAR(v_load_date , ‘Q’) = ‘1’ THEN ‘FIRST QUARTER’
WHEN TO_CHAR(v_load_date , ‘Q’) = ‘2’ THEN  ‘SECOND QUARTER’
WHEN TO_CHAR(v_load_date , ‘Q’) = ‘3’ THEN  ‘THIRD QUARTER’
WHEN TO_CHAR(v_load_date , ‘Q’) = ‘4’ THEN  ‘FORTH QUARTER’
ELSE ‘N/A’
END quartername,
CASE
WHEN to_char(v_load_date, ‘MM’) IN (‘1′,’2′,’3′,’4′,’5′,’6′,’01’,’02’,’03’,’04’,’05’,’06’)  THEN ‘FIRST HALF YEAR’
WHEN to_char(v_load_date, ‘MM’) IN (‘7′,’8′,’9′,’10’,’11’,’12’,’07’,’08’,’09’) THEN ‘SECOND HALF YEAR’
ELSE ‘N/A’
END half_year,
DECODE(LAST_DAY(v_load_date) , v_load_date , ‘Y’,  ‘N’) is_last_day,
CASE
WHEN ( LAST_DAY(v_load_date) = v_load_date AND  TO_NUMBER(TO_CHAR(v_load_date, ‘D’)) = 5)   THEN ‘Y’
ELSE ‘N’
END is_last_friday
INTO
v_qurtername, v_halfyear, v_last_day, v_last_friday
FROM dual;
END;

INSERT INTO D_TIME
VALUES
( v_seq_id,
TRUNC(v_load_date),
EXTRACT(YEAR FROM v_load_date),
TO_NUMBER(TO_CHAR(v_load_date , ‘Q’)),
EXTRACT(MONTH FROM v_load_date),
TRUNC(TO_NUMBER( TO_CHAR(v_load_date ,’WW’))),
EXTRACT(DAY FROM v_load_date),
TO_CHAR(v_load_date , ‘DAY’),
TO_CHAR(v_load_date , ‘MONTH’) ,
v_qurtername,
v_halfyear,
DECODE(TO_CHAR(v_load_date, ‘D’), ‘7’, ‘WEEKEND’, ‘6’, ‘WEEKEND’ ,’WEEKDAY’),
TO_CHAR(v_load_date , ‘YYYY’) ||’ Q ‘ ||TRUNC(TO_NUMBER( TO_CHAR(v_load_date,’Q’)) ),
v_last_day,
v_last_friday,
TO_CHAR(v_load_date ,’YYYYMM’) ,
TO_CHAR(v_load_date , ‘YYYY / MM’),
TRUNC(sysdate) – 1,
TRUNC(sysdate) + 1
);

 

IF  v_load_date > p_end_date THEN
EXIT;
END IF;

END LOOP;

COMMIT;
END      LOAD_TIME_DIM;

 

 

Now we are ready to use it.

BEGIN
LOAD_TIME_DIM(TO_DATE(‘31.12.1979′,’DD.MM.YYYY’),TO_DATE(‘30.12.2030′,’DD.MM.YYYY’));
END;

 

 

Cheers,

Maria

 

Categories: Development
%d bloggers like this: