This discussion is archived
13 Replies Latest reply: Sep 6, 2013 9:24 AM by Andreas Weiden RSS

cursor problem ?

newbi_egy Explorer
Currently Being Moderated

hi all ,

db and dev 10g rel2

trying to use this code in my form in a when-button-pressed trigger  :

 

{code}

declare

    cursor emp is

    select e.ename , e.job , d.deptno from emp e , dept d

    where e.deptno = d.deptno ;

begin

    open emp ;

    loop

        fetch emp into :ename , :job , :deptno ;

        down ;

        exit when emp%notfound ;

    end loop ;

end ;

{code}

but i've got a 14 employee with their jobs , and just 1 deptno which is "10" ?

 

why does this happen ?

thanks a lot

  • 1. Re: cursor problem ?
    Andreas Weiden Guru
    Currently Being Moderated

    There is no restriction to deptno=10 in your query. Also, why do you populate your block using a cursor, instead of building a database block?

  • 2. Re: cursor problem ?
    newbi_egy Explorer
    Currently Being Moderated

    >here is no restriction to deptno=10 in your query

    i know this , that's why iam asking .

     

    >why do you populate your block using a cursor, instead of building a database block?

    i 've two database blocks because the data are retrieved from two tables , if you notice ?

  • 3. Re: cursor problem ?
    Andreas Weiden Guru
    Currently Being Moderated

    >here is no restriction to deptno=10 in your query

    i know this , that's why iam asking .

     

    I understood that you get 14 employees but you expected onyl one. please clarify, if i misunderstood your question.

     

    >why do you populate your block using a cursor, instead of building a database block?

    i 've two database blocks because the data are retrieved from two tables , if you notice ?

    Sorry, i didn't notice, because you didn't mention two datablocks. In your code is just the emp-block and from your code you could simply populate it by using a database-block and a where-clause.

  • 4. Re: cursor problem ?
    newbi_egy Explorer
    Currently Being Moderated

    > In your code is just the emp-block and from your code you could simply populate it by using a database-block and a where-clause.

    How to populate it using a database-block , i already have two database-blocks , and a where -clause

     

    emp.deptno=dept.deptno

     

    what do you mean ,

    if you can modify my code please ?

     

     

    thanks

  • 5. Re: cursor problem ?
    CraigB Guru
    Currently Being Moderated

    newbi_egy wrote:

     

    cursor emp is 

        select e.ename , e.job , d.deptno from emp e , dept d

        where e.deptno = d.deptno ;

    But you are still only displaying data that is fetched from the EMP table because EMP has the DEPTNO column so there is no point in joining with the DEPT table.  I see no reason why you couldn't base your Forms block on the EMP table and let Forms handle all DML.  Even if you were displaying the department name (which is not in the EMP table) you could still base your Forms block on the EMP table and use a Post-Query trigger to get the Department Name from the DEPT table.

     

    Craig...

  • 6. Re: cursor problem ?
    Andreas Weiden Guru
    Currently Being Moderated

    I can't see that you have two datablocks as you don't use the datablock in your assignments, only the itemnames (which, btw., is bad practice).

  • 7. Re: cursor problem ?
    newbi_egy Explorer
    Currently Being Moderated

    To clarify my code , i replaced the deptno column with dname column , and i added blocks' names .

     

    {code}

    declare

        cursor emp is

        select e.ename , e.job , d.dname from emp e , dept d

        where e.deptno = d.deptno ;

    begin

        open emp ;

        loop

            fetch emp into :block1.ename , :block1.job , :block2.dname ;

            down ;

            exit when emp%notfound ;

        end loop ;

    end ;

    {code}

  • 8. Re: cursor problem ?
    newbi_egy Explorer
    Currently Being Moderated

    > Even if you were displaying the department name (which is not in the EMP table) you could still base your Forms block on the EMP table and use a Post-Query trigger to get the Department Name from the DEPT table.

     

    do you mean that i create one (emp) block , without the dept block ?

     

    what to write in the post-query trigger ?

    what is the field of the emp block to hold the dname retrieved from the dept table ?

  • 9. Re: cursor problem ?
    CraigB Guru
    Currently Being Moderated

    Yes, that is exactly what I mean.  You don't need the DEPT table because you aren't using it (DEPT table) to filter your EMP table (eg; only show employees where DEPT_NO = 10).  You are just doing a straight join where EMP.DEPT_NO = DEPT.DEPT_NO.

    newbi_egy wrote:

     

    do you mean that i create one (emp) block , without the dept block ?

     

    Yes, I recommend you use the Forms Data Block Wizard (DBW) to create your block and select the EMP table from the Wizard.  Upon completion of the DBW you will have what is referred to as a "Based Table Block" which Oracle Forms will handle all of the DML to SELECT, UPDATE, DELETE, and INSERT records into the EMP table; you don't have write any code at all to accomplish this because Forms handles it for you.

     

    With respects to my comment about displaying the Department Name, this was just simply an example of why you might need the include the DEPT table in your form.  However, if you want to pursue this - then...

    newbi_egy wrote:

     

    what to write in the post-query trigger ?

     

    Your Post-Query trigger would contain the SQL statement to fetch the Department Name (DNAME) from the DEPT table where the EMP.DEPT_NO = DEPT.DEPT_NO.  For example:

     

     

    BEGIN
      SELECT dname
        INTO :EMP.DNAME  /* This is a Non-DB item you manually add to the EMP Block */
        FROM dept
       WHERE dept_no = :EMP.dept_no /* Reference to Datablock item */
    END;

     

    Notice, that my select statement uses fully qualified references to the Data Block items.  As Andreas mentioned - it is very bad practice to reference just the :ITEM and not the :BLOCK.ITEM.

    newbi_egy wrote:

     

    what is the field of the emp block to hold the dname retrieved from the dept table ?

    After you create your Base Table block using the DBW, manually add a new item and name it; DNAME.  Then scroll to the Database section of the properties of this item and change the Database Item property from Yes to NO.

     

    Clearly,  you  are new to Oracle Forms.  There are a few really good Oracle Forms tutorials available on the web.  I recommend you take a look at iSelfSchooling - Basic Introduction to Oracle Forms series.  This is really good tutorial that applies to all versions of Oracle Forms.

     

    Craig...

  • 10. Re: cursor problem ?
    sekhar byna-SpineCompuech Newbie
    Currently Being Moderated

    Hi,

     

    Greetings

     

    you can set

     

    set_block_property('BLOCK_NAME',default_where';<HERE PASS YOUR STRING');

     

    Thanks and Regards,

    sekhar

  • 11. Re: cursor problem ?
    newbi_egy Explorer
    Currently Being Moderated

    thanks to you all , but i got nothing from your post , unfortunately , and i do not know why you can not get me , it is a simple question .

     

    why is the join not working ? ???

     

    suppose i have a block of three items , database block or not a database block , the items are "ename , job , dname" ,

    and i want to write a join to retrieve the three columns of the two tables in these items ?

    how to write the join , what is wrong with mine ?

     

     

    please briefly , then we can talk about anything else after i get my answer .

    thanks a lot

  • 12. Re: cursor problem ?
    Andreas Weiden Guru
    Currently Being Moderated

    There is nothing wrong with your join, but it seems you didn't get the forms-concepts correctly.

     

    In you query you query data from two tables but you try to write the data into two different blocks. Your DOWN in the code goes to the next record in just one of the blocks. And how do you want to synchronize user navigation through the records? You can't synchronize two blocks that smoothly.

     

    In your example you want to show employee data with some lookup-data from the delpt-table, so your block should be baed on the employee-table, and then you can read the lookup-data for each record into additional (non-database) items in your employee-block.

     

    And the way forms works is that you create your block as a database-block (best using the wizards), then to populate the block use a simple EXECUTE_QUERY (and no cursor-loop), and then in the POST-QUERY-trigger on the block read the lookup-data for the current record. All that has already been described by Craig.

     

    If you have a specific question, please ask.

     

     

    Andreas

  • 13. Re: cursor problem ?
    newbi_egy Explorer
    Currently Being Moderated

    Thanks to all of you , i got it

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points