Seaghost
asked on
Passing VARCHAR2 values to a Procedure
I have two procedures in Procedure A gathers data from a .CSV file and passes the values as VARCHAR2 values to Procedure B which begins calculation and insertion into a base table. The following code should help shed some light on my objectives:
The Code below is the shortened and to the point portion. It is also understood that values that are pulled from Table A are all VARCHAR2 datatypes and all values in Table B are also all VARCHAR2 datatypes.
Procedure P_Procedure_A()
V_SS_ID VARCHAR2(20);
V_TERM VARCHAR2(10);
V_TEAS VARCHAR2(10)
CURSOR c_newstudents IS
SELECT SSID, TERM_CODE, TEAS
FROM APP_NURSING.T_TABLE_A;
BEGIN
OPEN c_newstudents;
LOOP
FETCH c_newstudents INTO
V_SS_ID,
V_TERM,
V_TEAS;
P_PROCEDURE_B(V_SS_ID),V_T ERM,V_TEAS );
EXIT WHEN c_newstudents%NOTFOUND;
END LOOP;
CLOSE c_newstudents;
-------
PROCEDURE B
-------
PROCEDURE P_PROCEDURE_B(IN_SSID IN VARCHAR2, IN_TERM IN VARCHAR2, IN_TEAS IN VARCHAR2) IS
------
I receive the following error message when trying to run the procedures:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
Nothing is being converted and I do see my data values. Now when I take the values and run the Proecedure_B Independently, the calculations are performed accurately and are inserted into my base Table B.
This is all running on Oracle 11g.
Thank You,
The Code below is the shortened and to the point portion. It is also understood that values that are pulled from Table A are all VARCHAR2 datatypes and all values in Table B are also all VARCHAR2 datatypes.
Procedure P_Procedure_A()
V_SS_ID VARCHAR2(20);
V_TERM VARCHAR2(10);
V_TEAS VARCHAR2(10)
CURSOR c_newstudents IS
SELECT SSID, TERM_CODE, TEAS
FROM APP_NURSING.T_TABLE_A;
BEGIN
OPEN c_newstudents;
LOOP
FETCH c_newstudents INTO
V_SS_ID,
V_TERM,
V_TEAS;
P_PROCEDURE_B(V_SS_ID),V_T
EXIT WHEN c_newstudents%NOTFOUND;
END LOOP;
CLOSE c_newstudents;
-------
PROCEDURE B
-------
PROCEDURE P_PROCEDURE_B(IN_SSID IN VARCHAR2, IN_TERM IN VARCHAR2, IN_TEAS IN VARCHAR2) IS
------
I receive the following error message when trying to run the procedures:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
Nothing is being converted and I do see my data values. Now when I take the values and run the Proecedure_B Independently, the calculations are performed accurately and are inserted into my base Table B.
This is all running on Oracle 11g.
Thank You,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if B executes successfully, can you at least post more of A?
My "off the top of my head" guess would be that whatever table you are inserting into in PROCEDURE_B isn't all VARCHAR2 datatypes. That is what is causing the conversion that is failing.
Without table structures and more of the code, that is a total guess though.
Without table structures and more of the code, that is a total guess though.
I appreciate the points, but did I post anything that was actually helpful?
Is the missing semicolon for V_TEAS just a cut/paste typo?
V_SS_ID VARCHAR2(20);
V_TERM VARCHAR2(10);
V_TEAS VARCHAR2(10)
CURSOR c_newstudents IS
V_SS_ID VARCHAR2(20);
V_TERM VARCHAR2(10);
V_TEAS VARCHAR2(10)
CURSOR c_newstudents IS