Click here to Skip to main content
15,908,768 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi,
I am facing a problem while creating a function.....

SQL
create or replace
FUNCTION "XYZ" (P_ORDR_ID IN NUMBER)  
RETURN VARCHAR IS 
ab VARCHAR(15);
BEGIN 
SELECT tab3.ACNT_REF INTO ab
       FROM tab1,      tab2,
      tab3
    WHERE tab1.ORDR_ID                               = tab2.TXN_POS_NUM
    AND tab1.ORDR_POS_TYPE                           = tab2.TXN_POS_TYP
    AND TRIM(tab2.STLMNT_ACNT_POS_NUM)               = TO_CHAR(tab3.POS_ID)
    AND tab2.STLMNT_ACNT_POS_TYP                     = tab3.POS_TYPE
    AND tab1.BP_ID                                   = tab2.BP_ID
    AND tab1.STLMNT_CRNCY                            = tab3.POS_CRNCY 
    AND tab1.ORDR_ID                                 =  P_ORDR_ID;

RETURN (ab); 

     EXCEPTION
     WHEN OTHERS THEN RETURN (NULL);
END;



when i compile this fuction, it is throwing error -
1- table or view doesn't exist.
2- PL/SQL : SQL statement ignored.
both errors are coming on the bold line, although all the tables(tab1, tab2, tab3) are created in DB......

could ny 1 suggest me why this error is comin....
Posted
Updated 5-Jun-13 3:50am
v2
Comments
CHill60 5-Jun-13 9:50am    
Do you have three tables tab1, tab2 and tab3?
Sudhakar Shinde 5-Jun-13 9:59am    
Try running the following query and check if you are getting the same error.

SELECT tab3.ACNT_REF INTO ab
FROM tab1, tab2,tab3
WHERE tab1.ORDR_ID = tab2.TXN_POS_NUM
AND tab1.ORDR_POS_TYPE = tab2.TXN_POS_TYP
AND TRIM(tab2.STLMNT_ACNT_POS_NUM) = TO_CHAR(tab3.POS_ID)
AND tab2.STLMNT_ACNT_POS_TYP = tab3.POS_TYPE
AND tab1.BP_ID = tab2.BP_ID
AND tab1.STLMNT_CRNCY = tab3.POS_CRNCY
AND tab1.ORDR_ID = P_ORDR_ID;


In case you are getting the same error then please check you have sufficient grants to select from these tables.

1 solution

Are you creating the function within the correct DB ? I don't see a line where you specify what DB you're using.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900