error while using Cast function in oracle

Question

0.00/5 (No votes)

See more:

HI,

I have stored procedure in Oracle which is not getting compiled due to the following error:

The following error has occurred:

PROCEDURE STRL.SP_BEDOCC<br />
On line:  19<br />
PLS-00103: Encountered the symbol &quot;(&quot; when expecting one of the following:<br />
   ) varying large

Below is the line no 19 of my procedure

v_yearmonth := CAST(v_fin_year AS CHAR(4)) || v_month ;

v_month and v_yearmonth are of char type and v_fin_year is of number type. I need v_fin_year in 4 characters to concatnate it with v_month which is of 2 chars so that v_yearmonth is of 6 chars.

Can anybody let me know what could be the issue??

any help would be much appreciated.

Regards,
Gopal

Posted 15-Dec-10 23:15pm

gopalgupta

Updated 15-Dec-10 23:18pm

Toniyo Jackson

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

E.F. Nijboer · Accepted Answer · 2010-12-15T23:20:00

Solution 1

Try using to_char instead, like:

to_char(v_fin_year, '9999')

Good luck!

Posted 15-Dec-10 23:20pm

E.F. Nijboer

Comments

gopalgupta 16-Dec-10 6:43am

Thnx....that worked and the sp has compiled now.
But still it does not execute.

I am getting the below error while executing:
The following error has occurred:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "STRL.SP_BEDOCC", line 22
ORA-06512: at line 9

My sp till line 22 is as follows:
CREATE OR REPLACE PROCEDURE sp_bedocc
--exec sp_MISMonthlyBedPosting '15 may 2002','23 july 2002'
--sp_helptext sp_bedocc
--drop proc sp_bedocc

(
v_fin_year IN NUMBER DEFAULT NULL ,
v_month IN CHAR DEFAULT NULL
)
AS
--Exec sp_los @fin_year , @month
v_fn_yr Number(10,0);
v_yearmonth CHAR(6);
v_yr NUMBER(10,0);
v_temp NUMBER(1, 0) := 0;

BEGIN

v_yr := v_fin_year ;
v_fn_yr := v_fin_year ;
v_yearmonth := TO_CHAR(v_fin_year,'9999') || v_month ;

E.F. Nijboer 16-Dec-10 7:31am

You seem to be working with the char datatype and to_char returns VARCHAR2.
reference: http://psoug.org/reference/convert_func.html

gopalgupta 18-Dec-10 3:48am

thnx man...its working fine now...