CREATE OR REPLACE FUNCTION apps.xx_search_long(
p_obj_type IN VARCHAR2
, p_obj_name IN VARCHAR2
, p_owner IN VARCHAR2
, p_search_str IN VARCHAR2
)
RETURN VARCHAR2
IS
temporary_varchar VARCHAR2(31000);
v1 LONG;
v_src_code CLOB;
v_length NUMBER;
BEGIN
BEGIN
SELECT DBMS_METADATA.get_ddl(p_obj_type
, p_obj_name
, p_owner
)
INTO v_src_code
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Exception : ' || SUBSTR(SQLERRM
, 1
, 150
));
END;
v_length := DBMS_LOB.getlength(v_src_code);
DBMS_OUTPUT.put_line('Length of CLOB - ' || v_length);
IF DBMS_LOB.INSTR(v_src_code
, p_search_str
) > 0
THEN
DBMS_OUTPUT.put_line('Found ....');
RETURN('FOUND');
ELSE
DBMS_OUTPUT.put_line('Not Found ....');
RETURN('NOT FOUND');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN('NOT_FOUND');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Error .' || SQLERRM);
RETURN('EXCEPTION' || SUBSTR(SQLERRM
, 1
, 150
));
END;
/
p_obj_type IN VARCHAR2
, p_obj_name IN VARCHAR2
, p_owner IN VARCHAR2
, p_search_str IN VARCHAR2
)
RETURN VARCHAR2
IS
temporary_varchar VARCHAR2(31000);
v1 LONG;
v_src_code CLOB;
v_length NUMBER;
BEGIN
BEGIN
SELECT DBMS_METADATA.get_ddl(p_obj_type
, p_obj_name
, p_owner
)
INTO v_src_code
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Exception : ' || SUBSTR(SQLERRM
, 1
, 150
));
END;
v_length := DBMS_LOB.getlength(v_src_code);
DBMS_OUTPUT.put_line('Length of CLOB - ' || v_length);
IF DBMS_LOB.INSTR(v_src_code
, p_search_str
) > 0
THEN
DBMS_OUTPUT.put_line('Found ....');
RETURN('FOUND');
ELSE
DBMS_OUTPUT.put_line('Not Found ....');
RETURN('NOT FOUND');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN('NOT_FOUND');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Error .' || SQLERRM);
RETURN('EXCEPTION' || SUBSTR(SQLERRM
, 1
, 150
));
END;
/
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.