Debug procedure for PLSQL

DROP TABLE ENRICH_DEBUG
/
CREATE TABLE ENRICH_DEBUG
(
SEQ_LINE NUMBER,
MSG VARCHAR2(2000 BYTE)
)
/
DROP SEQUENCE ENRICH_DEBUG_SEQ
/
create sequence ENRICH_DEBUG_SEQ increment by 1 start with 1
/
CREATE OR REPLACE PROCEDURE ENRICH_DEBUG_PROC(MSG VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
LN_SEQ NUMBER;
BEGIN
SELECT ENRICH_DEBUG_SEQ.NEXTVAL INTO LN_SEQ FROM DUAL;
INSERT INTO ENRICH_DEBUG VALUES (LN_SEQ,MSG);
COMMIT;
END;
/
DBMS_OUTPUT.PUT_LINE Can print upto 255 chars only.
FND_FILE.PUT_LINE can print upto 32767 chars only.
Solution:
create or replace procedure xxfile_output(msg varchar2)
is
begin
if length(msg) > 255 then
dbms_output.put_line(substr(msg,1,255));
else
xxfile_output(substr(msg,256));
end if;
end;

AUTHID CURRENT_USER/ AUTHID DEFINER:
procedure xxfile(msg varchar2)
AUTHID CURRENT_USER
is
xxfileout(msg);
end;

In above example, procedure will compile reference to xxfileout at run time with the login user.
Else proc xxfile will compile xxfileout at declaration time with create user.

DECLARE
    lb_status BOOLEAN;
BEGIN
    lb_status := FND_USER_PKG.CHANGEPASSWORD
                                                            (
                                                                username           => 'SJAKKULA',
                                                                newpassword     => 'Welcome'
                                                           );

    IF lb_status
    THEN
        DBMS_OUTPUT.PUT_LINE('Request processed sucessfully'); 
    ELSE
        DBMS_OUTPUT.PUT_LINE('Error while processing the request'||substr(SQLERRM,1,500)); 
    END IF;
END;

Commit;

Commit is very important here. Otherwise Password would not change. If you write cursor also, commit is required.

There is patch to expire password of all users in application.