13 Replies Latest reply: Aug 19, 2014 1:44 PM by Zlatko Sirotic RSS

    How to assign values to the item where currently the cursor is??

    2685465

      Hello All,

       

      I have one 'tabular' data block ,B_Emp (from the table Employee) and one non-database & non-tabular block B_Test consisting of some text_items.

      'Emp_id' is one of the column of the Employee table and I want to assign all the values of the Emp_id  in all the text_items of the B_Test sequentially,when one particular button is pressed.

       

       

      Here's what I have written in the trigger

       

      Declare

       

        i number;

        l_last_record number;

        next varchar2(40); ---- to hold the values of  system cursor of the next item in the B_Test block

        next_r varchar2(40); --- to hold the values of system cursor of the the next record in the B_Emp block

       

      Begin

       

      go_block('B_Emp');

      Set_Block_Property('B_EMPLOYEES_ALL', DEFAULT_WHERE, 'Emp_id is not null');

        execute_query(no_validate);

        last_record;

        l_last_record := :system.cursor_record;

        first_record;

        next_r := :SYSTEM.CURSOR_ITEM;

        go_block('B_Test');

        next := :SYSTEM.CURSOR_ITEM;

       

        for i in 1..l_last_record

        Loop

             go_item(next);

            :next := :B_Emp.Emp_id; ---- here is the problem

             next_item;

             next := :SYSTEM.CURSOR_ITEM;

             go_item(next_r);

             next_record;

             next_r:= :SYSTEM.CURSOR_ITEM;

         EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';

        End Loop;

      End;

       

      Now this gives me the expected error of Bad Bind Variable as there is no data block with the name 'next'.

      However I have checked the value of  'next' and 'next_r' (message('next value= '||next) )and I am getting the expected values.

       

      So if somehow I am able to insert my value in the current cursor item(because in the beginning of the loop my system cursor is exactly at the text item where i need to insert the value) then i don't have to use this " :next := :B_Emp.Emp_id;"

                 OR

      If somehow I am able to refer the items in my B_test block dynamically then i can simply use

       

      Loop

      :B_test.''dynamic reference for the item" := :B_Emp.Emp_id;

      next_record;

      EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';



      Please help me out of this!!

        • 1. Re: How to assign values to the item where currently the cursor is??
          CraigB

          Well, the first thing I notice in your code is that you have variables with the same name as items and you have variables and items and you are not fully qualifying your reference to items in your code.  This is really bad coding practice.  You should concider adopting a naming convention standard for your Forms and PL/SQL blocks.

          Here is a good place to start: Naming Standards - Oracle Documentation
          Another good article is: Ideas for Oracle PL/SQL Naming Conventions and ... - Toad World by Steven Feurstein.

          A quick GOOGLE search on: "Oracle Forms PL/SQL Naming Standards" will give you other good examples of naming standards.

           

          Looking at your code:

           

           

          ...
          Loop
                 go_item(next);
                :next := :B_Emp.Emp_id; ---- here is the problem
                 -- Is :NEXT a block Item or a variable (as you have referenced below with your :SYSTEM.CURSOR_ITEM assignment?
                 -- The ":" infers this is a Block.Item rather than a variable.  My guess is that this is a variable.
                 -- as such, the ":" (semicolan) makes this a Bind Variable instead of a Block.Item reference.  
                 next_item;
                 next := :SYSTEM.CURSOR_ITEM;
          -- You don't use the preceeding semicolan here, so correctly references the "NEXT" variable you created in your declaration section.
          

           

          Craig...

          • 2. Re: How to assign values to the item where currently the cursor is??
            2685465

            Hey Craig!!

            Thanks for the link...its has really got some good stuffs for newbies like me.

             

            1.       :next := :B_Emp.Emp_id; ---- here is the problem 
            2.        -- Is :NEXT a block Item or a variable (as you have referenced below with your :SYSTEM.CURSOR_ITEM assignment? 
            3.        -- The ":" infers this is a Block.Item rather than a variable.  My guess is that this is a variable. 

             

            'NEXT' is a variable only and this variable holds the address of the item of the block where I need to insert the value of  ':B_Emp.Emp_id' which itself is dynamically changing as I have  looped it with next_record.

             

            1.       :next := :B_Emp.Emp_id; ---- here is the problem 

             

            By this I am trying to refer (for data insertion) the address in the 'NEXT' variable.

             

            So basically what I am trying here is, dynamically changing the item name of a particular block and then inserting the values in these items.

            However I am able to get the address of the item(in the NEXT variable) but I am not able to refer the name of the item for the data insertion. So the basic question is ....Can I dynamically change the item name for data insertion??

             

            One more thing

             

            1. ... 
            2. Loop 
            3.        go_item(next); 

             

             

            At the beginning of the loop,my System Cursor is at the item(of a block) where I need to insert the values.So I would like to ask you,Is there any way to insert the values in the item where currently the System Cursor is present?? In this way I won't have to bother about changing the item name dynamically as I will be taking the System Cursor to that item.

             

            Thanks!!

            • 3. Re: How to assign values to the item where currently the cursor is??
              Amatu Allah Neveen Ebrahim

              Hi

               

              Actually am not well recognized the whole code, but asking for insertion into the database you could use...

               

              >commit;

              or

              > forms_dll('COMMIT');

               

              after the end of the loop.

               

              Amatu Allah.

              • 4. Re: How to assign values to the item where currently the cursor is??
                2685465

                Thanks for your concern Amatu.

                 

                It seems you didn't got my requirement.

                >commit;

                or

                > forms_dll('COMMIT');

                 

                I can using these to "save"  the records in my database but for that first of all I need to have some data in my tables. Now in order to have data in my table ,my requirement needs me to refer the item of the data block dynamically and this where the problem is.

                • 5. Re: How to assign values to the item where currently the cursor is??
                  CraigB

                  I will try and answer your questions...

                   

                  2685465 wrote:

                   

                  ...So basically what I am trying here is, dynamically changing the item name of a particular block and then inserting the values in these items.

                  However I am able to get the address of the item(in the NEXT variable) but I am not able to refer the name of the item for the data insertion. So the basic question is ....Can I dynamically change the item name for data insertion??...

                  Data blocks and block items are defined during design and can't be changed programatically.  I'm not really sure why you would want to.  One thing you need to keep in mind is that Oracle Forms is designed to interact directly with the Oracle Database.  This means, if you have a Forms data block based on a table - Forms will handle all of the DML (Data Manipulation Language) related to that table; you don't have to write any code at all - Forms will handle it.  Basically, any data you display on screen in this Table Based Data Block - Forms will handle the Insert, Update or Delete SQL Statements to make those changes to the underlying database table.  This is one of the reasons why Forms is classified as a "Rapid Application Development (RAD)" tool.  This is extreamly powerful, but it can also be limiting depending on how your database is designed and if you have to hit more than one table to display the data you need in your form. This is also the number one thing that trips up new Forms developers - especially if they have done some object oriented development.

                   

                  Since you are new to Oracle Forms development - it might be good for you to review a few Forms Tutorials just to become familiar with Oracle Forms concepts.  Although the Forms version is different between each of these links, the concepts are still valid across all Forms versions.

                  There are also some really good You Tube tutorials out there as well.

                   

                  Back to your issue.  In all honesty, I'm not really sure what it is you are trying to accomplish.  It sounds like you want to use a single data block to insert into different tables.  If this is correct, then as I mentioned before, data blocks are created during "Design" and can't be created or modified programatically.  There are properties of a data block that can be changed programatically, but you can't add or remove items and create new blocks during runtime.

                   

                  Perhaps if you could provide just a brief overview of what you are trying to do, we could suggest some ways of accomplishing it.

                   

                  Craig...

                  • 6. Re: How to assign values to the item where currently the cursor is??
                    2685465

                    Thanks again for the links.I will make the best use of it.

                     

                     

                    Data blocks and block items are defined during design and can't be changed programatically.

                     

                      It sounds like you want to use a single data block to insert into different tables. 

                    I am neither trying to change the data blocks(or their items) programatically nor I am inserting into different tables. What I was trying up there was to insert all the data from a tabular block(B_Emp) to  a non tabular block(B_test) by dynamically going to the next item .

                     

                    Perhaps if you could provide just a brief overview of what you are trying to do, we could suggest some ways of accomplishing it.

                    Okay!!Lets start from scratch.Forget about the trigger code.

                    I have a non-tabular B_test block which has four non database Text Items say Item1,item2,Item3 and Item4 . I have a table Employees having Emp_id as one of its column and lets say that the table has 3 rows and the values for  Emp_id is 1,2,and 3.

                    Now there is one push button which when pressed should get the values of all the emp_id present in the table Employees into the Item1,Item2,Item3 and Item4(Item4 will be null and its property should be set to invisible but that we will see later) respectively.

                     

                    So could you please help me in achieving this?

                     

                    Thanks in advance!!

                    • 7. Re: How to assign values to the item where currently the cursor is??
                      Zlatko Sirotic 2

                      Does "non-tabular" block means that Database Block property = No?

                       

                      If yes, do you want to populate a non-database block from the database table rows?

                      For example:

                       

                      -- e.g. WHEN-NEW-FORM-INSTANCE trigger

                      FOR emp_c IN

                        (SELECT empno, ename, mgr FROM emp ORDER BY empno)

                      LOOP

                        :emp.empno  := emp_c.empno;

                        :emp.ename  := emp_c.ename;

                        :emp.mgr    := emp_c.mgr;

                        NEXT_RECORD;

                      END LOOP;

                       

                      But, why you do not use a database block?

                       

                       

                      Regards,

                      Zlatko

                      • 8. Re: How to assign values to the item where currently the cursor is??
                        2685465

                        Thanks Zlatko for your concern!!

                         

                        The database block property is YES.

                        By non-tabular I mean a block in which 'Number of Records Displayed = 1'.

                        -- e.g. WHEN-NEW-FORM-INSTANCE trigger

                        FOR emp_c IN

                        What is emp_c?? A cursor or just a variable??         

                          :emp.empno  := emp_c.empno;

                          :emp.ename  := emp_c.ename;

                          :emp.mgr    := emp_c.mgr;

                        Here 'emp' is a block having 'empno','ename','mgr' as its items..... right??

                        If yes, then what I want is:

                        'empno' should have the 'empno' of first employee

                        'ename' should contain the 'empno' of second employee

                        'mgr' should contain the 'empno' of third employee AND in the property palette of the 'emp' block  Number of Records Displayed = 1.

                         

                        Hope I am able to convey my requirements to you!!

                         

                        Regards,

                        Utsav

                        • 9. Re: How to assign values to the item where currently the cursor is??
                          Zlatko Sirotic 2

                          Emp_c is the cursor (explicit, or "half-explicit") and the variable.

                          We can say that the (Oracle) cursor is a pointer to the database code that (code) is used to read rows from the database.

                          (SELECT ... is implicit cursor, CURSOR emp_c ... is explicit cursor).

                           

                          Your request is very unusual.

                           

                          If you only want to see data in a single row, you can do this:

                          - use database block that has only empno item

                          - set Number of Records Displayed = N, where N is the number of fields visible in a single row

                          - set Record Orientation = Horizontal.

                           

                           

                          Regards,

                          Zlatko

                          • 10. Re: How to assign values to the item where currently the cursor is??
                            2685465

                            - set Number of Records Displayed = N, where N is the number of fields visible in a single row

                                 Do I really need to set the number of records displayed =N ??

                                 I mean we can display for say 4 records and then simply enable the scroll-bar...right??

                            • 11. Re: How to assign values to the item where currently the cursor is??
                              Zlatko Sirotic 2

                              Yes, set the Number of records displayed to (say) 4, and use a scrollbar.

                               

                               

                              Regards,

                              Zlatko

                              • 12. Re: How to assign values to the item where currently the cursor is??
                                2685465

                                Guys!! I have found one solution for the same.

                                We can use one built-in 'Copy' in order to assign the values to the item where currently system cursor is.

                                 

                                Syntax will be

                                 

                                Copy((new_value), :System.Cursor_Item);

                                --- where new_value is the variable holding the data to be assigned.

                                Also, we can assign the values of the system cursor to any variable.

                                Syntax will be'

                                new_value:= :System.Cursor_Value;

                                 

                                Thanks a lot guys for your help and concern

                                Cheers!

                                • 13. Re: How to assign values to the item where currently the cursor is??
                                  Zlatko Sirotic

                                  I am glad you found a satisfactory solution.

                                   

                                  (Now I can again use my primary userid, which was not working after the OTN forum upgrade.)

                                   

                                  Regards,

                                  Zlatko