Results 1 to 7 of 7

Thread: Tabular Fields

  1. #1
    Join Date
    Jan 2004
    Posts
    15

    Unanswered: Tabular Fields

    Hi,

    My form has a field (Display Item), called ORG_FIELD with 8 records in a tabular appearence.

    I also have a button in that form called BUTTON.
    I've created a stored procedure with a select statment :

    PROCEDURE areas_organismo IS
    BEGIN

    select name
    into :ORG.ORG_FIELD
    from org_table;

    end;

    this query is ok and returns many rows. The problem is that i want to fetch this rows into

    the ORG_FIELD when the button is pressed. I get this error:

    FRM-10831: Truncation ocurred: value too long for the field ORG_FIELD

    I've already set the ORG_FIELD with maximum lenght of 500... so the problem is not from

    that. I suppose that all the results are fetched in one single record.... the first one of

    the 8 records of the ORG_FIELD.

    I've tried to use a cursor:

    PROCEDURE areas_organismo(orgid number) IS
    BEGIN
    DECLARE

    cursor cur is
    select name
    from org_table;

    Rec Cur%rowtype;

    begin

    OPEN Cur;
    FETCH Cur into Rec;
    LOOP
    IF Cur%NOTFOUND THEN
    exit;
    END IF;

    :ORG.ORG_FIELD := Rec.name;

    FETCH Cur INTO Rec;
    END LOOP;
    CLOSE Cur;

    end;


    The results are better ,I've got no errors, but just one record from all the eight are fetched. I've got at least 10 name in the org_table.
    The real problem is how to fetch many rows from a query into a tabular field.

    Thanks and Regards.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Code:
    create or replace procedure test IS
    Begin
    Declare
      CURSOR c is select value from table;
      res c%ROWTYPE;
      str varchar2(100);
    Begin
      Open c;
      loop
        fetch c INTO res;   
        exit when c%NOTFOUND;
        str := str||res.value||', ';
      end loop;
      str := SUBSTR(str, 1, Length(str)-2);
      dbms_output.put_line(str||'.');
    close c;
    End;
    End;
    /
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Return ref cursor from the procedure.
    The frontend can receive it as a resultset/recordset.
    Oracle can do wonders !

  4. #4
    Join Date
    Jan 2004
    Posts
    15
    Originally posted by r123456
    Code:
    create or replace procedure test IS
    Begin
    Declare
      CURSOR c is select value from table;
      res c%ROWTYPE;
      str varchar2(100);
    Begin
      Open c;
      loop
        fetch c INTO res;   
        exit when c%NOTFOUND;
        str := str||res.value||', ';
      end loop;
      str := SUBSTR(str, 1, Length(str)-2);
      dbms_output.put_line(str||'.');
    close c;
    End;
    End;
    /

    Thank you.
    But i still with the same problem, instead of 'dbms_output.put_line(str||'.');' i need to put that values in my tabular field named :ORG.ORG_FIELD. if i put

    :ORG.ORG_FIELD := str||'.' ;

    I get the string str just in the first field, something like this name1,name2,name3,.....

    I need to distribute name1 name2 name3 on each record inside the tabular field.

    Maybe I can't do that by this way...calling a stored procedure (from a button on another block) to fullfil my record in the tabular field ORG_FIELD.

    Is there anyother alternative? I really need to call the procedure ...because the query is not so simple and consults half of the database.

  5. #5
    Join Date
    Jan 2004
    Posts
    15
    Originally posted by cmasharma
    Return ref cursor from the procedure.
    The frontend can receive it as a resultset/recordset.
    I did like this. This is the package spec and the body:

    /*******************************************/
    PACKAGE get_org IS
    TYPE resultado is REF CURSOR;

    function organism
    (num_org in SGFTORGN00.NRINORG%type)
    return resultado;

    END;

    PACKAGE BODY GET_ORG IS

    function organismos
    (num_org in SGFTORGN00.NRINORG%type)
    return resultado is

    res resultado;

    begin

    open res for
    select DSORGAN
    from SGFTORGN00;

    end organismos;

    END;
    /*******************************************/

    And from the trigger WHEN-BUTTON-PRESSED from my button I have this code:

    declare
    res GET_ORG.resultado;

    begin
    res := GET_ORG.organismos(GFTORGN00.NRINORG);
    :ORG.ORG_FIELD := res;
    end;


    And the compiler says that 'expression is of wrong type' (for the ':ORG.ORG_FIELD := res' line) saying that the REF CURSOR can't be assign to the ORG.ORG_FIELD (a tabular display item)

    Can you help me?

  6. #6
    Join Date
    Jan 2004
    Posts
    15
    Originally posted by cmasharma
    Return ref cursor from the procedure.
    The frontend can receive it as a resultset/recordset.
    The function organismos return res off course

  7. #7
    Join Date
    Jan 2004
    Posts
    15
    I've changed the type of my item field (ORG.ORG_FIELD) to Object REF. And now for the same assign :ORG.ORG_FIELD := res; the compiler says that is 'bad bind variable ORG.ORG_FIELD'

    Why?

Posting Permissions

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