Solved

Passing VARCHAR2 values to a Procedure

Posted on 2012-03-28
5
493 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

740 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