DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: PRO/C -- cursor problem

  1. #1
    Join Date
    Feb 2001
    Posts
    6
    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) !

  2. #2
    Join Date
    Feb 2001
    Posts
    6
    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.

  3. #3
    Join Date
    Feb 2001
    Posts
    6
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width