Referencing fields fetched from a cursor

[SashaBuilder3]

Hi everybody,


My cursor takes data from a many-column table and I need to process the fields in similar ways.

Say, I have a table t1 with fields f0,f1,f2,f3,f4. Values of fields f1 and f3 are set, of fields f2,f4 are not. Field f0 is the primary key.


Here is a snippet from the simplified procedure which sets values in even fields (f2 and f4) depending on corresponding values in odd columns.


PROCEDURE My_proc AS
CURSOR crs_t1 IS
SELECT * from t1;

row_t1 crs_t1%ROWTYPE;

some_value NUMBER;

BEGIN

...

FETCH crs_t1 into row_t1;

some_value=some_function(row_t1.f1);
UPDATE t1 set f2=some_value
WHERE f0=row_t1.f0;

some_value=some_function(row_t1.f3);
UPDATE t1 set f4=some_value
WHERE f0=row_t1.f0;

...

END;


As you see I repeated the UPDATE block twice. It's OK in this example but my real table has about a hundred columns and I am wondering whether it's possible to place the block in a loop. So my question is if it's possible somehow to refer to the fields by an index, something like: row_t1.(1) - and not by explicit names. I know that there is such a thing in MS-Access when working with recordsets, but what about Oracle?


Can anyone share an idea how to solve this?


Thanks,

Alexandre

 

[lewisp]

Presuming you want to use a loop for a specific reason, is this the sort of thing you want?

PROCEDURE My_proc AS
CURSOR crs_t1 IS
SELECT * from t1;

BEGIN

FOR c IN crs_t1
LOOP

UPDATE t1
SET f2 = some_function(c.f1),
f4 = some_function(c.f3)
WHERE f0 = c.f0;

END LOOP;

END;

You could also select the ROWID and use that when doing your update which would be quicker than referencing a column.

Of course, the quickest method would be to use a single UPDATE statement:

BEGIN
UPDATE t1
SET f2 = some_function(c.f1),
f4 = some_function(c.f3);
END;

 

[SashaBuilder3]

lewisp,

Thanks for responding. Well, in the construct you use:

UPDATE t1
SET f2 = some_function(c.f1),
f4 = some_function(c.f3)
WHERE f0 = c.f0;

the problem persists. We have to enumerate all fields. Can you imagine how cumbersome the statement will be if a number of fields are more than a hundred?


I was thinking about something like this:

i:=2;
WHILE i<my_max LOOP
UPDATE t1
SET t1.Field(i)=some_function(row_t1.Field(i-1));
i:=i+2;
END LOOP;



Hmmm, I see that I need to refer to the table columns also through an index...


Do I want too much?


Still waiting for ideas...


Alexandre

 

[lewisp]

In that case you need

l_count := 2;

LOOP
EXECUTE IMMEDIATE 'UPDATE t1 SET f' ||
To_Char(l_count) || ' = some_function(f' ||
To_Char(l_count - 1) || ')';
--
l_count := l_count + 2;
EXIT WHEN l_count > my_max;
END LOOP;

 

[bjorneh]

Hi

A classical example of a developer wanting a simple/easy way of coding something. The downside to this, is complex/unreadable/unmaintainable code.

Many lines simple code is better than few lines complex code (In most situations, where you aren't dependant of having very few lines optimized code. But then you should probably look for something different than PL/SQL to execute it anyhow IMHO).

So, I think you would be best off creating a small script that generates your update statement based on a select from USER_TAB_COLUMNS

 

[SashaBuilder3]

Lewisp,


I am trying to put to work what you suggested but something is going wrong. Maybe it's better to tell all the story.


So, here is my sample table t1:

F0 F1 F2 F3 F4 F5 F6
-- -- ------- -- ------- -- --------
1 5 2 3
2 5 1
3 4 0 2
4 1 5 6
5 8 0

Fields F0,F1,F3,F5 are of NUMBER type; F2,F4,F6 - VARCHAR2.



What I need is to fill in fields F2,F4,F6 depending on values in preceding columns. The following function is called to do this:


FUNCTION some_func1(nmbValue IN NUMBER) RETURN VARCHAR2 AS
flag VARCHAR2(20);
BEGIN

IF nmbValue IS NULL OR nmbValue=0 THEN
flag := 'NO';
ELSE
flag := 'YES';
END IF;

RETURN flag;

END some_func1;





And here is the procedure I created based on your advice:


PROCEDURE my_proc AS
i PLS_INTEGER;
nParam PLS_INTEGER :=6;
SQL_str VARCHAR2(1024);

CURSOR crs_t1 IS
SELECT *
FROM T1;

row_t1 crs_t1%ROWTYPE;

BEGIN

OPEN crs_t1;

LOOP
FETCH crs_t1 INTO row_t1;
EXIT WHEN crs_t1%NotFound;

SQL_str:='UPDATE T1 SET ';
i:=1;
--loop to compile F2 and F4
While i < nParam-1 LOOP
SQL_str:=SQL_str || 'F' || TO_CHAR(i+1) || '=some_func1(F' || TO_CHAR(i) || '),' ;

i:=i+2;
END LOOP;

-- add F6
SQL_str:=SQL_str || 'F' || TO_CHAR(nParam) || '=some_func1(F' || TO_CHAR(nParam-1) || ') WHERE t0=' || row_t1.f0;


DBMS_OUTPUT.put_line(SQL_str); --debug
EXECUTE IMMEDIATE SQL_str;

END LOOP;

CLOSE crs_t1;

END;





However when I run the procedure I get an error message:


ERROR at line 1:
ORA-00904: invalid column name
ORA-06512: at &quot;AM.PCG_TST1&quot;, line 50
ORA-06512: at line 2


Line 50 is 'EXECUTE IMMEDIATE SQL_str;'



The constructed SQL_str looks good (at least to me):

UPDATE T1 SET F2=some_func1(F1),F4=some_func1(F3),F6=some_func1(F5) WHERE t0=1



Any idea what maybe wrong?



Thanks,

Alexandre

 

[SashaBuilder3]

Bjorneh,

I am new to Oracle, many things are unclear to me. Could you, please, give me an example of such a script?

I work with Oracle8i Enterprise Edition Release 8.1.7.0.0 , in Win2000 Server environment.


Thanks,

Alexandre

 

[lewisp]

Sashabuilder3, where is column t0 in your table?

 

[SashaBuilder3]

Lewisp,

Sorry, that was a typo. The correct column name ('F0') gives little, I am getting same error message...



Alexandre

 

[lewisp]

Have you tried running the generated SQL in a SqlPlus session?

I assume the procedure is being run by the same user who created it? Stored procedures will normaly run with the privileges of its creator, and you could get problems if you have a local copy of a table that also exists in the user who created the procedure.

 

[SashaBuilder3]

Lewisp,


Your suggestion to run the SQL in a SqlPlus helped. You see, I created these procedures as a part of a package. To run the SQL in SqlPlus forced me to add the package name before some_proc1:

UPDATE T1 SET
F2=PCG_tst1.some_func1(F1),
F4=PCG_tst1.some_func1(F3),
F6=PCG_tst1.some_func1(F5);


Then I did the same addition in the text of the proc genereting the SQL string:


SQL_str:='UPDATE T1 SET ';
i:=1;
While i < nParam-1 LOOP
SQL_str:=SQL_str || 'F' || TO_CHAR(i+1) || '=PCG_tst1.some_func1(F' ||
TO_CHAR(i) || '),' ;
i:=i+2;
END LOOP;

SQL_str:=SQL_str || 'F' || TO_CHAR(nParam) || '=PCG_tst1.some_func1(F' ||
TO_CHAR(nParam-1) || ')' ;



After this the entire procedure ran fine. It was hard to foresee such a thing: a procedure inside its package would require to explicitly name the package. Well, there are still a bundle of things I have to learn...



Thanks a lot!!!

Alexandre