Home » SQL & PL/SQL » SQL & PL/SQL » Problem in executing a procedure  () 1 Vote
Problem in executing a procedure [message #162200] Thu, 09 March 2006 03:02 Go to next message
shilpa_sudhakar
Messages: 25
Registered: March 2006
Location: Australia
Junior Member
Hi All ,

I have created a procedure which got compiled successfully.
However i am facing problems in executing it .

The procedure is as follows:


CREATE or replace Procedure spCRTS_load (start_date IN OUT date ,
end_date IN OUT date ,
region IN OUT VARCHAR2 ,
system IN OUT VARCHAR2,
source IN OUT VARCHAR2,
trace IN OUT VARCHAR2)

As


today date ;
run_month date ;
day1 number(10,2);
total_rows integer :=0 ;


BEGIN

spCRTS_load.today :=sysdate();
spCRTS_load.run_month := dateadd('M',-1,spCRTS_load.today);

if spCRTS_load.start_date is null then
begin
spCRTS_load.start_date := to_char(spCRTS_load.run_month,'MM')+ '1' || to_char(spCRTS_load.run_month,'YYYY');

spCRTS_load.day1 := to_number(spCRTS_load.run_month,'MONTH');

spCRTS_load.start_date := dateadd('M', -(spCRTS_load.day1-1), spCRTS_load.start_date);

end;
end if;


if spCRTS_load.end_date is null then
spCRTS_load.end_date := sysdate();
end if;

if spCRTS_load.region is null then
spCRTS_load.region := 'US';
end if;

if spCRTS_load.system is null then
spCRTS_load.system:= 'CRTS';
end if;

if spCRTS_load.trace = 'D' then
begin
DBMS_OUTPUT.PUT_LINE('Start Date :'||spCRTS_load.start_date);
DBMS_OUTPUT.PUT_LINE('End Date :'||spCRTS_load.end_date);
DBMS_OUTPUT.PUT_LINE('Region :'||spCRTS_load.region);
DBMS_OUTPUT.PUT_LINE('System :'||spCRTS_load.system);
DBMS_OUTPUT.PUT_LINE('Source :'||spCRTS_load.source);
end ;
end if;

delete from gc_tbltrans;



insert into gc_tbltrans(ORDER_ID,TRADE_ID,ACCOUNT,ACCT_TYP,ACCT_NAME,TRANS_TYPE,CUSIP,TICKER,
SEDOL,ISIN_NO,SEC_NAME,EXEC_BROKER,BROKER_NAME,BROKER_REASON,QUANTITY,
AMOUNT,COMM_RATE,COMM_AMT,IMPL_COMM_AMT,STEP_OUT_BROKER,PRICE,
BASE_PRICE,TRADE_DATE,SETTLE_DATE,NET_PRIN_AMT,NET_PRIN_BASE_AMT,COMM_SETTLE_AMT,
COMM_BASE_AMT,IMPL_COMM_BASE_AMT,CREATE_DATE,CREATE_USER,ORIGINAL_FACE,SOFT_DOLLAR_CD,
SOFT_DOLLAR_AMT,SOFT_DOLLAR_PCT,ORDER_MANAGER,SYSTEM,REGION,
SOURCE_CODE,STATUS,EXEC_ORIGINAL_FACE,FUTR_CONTRACT_AMT,EXEC_SETTLE_PRICE,
CUSTODIAN,SOFTDOLLAR_FLG,LOC_CURR,CURR,Manager,InvType,CcyFxRate,
Product,TotalCommissionLC,TotalAccruedCommLC,WithholdingTax,
TotalCommission,TotalAccruedComm,SettleCcy,IssueDate,MaturityDate,
Portfolio,EffectiveDuration,AverageLife,AverageLifeIS,TicketNum,Netnumber,AntaresTradeId,PM)
select a.ORDER_ID,a.TRADE_ID,a.ACCT_CD,e.ACCT_TYP_CD,e.ACCT_NAME,TRANS_TYPE,d.CUSIP,d.TICKER,
d.SEDOL,d.ISIN_NO,d.SEC_NAME,a.EXEC_BROKER,c.BKR_NAME,b.BROKER_REASON,a.EXEC_QTY,
a.EXEC_AMT,a.COMMISION_RATE,a.COMMISION_AMT,0,a.DIRECTED_BROKER,
a.EXEC_PRICE, a.EXEC_BASE_PRICE,b.TRADE_DATE,b.SETTLE_DATE,a.NET_PRIN_AMT,a.NET_PRIN_BASE_AMT,
a.COMMISION_SETTLE_AMT,a.COMMISION_BASE_AMT,a.INC_BASE_AMT,a.CREATE_DATE,a.CREATE_USER,
a.ORIGINAL_FACE,a.SOFT_DOLLAR_CD,a.SOFT_DOLLAR_AMT,a.SOFT_DOLLAR_PCT,a.ORDER_MANAGER,spCRTS_load.system,spCRTS_load.region,spCRTS_loa d.source,
b.STATUS,a.EXEC_ORIGINAL_FACE,a.FUTR_CONTRACT_AMT,a.EXEC_SETTLE_PRICE,a.CUSTODIAN,null,d.LOC_CRRNCY_CD,
d.ASSET_CRRNCY_CD,spCRTS_load.source,'N/A',a.TO_CRRNCY_BASE_FX_RATE,'N/A',0,0,0,0,0,'N/A',d.issue_date,null,'N/A',0.0,0.0,0,0,0,null, null
from TS_ORDER_ALLOC a,
TS_ORDER b,
CS_BROKER c,
CSM_SECURITY d,
CS_FUND e
where a.ORDER_ID = b.ORDER_ID
and b.EXEC_BROKER = c.BKR_CD
and b.SEC_ID = d.SEC_ID
and a.ACCT_CD = e.ACCT_CD
and b.status = 'ACCT'
and b.trade_date >= spCRTS_load.start_date
and b.trade_date <= spCRTS_load.end_date;

total_rows := SQL%ROWCOUNT;

dbms_output.put_line(spCRTS_load.total_rows ||' Transactions loaded');

end;
/
Procedure Created.

Then i create an anonymous block to call the procedure

DECLARE


START_DATE DATE;
END_DATE DATE ;
REGION VARCHAR2(10) := 'US';
SYSTEM VARCHAR2(10) := 'CRTS';
SOURCE VARCHAR2(10) := 'EQ';
TRACE VARCHAR2(10) := 'D';
BEGIN
start_date := to_date('20-Dec-2005','DD-MON-YYYY');
end_date := to_date('20-Jan-2006','DD-MON-YYYY');
SPCRTS_LOAD(START_DATE,END_DATE,REGION,SYSTEM,SOURCE,TRACE);
END;
/

but this gives me the following error :
DECLARE
*
ERROR at line 1:
ORA-01821: date format not recognized
ORA-06512: at line 11

I would like to know where exactly i am wrong. Is it in calling IN OUT parameters or only the data part.

Thanks in advance.
Shilpa
Re: Problem in executing a procedure [message #162213 is a reply to message #162200] Thu, 09 March 2006 03:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:

spCRTS_load.start_date := to_char(spCRTS_load.run_month,'MM')+ '1' || to_char(spCRTS_load.run_month,'YYYY');


Use add_months for this. Your method would fail terribly if run_month is december.
Besides, you create a character with format MMYYYY. Yet, start_date is expecting a date. Use an explicit to_date around the character. (which would no longer be necessary if you'd use add_months)

Please use code-tags around your code for better readability.

[Updated on: Thu, 09 March 2006 03:32]

Report message to a moderator

Re: Problem in executing a procedure [message #162224 is a reply to message #162213] Thu, 09 March 2006 04:03 Go to previous messageGo to next message
shilpa_sudhakar
Messages: 25
Registered: March 2006
Location: Australia
Junior Member
hi Frank ,

Thanks for replying.

i changed the code using add_months as follows :
    spCRTS_load.start_date := add_months(spCRTS_load.run_month,1);


After this, when i compile the procedure, it gets created successfully.

However when i execute it by using this anonymous block

DECLARE 

	
	START_DATE DATE;
	END_DATE DATE ;
	REGION VARCHAR2(10) := 'US';
	SYSTEM VARCHAR2(10) := 'CRTS';
	SOURCE VARCHAR2(10) := 'EQ';
	TRACE VARCHAR2(10) := 'D';
BEGIN
start_date := to_date('20-Dec-2005','DD-MON-YYYY');
end_date := to_date('20-Jan-2006','DD-MON-YYYY');
SPCRTS_LOAD(START_DATE,END_DATE,REGION,SYSTEM,SOURCE,TRACE);
END;
/


i get the following error.

DECLARE
*
ERROR at line 1:
ORA-20107: ONLY VALID VALUES FOR DATEPART ARE: DD or Day for days, WK or Week
for week, M or MONTH for month, YY or YEAR for year
ORA-06512: at "TM_DEV.DATEADD", line 21
ORA-06512: at "TM_DEV.SPCRTS_LOAD", line 21
ORA-06512: at line 13


I am not sure how to proceed further on this.

Please assist on this .

Thanks and Regards,
Shilpa
Re: Problem in executing a procedure [message #162260 is a reply to message #162224] Thu, 09 March 2006 07:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Your parameter in the call to dateadd is wrong. Since I do not know that function, you will have to investigate.
Show us the signature of that function.
Re: Problem in executing a procedure [message #162355 is a reply to message #162260] Thu, 09 March 2006 19:14 Go to previous messageGo to next message
shilpa_sudhakar
Messages: 25
Registered: March 2006
Location: Australia
Junior Member
Hi Frank ,

The dateadd function is the in-built oracle function that i have used.

Hope this is what you actually meant .

Thanks
Shilpa
Re: Problem in executing a procedure [message #162370 is a reply to message #162355] Thu, 09 March 2006 22:34 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
That is a user-defined function, not a built-in function.
Re: Problem in executing a procedure [message #162410 is a reply to message #162370] Fri, 10 March 2006 01:56 Go to previous messageGo to next message
shilpa_sudhakar
Messages: 25
Registered: March 2006
Location: Australia
Junior Member
Hi ,

I still think that dateadd function is a oracle function .
Even the link below shows examples of Dateadd function.
http://www.databasejournal.com/scripts/article.php/3336941

Also i am not getting any errors as such when i compile the procedure.

Sorry, if i am wrong.

i do realise that there is some problem with date in the procedure but not able to figure out the exact problem.

Please advice as to where i am going wrong.

Thanks,
Shilpa
Re: Problem in executing a procedure [message #162413 is a reply to message #162410] Fri, 10 March 2006 02:21 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
DATEADD is built-in function, but not in Oracle. Page you provided is either mis-linked (should be at SQL Server or Visual Basic) or the author got confused while writing the article.
Re: Problem in executing a procedure [message #162431 is a reply to message #162413] Fri, 10 March 2006 03:51 Go to previous messageGo to next message
shilpa_sudhakar
Messages: 25
Registered: March 2006
Location: Australia
Junior Member
hi ,

You all are right.

I thought dateadd to be an oracle function. This is what created the entire problem.

I changed the dateadd function with add_months and now the procedure gets compiled and executed successfully.

Thanks a lot for all your help .

Regards,
Shilpa
Re: Problem in executing a procedure [message #191149 is a reply to message #162431] Tue, 05 September 2006 01:07 Go to previous messageGo to next message
senthilk44
Messages: 5
Registered: September 2006
Junior Member
by using add month i can add only month isn't,
if i want to add date,what fuction should i use?
Re: Problem in executing a procedure [message #191156 is a reply to message #191149] Tue, 05 September 2006 01:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How would you add a date to a date?
What is 01-01-2000 + 02-01-2000 ???

Re: Problem in executing a procedure [message #191157 is a reply to message #191156] Tue, 05 September 2006 01:37 Go to previous messageGo to next message
senthilk44
Messages: 5
Registered: September 2006
Junior Member
Hi ,
Thx for u reply,
i am retriving DOB column from oracle9i datebase ,
DOB column datatype is DATE
Now i want to add 20 days to the retrived DOB

In sql DateAdd built-in function is thr,but in oracle how to achive this?

Re: Problem in executing a procedure [message #191174 is a reply to message #191157] Tue, 05 September 2006 02:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It's simple: add 20 to the DOB. ( + 20)
icon10.gif  Re: Problem in executing a procedure [message #191184 is a reply to message #191174] Tue, 05 September 2006 02:19 Go to previous messageGo to next message
senthilk44
Messages: 5
Registered: September 2006
Junior Member
Thanx dude,
its working fine............

but i have one doubt...

DOB+20 it's like normal addtion...
but how itz adding 20 to the date part of the DOB ...

if i change the date format will it work?

Re: Problem in executing a procedure [message #191220 is a reply to message #191184] Tue, 05 September 2006 04:29 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You can't change DATE format; all dates are stored into tables using Oracle's internal format (which you can not modify). What you can do, is to change this date representation to the end user using the TO_CHAR function (whether it will be DD/MM/YY or MM/YY/DD or whatever). It seems that you are talking about adding 20 days to a character string, not the date.

For example:
SQL> select to_date('12.03.2006', 'dd.mm.yyyy') + 20 first_example,
  2         to_char(to_date('12.03.2006', 'dd.mm.yyyy') + 20, 'dd-mon-yyyy')
  3           second_example
  4  from dual;

FIRST_EX SECOND_EXAM
-------- -----------
01.04.06 01-apr-2006

SQL>
While this won't work:
SQL> select '12.03.2006' + 20 from dual;
select '12.03.2006' + 20 from dual
       *
ERROR at line 1:
ORA-01722: invalid number


SQL>
Previous Topic: number to word ( first, second , third, forth ...etc)
Next Topic: ORA-00054
Goto Forum:
  


Current Time: Sun Dec 04 20:44:50 CST 2016

Total time taken to generate the page: 0.19034 seconds