Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2012
    Posts
    4

    Unanswered: Get column name as a variable

    Hi,
    I am programming a procedure to group order proposal lines into orders, based in different variable parameters (columns names of proposal line table), but I get the error PLS-00302: the component 'V_PARAMETROSCENTRO' must be declared, when I try to do the red sentence.
    Is it possible to execute this sentence? Anyone have any other idea to do this?
    I show you a piece of my code and I hope your answers.
    Thank you in advance.


    ....
    TYPE t_listaParametros IS TABLE OF VARCHAR2(255); -- Table of parameters Type
    v_parametrosCentro t_listaParametros;

    TYPE t_linprop IS TABLE OF LINEAPROPUESTAPEDIDO%ROWTYPE INDEX BY BINARY_INTEGER; -- Table of order proposal lines
    v_linprop t_linprop;
    ...
    ------------------


    -- If exist proposal lines
    IF v_linprop.COUNT >0 THEN

    -- I take the first order proposal line and I create the first order

    v_lineaComparar:= v_linprop(1); -- line to compare
    v_idPedido:= logas_pa_cabecera_generar (v_lineaComparar);
    logas_pa_lineapedido_generar (v_lineaComparar,v_idPedido);


    FOR i in v_linprop.FIRST+1..v_linprop.LAST LOOP
    j:=v_parametrosCentro.FIRST;
    v_param:=v_parametrosCentro(j);
    -- For earch order proposal line I compare the parameter with the line to compare parameters,
    WHILE v_linprop(i).v_parametrosCentro(j) = v_lineaComparar.v_parametrosCentro(j) AND j<v_parametrosCentro.LAST LOOP
    j:=v_parametrosCentro.NEXT(j);
    v_param:=v_parametrosCentro(j);
    END LOOP;

    IF j>= v_parametrosCentro.LAST THEN --If parameter values are the same--> the proposal line is added to the order
    lineapedido_generar(v_linprop(i),v_idPedido);
    .....

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I believe Oracle's error message accurately reports reality.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2012
    Posts
    4
    Yes anacedent, but how can I solve the problem?
    For example if I write
    WHILE v_linprop(i).IDPROGRAMA = v_lineaComparar.IDPROGRAMA AND j<v_parametrosCentro.LAST LOOP
    ....
    it works, but I don't know the name of the colunm at compilation time.
    Do you have any idea to solve it? Thank you in advanced

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >it works, but I don't know the name of the colunm at compilation time.
    The mot common solution is to (ab)use EXECUTE IMMEDIATE by forming "Dynamic SQL".
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jul 2012
    Posts
    4
    Thank you anacedent, but I tried too. But I get this error
    PLS-00457:expressions have to be of SQL types

    I make this:

    FOR i in v_linprop.FIRST+1..v_linprop.LAST LOOP
    j:=v_parametrosCentro.FIRST;
    EXECUTE IMMEDIATE 'BEGIN' || ' :x := v_linprop(i).' || v_parametrosCentro(j) || ';' || ' :y := v_lineaComparar.' || v_parametrosCentro(j) || ';' ||'END;' USING OUT v_param, v_lineaComparar;
    DBMS_OUTPUT.PUT_LINE (" Comparar " || v_param || " con " || v_lineaComparar);


    WHILE v_param = v_lineaComparar AND j<v_parametrosCentro.LAST LOOP
    j:=v_parametrosCentro.NEXT(j);
    v_param:=v_parametrosCentro(j);
    DBMS_OUTPUT.PUT_LINE ('Parametro ' || j ||' a comparar dentro while : ' || v_param);
    END LOOP;

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    from my perspective a table consist of 0 to N rows.
    A row consists of 1 or more columns & the columns are uniquely name.
    I have NO idea to what you refer to as column names.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jul 2012
    Posts
    4
    I explain the problem:
    I have a table of proposal orders lines, wih columns like delivery date, supplier...
    I have another table with parameter to group the lines, the parameters are the name of proposal order lines colmns.then if the parameters choosen are delivery date and suplier I have to group the lines with the sames values in this parameters (columns) in the same order. Did you understand? Did you have any idea?
    Thank you for your attention

Posting Permissions

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