Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Passing VARCHAR2 values to a Procedure

Posted on 2012-03-28
5
Medium Priority
?
501 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 1700 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

660 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