-
Hi all,
my comeback to this forums(s) starts with a little problem I have regarding dealing with cursors in PRO/C.
Here it is:
I have to make a SELECT which returns a number of rows (more than 1 actually, and not much larger then 1 either -- around 10-20 each time, but this not the issue). The fields extracted are of (VAR)CHAR, NUMBER and FLOAT type (this either, I don't think it matters).
For some reason I have to deal with those values returned by the SELECT into some "host" variables in PRO/C (these host variables should match the type of the fields extracted, thus they should be of the following tyes correspondly : char, long, double).
From what I've read so far in Oracle's documents (those PDF named with numbers like A89861.PDF, but which are the "PRO C/C++ Precompiler Programmer's Guide" a.s.o.) I come to a conclusion that these "host" variables should be vectors (ex. : VV_NUMBER long[50]; VV_FLOAT double[50]; VV_CHAR char[50][100] a.s.o.).
OK now, my problem is this:
given the fact that the only way I should select my values are by means of a cursor (is it really so ???), how can I fill my "host vectors" with each value returned at each FETCH inside my PL/SQL block ?
Here is some ex. with the code I'm trying and which gives my a compile error:
EXEC SQL BEGIN DECLARE SECTION;
long v_userid;
long v_agentid;
long v_nrcmd;
long v_nrzile;
long v_valuta; long vv_valuta[100];
long v_cursval;
long v_cantitate; long vv_cantitate[100];
long v_reducere; long vv_reducere[100];
long v_codtermen; long vv_codtermen[100];
double v_pretunit; double vv_pretunit[100];
double v_pretunitval; double vv_pretunitval[100];
VARCHAR V_DENMAT[200]; VARCHAR VV_DENMAT[100][200];
VARCHAR V_UMORA[200]; VARCHAR VV_UMORA[100][200];
VARCHAR V_CODPOZ[200]; VARCHAR VV_CODPOZ[100][200];
long IDCOM;
int nrrec;
double temp_cant; double vv_temp_cant[100];
double temp_pretu; double vv_temp_pretu[100];
double temp_reducere; double vv_temp_reducere[100];
int i, j;
[......... I hope I'm not cutting anything useful here, for this example]
EXEC SQL END DECLARE SECTION;
[......]
i = 0;
j = 0;
EXEC SQL EXECUTE
DECLARE CURSOR curs1 IS
select (cmd.pretu / (1 - NVL(disc.procent,0) / 100)),
(cmd.pretu / (1 - NVL(disc.procent,0) / 100) * :v_cursval),
(NVL(cmd.reducere,0) + NVL(disc.procent,0)),
nm_prod.den_mat, nm_prod.um,
cmd.valuta, cmd.cantitate, cmd.pretu, NVL(cmd.reducere,0),
cmd.cod_termen, cmd.codp
from icomm.cmd cmd, icomm.usercmd uc, icomm.users users, icomm.sl_clienti sl, icomm.sl_disc_tip disc,
icomm.nm_prod nm_prod
where cmd.cmd_id = uc.id
and uc.user_id = users.id
and sl.tert_id = users.tert_id
and disc.cod_disc_cl = sl.cod_disc_cl
and nm_prod.cod_mat = cmd.codp
and cmd.cmd_id = :IDCOM;
contor number;
BEGIN
contor := 0;
OPEN curs1;
LOOP
FETCH curs1 INTO :v_pretunitval, :v_pretunit, :v_reducere, :V_DENMAT, :V_UMORA, :v_valuta, :temp_cant, :temp_pretu, :temp_reducere, :v_codtermen, :V_CODPOZ;
EXIT WHEN curs1%NOTFOUND;
/* :vv_pretunit(:i) := :v_pretunit;
:vv_reducere(:i) := :v_reducere;
:vv_temp_cant(:i) := :temp_cant;
:vv_temp_pretu(:i) := :temp_pretu;
:vv_temp_reducere(:i) := :temp_reducere;
:VV_DENMAT(:i) := :V_DENMAT;
:VV_UMORA(:i) := :V_UMORA;
:VV_CODPOZ(:i) := :V_CODPOZ;
*/
INSERT INTO AAA VALUES(:v_pretunitval, :v_pretunit, :v_reducere, :V_DENMAT, :V_UMORA, :v_valuta, :temp_cant, :temp_pretu, :temp_reducere, :v_codtermen, :V_CODPOZ, 'comment, comment here!', contor);
:i := :i + 1;
contor := :i;
END LOOP;
CLOSE curs1;
:nrrec := contor;
END;
END-EXEC;
Here, v_cursval is already filled with a long value somewhere before, and IDCOM likewise (in fact this last is passed as a parameter to my PRO/C function !).
Actually, that exact piece of code doesn't give an compile error, but if I uncomment the part which is surrounded by comments, it's OK from the compiller point of view, but all those vectors contains rubbish (I can't tell why !!!), and if I try something like
:vv_pretunit[:i] := :v_pretunit; (which, BTW, looks more inteligible to me !!) the compiller gives me the error PLS-S-00103 which says something about wrong symbol "[" beeing present there.
That's it !
Anyone have o clue (no matter what) ?
Thanx anyway,
Cornel.
P.S. With those comments beeing there, the last row of data gets filled OK (as it should be !) and those values are exactly what I want (I've written them to a log file); more : that table AAA contains correct values form the FETCHed line !
Unfortunattely, I can't put them together in those vectors, so that I can manipulate them later in some way (namely, gather them together in a string) !
-
Eventually, I've managed to come to a solution to this problem ;-)
I rewrote the PL/SQL block which contains the cursor, into the following form:
EXEC SQL DECLARE curs1 CURSOR FOR
select [.....] (the same stuff here !!!)
EXEC SQL OPEN curs1;
nrrec = 0;
i = 0;
for (;;)
{
EXEC SQL FETCH curs1 INTO :vv_pretunitval[i], :vv_pretunit[i], :vv_reducere[i] [.... and similar stuff further] ;
if (sqlca.sqlerrd[2] - nrrec <= 0)
break;
else
{
nrrec = sqlca.sqlerrd[2];
i++;
}
}
EXEC SQL CLOSE curs1;
As you can all see, the clue was to fetch those fields into the vectors directly (:vv_pretunit[i] and the like).
The PRO*C manual from Oracle sugests some other form of writing the FETCH command (namely, without the [i] part for each vector), but it didn't work for me, and this one does ....
That's all.
Cornel.
-
Oops !!
I didn't made a preview to my message, and that's why it looks so stupid !
That "for" should be read as follows:
for ( ; ; )
[.....] (it was not my intention to place a emoticon there !!!)
Sorry,
Cornel.
