Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    19

    Question Unanswered: urgent help needed please.... my first request

    hi all!
    i am new to Oracle development.
    i am using 6i and 10g to create user interface (forms/reports).
    i want to sort data in a non-base table multi records block after user completed entry (not saved yet).
    for example:
    if i have deptno, empno, ename and i want to sort data on deptno and ename after completion of data entry by user to verify.
    can i do it in form??? how??
    please if possible give an example of code as i am biggner.
    thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Since you are a beginner, it would be best to try to avoid having to do this - it is not something normally done in a Forms application!

    One way would be to POST (but not COMMIT) the data and then re-query the block. The data will then be re-ordered according to the block's ORDER BY clause.

  3. #3
    Join Date
    Nov 2004
    Posts
    19
    thanks for the valueable time.
    its my assignment at WORK and i have to give them a solution so please help me to survive. (also i will learn few new things about it).

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    So try my suggestion. Write trigger code like this (e.g. in a WHEN-BUTTON-PRESSED trigger):

    POST;
    GO_BLOCK('emp');
    EXECUTE_QUERY;

  5. #5
    Join Date
    Nov 2004
    Posts
    19
    thanks again andrewst, you are really helpful.

    someone give me the code below to use PL/SQL Collection.

    Step #1
    -------
    go to SQL*PLUS environment and issue command as:

    CREATE OR REPLACE TYPE my_varchar2 AS TABLE OF VARCHAR2(100);

    Step #2
    -------
    in form's program unit create a procedure as:

    PROCEDURE fill_values IS
    /*create a variable of type my_varchar2*/
    enames my_varchar2 := my_varchar2(null);
    total_records number;
    ndx number:=1;
    begin
    go_block('emp');
    last_record;
    total_records := :system.cursor_record;
    first_record;
    for i in 1..total_records
    Loop
    enames.extend; -- this will extend the array to store value
    enames(ndx) := :emp.ename;
    if to_number(:system.cursor_record) = total_records then
    exit;
    else
    ndx:=ndx+1;
    next_record;
    end if;
    End Loop;
    go_block('TEMP');
    ndx:=1;
    /* the select statement will select from pl/sql table enames
    we can use order by clause to sort the data but be sure
    that we can use only one column for this purpose
    */
    for r in (select column_value val from table(enames) order by 1)
    Loop
    if r.enames is not null then
    :TEMP.m_empname := r.enames; -- store data in TEMP block's m_empname
    ndx:=ndx+1;
    end if;
    if to_number(:system.cursor_record) = total_records then
    exit;
    else
    next_record;
    end if;
    End Loop;
    END;

    when i am using it in SQL*PLUS environment (with little modification) it works fine but in Form its generating error on the line contains: enames my_varchar2 := my_varchar2(null); i don't understand whats wrong with this. if you think there is any mistake so please modify it but if there any limitation of Form then what we can do???

Posting Permissions

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