DESPERATE HELP with substitution variables 3

[EdRev]

I have a script that execute the following stored procedure:

Create or Replace Procedure Incxxx.spFCTVersion(
IN_YR IN Varchar2,
IN_Ver IN Varcahr2,
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_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_apr tbl_base.apr%TYPE;
v_may tbl_base.may%TYPE;
v_jun tbl_base.jun%TYPE;
v_jul tbl_base.jul%TYPE;
v_aug tbl_base.aug%TYPE;
v_sep tbl_base.sep%TYPE;
v_oct tbl_base.oct%TYPE;
v_nov tbl_base.nov%TYPE;
v_dec tbl_base.dec%TYPE;

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

Select mem_ID
into IN_Year_ID
from Incxxx.year
where mem_name = IN_Year;

Select mem_ID
into IN_VerID
from Incxxx.ver
where upper(mem_name) = IN_Ver';

Select mem_id
into IN_Dest_VerID
from Incxxx.ver
where upper(mem_name) = IN_Dest_Ver;

--retrieve source Period from source version
CURSOR c_Ver IS
SELECT unit_id, year_id, line_id, ver_id, IN_Month
FROM Incxxx.tbl
WHERE ver_id = IN_VerID;

BEGIN

OPEN c_Ver
LOOP
FETCH c_Ver INTO v_unitid, v_yearid, v_lineid, v_verid, v_|| IN_Month ||
EXIT WHEN c_Ver%NOTFOUND;

Update Incxxx.tbl_base
set || IN_Dest_Month || = v_ || IN_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
Insert into Incxxx.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_|| IN_Month ||);
END IF;
END LOOP;

--Free resources used by cursor
CLOSE c_Ver

--Commit update
COMMIt

Can somebody tell me if I did the substitution variable correctly.

 

[carp]

Not quite.

First,
IN_Ver IN Varcahr2
should be
IN_Ver IN Varchar2

Next, the structure you have is summarized as:

PROCEDURE Incxxx.spFCTVersion(arguments)
BEGIN
DECLARE
-- local variables
-- executable code
END;

The correct structure is
PROCEDURE Incxxx.spFCTVersion(arguments)
-- local variables
BEGIN
-- executable code
END;

Note that the reserved word DECLARE is not used.

Finally, while the COMMIT will not hurt anything, it is not necessary. CREATE is a DDL statement and thus does an implicit commit.

 

[EdRev]

Thanks carp. I will revise the procedure. The syntax I desperately need help with is with the sql statements. I was trying to pass the variables (local or arguments) into my sql statement. Am I doing it correctly?

 

[lewisp]

You need dynamic SQL or ref cursors (I think). Heres a dynamic example, if I'm reading your procedure correctly:

EXECUTE IMMEDIATE 'Update Incxxx.tbl_base
set || ' || IN_Dest_Month || ' = v_' || IN_Month ||
'where year_id = v_yearid and unit_id = v_unitid and ver_id=IN_Dest_VerID and line_id=v_lineid';

 

[nme]

The line about commit from carp is very true but since you aren't creating anything IN your procedure you aren't making a call to CREATE and therefore aren't makeing an implicit commit - leave the commit in!!! The only time you are creating something is when you are actually creating the procedure.

 

[nme]

lewisp is correct but I think you need to take it a litle further:
EXECUTE IMMEDIATE 'Update Incxxx.tbl_base set ' || IN_Dest_Month || ' = v_' || IN_Month || ' where year_id = ' || v_yearid || ' and unit_id = ' || v_unitid || ' and ver_id = ' || IN_Dest_VerID || ' and line_id = ' || v_lineid;

This will only work for numbers - if you variables contain text you need to do it a little differently, e.g.

EXECUTE IMMEDIATE 'Update my_table set my_text_field = ''' || v_variable_containing_text || '''';

If you tell us what types you are working with and what EXACTLY you are trying to do - i.e. are you trying to dynamically insert into a table where you only know which column it is once the procedure is called (at Runtime) - i.e. depending on a value passed in? - we will be able to give more assistance.

 

[EdRev]

nme,

in your example, my_text_field will be a char variable containing the name of a column on my_table. I need to know the syntax to pass (concatenate) this string variables and arguments into my sql statements.

 

[nme]

You're on-line now eh? - lets get down to business:

In the case you are on about above, you need to put:

text in bold is a string:

EXECUTE IMMEDIATE 'Update my_table set ' || my_text_field || ' = ''' || v_variable_containing_text || '''';

for this example above, say my_text_field contained the text "column1" and v_variable_containing_text contained the text "value1". The statement that was executed would be:

Update my_table set column1 = 'value1';

Is this helping?

 

[EdRev]

In your example, my_text_field is an argument passed to the stored procedure and v_variable_containing_text is a local variable.

In my example, it should be like this:

EXECUTE IMMEDIATE 'Update tbl_base set ' || IN_Dest_Month ||' = '" || v_Month where ........'

Is this correct?

 

[nme]

Almost:

EXECUTE IMMEDIATE 'Update tbl_base set ' || IN_Dest_Month ||' = ''' || v_Month'' where ........'


Don't use double quotes it is all single quotes
i.e.
[single quote]'Update tbl_base set [single quote} || IN_Dest_Month || [single quote] = [single quote][single quote][single quote] || v_Month[single quote][single quote] where .......'

the three single quotes are saying close the string but add a single quote into the string, therefore the:

[single quote] = [single quote][single quote][single quote]
gives you a space, then an equals sign, then a space, then a single quote and that's the string finished.


1 single quote (sq from here on in) gives you either start or end a string

2 sq on their own is a 0 lengthed string - if inside a string, its a sq.

3 sq either start a string with the first character as a sq or end a string with the last character as a sq

4 sq on their own is a sq - inside a string its 2 sq.

etc.

To see what it does you are gonna have to play with it - use it sleecting against dual, like
select 'a=''a''' from dual;
It takes a little while but once you get used to it you will understand the EXECUTE IMMEDIATE statments a whole lot better.

 

[EdRev]

I tried it but I got an invalid column name on the update statement.

How can I get the value of IN_Dest_Month that is being passed?

 

[nme]

Where is the invalid column - i.e. I don't think it will be the substituted IN_Dest_Month - I think it will be in your where clause.

Can you send the error message? alternatively - just create another table and insert a row into it, then update this procedure to write to that table and run the update against it without the where clause, just so we can be sure it isn't a problem somewhere else. I'm saying this as I reckon the code I gave you WILL work, its just the .... of the where clause that worries me.

PS - thanks for the star, I don't think I've ever got one before, still I haven't realy participated much yet.

 

[EdRev]

nme,

Thanks a lot for all your help. I hope you bear with me for a little longer, I'm kinda newbie with Oracle.

Is it possible that the invalid column name error is due to my assigning 'set '|| IN_Dest_Month ||' = ' || v_Month.
If you look at my declaration, cursor and fetch statement
.......

v_feb tbl_base.feb%TYPE;
v_mar tbl_base.mar%TYPE;
v_Month Char(5);

v_Month := 'v_' || IN_Month;



CURSOR c_Ver1 IS
SELECT unit_id, year_id, line_id, ver_id, IN_Month //IN_Month is an argument passed to this proc and also the name of a column from tbl_base. Is this correct?
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);

OPEN c_Ver1;
LOOP
FETCH c_Ver1 INTO v_unitid, v_yearid, v_lineid, v_verid, v_Month; //v_Month is a local var, I need to pass the string assigned to this var (i.e v_feb) to this fetch statement and the datatype of this column name is tbl_base.feb%TYPE
EXIT WHEN c_Ver1%NOTFOUND;

EXECUTE IMMEDIATE 'Update tbl_base
set '||IN_Dest_Month ||' = ' v_Month //Again, I need to pass the string assigned to this var (i.e v_feb) to this set statement.
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 )'; //Once again, I need to pass the string assigned to this var (i.e v_feb) to this insert statement.
END IF;
END LOOP;

Do you mind showing me the correct syntax for passing these variables to each of the statements.

 

[nme]

You appear to have reverted back to code that won't work, but you were right, the code I gave you wouldn't work as I messed it up a bit at the end (sorry - I'm only human)

You need to put this:

EXECUTE IMMEDIATE 'Update tbl_base set ' || IN_Dest_Month || ' = ''' || v_Month || ' where ........'

where blue signifies it is a string and red will be converted to the value of the variable. You have to be careful with your where clause though as I see you are also trying to use variables in there to filter the update.

if you send me the type of each of the columns you are trying to update/insert into - I will just write the EXECUTE IMMEDIATE statement for you.


as for using parameters with the same name as a column in a table - DON'T - prefix it with p_ to show its a parameter!!!

HOWEVER - I think what you are trying to do is take a variable, v_Month - assign it a value, v_jan and then use the variable's value as a variable name to get the value from - double substitution. All the posts I have put so far won't do this as its a little complex.

Do your v_jan, v_feb, v_mar column have differring types?

You are really going to have to describe the table you are trying to manipulate!!!

 

[EdRev]

As far as I know, v_jan - v_dec has the same data type as jan - dec from the tbl_base and so are all the other columns.

The IN_Dest_Month that I was passing to the set statement will have the string value (i.e jan) which correspond to a column name of the tbl_base and the v_Month will have a string value (i.e v_jan) that I'm passing to the fetch and set statement (sorry if I'm repeating myself).

I don't know if these are enough info, if not, I might have to get back to you on these on Monday as I am working from home right now and I don't have access to the db.

AS ALWAYS, THANKS FOR ALL YOUR HELP.

 

[nme]

Right, as long as the data type is the same across all of the columns (tbl_base.jan, tbl_base.feb ... tbl_bae.dec) - get rif of the concept of having one variable per column and just use one, therefore your code:


v_jan tbl_base.jan%TYPE;
v_feb tbl_base.feb%TYPE;
v_mar tbl_base.mar%TYPE;
v_Month Char(5);

v_Month := 'v_' || IN_Month;



will become (note that I have used p_IN_Month to denote the parameter - it just makes reading the code easier):


v_value tbl_base.jan%TYPE;
v_Month Char(5);

v_Month := 'v_' || p_IN_Month;



then the rest of your code should be changed as follows (/* is a marker to start a multi-line comment and */ ends the multi-line comment, just in case you are wondering...):



/* you don't need to use concatenation (||) here as PL/SQL will use the variables values. */

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);

OPEN c_Ver1;
LOOP
FETCH c_Ver1
INTO v_unitid, v_yearid, v_lineid, v_verid, v_value;

EXIT WHEN c_Ver1%NOTFOUND;

/* Here, unlike the cursor, you need to use concatenation as EXECUTE IMMEDIATE only acepts a string.
If you pass a variable name into this string, EXECUTE IMMEDIATE will assume that is the value
and not a refernece to a value, so you must build the string up with concatenation and let PL/SQL
convert the variable's into vaules for you - I am not explaining this very well - look at the
documentation and examples from Oracle!*/

EXECUTE IMMEDIATE 'UPDATE tbl_base SET ' ||
p_IN_Month || ' = ' || v_value ||
' WHERE year_id = ' || v_yearid ||
' AND unit_id = ' || v_unitid ||
' AND ver_id = ' || IN_Dest_VerID ||
' AND line_id = ' || v_lineid;

/* I have assumed that the values you are retrieving from the cursor C_Ver1
are all numeric as you have not given us any indiction as to what type
they are - tbl_base.jan%TYPE means absolutley nothing unless we see the
table's description. */

IF SQL%NOTFOUND THEN
EXECUTE IMMEDIATE 'INSERT INTO tbl_base (year_id,line_id,unit_id,ver_id,PO, ' || p_IN_Month || ')
VALUES (' || v_yearid || ', ' || v_lineid || ', ' || v_unitid || ', ' || IN_Dest_VerID ||
', null, ' || v_Month || ')';

END IF;
/* you should have noticed that I have replaced the IN_Dest_Month with p_IN_Month
as I could not see what you were doing with IN_Dest_Month and p_IN_Month appears
to be the logical choice for the column name - feel free to change this back to
IN_Dest_Month if you think I am wrong. */
END LOOP;

 

[EdRev]

nme, that was a very extensive tutorial. I will revise my code and have it ready to compile on Monday as soon as I get in the office. Can't wait.

I will keep you posted.

Thanks again for all your help!!!

 

[EdRev]

Hi nme, hope you're there.

I checked the data types of the table and they're all numeric.

YEAR_ID NOT NULL NUMBER(8)
LINE_ID NOT NULL NUMBER(8)
UNIT_ID NOT NULL NUMBER(8)
VER_ID NOT NULL NUMBER(8)
P0 NUMBER(28,9)
JAN NUMBER(28,9)
FEB NUMBER(28,9)
........

When I run proc I got this error message on the fetch statement.

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SPMIDGETVERSION", line 60
ORA-06512: at line 1

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 finloc_base.unit_id%TYPE;
v_yearid finloc_base.year_id%TYPE;
v_lineid finloc_base.line_id%TYPE;
v_verid finloc_base.ver_id%TYPE;
v_month finloc_base.jan%TYPE;

CURSOR c_Ver1 IS
SELECT unit_id, year_id, line_id, ver_id, IN_Month
FROM finloc_base
WHERE ver_id =(select mem_id from ver where mem_name = IN_Ver) and year_id =(select mem_id from yr where mem_name = IN_year);

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

 

[nme]

You said previously:

IN_Month is an argument passed to this proc and also the name of a column from tbl_base. Is this correct?


if IN_Month is a field in the table - is it a character field? If its a number field then maybe the parameters is getting passed rather than the field value (but I think the parser will get the field before looking for a variable/parameter with that name). To make sure its not getting IN_Month from your parameter rather than teh table, you could rename your parameter so that it doesn't conflict with and database field name.

Looking at the code and if the error is where you said it was - this is the only possible field in the cursor that could be trying to convert from character to number.

I'll try to check the postings tomorrow - but I am away for the rest of the week at a customer site - good luck.

 

[EdRev]

I believe I found out where the problem is:

CURSOR c_Ver1 IS
SELECT unit_id, year_id, line_id, ver_id, p_month
FROM tbl_base
WHERE ver_id =(select mem_id from ver where mem_name = p_Ver) and year_id =(select mem_id from yr where mem_name = p_Year);

where
p_month, p_ver and p_year are parameters passed into the stroed procedure.

On the cursor select statement, I did not have any problem when I hard-coded the name of the column (i.e jan) instead of p_month .