SQL & PL/SQL

Hi,

So below is the scenario I have. I was wondering if there is any function oracle provides or is there any way I could achieve this.

CREATE OR REPLACE procedure TestA 
(
      partnumber               IN  number,
      partsubnum               IN  varchar2,
      errstring             OUT varchar2
) as

--  Declare local variables

ll_inputpartnum varchar2(100);


Begin

    ll_inputpartnum := partnumber || partsubnum; -- Concatinate partnumber and partsubnum
  -- Will need to convert this variable to number.

    TestB.Process(ll_inputpartnum,parameter2, s_errstring, errnum); --ll_inputpartnum must be of type number 
     if  errnum <> compkg.success then
       errstring := s_errstring;
       return;
     end if;
  
  
Exception 
    When others then 
    Null;    
End;
/

I do not have option to change anything on TestB.Process Procedure.

So there are two problem I am trying to solve in this scenario.

First, the input parameter - ll_inputpartnum in TestB.Process takes input of only number type. So i need to somehow convert the concatenated ll_inputpartnum into Integer.

Second problem is, Procedure TestB.Process - after completing its processing, passes ll_inputpartnum to the Third procedure. Let's say Procedure TestC. So in Procedure TestC, I need to again break down the concatenated value into original partnumber and partsubnum.

The concatenated value - ll_inputpartnum does not change through out this process.

So, i am trying to figure out the best way to approach this situation.

Thank you

Edited by: ARIZ on May 25, 2010 1:18 PM