How to Create Multiple or Replace Procedure in Oracle

1.7k
0
0

CREATE
OR REPLACE PROCEDURE spr_SplitXml_H2H (
--vSFilename varchar2,
vformat varchar2,
vChk_Condition IN VARCHAR2,
vFilesize varchar2,
vFileAction varchar2,
vFDATE varchar2,
vProcessType varchar2,
vFileID varchar2,
vFileName varchar2,
vSplitlimit varchar2,
vFILE_MST_ID varchar2,
vSPLITFILE_DATE varchar2,
-------------EBT-----------------
vTYPE varchar2,
vGroupOFHeader varchar2,
vAmount varchar2,
vTXN_NO varchar2,
-------------EBT_CLILD------------
vCount varchar2,
vData varchar2,
cur_Out OUT SYS_REFCURSOR
) AS mQuery varchar(4000);
mValidationMsg VARCHAR2(50);
vTXNTYPE varchar(50);
mErr_Msg VARCHAR2(200);
BEGIN -- select SPLIT_FILE....1
IF (
vChk_Condition = 'SelectFILE_MASTER'
) THEN mQuery := 'select folder_Path from File_Master where [CATEGORY]=''' || || ''' and [FILE_TYPE]=''' || || ''' and [FILE_ACTION]=''SPLIT'' and [TXNTYPE]=' || vTXNTYPE || '';
open cur_Out for mQuery;
END IF;
-- select file master....2
IF (vChk_Condition = 'genrate_file') THEN mQuery := 'select * from file_master where CATEGORY=''' || || ''' AND TXNTYPE=''' || || '''';
open cur_Out for mQuery;
END IF;
--3 Query-------------
IF(
vChk_Condition = 'SplitFile_Details'
) Then mQuery := 'select count(FILE_MST_ID) from SplitFile_Details where FILE_MST_ID=''' || || '''';
-- and Convert (varchar(10),[date],103) ='" + DateTime.Now.ToString("dd/MM/yyyy") + "'";
open cur_Out for mQuery;
END IF;
-------Ouery 4------------------
IF(vChk_Condition = 'GroupOFHeader') THEN mQuery := 'select GroupOFHeader from EBT_Master';
open cur_Out for mQuery;
END IF;
IF (vChk_Condition = 'insert_Xml') THEN mQuery := 'insert into File_Details(Filename ,[Filesize],FileAction,FDATE,TXNTYPE,ProcessType)' || -- values ('" + FileName + "','" + size + "','SPLIT',convert(datetime,'" + DateTime.Now.ToString("dd/MM/yyyy") + "',103),'" + type[6] + "','" + type[0] + type[1] + "')";
' VALUES (:1,:2,:3,:4,:5,:6)';
EXECUTE IMMEDIATE mQuery USING vFilename,
vFilesize,
vFileAction,
vFDATE,
vTXNTYPE,
vProcessType;
COMMIT;
OPEN cur_out FOR
SELECT
'SUCESSFULLY'
FROM
dual;
END IF;
----------Query insert 2-------------
IF(vChk_Condition = 'insert_EBT') THEN mQuery := 'insert into EBT_master(TYPE,GroupOFHeader,Amount,TXN_NO)' || --values('"+type1+"','" + GroupHeader + "','" + amtResult + "','" + TxsCountResult + "')";
' VALUES (:1,:2,:3,:4)';
EXECUTE IMMEDIATE mQuery USING vTYPE,
vGroupOFHeader,
vAmount,
vTXN_NO;
COMMIT;
OPEN cur_out FOR
SELECT
'SUCESSFULLY'
FROM
dual;
END IF;
-------qUERY-----------
IF(
vChk_Condition = 'Insert_EBT_child'
) THEN --query = "insert into SplitFile_Details (FileID,FileName,[FileSize],Date,FILE_MST_ID) values('" + fileid.ToString() + "','" + Path.GetFileName(path) + "','" + size1 + "',convert(datetime,'" + DateTime.Now.ToString("dd/MM/yyyy") + "',103),'" + FILE_MST_ID + "')";
mQuery := 'insert into EBT_child (Count,Data,Amount)' || -- values('" + i + "','" + match + "','" + match2.Groups[1].ToString() + "')";
' VALUES (:1,:2,:3)';
EXECUTE IMMEDIATE mQuery USING vCount,
vData,
vAmount;
COMMIT;
OPEN cur_out FOR
SELECT
'SUCESSFULLY'
FROM
dual;
END IF;
-------------------------------------------------
IF(
vChk_Condition = 'Insert_SplitFile_Details'
) THEN mQuery := 'insert into SplitFile_Details (FileID,FileName,[FileSize],SPLITFILE_DATE,FILE_MST_ID)' || -- values('" + fileid.ToString() + "','" + Path.GetFileName(path) + "','" + size1 + "',convert(datetime,'" + DateTime.Now.ToString("dd/MM/yyyy") + "',103),'" + FILE_MST_ID + "')";
' VALUES (:1,:2,:3,:4,:5)';
EXECUTE IMMEDIATE mQuery USING vFileID,
vFileName,
vFileSize,
vSPLITFILE_DATE,
vFILE_MST_ID;
COMMIT;
OPEN cur_out FOR
SELECT
'SUCESSFULLY'
FROM
dual;
END IF;
------------------------------------------------
IF(
vChk_Condition = 'Select_fileDetails'
) THEN mQuery := 'select FileID from File_Details where Filename=''' || || '''';
open cur_Out for mQuery;
END IF;
----QUERYsearch-------
IF(
vChk_Condition = 'insert_SplitFile_Details1'
) THEN mQuery := 'insert into SplitFile_Details (FileID,FileName,[FileSize],Date,FILE_MST_ID)' || -- values('" + fileid1.ToString() + "','" + Path.GetFileName(path) + "','" + size2 + "',convert(datetime,'" + DateTime.Now.ToString("dd/MM/yyyy") + "',103),'" + FILE_MST_ID + "')";
' VALUES (:1,:2,:3,:4,:5)';
EXECUTE IMMEDIATE mQuery USING vFileID,
vFileName,
vFileSize,
vSPLITFILE_DATE,
vFILE_MST_ID;
COMMIT;
OPEN cur_out FOR
SELECT
'SUCESSFULLY'
FROM
dual;
END IF;
------------------------------------------new---------------------------------------
IF(vChk_Condition = 'Delete') THEN mQuery := 'Delete from EBT_Child where Data=''' || || '''';
COMMIT;
OPEN cur_out FOR
SELECT
'DELETE SUCESSFULLY'
FROM
dual;
END IF;
IF(
vChk_Condition = 'Xml_File_Details'
) THEN mQuery := ' select FileID from File_Details where Filename=''' || || '''';
open cur_Out for mQuery;
END IF;
EXCEPTION WHEN OTHERS THEN dbms_output.put_line(';SQLERRM = ' || SQLERRM);
-- dbms_output.put_line(';SQLERRM = '; || SQLERRM);
mErr_Msg := SQLERRM;
OPEN cur_Out FOR
SELECT
';FAILED#' || mErr_Msg "Status"
from
dual;
-- SELECT ';FAILED#'; || mErr_Msg "Status" from dual;
ROLLBACK;
END;
/