Solved

Passing VARCHAR2 values to a Procedure

Posted on 2012-03-28
5
495 Views
Last Modified: 2012-03-28
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_TERM,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,
0
Comment
Question by:Seaghost
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 425 total points
ID: 37778719
I'm sorry,  nothing in what you have posted would produce that error.

There are a few minor syntax errors, but I assume those are from the simplification and even those wouldn't produce the error you mentioned.

Can you post more of the code?  Either you have a found an oracle bug, or there is something happening that you're not  seeing and hence have not included in the snippet above
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37778726
if B executes successfully, can you at least post more of  A?
0
 
LVL 35

Expert Comment

by:johnsone
ID: 37778762
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.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37778989
I appreciate the points, but did I post anything that was actually helpful?
0
 
LVL 23

Expert Comment

by:David
ID: 37779876
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question