MySQL Forums
Forum List  »  Oracle

How to convert this Oracle PL/SQL Function to MySQL
Posted by: Sathya Raj
Date: January 13, 2015 05:43AM

<<Hi Friends Please Treat it as urget as i should submit this by tomorrow ie.. 14th Jan 2015>> plzzzzzzzz waiting for ur reply..!

CREATE OR REPLACE PROCEDURE v_dup_filename_validation_proc
( v_file_name IN VARCHAR2
, v_filename_value IN VARCHAR2
, v_correl_id IN NUMBER
, v_dupfilename_found OUT BOOLEAN

)
IS


/* Cursor for Extraction of CorrelID */

CURSOR v_correlid_values IS
SELECT al.CORRELID
FROM audit_log al,
audit_detail ad,
WHERE
1=1
AND al.audit_log_id = ad.audit_log_id
AND ad.detail_name = v_file_name
AND ad.detail_value= v_filename_value
AND al.audit_type_id = IOG_ADT1
AND al.correl_id != v_correl_id
AND al.status = 'S';

TYPE collect_correlid_val IS TABLE OF audit_log%TYPE INDEX BY BINARY_INTEGER;

t_correlid_val collect_correlid_val;
t_count NUMBER(10);

BEGIN
OPEN v_correlid_values;
LOOP
FETCH v_correlid_values BULK COLLECT INTO t_correlid_val LIMIT 1000;
EXIT WHEN v_correlid_values.COUNT = 0;
END LOOP;
CLOSE v_correlid_values;

FORALL v_idx IN 1 .. t_correlid_val.LAST SAVE EXCEPTIONS
SELECT COUNT(al.audit_log_id)
INTO t_count
FROM audit_log al
WHERE al.audit_type_id = 'IOG_ADT4'
AND al.audit_status = 'S'
AND al.correl_id = v_idx.correl_id;

/*Checking for Duplicate file name found or not found */

IF t_count > 1 THEN
v_dupfilename_found:= TRUE
ELSE
v_dupfilename_found:= FALSE
END IF;
EXCEPTION
WHEN SQLCODE = 100 THEN
FOR idx in 1..SQL%BULK_EXCEPTIONS_COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('ERROR INDEX::'
|| SQL%BULK_EXCEPTIONS(idx).ERROR_INDEX)
|| 'ERROR CODE::'
|| SQL%BULK_EXCEPTIONS(idx).ERROR_CODE
);
END v_dup_filename_validation_proc;

Options: ReplyQuote


Subject
Views
Written By
Posted
How to convert this Oracle PL/SQL Function to MySQL
2973
January 13, 2015 05:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.