13 Replies Latest reply: Sep 6, 2013 11:24 AM by Andreas Weiden RSS

    cursor problem ?

    salute-Salem

      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

          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 ?
            salute-Salem

            >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

              >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 ?
                salute-Salem

                > 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

                  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

                    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 ?
                      salute-Salem

                      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 ?
                        salute-Salem

                        > 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

                          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

                            Hi,

                             

                            Greetings

                             

                            you can set

                             

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

                             

                            Thanks and Regards,

                            sekhar

                            • 11. Re: cursor problem ?
                              salute-Salem

                              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

                                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 ?
                                  salute-Salem

                                  Thanks to all of you , i got it