rem ----------------------------------------------------------------------- rem Filename: showcall.sql rem Purpose: Show details about a transcation - including old and new rem column values. rem ----------------------------------------------------------------------- connect repadmin/repadmin CREATE OR REPLACE PROCEDURE "REPADMIN"."SHOW_CALL" (IntxAn IN VARCHAR2, Incall IN NUMBER) IS argNo NUMBER; argtyp NUMBER; argForm NUMBER; CallNo NUMBER; tRanid VARCHAR2(30); typdsc CHAR(15); RowId_val ROWID; Char_val VARCHAR2(255); nChar_val NVARCHAR2(255); Date_val DATE; Number_val NUMBER; vArchar2_val VARCHAR2(2000); nvArchar2_val NVARCHAR2(2000); Raw_val RAW(255); arg_Name VARCHAR2(30); arg_Name_c CHAR(30); Table_Name VARCHAR2(100); col_Name VARCHAR2(100); pk_Char CHAR(1); Version VARCHAR2(10) := '1.0.1'; CURSOR defCall_Cur(Incall NUMBER, IntxAn VARCHAR2) IS SELECT CallNo, Deferred_tRan_Id, scHemAnAme, PackageName, ProcName, argCount FROM defCall WHERE CallNo = Incall AND Deferred_tRan_Id = IntxAn; CURSOR arg_Name_Cur(p_scHema VARCHAR2, p_ProcName VARCHAR2, p_pkgName VARCHAR2, p_Call_Count VARCHAR2) IS SELECT Argument_Name FROM All_Arguments WHERE Owner = p_scHema AND Package_Name = p_pkgName AND Object_Name = p_ProcName AND (OverLoad = (SELECT ovrld.OverLoad FROM (SELECT OverLoad, Object_Name, Package_Name, MAX(Position) pos FROM All_Arguments WHERE Object_Name = p_ProcName AND Package_Name = p_pkgName GROUP BY OverLoad, Object_Name, Package_Name) ovrld WHERE p_Call_Count = ovrld.pos AND Object_Name = p_ProcName AND Package_Name = p_pkgName) OR OverLoad IS NULL ) ORDER BY Position; BEGIN dbms_Output.ENABLE(10000000); FOR defCall_rec IN defCall_Cur(Incall,IntxAn) LOOP dbms_Output.Put_Line('Version : ' ||Version); dbms_Output.Put_Line('Call Number : ' ||defCall_rec.CallNo); dbms_Output.Put_Line('Call to : '||defCall_rec.scHemAnAme||'.'||defCall_rec.PackageName||'.'||defCall_ rec.ProcName); dbms_Output.Put_Line('# of Arguments: ' ||defCall_rec.argCount); dbms_Output.Put_Line(' Arg ' ||'Data Type ' ||'Value'); dbms_Output.Put_Line(' --------------------------------- ' ||'--------------- ' ||'----------------------'); argNo := 1; CallNo := defCall_rec.CallNo; tRanid := defCall_rec.Deferred_tRan_Id; OPEN arg_Name_Cur(defCall_rec.scHemAnAme, defCall_rec.ProcName, defCall_rec.PackageName, defCall_rec.argCount); WHILE argNo <= defCall_rec.argCount LOOP FETCH arg_Name_Cur INTO arg_Name; arg_Name_c := arg_Name; Table_Name := Substr(defCall_rec.PackageName,1,Instr(defCall_rec.PackageName,'$') - 1); col_Name := Substr(arg_Name,1,Length(arg_Name) - 5) ||'%'; argtyp := dbms_Defer_Query.Get_arg_Type(CallNo,argNo,tRanid); argForm := dbms_Defer_Query.Get_arg_Form(CallNo,argNo,tRanid); IF argtyp = 1 AND argForm = 1 THEN typdsc := 'VARCHAR2'; vArchar2_val := dbms_Defer_Query.Get_vArchar2_arg(CallNo,argNo,tRanid); dbms_Output.Put_Line(Substr(To_Char(argNo,'009') ||' ' ||arg_Name_c ||typdsc ||' ' ||nvl(vArchar2_val,'(NULL)'),0,255)); ELSIF argtyp = 1 AND argForm = 2 THEN typdsc := 'NVARCHAR2'; nvArchar2_val := dbms_Defer_Query.Get_nvArchar2_arg(CallNo,argNo,tRanid); dbms_Output.Put_Line(Substr(To_Char(argNo,'009')||' '||arg_Name_c||typdsc||' '||nvl(Translate(nvArchar2_val USING Char_cs),'(NULL)'), 0, 255)); ELSIF argtyp = 2 THEN typdsc := 'NUMBER'; Number_val := dbms_Defer_Query.Get_Number_arg(CallNo,argNo,tRanid); dbms_Output.Put_Line(Substr(To_Char(argNo,'009') ||' ' ||arg_Name_c ||typdsc ||' ' ||nvl(To_Char(Number_val),'(NULL)'),0,255)); ELSIF argtyp = 11 THEN typdsc := 'ROWID'; RowId_val := dbms_Defer_Query.Get_RowId_arg(CallNo,argNo,tRanid); dbms_Output.Put_Line(Substr(To_Char(argNo,'009') ||' ' ||arg_Name_c ||typdsc ||' ' ||nvl(RowId_val,'(NULL)'),0,255)); ELSIF argtyp = 12 THEN typdsc := 'DATE'; Date_val := dbms_Defer_Query.Get_Date_arg(CallNo,argNo,tRanid); dbms_Output.Put_Line(Substr(To_Char(argNo,'009') ||' ' ||arg_Name_c ||typdsc ||' ' ||nvl(To_Char(Date_val,'YYYY-MM-DD HH24:MI:SS'),'(NULL)'),0,255)); ELSIF argtyp = 23 THEN typdsc := 'RAW'; Raw_val := dbms_Defer_Query.Get_Raw_arg(CallNo,argNo,tRanid); dbms_Output.Put_Line(Substr(To_Char(argNo,'009') ||' ' ||arg_Name_c ||typdsc ||' ' ||nvl(Raw_val,'(NULL)'),0,255)); ELSIF argtyp = 96 AND argForm = 1 THEN typdsc := 'CHAR'; Char_val := dbms_Defer_Query.Get_Char_arg(CallNo,argNo,tRanid); dbms_Output.Put_Line(Substr(To_Char(argNo,'009') ||' ' ||arg_Name_c ||typdsc ||' ' ||nvl(Char_val,'(NULL)') ||'|',0,255)); ELSIF argtyp = 96 AND argForm = 2 THEN typdsc := 'NCHAR'; nChar_val := dbms_Defer_Query.Get_nChar_arg(CallNo,argNo,tRanid); dbms_Output.Put_Line(Substr(To_Char(argNo,'009')||' '||arg_Name_c||typdsc||' '||nvl(Translate(nChar_val USING Char_cs),'(NULL)')||'|', 0, 255)); ELSIF argtyp = 113 THEN typdsc := 'BLOB'; vArchar2_val := dbms_Lob.Substr(dbms_Defer_Query.Get_Blob_arg(CallNo,argNo,tRanid)); dbms_Output.Put_Line(Substr(To_Char(argNo,'009') ||' ' ||arg_Name_c ||typdsc ||' ' ||nvl(vArchar2_val,'(NULL)'),0,255)); ELSIF argtyp = 112 AND argForm = 1 THEN typdsc := 'CLOB'; vArchar2_val := dbms_Lob.Substr(dbms_Defer_Query.Get_cLob_arg(CallNo,argNo,tRanid)); dbms_Output.Put_Line(Substr(To_Char(argNo,'009') ||' ' ||arg_Name_c ||typdsc ||' ' ||nvl(vArchar2_val,'(NULL)'),0,255)); ELSIF argtyp = 112 AND argForm = 2 THEN typdsc := 'NCLOB'; nvArchar2_val := dbms_Lob.Substr(dbms_Defer_Query.Get_ncLob_arg(CallNo,argNo,tRanid)); dbms_Output.Put_Line(Substr(To_Char(argNo,'009')||' '||arg_Name_c||typdsc||' '||nvl(Translate(nvArchar2_val USING Char_cs),'(NULL)'), 0, 255)); END IF; argNo := argNo + 1; END LOOP; CLOSE arg_Name_Cur; END LOOP; END; /