Solved

Passing VARCHAR2 values to a Procedure

Posted on 2012-03-28
5
488 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
  • 3
5 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 425 total points
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
if B executes successfully, can you at least post more of  A?
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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 73

Expert Comment

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

Expert Comment

by:David
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now