Problem in datatype conversion

[JawwadLone]

Hello everybody,

I am trying to solve one problem from many days but i am unable. Problem is that I am writting a procedure in oracle and want to convert the datatype of variable from varchar(20) to number in the oracle procedure. My varible can have different values, number as well as characters that is why i have declared the variable as varchar(20). Now i have to see if the value in the variable is > 14 or less then 14 then execute a query otherwise go for some other queries.

if V_AS_UNIT>14 then
--Insert Statements
else
--Select Statements
end if;

This V_AS_UNIT is varchar(20) type variable. I would be very greatfull if someone can give me the solution.

Regards,

Jawwad Lone.

 

[SantaMufasa]

Jawwad, (Halé shoma hoobé.)

I am trying to solve one problem from many days but i am unable. Problem is that I ... want to convert the datatype of variable from varchar(20) to number in the oracle procedure. My varible can have...number as well as characters...

Oracle allows you to assign a "varchar" value to a "number". Oracle even does the data conversion for you. The problem comes if the value that you are trying to convert is not numeric. What do you want to do if the variable contains non-numeric characters that will not convert to a number datatype?

Here is a variation on your original code that do what you want in cases where the contents of the original field are numeric:

DECLARE
    number_hold    number;
    bad_number     exception;
    pragma exception_init(bad_number,-1722);
    ...
BEGIN
    ...
    BEGIN
        number_hold := to_number(V_AS_UNIT);
        if number_hold > 14 then
            --Insert Statements
        else
            --Select Statements
        end if; 
    EXCEPTION
        WHEN bad_number then
            --Logic for non-numeric data
    END;
    --Rest of program
END;

Let us know if this information is helpful and if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[JawwadLone]

Hi SantaMufasa,
How are you i hope you would be fine. Sorry for late reply as i was out of city. I have tested to_number thing in my procedure but it still give me an error

The following error has occurred:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "PLI.GET_PROPOSAL_FORM_TEST", line 126
ORA-01403: no data found

This is the procedure which give me the error

PROCEDURE       GET_PROPOSAL_FORM_Test (P_POL_NO MAIN.POL_NO %TYPE)

AS

V_POL_NO MAIN.POL_NO %TYPE;
V_RISK_DATE DATE;
V_TERM MAIN.TERM %TYPE;
V_AGE_AT_ENT NUMBER;
V_TABLE MAIN.TABLE_NO %TYPE;
V_SUM_ASS MAIN.SUM_ASS %TYPE;
V_MON_PREM MAIN.MON_PREM %TYPE;
V_DOB DATE;
V_PROP_DATE DATE;
V_DECL_DATE DATE;
V_ACCE_DATE DATE;
V_FINAL_MONTH MAIN.DUE_MON %TYPE;
V_FINAL_YEAR MAIN.DUE_YEAR %TYPE;
V_MATU_DATE DATE;
V_FATHER_NAME VARCHAR(40);
V_NAME VARCHAR(40);
V_ADDRESS VARCHAR(100);
V_CITY VARCHAR(40);
V_CLASS VARCHAR(50);
V_NOMINEES VARCHAR(200);
V_FLAG BOOLEAN;
V_CONV_CODE MAIN.CONV_CODE %TYPE;
V_CONV_NAME MAIN.CONV_NAME %TYPE;
V_CONV_ADDRESS MAIN.CONV_ADDRESS %TYPE;
V_LIR_NO MAIN.LIR_NO %TYPE;
V_DATED DATE;
V_AMOUNT_PAID MAIN.AMOUNT_PAID %TYPE;
V_CREDIT_DATE MAIN.CREDIT_DT %TYPE;
V_POST_OFFICE VARCHAR (20);
V_AD_UNIT VARCHAR(20) ;
V_AS_UNIT VARCHAR(20) ;
V_PM_UNIT VARCHAR(20) ;
V_AMOUNT_PREM MAIN.AMOUNT_PREM%TYPE;
V_AMOUNT_REBATE MAIN.AMOUNT_REBATE%TYPE;
V_AMOUNT_PAYABLE MAIN.AMOUNT_PAYABLE%TYPE;
V_AMOUNT_ACC_LESS MAIN.AMOUNT_ACC_LESS%TYPE;
V_P_MODE PMODE.P_MODE%TYPE;

CURSOR C_MAIN IS
SELECT POL_NO,RISK_DT,TERM,TABLE_NO,SUM_ASS,MON_PREM,PROP_DT,DECL_DT,ACCE_DT,DUE_MON,DUE_YEAR,MATU_DT,DECODE(CONV_CODE,NULL,'-',CONV_CODE) "CONV_CODE",DECODE(CONV_NAME,NULL,'-',CONV_NAME) "CONV_NAME",DECODE(CONV_ADDRESS,NULL,'-',CONV_ADDRESS) "CONV_ADDRESS"
,LIR_NO,SYSDATE,AMOUNT_PAID,CREDIT_DT
,AMOUNT_PREM,AMOUNT_REBATE
,AMOUNT_PAYABLE,AMOUNT_ACC_LESS
FROM MAIN WHERE POL_NO=P_POL_NO;

CURSOR C_INSURED IS
SELECT AGE_AT_ENT,DOB,FH_NAME,F_NAME||' '||L_NAME,ADD1_LN1||' '||ADD1_LN2,CITY1 FROM INSURED WHERE POL_NO=P_POL_NO;

CURSOR C_CLASS IS
SELECT C.DESCRIPTION FROM MAIN M, CLASS C WHERE M.CLASS_CODE=C.CLASS_CODE AND M.POL_NO=P_POL_NO;

CURSOR C_NOMINEE IS
SELECT NAME,RELATION FROM NOMINEE WHERE POL_NO=P_POL_NO;
BEGIN


SELECT OGPO INTO V_POST_OFFICE FROM MAIN WHERE POL_NO=P_POL_NO;
SELECT AD_UNIT INTO V_AD_UNIT FROM MAIN WHERE POL_NO=P_POL_NO;
SELECT AS_UNIT INTO V_AS_UNIT FROM MAIN WHERE POL_NO=P_POL_NO;
SELECT PM_UNIT INTO V_PM_UNIT FROM MAIN WHERE POL_NO=P_POL_NO;
SELECT P_MODE INTO V_P_MODE FROM PMODE WHERE POL_NO=P_POL_NO;

IF V_POST_OFFICE IS NULL THEN
V_POST_OFFICE:='-';
ELSE
SELECT G.NAME INTO V_POST_OFFICE FROM GPO G,MAIN M WHERE G.GPO_CODE=M.OGPO AND M.POL_NO=P_POL_NO;
END IF;

IF V_AD_UNIT IS NULL THEN
V_AD_UNIT:='-';
ELSE
SELECT AD.NAME INTO V_AD_UNIT FROM GPO AD,MAIN M WHERE AD.GPO_CODE=M.AD_UNIT AND M.POL_NO=P_POL_NO;
END IF;

[COLOR=red]
--THIS BLOCK GENERATES ERROR
--*******************************************************
IF V_AS_UNIT IS NULL THEN
V_AS_UNIT:='-';
ELSE
if to_number(V_AS_UNIT)>14 then
SELECT AS_.NAME INTO V_AS_UNIT FROM GPO AS_,MAIN M WHERE AS_.GPO_CODE=M.OGPO AND M.POL_NO=P_POL_NO;
else
SELECT AS_.NAME INTO V_AS_UNIT FROM SUB_GPO AS_,MAIN M WHERE AS_.SUB_GPO_CODE=M.OGPO AND M.POL_NO=P_POL_NO;
end if;
END IF;
--*********************************************************
--END OF BLOCK
[/color]
IF V_PM_UNIT IS NULL THEN
V_PM_UNIT:='-';
ELSE
SELECT PM.NAME INTO V_PM_UNIT FROM GPO PM,MAIN M WHERE PM.GPO_CODE=M.PM_UNIT AND M.POL_NO=P_POL_NO;
END IF;


DELETE FROM PROPOSAL_FORM;
COMMIT;
OPEN C_MAIN;
FETCH C_MAIN INTO V_POL_NO,V_RISK_DATE,V_TERM,V_TABLE,V_SUM_ASS,V_MON_PREM,V_PROP_DATE,V_DECL_DATE,V_ACCE_DATE,V_FINAL_MONTH,V_FINAL_YEAR,V_MATU_DATE,V_CONV_CODE,V_CONV_NAME,V_CONV_ADDRESS,V_LIR_NO,V_DATED,V_AMOUNT_PAID,V_CREDIT_DATE,V_AMOUNT_PREM,V_AMOUNT_REBATE,V_AMOUNT_PAYABLE,V_AMOUNT_ACC_LESS;
INSERT INTO PROPOSAL_FORM(POL_NO,RISK_DATE,TERM,TABLE_,SUM_ASS,MON_PREM,PROP_DATE,DECL_DATE,ACC_DATE,FINAL_MONTH,FINAL_YEAR,MATU_DAT,CONV_CODE,CONV_NAME,CONV_ADDRESS,LIR_NO,DATED,AMOUNT_PAID,CREDIT_DATE,POST_OFFICE,AD_UNIT,AS_UNIT,PM_UNIT,AMOUNT_PREM,AMOUNT_REBATE,AMOUNT_PAYABLE,AMOUNT_ACC_LESS,P_MODE)VALUES(V_POL_NO,V_RISK_DATE,V_TERM,V_TABLE,V_SUM_ASS,V_MON_PREM,V_PROP_DATE,V_DECL_DATE,V_ACCE_DATE,V_FINAL_MONTH,V_FINAL_YEAR,V_MATU_DATE,V_CONV_CODE,V_CONV_NAME,V_CONV_ADDRESS,V_LIR_NO,V_DATED,V_AMOUNT_PAID,V_CREDIT_DATE,V_POST_OFFICE,V_AD_UNIT,V_AS_UNIT,V_PM_UNIT,V_AMOUNT_PREM,V_AMOUNT_REBATE,V_AMOUNT_PAYABLE,V_AMOUNT_ACC_LESS,V_P_MODE);

COMMIT;
CLOSE C_MAIN;
OPEN C_INSURED;
FETCH C_INSURED INTO V_AGE_AT_ENT,V_DOB,V_FATHER_NAME,V_NAME,V_ADDRESS,V_CITY;
UPDATE PROPOSAL_FORM SET AGE_AT_ENT=V_AGE_AT_ENT,DOB=V_DOB,FATHER_NAME=V_FATHER_NAME,NAME=V_NAME,ADDRESS=V_ADDRESS,CITY=V_CITY;
COMMIT;
CLOSE C_INSURED;
OPEN C_CLASS;
FETCH C_CLASS INTO V_CLASS;
UPDATE PROPOSAL_FORM SET CLASS=V_CLASS;
COMMIT;
CLOSE C_CLASS;
FOR X IN C_NOMINEE LOOP
IF V_FLAG=TRUE THEN
V_NOMINEES:=V_NOMINEES||'/'||X.NAME||'('||X.RELATION||')';
ELSE
V_NOMINEES:=X.NAME||'('||X.RELATION||')';
END IF;
V_FLAG:=TRUE;
END LOOP;
UPDATE PROPOSAL_FORM SET NOMINEES=V_NOMINEES;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR('HELLO',19000);

COMMIT;
END;

I have blocked the code where i am expecting the error comes. Thanks for the help.

Regards,
Jawwad Lone.

 

[SantaMufasa]

Jawwad,

Your problem occurs because in PL/SQL, a SELECT that does not reside within a cursor must return exactly one row...if the result set is zero rows, then you receive the above error ("ORA-01403: no data found"); if the result set has two or more rows, then you receive the error "ORA-01422: exact fetch returns more than requested number of rows".

Therefore, one of your two SELECT statements, below, returns no rows. Since you did not post a line-numbered version of your code from SQL*Plus (which will number the lines if you ask SQL*Plus to "list" your code [with the "l" command]) and I wasn't willing to manually count to 126, I am just posting the possible offending SELECTs here:

SELECT AS_.NAME INTO V_AS_UNIT FROM GPO AS_,MAIN M WHERE AS_.GPO_CODE=M.OGPO AND M.POL_NO=P_POL_NO;
else
SELECT AS_.NAME INTO V_AS_UNIT FROM SUB_GPO AS_,MAIN M WHERE AS_.SUB_GPO_CODE=M.OGPO AND M.POL_NO=P_POL_NO;

So, your earlier problem will go away if you can reinvent your WHERE clause(s) in the above SELECT(s) to return exactly one row.

Let us know how things turn out. (Since I'm guessing that you are 11 hours ahead of me [21:15 your time], I don't know how much longer you will be on-line. But I am working all day today and can help until we get this resolved.)

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[JawwadLone]

Thanks for your cooperation. I have tried to execute
the procedure after commenting few lines. finally i
came to know about the block which is generating
error, which is very same which i have highlighted in
my previous post

if V_AS_UNIT>14 then
SELECT AS_.NAME INTO V_AS_UNIT FROM GPO AS_,MAIN M
WHERE AS_.GPO_CODE=M.OGPO AND M.POL_NO=P_POL_NO;
else
SELECT AS_.NAME INTO V_AS_UNIT FROM SUB_GPO AS_,MAIN M
WHERE AS_.SUB_GPO_CODE=M.OGPO AND M.POL_NO=P_POL_NO;
end if;

I have also executed Select queries present in this
block individually in SQL plus and received following
results.

First query returns “OKARA”
Second one returns “no row selected message”
And the value of V_AS_UNIT in if condition is
"DEPALPUR" which is not a number.

Is there isNumeric function in oracle to check the
value of a variable if it is a number or not? And any
function to replace the null value returned by the
query to the variable?

Problem 1 comes with the if condition
Problem 2 comes with 2nd Select query

Regards,
Jawwad Lone.

 

[SantaMufasa]

Jawwad,

Although Oracle does not have an "isNumeric" function, We can build a user-defined function (such as "Num_Check", below) that has one incoming argument; if the argument is numeric, then the function returns that argument; if the argument is not numeric, then the function returns NULL:

create or replace function Num_check (x varchar2) return number is
    num_hold number;
begin
    num_hold := x;
    return num_hold;
exception
    when others then
        return null;
end;
/

function created.

Then, you can combine this function with Oracle's built-in "NVL" (NULL-Value) function that tests its argument-1 for NULL; if argument-1 is not null, then it returns argument-1; if argument-1 IS NULL, then it returns argument-2 (which people often specify as "0"). Here is your code, which I have modified to use both functions:

if [b]NVL(Num_Check(V_AS_UNIT),0)[/b]>14 then
SELECT AS_.NAME INTO V_AS_UNIT FROM GPO AS_,MAIN M
WHERE AS_.GPO_CODE=M.OGPO AND M.POL_NO=P_POL_NO;
else
SELECT AS_.NAME INTO V_AS_UNIT FROM SUB_GPO AS_,MAIN M
WHERE AS_.SUB_GPO_CODE=M.OGPO AND M.POL_NO=P_POL_NO;
end if;

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.