dESPERATE HELP TO PASS ARGUMENTS AND LOCAL VAR TO SQL Part 2

[EdRev]

This stored procedure accepts 5 arguments. I'm having problem passing the arguments and local variable into the sql statememts. Can anybody help me with this.
Create or Replace Procedure spVersion(
IN_YR IN Varchar2,
IN_Ver IN Varchar2,
IN_Month IN Varchar2,
IN_Dest_Ver IN Varchar2,
IN_Dest_Month IN VarChar2)
AS
Begin
DECLARE
IN_Year NUMBER(8) :=To_Number(IN_YR);
IN_Year_ID NUMBER(8);
IN_VerID NUMBER(8);
IN_Dest_VerID NUMBER(8);
v_ErsID NUMBER :=1;
v_CurrID NUMBER(8);
v_unitid tbl_base.unit_id%TYPE;
v_yearid tbl_base.year_id%TYPE;
v_lineid tbl_base.line_id%TYPE;
v_verid tbl_base.ver_id%TYPE;
v_jan tbl_base.jan%TYPE;
v_feb tbl_base.feb%TYPE;
v_mar tbl_base.mar%TYPE;
v_Month Char(5);

CURSOR c_Ver1 IS
SELECT unit_id, year_id, line_id, ver_id, IN_Month
FROM tbl_base
WHERE ver_id =(select mem_id from ver where mem_name = IN_Ver) and year_ID =(select mem_id from year where mem_name = IN_Year);

CURSOR c_Ver2 IS
SELECT unit_id, year_id, line_id, ver_id, IN_Month
FROM finper_base
WHERE ver_id =(select mem_id from ver where mem_name = IN_Ver) and year_ID =(select mem_id from year where mem_name = IN_Year) and ers_id = v_ErsID and
curr_ID = (select mem_id from curr where mem_name='USD');

CURSOR c_Ver3 IS
SELECT unit_id, year_id, line_id, ver_id, IN_Month
FROM finytd_base
WHERE ver_id = (select mem_id from ver where mem_name = IN_Ver) and year_ID =(select mem_id from year where mem_name = IN_Year) and ers_id = v_ErsID and
curr_ID = (select mem_id from curr where mem_name='USD');


--********* Get all the version ids from ver table ***************
BEGIN

Select mem_ID
into IN_Year_ID
from year
where mem_name = IN_Year;

Select mem_ID
into IN_VerID
from ver
where mem_name like IN_Ver;

Select mem_id
into IN_Dest_VerID
from ver
where mem_name like IN_Dest_Ver;

Select mem_id
into v_currID
from curr
where mem_name like 'USD';

if (IN_Month like 'jan')
then v_Month := 'v_jan';
elsif (IN_Month like 'feb')
then v_Month := 'v_feb';
elsif (IN_Month like 'mar')
then v_Month := 'v_mar';
end if;


OPEN c_Ver1;
LOOP
FETCH c_Ver1 INTO v_unitid, v_yearid, v_lineid, v_verid, v_Month;
EXIT WHEN c_Ver1%NOTFOUND;

EXECUTE IMMEDIATE 'Update tbl_base
set IN_Dest_Month = v_Month
where year_id = v_yearid and unit_id = v_unitid and ver_id=IN_Dest_VerID and line_id=v_lineid';

IF SQL%NOTFOUND THEN
EXECUTE IMMEDIATE 'Insert into tbl_base
(year_id,line_id,unit_id,ver_id,PO, IN_Dest_Month )
values(v_yearid,v_lineid,v_unitid, IN_Dest_VerID,null,v_Month )';
END IF;
END LOOP;

--Free resources used by cursor
CLOSE c_Ver1;
CLOSE c_Ver2;
CLOSE c_Ver3;

--Commit update
COMMIT;
end;
end;
/

 

[nme]

I am hoping you have cut this down to make it a little easier to post as you don't open the 2nd and 3rd cursors anywhere but close them ???

You also have some redundant code in there - After you declare the procedure you have:

"AS
Begin
DECLARE"

all you need to put is:

"AS"

then declare your variables, this means you will also need to get rid of one of the "end;" statements at the end of your procedure. CARP already suggested this!!!


Next we'll try to make the code easier to read as you have some real confusing variable names - try putting p_ in front of parameters to your procedure to distinguish them (or a_ if you want to refer to them as arguments)

I’ve put an explanation of the EXCUTE IMMEDIATE with lewisp’s suggestion – what you have done here is wrong (I think).

I'd also advise the use of UPPER() around your criteria. You had it in the first version.

I assume the types match your variable types, if not you are gonna have to do conversions - this may be patronising (if so - sorry) but I can't see your schema and don't know how proficient you are with PL/SQL.

I notice you are inserting null while naming the columns in the insert statement, just don't name the PO column in the insert and it will automatically insert a NULL (unless the table has a trigger/default value for this column that you want to override)

In your IF statement (which I don’t think you need as lewisp’s solution has done this for you in the EXECUTE IMMEDIATE statement) you appear to be just prefixing the check with v_ so just replace that if-elsif statement with:
v_Month := 'v_' || IN_Month;


why are you using like statements everywhere? you aren't using any wildcards so you may as well just use equals.


Give me a bit more insight into what you want it to do and what you think the code is doing and what the code is actually doing and I will try to help you out.

 

[EdRev]

Thanks again nme,

how do I pass this v_Month (local var) and the arguments (i.e IN_Dest_Month) into my sql statements.

 

[nme]

Can we keep the discussion in your first thread, please.