Solved

Passing VARCHAR2 values to a Procedure

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

Expert Comment

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

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 73

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
one-way data "masking" MD5 sql 26 145
T-SQL Convert to PL/SQL 23 77
oracle report printing 2 pages in one page 2 58
File generation using utl_file 4 45
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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

911 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

19 Experts available now in Live!

Get 1:1 Help Now