Oracle stored procedure 8: Pit 2 when stored procedure (end)

Time:2021-4-14

Previously, I wrote an article to summarize the pit that Oracle stored procedure stepped on when it was used to store 8 stored procedure(https://www.cnblogs.com/kingstarer/p/13379053.html)

At that time, there were only three big pits. In fact, I encountered many small pits

Because during this period, our project team decided to rewrite the old system with Java instead of using stored procedures, so we haven’t cleaned up the previous pits recently.

I just remember it today, so I’ll take some time to sort it out. Although I don’t need the experience, I hope it can help others.

(using java to write logic is much more convenient than using stored procedures. I suggest you give up stored procedures as much as possible.)

Time zone issues

After the installation of Dameng 8, the default time zone is not the operating system time zone, but the 0 time zone. This will cause the return time of sysdate to be incorrect and / etc / DM needs to be modified_ svc.conf Add time to the file_ Zone = (480) is normal, as follows:

[[email protected] etc]# vi /etc/dm_svc.conf
#Lines beginning with #, denote comments
#Global configuration area DM_ svc.conf
TIME_ZONE=(480)
LANGUAGE=(cn)

DMHTGX=(192.168.0.137:5236)

#Service configuration area
[DMHTGX]
LOGIN_MODE=(2)

regexp_replace

There is something wrong with the regular matching of Dameng. One of the pits I stepped on is this:


select regexp_replace(‘CC4.city’, ‘([(+-*/|><=,]|^)(.+)’, ‘\2’, 1, 1, ‘i’) from dual;

The result of this statement is different between Oracle and Dameng


select regexp_ Replace (‘cc4. City ‘,’ ([+ – *] | ^) (. +) ‘,’ \ 2 ‘, 1,1,’ I ‘) from dual; — output C4. City
–Switch + and – the output of Oracle is the same, but Dameng is different
select regexp_ Replace (‘cc4. City ‘,’ ([- + *] | ^) (. +) ‘,’ \ 2 ‘, 1,1,’ I ‘) from dual; — output CC4. City


If we analyze it carefully, it is because Dameng regards the + character in [] as the metacharacter + (matches the previous subexpression one or more times)

Cascade deletion problem

After Oracle users migrated to the dream database, they found many triggers. If you look at the code carefully, you should realize the foreign key case delete. It is estimated that Dameng does not support cascading deletion of foreign keys. These cascading deletions are automatically changed into triggers during migration.

However, after the trigger is changed, the delay constraint function of Oracle cannot be realized (alter session set constraints = deferred)

There is no solution to this problem

Bulk collect problem

Using the query statement of bulk collect, the behavior is different when no record is found: by default, Oracle’s bulk collect query will not throw No_ data_ Find abnormal, and reach the dream will.

The solution is to capture No_ data_ Ignore the found exception.

DBMS_ SQL package problems

DBMS_ SQL has a bug, get out of the col_ max_ Len is 0, for example:

create table mydual as
 select * from dual;

 declare
  v_col_cnt           NUMBER;
  v_cursorid          NUMBER;
  v_desc_t            DBMS_SQL.desc_tab2;
 begin
 	dbms_output.enable;
  	v_cursorid := DBMS_SQL.open_cursor;
 	DBMS_SQL.parse(v_cursorid, 'select ''123'' c1, DUMMY c2 from mydual', dbms_sql.native);
    DBMS_SQL.describe_columns(v_cursorid, v_col_cnt, v_desc_t);
    FOR i IN 1..v_col_cnt LOOP
		dbms_output.put_line('i ' || i || ' name = ' || v_desc_t(i).col_name || 
			' col_max_len = ' || v_desc_t(i).col_max_len);
	END LOOP;
 end;

DBMS_ There are many other bugs in SQL packageI didn’t write down the details. Please use it carefully.

The problem of prior and next

When the subscript value cannot be found in the container, Dameng cannot get the prior and next correctly. The verification stored procedure is as follows:

declare
    type v_mp_type is table of number index by PLS_INTEGER;
    v_mp v_mp_type;
begin
    dbms_output.enable;
	v_mp(1) := 1;
	v_mp(3) := 2;
	--Oracle output 1 to dream output empty
	dbms_output.put_line('v_mp.prior(2) = ' || v_mp.prior(2));
end;

The solution is to write the prior and next functions

--You need to write functions to replace Oracle's prior and next
function get_prior_index(v_mp IN v_mp_type, v_ind IN PLS_INTEGER) return PLS_INTEGER
is 
    v_vv_last PLS_INTEGER := null;
	vv PLS_INTEGER := v_mp.first;
begin
	--Ergodic V_ Make a comparison 
	while vv is not null
	loop
		--If a subscript value is found to be higher than the V passed in_ If ind is large or equal, the previous subscript value is returned
		--(null if the first subscript)
	    if (vv >= v_ind) then return v_vv_last; end if;
	    v_vv_last := vv;
		vv := v_mp.next(vv);
	end loop;
	

	--If all the subscripts are traversed, no more than or equal to V is found_ Ind, the largest subscript v is returned_ mp.last
	return v_vv_last;

end;

function get_next_index(v_mp IN v_mp_type, v_ind IN PLS_INTEGER) return PLS_INTEGER
is 
	v_vv_last PLS_INTEGER := null;
	vv PLS_INTEGER := v_mp.last;
begin
	--Reverse order traversal V_ Make a comparison 
	while vv is not null
	loop
		--If a subscript value is found to be less than or equal to v_ Ind returns the previous subscript value
		--(null if it is the largest subscript)
		if (vv <= v_ind) then return v_vv_last; end if;
		v_vv_last := vv;
		vv := v_mp.prior(vv);
	end loop;
	

	--If all subscripts are traversed in reverse order, no less than or equal to V is found_ Ind, the smallest subscript v is returned_ mp.first
	return v_vv_last;

end;

Date calculation problem

There are a lot of articles on this website. By default, Dameng divides two integers, and the result type is integer, while Oracle is decimal.

So we can use TRUNC (V) in Oracle_ Date) – 1 / 86400 gets the time one second ago, but when it comes to daydream, it’s similar to TRUNC (V)_ Date) – 0 is the same.

The solution is to change it to TRUNC (V)_ date)-1.0/86400

Parametric problem

If a variable is passed to a function as a function parameter to obtain the function return value, Oracle will empty the function by default, but Dameng will not.

This leads to a problem,

The verification code is as follows:

/*The test result shows that the output of Oracle is expected to be empty, but error will appear in Dameng*/
create or replace procedure testKinstarerOutParam(str OUT varchar2) as
begin
    dbms_output.put_line('str = ' || str);
    if (str is not null) THEN
        RAISE_ APPLICATION_ Error (- 20001, 'output parameter not cleared');
    end if;
end;
/

create or replace procedure testKinstarerCallOutParam as
       strIn varchar2(64) := 'error';
begin
       testKinstarerOutParam(strIn);
end;
/

dbms_output.enable;
begin testKinstarerCallOutParam(); end;

Lob support issues

Oracle can use to_ Char function operates on lob type field, but in Dameng, sometimes such operation will fail, and an error is reported as DBMS_ LOB.READ line 1157

Diutil package missing

I don’t know why, Damon didn’t provide the diutil package. There are some functions in it. It’s very convenient. It’s a pity that there isn’t any. So I wrote one myself

CREATE OR REPLACE PACKAGE diutil IS


  -- bool_to_int:  translates 3-valued BOOLEAN TO NUMBER FOR USE
  --               IN sending BOOLEAN parameter / RETURN VALUES
  --               BETWEEN pls v1 (client) AND pls v2. since sqlnet
  --               has no BOOLEAN bind variable TYPE, we encode
  --               booleans AS false = 0, true = 1, NULL = NULL FOR
  --               network transfer AS NUMBER
  --
  FUNCTION bool_to_int( b BOOLEAN) RETURN NUMBER;
  
    -- int_to_bool:  translates 3-valued NUMBER encoding TO BOOLEAN FOR USE
  --               IN sending BOOLEAN parameter / RETURN VALUES
  --               BETWEEN pls v1 (client) AND pls v2. since sqlnet
  --               has no BOOLEAN bind variable TYPE, we encode
  --               booleans AS false = 0, true = 1, NULL = NULL FOR
  --               network transfer AS NUMBER
  --
  function int_to_bool( n NUMBER) return boolean;
  
  function get_sql_hash(name IN varchar2, v_hash OUT RAW,
                        pre10ihash OUT number)
    return number;

  function rpad_dm(string varchar2, padded_length number, pad_string varchar2 := ' ')
    return varchar2;
  
  function copy1kList(v_input ua_utl_def.t_str_1k_list) return ua_utl_def.t_str_1k_list;
end diutil;

CREATE OR REPLACE PACKAGE BODY diutil IS
  --------------------
  -- bool_to_int
  --------------------
  FUNCTION bool_to_int(b BOOLEAN) RETURN NUMBER IS
  BEGIN
    IF b THEN
      RETURN 1;
    ELSIF NOT b THEN
      RETURN 0;
    ELSE
      RETURN NULL;
    END IF;
  END bool_to_int;
  
    --------------------
  -- int_to_bool
  --------------------
  FUNCTION int_to_bool(n NUMBER) RETURN BOOLEAN IS
  BEGIN
    IF n IS NULL THEN
      RETURN NULL;
    ELSIF n = 1 THEN
      RETURN true;
    ELSIF n = 0 THEN
      RETURN false;
    ELSE
      RAISE value_error;
    END IF;
  END int_to_bool;
  
  function get_sql_hash(name IN varchar2, v_hash OUT RAW,
                        pre10ihash OUT number)
    return number IS
    v_hash_varchar2 VARCHAR2(128);
    v_hash_tmp VARCHAR2(128);
  BEGIN
    --  Compute a hash value for the given string using md5 algo
  --  Input arguments:
  --    name  - The string to be hashed.
  --    hash  - An optional field to store all 16 bytes of returned
  --            hash value.
  --    pre10ihash - An optional field to store the pre 10i database
  --                 version hash value.
  --  Returns:
  --    A hash value (last 4 bytes)  based on the input string.
  --    The md5 hash algorithm computes a 16 byte hash value, but
  --    we only return the last 4 bytes so that we can return an
  --    actual number.  One could use an optional RAW parameter to
  --    get all 16 bytes and to store the pre 10i hash value of 4
  --    4 bytes in the pre10ihash optional parameter.
  	-- Utl_Raw.Cast_To_Raw(
  	
  	v_hash_varchar2 := DBMS_OBFUSCATION_TOOLKIT.MD5(name);
  	v_hash := Utl_Raw.cast_to_raw(v_hash_varchar2);
  	v_hash_tmp := substrb(v_hash, 13, 4);
  	
  	pre10ihash := to_ number(v_ hash_ TMP, 'XXXXXXXXXXX'); -- todo: there is a problem with the implementation here. What does pre10ihash mean? I don't understand it
  	
  	-- select Utl_Raw.Cast_To_Raw(DBMS_OBFUSCATION_TOOLKIT.MD5(input_string =>'abc')) a from Dual
  	return to_number(v_hash_tmp, 'XXXXXXXXXX'); 
  END;
  
  function rpad_dm(string varchar2, padded_length number, pad_string varchar2 := ' ')
    return varchar2 IS
    v_len number := lengthb(string);
  BEGIN
  	    dbms_output.put_line('v_len - padded_length = ' );
  	if padded_length < v_len THEN
  		return substrb(string, 1, padded_ Length); -- if the input length is less than the original string length, the substrb truncation is called
  	elsif padded_length = v_len THEN
  		Return string; -- if the length is equal, return the original string directly
  	else
  		return string || rpad(' ', padded_ length - v_ len, pad_ String); -- if the length is greater than the original string, fill in a space after it
  	end if;
  END;


  function copy1kList(v_input ua_utl_def.t_str_1k_list) return ua_utl_def.t_str_1k_list IS
    v_tmplist ua_utl_def.t_str_1k_list;
    v_ind PLS_INTEGER;
  begin
  	if v_input.count > 0 then
  		/*
  		for vv in v_input.first .. v_input.last LOOP
  			v_tmplist(vv) := v_input(vv);
  		end loop;
  		*/
  		v_ind = v_input.first;
  		while v_ind is not null
  		loop
  			v_tmplist(v_ind) := v_input(v_ind);
  			v_ind = v_input.next(v_ind);
  		end loop;
  	end if;
  	return v_tmplist;
  end;
end diutil;

The stored procedure will not be prompted if it fails to build

When the Damong client executes the new stored procedure, it should be noted that even if it is successfully created, it only means that the syntax is correct. It is very likely that the stored procedure is not successfully built due to other problems, and it is still in an invalid state.

The solution is to manually execute alter procedure after creating the stored procedure;

FORMAT_ ERROR_ Backtrace has no line number problem at the call

As we all know, Oracle provides a function DBMS_ utility.format_ error_ Backtrace is used to get the function stack information when the exception module is called during processing. There will be clear function name and source code location information in it

But Damon calls this function to return a bunch of internal symbols that he can’t understand

This problem has caused me a lot of trouble in migration, because the main logic of our business is implemented in stored procedures. We need to register log and record function stack information when the program is abnormal to facilitate tracking.

After my unremitting research, I finally solved the problem that Dameng can’t get stack information. Here’s the solution:

dbms_output.enable;
select * from  q$log order by 1 desc;
select * from q$error_instance order by 1 desc;

CREATE OR REPLACE PROCEDURE logIntoDb(loglevel PLS_INTEGER, inf IN varchar2, callStack IN varchar2)
IS
    PRAGMA AUTONOMOUS_ Transaction; -- log enlistment requires autonomous transactions
BEGIN
	-- loglevel 0 debug 10 inf 20 err
	INSERT INTO q$log
          (id, "CONTEXT", text, call_stack, created_on, created_by, app_system, app_module)
        VALUES
          (q$log_seq.nextval,
           decode(logLevel, 0, 'debug', 'other'),
           inf,
           callStack,
           SYSDATE,
           USER,
           'unify_audit',
           'logIntoDb');
    commit;
END;
alter PROCEDURE logIntoDb compile;

CREATE OR REPLACE FUNCTION getErrorBackTrace() return varchar2
IS
	--Dameng can't get stack information directly, it needs to be set in the function 
	c_stack VARCHAR2(6000) := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
BEGIN
    return c_stack;
END;
/
alter FUNCTION getErrorBackTrace COMPILE;

CREATE OR REPLACE PROCEDURE debugHt(inf IN varchar2)
IS
	--Exception is not used by default, so the line number cannot be recorded
	--Use exception to record the line number, but the performance will be degraded, which is used for debugging
	v_useException boolean := true;
BEGIN
	if (v_useException) then
		--Take the initiative to create an exception so that you can format_ ERROR_ The backtrace function has a value
		RAISE_APPLICATION_ERROR(-20001, 'debug');
	else
		logIntoDb(0, inf, DBMS_UTILITY.format_call_stack);
	end if;
exception
  when others then
    --DBMS of Dameng_ UTILITY.FORMAT_ ERROR_ Backtrace function must be obtained every other bit
    --Otherwise, only the stack information of the current function can be obtained
	logIntoDb(0, inf, getErrorBackTrace());
END;
/
alter PROCEDURE debugHt COMPILE;


CREATE OR REPLACE PROCEDURE proc2
IS
BEGIN
    debugHt('hello log');
	execute immediate 'delete * from dual1233';
exception 
    when others then
    	debugHt('hello exp');
END;
/
alter PROCEDURE proc2 COMPILE;

CREATE OR REPLACE PROCEDURE proc3
IS
BEGIN
    proc2();
END;
/

CREATE OR REPLACE PROCEDURE proc4
IS
BEGIN
    proc3();
END;
/

begin proc4(); end;

Recommended Today

Review of SQL Sever basic command

catalogue preface Installation of virtual machine Commands and operations Basic command syntax Case sensitive SQL keyword and function name Column and Index Names alias Too long to see? Space Database connection Connection of SSMS Connection of command line Database operation establish delete constraint integrity constraint Common constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY DEFAULT […]