Debugging

JSPs:

System.out.println("Object ->"+obj); ---to see in the logfile

HTML:

%>

ArrayList = <%=arrList%>

<%

PL/SQL:

dbms_output.put_line('blah blah ');

PL/SQL: function that will check if the NUMBER fields entered are valid numbers

-- A function that will check if the NUMBER fields entered are valid numbers
--- To Make sure that all the Number Fields are valid numbers
---------------------------------------------------------------------------------
FUNCTION MCIBE_ISNUMBER( p_string IN VARCHAR2) RETURN VARCHAR2 IS
l_number NUMBER;
BEGIN
l_number := to_number( NVL(TRIM(p_string),'-9999') );
IF ( CEIL(l_number) - (l_number) ) > 0 THEN
RETURN '2'; --- A number with decimal point
ELSIF l_number = 0 THEN
RETURN '1';
ELSE
RETURN '0'; ---A whole number
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN '1';
END MCIBE_ISNUMBER;

PL/SQL: function that will check if the NUMBER fields entered are valid numbers

-- A function that will check if the NUMBER fields entered are valid numbers
--- To Make sure that all the Number Fields are valid numbers
---------------------------------------------------------------------------------
FUNCTION MCIBE_ISNUMBER( p_string IN VARCHAR2) RETURN VARCHAR2 IS
l_number NUMBER;
BEGIN
l_number := to_number( NVL(TRIM(p_string),'-9999') );
IF ( CEIL(l_number) - (l_number) ) > 0 THEN
RETURN '2'; --- A number with decimal point
ELSIF l_number = 0 THEN
RETURN '1';
ELSE
RETURN '0'; ---A whole number
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN '1';
END MCIBE_ISNUMBER;

PL/SQL: A procedure to validate an email id

PROCEDURE MCIBE_VALID_EMAIL_ID (p_email_id VARCHAR2
,p_partner_enduser IN VARCHAR2
,p_err_code IN OUT NUMBER
,p_err_msg IN OUT VARCHAR2) IS
----Variable Declaration
l_first_char VARCHAR2(1);
l_char_in_id VARCHAR2(100);
l_check_dot VARCHAR2(1);
l_email_id VARCHAR2(100);
l_error_flag VARCHAR2(50) := 0;
l_position_of_at NUMBER := -1;
l_position_of_dot NUMBER := -1;
l_len NUMBER;
l_no_of_at NUMBER :=0;
BEGIN
--p_err_code := 0;
l_len := length(TRIM(p_email_id));
IF (l_len <> 0) THEN
----Read Id character by Character
FOR i IN 1 .. l_len
LOOP
l_char_in_id := SUBSTR(p_email_id,i,1);
---Position of the '@'
IF l_char_in_id = '@' THEN
l_position_of_at := i;
l_no_of_at := l_no_of_at +1;
END IF;
---position of the '.'
IF l_char_in_id = '.' THEN
l_position_of_dot := i;
END IF;
END LOOP;
l_first_char := substr(p_email_id,1,1);
---IF '@' or '.' doesn't exists at all
IF (l_position_of_at = -1 OR l_position_of_dot = -1 ) THEN
l_error_flag := 1;
----Check if there are multiple '@'
ELSIF l_no_of_at >1 AND l_error_flag = 0 THEN
l_error_flag := 1;
---Check if first letter is '@' or '.'
ELSIF l_first_char ='.' OR l_first_char = '@' THEN
l_error_flag := 1;
---Check if last letter is '@' or '.'
ELSIF (l_position_of_at = l_len OR l_position_of_dot = l_len) THEN
l_error_flag := 1;
---Check IF '@' never occurs before '.'
ELSIF (l_position_of_at > l_position_of_dot)THEN
l_error_flag := 1;
---Check if '@' occurs one position before '.'
ELSIF (l_position_of_dot - l_position_of_at) = 1 THEN
l_error_flag := 1;
ELSE
l_check_dot := SUBSTR(p_email_id,(l_position_of_at)-1,1) ;
IF(l_check_dot = '.')THEN
l_error_flag := 1;
ELSE
---Check if '.' comes right after '.'
l_check_dot := SUBSTR(p_email_id,(l_position_of_dot)-1,1) ;
IF(l_check_dot = '.')THEN
l_error_flag := 1;
END IF;
END IF;
END IF;
END IF;

----return 1 for error and 0 for no error
IF( l_error_flag = 1) THEN
p_err_code := 1;
p_err_msg := p_err_msg|| ' ; '||'Invalid ' || p_partner_enduser ||' contact Email Id';
END IF;

EXCEPTION
WHEN OTHERS THEN
p_err_code := 1;
p_err_msg := p_err_Msg || ' ; ' ||(SQLCODE || ' ' || substr(SQLERRM,1,100));

END MCIBE_VALID_EMAIL_ID;

PL/SQL: DB Links

DB1: ED01 --This has the table oe_order_stg
DB2: CRD01--Need to access that table in ED01 from this DB.

1. Create a DB link(O11i_TO_CAT.ISUS.MC.COM) ..CRD01 -> ED01
2. Create a Synonym for the ED01 table(oe_order_stg) in CRD01:

CREATE OR REPLACE SYNONYM oe_order_stg
FOR oe_order_stg@O11i_TO_CAT.ISUS.MC.COM
/

3. Now in CRD01. you can access oe_order_stg directly.

Select * from oe_order_stg.

------------------

without synonym:

Select * from oe_order_stg@O11i_TO_CAT.ISUS.MC.COM (In CRD01).

Oracle Apps: Profile Option

Create a new Profile Option:

1. Login as "Application Developer" Responsibility. Go to Profile and create a new profile and save.

2. Login as "System Administrator" Responsibility. Go to Profile -> System. Serch for the name entered in the "user profile field" in step 1. Enter the value and save.

Getting the Profile option value in SQL:

Value := fnd_profile.value('Profile option name');

PL/SQL: Check to see if the date is in the format of 'MM/DD/YYYY'

CREATE or REPLACE FUNCTION validate_date_format(p_input_date VARCHAR2)
RETURN BOLEAN IS

l_result_date DATE;

BEGIN

l_result_date := TO_DATE(p_input_date, 'MM/DD/YYYY');

IF LENGTH(SUBSTR(p_input_date, INSTR(p_input_date, '/', 1, 2) + 1)) = 4
THEN

RETURN TRUE;

ELSE

RETURN FALSE;

END IF;

EXCEPTION
WHEN OTHERS then
RETURN FALSE;

END;

PL/SQL: String concatenation

FUNCTION dyna_string1(p_string varchar2) RETURN VARCHAR2 IS

t VARCHAR2(2) :=',';
t1 VARCHAR2(2) :='''';
t2 VARCHAR2(32767);
t_check NUMBER(10);
newstring VARCHAR2(32767);

BEGIN

newstring :=p_string;
t_check :=instr(newstring,',');
IF t_check!=0 THEN
t2 := t1tt1;
newstring := replace(newstring,',',t2);
newstring := '('''''newstring''''')';
ELSE
newstring:=newstring;
newstring:='('''''newstring''''')';
END IF;
RETURN newstring;

END dyna_string1;

PL/SQL: Bulk collecting and updating

BULK Collection:

- declare the table and record to hold data

TYPE r_rec is RECORD(
--table DDL);

TYPE t_table IS TABLE OF r_rec
INDEX BY binary_integer;

t_info t_table;

OPEN cursor;
FETCH cursor BULK COLLECT INTO t_info;
CLOSE cursor;
-----------------------------------------------------------------------------------------

Bulk Update:


TYPE t_num_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
t_del_info t_num_array;

FOR i IN t_info.FIRST..t_info.LAST
LOOP
t_del_info(i) := t_info(i).delivery_line_detail_id;

END LOOP;

FORALL i in 1..t_del_info.COUNT
UPDATE table
SET ----
WHERE delivery_line_detail_id = t_del_info(i);