Home » SQL & PL/SQL » SQL & PL/SQL » Tips and Tricks Involved how to write a Test Script (Oracle 9i,10g.. Windows XP)
Tips and Tricks Involved how to write a Test Script [message #350602] Thu, 25 September 2008 10:23 Go to next message
vasanthanand
Messages: 3
Registered: February 2008
Junior Member
Hi Oracle Gurus,

I am Novice in PL/SQL Programming,
My job involves develop applications using PL/SQL
but i always Mess up wheni t comes to write Test Script that will test my Application that i have written.

Please help me here as my job inovolves writing heavy unit testing.

I am looking for Best practices for writing a Test Script for PL/SQL Procedures,Packages..
Now this is my Problem how do i Write a Script which will test this Procedure.I will appreciate when you reply this mail I will be happy to see what was your appraoch to write the Script.

all i am looking any best way and also Tips and tricks involved in Writing a Test script.

Pasted below is my Procedure..


Thanks
Anand


This is my Procedure.
---------------------------------------------------------------
CREATE OR REPLACE PROCEDURE CGETZ.teva_pre_val_chk_ar
(
--p_position in allegro.position%type,
p_valuationtime IN allegro_ext.t_eva_valuation.valuationtime%type,
p_valuationmode IN allegro_ext.t_eva_valuation.valuationmode%type,
p_tradebook IN allegro.position.tradebook%type
--p_out_Proc_status OUT PLS_INTEGER,

)
AS
v_tradedate DATE;
v_begtime DATE;
v_endtime DATE;
--v_valuationtime DATE;
--v_forwardmark VARCHAR2(50);
v_position VARCHAR2(10);
v_trade VARCHAR2(10);
--v_point VARCHAR2(50);
--v_marketarea VARCHAR2(50);
v_execution VARCHAR2(50);
--v_valuationmode VARCHAR2(50);
--v_product VARCHAR2(50);
--v_timeperiod VARCHAR2(50);
v_status VARCHAR2(10);
v_count NUMBER;

BEGIN

-- SELECT DISTINCT T.TRADE, T.EXECUTION, T.BEGTIME, T.ENDTIME, T.TRADEDATE,T.STATUS,
-- P.POSITION
-- INTO V_TRADE, V_EXECUTION, V_BEGTIME, V_ENDTIME, V_TRADEDATE,V_STATUS,V_POSITION
-- FROM ALLEGRO.POSITION P, ALLEGRO.TRADE T, ALLEGRO.EXECUTION E,ALLEGRO.VALUATIONSEGMENT VS
-- WHERE P.TRADE = T.TRADE
-- AND T.EXECUTION = E.EXECUTION
-- AND E.EXECUTION = VS.EXECUTION
-- AND VS.VALUATIONMODE = P_VALUATIONMODE
-- AND T.ENDTIME >= P_VALUATIONTIME
-- AND P.TRADEBOOK = P_TRADEBOOK ;

FOR cur1 in (SELECT DISTINCT t.trade, t.execution, t.begtime, t.endtime, t.tradedate,t.status,p.POSITION
FROM allegro.POSITION p, allegro.trade t, allegro.execution e,allegro.valuationsegment vs
WHERE p.trade = t.trade
AND t.execution = e.execution
ANd e.execution = vs.execution
ANd vs.valuationmode = p_valuationmode
AND t.endtime >= p_valuationtime
AND p.tradebook = p_tradebook )LOOP

IF cur1.status = 'INACTIVE' THEN DBMS_OUTPUT.PUT_LINE ('The Following list of Trades have Trade Status INACTIVE' || cur1.trade);

ELSIF cur1.tradedate > p_valuationtime THEN DBMS_OUTPUT.PUT_LINE ('These Trades below are not are not started for the Valuationtime' || p_valuationtime);

ELSIF cur1.endtime < p_valuationtime THEN DBMS_OUTPUT.PUT_LINE('The Following list of trades are Expired'|| cur1.trade);

ELSIF cur1.endtime >= p_valuationtime THEN

SELECT endtime
INTO v_endtime
FROM powerposition pp
WHERE pp.position = cur1.position;

DBMS_OUTPUT.PUT_LINE('Checking the Positions are Expired in powerposition table');

IF v_endtime < p_valuationtime THEN DBMS_OUTPUT.PUT_LINE('This Position doesnot have a Forward or Current Position'|| cur1.position);
ELSE
SELECT endtime
INTO v_endtime
FROM ngposition pp
WHERE pp.position = cur1.position;

DBMS_OUTPUT.PUT_LINE('Checking the Positions are Expired in ngposition table');

IF v_endtime < p_valuationtime THEN
DBMS_OUTPUT.PUT_LINE('This NGPosition doesnot have a Forward or Current Position'|| cur1.position);

END IF;


END IF;
END IF;

END LOOP;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Message content Not specified');

END;
/

----------------------------------------------------------------------------------

Re: Tips and Tricks Involved how to write a Test Script [message #350604 is a reply to message #350602] Thu, 25 September 2008 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One of the best practices: please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Tips and Tricks Involved how to write a Test Script [message #350613 is a reply to message #350602] Thu, 25 September 2008 10:43 Go to previous messageGo to next message
vasanthanand
Messages: 3
Registered: February 2008
Junior Member
Hi I have copy pated the Formatted code.....

CREATE OR REPLACE PROCEDURE cGetz.teva_pre_val_chk_ar
(
--p_position in allegro.position%type,
p_ValuationTime IN Allegro_ext.t_eva_Valuation.ValuationTime%TYPE,
p_ValuationMode IN Allegro_ext.t_eva_Valuation.ValuationMode%TYPE,
p_TradeBook IN Allegro.Position.TradeBook%TYPE
--p_out_Proc_status OUT PLS_INTEGER,
)
AS
v_TradeDate DATE;
v_BegTime DATE;
v_EndTime DATE;
--v_valuationtime DATE;
--v_forwardmark VARCHAR2(50);
v_Position VARCHAR2(10);
v_Trade VARCHAR2(10);
--v_point VARCHAR2(50);
--v_marketarea VARCHAR2(50);
v_Execution VARCHAR2(50);
--v_valuationmode VARCHAR2(50);
--v_product VARCHAR2(50);
--v_timeperiod VARCHAR2(50);
v_Status VARCHAR2(10);
v_Count NUMBER;
BEGIN
-- SELECT DISTINCT T.TRADE, T.EXECUTION, T.BEGTIME, T.ENDTIME, T.TRADEDATE,T.STATUS,
-- P.POSITION
-- INTO V_TRADE, V_EXECUTION, V_BEGTIME, V_ENDTIME, V_TRADEDATE,V_STATUS,V_POSITION
-- FROM ALLEGRO.POSITION P, ALLEGRO.TRADE T, ALLEGRO.EXECUTION E,ALLEGRO.VALUATIONSEGMENT VS
-- WHERE P.TRADE = T.TRADE
-- AND T.EXECUTION = E.EXECUTION
-- AND E.EXECUTION = VS.EXECUTION
-- AND VS.VALUATIONMODE = P_VALUATIONMODE
-- AND T.ENDTIME >= P_VALUATIONTIME
-- AND P.TRADEBOOK = P_TRADEBOOK ;

FOR Cur1 IN (SELECT DISTINCT t.Trade,
t.Execution,
t.BegTime,
t.EndTime,
t.TradeDate,
t.Status,
p.Position
FROM Allegro.Position p,
Allegro.Trade t,
Allegro.Execution e,
Allegro.ValuationSegment vs
WHERE p.Trade = t.Trade
AND t.Execution = e.Execution
AND e.Execution = vs.Execution
AND vs.ValuationMode = p_ValuationMode
AND t.EndTime >= p_ValuationTime
AND p.TradeBook = p_TradeBook)
LOOP
IF Cur1.Status = 'INACTIVE' THEN
dbms_Output.Put_Line('The Following list of Trades have Trade Status INACTIVE'
||Cur1.Trade);
ELSIF Cur1.TradeDate > p_ValuationTime THEN
dbms_Output.Put_Line('These Trades below are not are not started for the Valuationtime'
||p_ValuationTime);
ELSIF Cur1.EndTime < p_ValuationTime THEN
dbms_Output.Put_Line('The Following list of trades are Expired'
||Cur1.Trade);
ELSIF Cur1.EndTime >= p_ValuationTime THEN
SELECT EndTime
INTO v_EndTime
FROM PowerPosition pp
WHERE pp.Position = Cur1.Position;

dbms_Output.Put_Line('Checking the Positions are Expired in powerposition table');

IF v_EndTime < p_ValuationTime THEN
dbms_Output.Put_Line('This Position doesnot have a Forward or Current Position'
||Cur1.Position);
ELSE
SELECT EndTime
INTO v_EndTime
FROM ngPosition pp
WHERE pp.Position = Cur1.Position;

dbms_Output.Put_Line('Checking the Positions are Expired in ngposition table');

IF v_EndTime < p_ValuationTime THEN
dbms_Output.Put_Line('This NGPosition doesnot have a Forward or Current Position'
||Cur1.Position);
END IF;
END IF;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_Output.Put_Line('Message content Not specified');
END;
/
Re: Tips and Tricks Involved how to write a Test Script [message #350630 is a reply to message #350613] Thu, 25 September 2008 12:53 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Please use CODE tags to make you code formatted for reading purposes, as mentioned in the guide.

Like this:

begin
  null;
end;
/
Previous Topic: oracle not connect throuth scott/tiger
Next Topic: DIFFERENCE BETWEEN APPLICATION TRIGGER AND DATABASE TRIGGER
Goto Forum:
  


Current Time: Sat Dec 10 07:16:55 CST 2016

Total time taken to generate the page: 0.06622 seconds