Dim ProviderString As String
ProviderString = ProviderString.Format(ConnStr, ds, dbUser, dbPass)ProviderString = "Data Source=GEMS;USER ID=scott;PASSWORD=tiger
Conn.ConnectionString = ProviderString
Dim Cmd As New Data.OracleClient.OracleCommand()
Cmd.Connection = conn
Cmd.CommandText = "PCK_TREEVIEW.TREEVIEW"
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(New Data.OracleClient.OracleParameter("TREEID", Data.OracleClient.OracleType.VarChar)).Value = TreeID
Cmd.Parameters.Add(New Data.OracleClient.OracleParameter("RET_CUR", Data.OracleClient.OracleType.Cursor)).Direction = ParameterDirection.Output
Dim MyDA As New Data.OracleClient.OracleDataAdapter(Cmd)
Try
MyDA.Fill(ds)
Return ds
Catch x
End Try
I have used oracle connection.
now for the SP part.
CREATE OR REPLACE PACKAGE PCK_TREEVIEW AS
TYPE REF_CUR IS REF CURSOR ;
PROCEDURE TREEVIEW(TREEID IN VARCHAR2 :='', FROMDATE IN VARCHAR2 :='' , TODATE IN VARCHAR2 :='', SCREEN IN VARCHAR2 :='', RET_CUR IN OUT REF_CUR);
END PCK_TREEVIEW;
/
CREATE OR REPLACE PACKAGE BODY PCK_TREEVIEW
IS
--- { SCREEN : PM FOR PORTFOLIO MASTER SCREEN AND HM FOR HIERARCHY MAINTENANCE SCREEN AND WS FOR WEBSERVICE .} ---
PROCEDURE TREEVIEW(TREEID IN VARCHAR2:='' , FROMDATE IN VARCHAR2:='' , TODATE IN VARCHAR2 :='', SCREEN IN VARCHAR2 :='', RET_CUR IN OUT REF_CUR)
IS
ROW_PROCESSED NUMBER:= 1; PARENTID VARCHAR2(8); OLDPARENT VARCHAR2(8):='';
TOTALROWS NUMBER:=1; BBTOTAL NUMBER:=0; SCREENMOD INTEGER; CIDUPDATE INTEGER; ROW_PRO NUMBER;
CURSOR CWEBSER IS (SELECT DISTINCT FLD_HIERARCHY_TREE_ID FROM HIERARCHY
WHERE FLD_HIERARCHY_TREE_ID NOT IN
(SELECT FLD_HIERARCHY_TREE_ID FROM TREE_TEMPLATE WHERE FLD_TREE_DESIGNATION ='Building Block'));
CURSOR CPMPAR IS (SELECT DISTINCT FLD_PARENT_PORTF FROM HIERARCHY WHERE FLD_PARENT_PORTF NOT IN
(SELECT FLD_CHILD_PORTF FROM HIERARCHY
WHERE FLD_HIERARCHY_TREE_ID = M_TREEID) AND FLD_HIERARCHY_TREE_ID = M_TREEID);
CURSOR CHMPAR IS (SELECT DISTINCT FLD_PARENT_PORTF FROM HIERARCHY WHERE FLD_PARENT_PORTF NOT IN
(SELECT FLD_CHILD_PORTF FROM HIERARCHY
WHERE FLD_HIERARCHY_TREE_ID = M_TREEID) AND FLD_HIERARCHY_TREE_ID = M_TREEID AND
(TO_DATE(FLD_START_DT,'dd/MM/yyyy') BETWEEN TO_DATE(FROMDATE,'dd/MM/yyyy') AND TO_DATE(TODATE,'dd/MM/yyyy')
OR TO_DATE(FLD_END_DT,'dd/MM/yyyy') BETWEEN TO_DATE(FROMDATE,'dd/MM/yyyy') AND TO_DATE(TODATE,'dd/MM/yyyy')));
CURSOR CALL IS (SELECT FLD_HIERARCHY_TREE_ID , FLD_PARENT_PORTF, FLD_CHILD_PORTF, FLD_START_DT ,
FLD_END_DT , FLD_LEVEL_ID , LEVEL+1 LEVELID FROM HIERARCHY
WHERE FLD_HIERARCHY_TREE_ID = M_TREEID OR FLD_HIERARCHY_TREE_ID IN (SELECT FLD_HIERARCHY_TREE_ID FROM HIERARCHY
WHERE FLD_PARENT_PORTF IN (SELECT FLD_CHILD_PORTF FROM HIERARCHY
WHERE FLD_HIERARCHY_TREE_ID IN (M_TREEID)
CONNECT BY PRIOR FLD_CHILD_PORTF = FLD_PARENT_PORTF
START WITH FLD_PARENT_PORTF = PARENTID) AND FLD_HIERARCHY_TREE_ID IN
(SELECT FLD_HIERARCHY_TREE_ID FROM TREE_TEMPLATE WHERE FLD_TREE_DESIGNATION ='Building Block'))
CONNECT BY PRIOR FLD_CHILD_PORTF = FLD_PARENT_PORTF
START WITH FLD_PARENT_PORTF = PARENTID AND FLD_HIERARCHY_TREE_ID = M_TREEID);
IF SCREEN = 'PM' THEN
SCREENMOD := 1;
ELSIF SCREEN = 'HM' THEN
IF LTRIM(RTRIM(FROMDATE)) IS NULL THEN
SCREENMOD := 1;
ELSE
SCREENMOD := 2;
END IF;
ELSIF SCREEN = 'WS' THEN
SCREENMOD := 3;
END IF;
IF SCREENMOD = 1 THEN
M_TREEID := TREEID;
FOR REC_CUR IN CPMPAR
LOOP
IF CPMPAR%NOTFOUND THEN
EXIT;
END IF;
PARENTID := REC_CUR.FLD_PARENT_PORTF;
IF OLDPARENT = REC_CUR.FLD_PARENT_PORTF THEN
EXIT;
END IF;
INSERT INTO TREEVIEW_TMP (FLD_HIERARCHY_TREE_ID , FLD_PARENT_PORTF , FLD_CHILD_PORTF , FLD_START_DT ,
FLD_END_DT , FLD_LEVEL_ID , FLD_LEVEL , SLNO)
VALUES (M_TREEID ,M_TREEID, PARENTID, SYSDATE ,
SYSDATE , 0 ,1, TOTALROWS);
TOTALROWS := TOTALROWS + 1;
OPEN CALL;
LOOP
IF CALL%NOTFOUND THEN
EXIT;
END IF;
IF OV_FLD_HIERARCHY_TREE_ID = V_FLD_HIERARCHY_TREE_ID AND OV_FLD_PARENT_PORTF = V_FLD_PARENT_PORTF AND OV_FLD_CHILD_PORTF = V_FLD_CHILD_PORTF THEN
EXIT;
END IF;
OLDPARENT:= PARENTID;
ROW_PROCESSED := ROW_PROCESSED + 1;
END LOOP;
ELSIF SCREENMOD = 2 THEN
M_TREEID := TREEID;
FOR REC_CUR IN CHMPAR
LOOP
IF CHMPAR%NOTFOUND THEN
EXIT;
END IF;
PARENTID := REC_CUR.FLD_PARENT_PORTF;
IF OLDPARENT = REC_CUR.FLD_PARENT_PORTF THEN
EXIT;
END IF;
INSERT INTO TREEVIEW_TMP (FLD_HIERARCHY_TREE_ID , FLD_PARENT_PORTF , FLD_CHILD_PORTF , FLD_START_DT ,
FLD_END_DT , FLD_LEVEL_ID , FLD_LEVEL , SLNO)
VALUES (M_TREEID ,M_TREEID, PARENTID, SYSDATE ,
SYSDATE , 0 ,1, TOTALROWS);
TOTALROWS := TOTALROWS + 1;
OPEN CALL;
LOOP
IF CALL%NOTFOUND THEN
EXIT;
END IF;
IF OV_FLD_HIERARCHY_TREE_ID = V_FLD_HIERARCHY_TREE_ID AND OV_FLD_PARENT_PORTF = V_FLD_PARENT_PORTF AND OV_FLD_CHILD_PORTF = V_FLD_CHILD_PORTF THEN
EXIT;
END IF;
IF OV_FLD_HIERARCHY_TREE_ID = V_FLD_HIERARCHY_TREE_ID AND OV_FLD_PARENT_PORTF = V_FLD_PARENT_PORTF AND OV_FLD_CHILD_PORTF = V_FLD_CHILD_PORTF THEN
EXIT;
END IF;
OLDPARENT:= PARENTID;
ROW_PROCESSED := ROW_PROCESSED + 1;
END LOOP;
END LOOP;
END IF;
COMMIT;
OPEN RET_CUR FOR
SELECT FLD_HIERARCHY_TREE_ID , FLD_PARENT_PORTF , FLD_CHILD_PORTF , FLD_START_DT ,
FLD_END_DT , FLD_LEVEL_ID , FLD_LEVEL , SLNO FROM TREEVIEW_TMP ORDER BY SLNO;
COMMIT;
END TREEVIEW;
END PCK_TREEVIEW;
/
pralvika@yahoo.com is my personal id. If u have any clarifications, Pls dont hesitate to contact me.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.