9 Replies Latest reply: Jan 30, 2013 3:45 AM by Nicosa-Oracle RSS

    Newbie to PL/SQL.

    987851
      This is a simplified description of the real case. I have a table, let's say MY_TABLE, with 3 columns. One identity column ID, and two date columns; DATE1, DATE2.

      Each identity can have an arbitrary number of records. Every date has to be considered for each identity. Depending on certain rules regarding the dates, a variable, RESULT, should have the value 'Y' or 'N'.

      I would like to get a new table, RESULT_TABLE with the variables ID and RESULT.

      Eg. ID=17 could have 3 records and consequently 6 dates, and ID=54 could have 5 records and consequently 10 dates, and so on. Note that the number of records for each identity are unknown.

      So first I would like to read in the the first identitys unknown number of records, and depending on the date rules determine the value for RESULT, then put the values for ID and RESULT in the RESULT_TABLE. Then read the next identitys unknown number of records and process, and so on.

      The RESULT_TABLE could look like this(**** only for layout purpose):

      ID****RESULT
      10****N
      54****Y
      60****Y
      .
      .
      .

      The problem for me is to read the varying number of records for each identity and put the dates into arrays, so I could apply the date rules.

      Nested cursors, dynamic arrays? Thanks if I could get some help.

      /Abra
        • 1. Re: Newbie to PL/SQL.
          Solomon Yakobson
          Are your date rules so complex you can't write it up in SQL? Give us an example of date rules.

          SY.
          • 2. Re: Newbie to PL/SQL.
            Frank Kulash
            Hi, Abra,

            Welcome to the forum!
            984848 wrote:
            This is a simplified description of the real case.
            Good idea!
            I have a table, let's say MY_TABLE, with 3 columns. One identity column ID, and two date columns; DATE1, DATE2.
            Whenever you have a question, post CREATE TABLE and INSERT statements for a little samle data, and the results you want from that data. Explain how you get those results from that data (that is, what the relevant rules are).
            See the forum FAQ {message:id=9360002}

            As Solomon said, you probably don't need PL/SQL for this. Oracle provides several functions for dealing with DATEs, and analytic functions that operate on variably-sized sets of rows (such as all the rows with the same id) so, chances are you can get the results you want more easily and more efficiently just using SQL. Exactly how to do it depends on exactly what you need to do.
            • 3. Re: Newbie to PL/SQL.
              987851
              As I said, it was a simplified version I gave. The date rules are very complicated. And I want to learn PL/SQL and use it particularly in this case. The data are extensive and not mine, so I can't show them to anyone.

              The main problem is to read a varying unknown number of records and assign the values to arrays, as I have described.

              /Abra
              • 4. Re: Newbie to PL/SQL.
                Solomon Yakobson
                984848 wrote:
                The date rules are very complicated.
                Oracle analytic functions, MODEL clause and many other tools provide ton of ways for dealing with complex cross-row calculations.

                SY.
                • 5. Re: Newbie to PL/SQL.
                  987851
                  The main problem is to read a varying unknown number of records and assign the values to arrays, as I have described.

                  That's what I want to learn. I am sure I will face this problem many times.

                  Forget the date rules.

                  /Abra
                  • 6. Re: Newbie to PL/SQL.
                    6363
                    984848 wrote:
                    The date rules are very complicated.
                    Which functions or operators do you need to use for these rules that are only available in PL/SQL and not in SQL?
                    And I want to learn PL/SQL and use it particularly in this case.
                    Does it need to be particularly slow then? Or maybe consume a particularly large amount of CPU?
                    The data are extensive
                    Oh so you want to use PL/SQL to make it go real slow, so slow that people really notice.
                    The main problem is to read a varying unknown number of records and assign the values to arrays, as I have described.
                    This is not a problem description, this is a description of a really bad solution to some unknown problem.
                    • 7. Re: Newbie to PL/SQL.
                      Frank Kulash
                      Hi, Abra,
                      984848 wrote:
                      As I said, it was a simplified version I gave. The date rules are very complicated.
                      So, just like you simplified the table, now simplify the rules. Give an example of some of the simpler rules, or something similar to the rules, so we can show you how to solve the simplified problem. Once you've seen how to do that, you'll know how to approach your full set of real, more complicated rules.
                      And I want to learn PL/SQL and use it particularly in this case.
                      PL/SQL is probably the wrong tool for this job. If you wanted to learn how to use pliers, would you practice by driving nails with the pliers? Of course not. Driving nails is best done by a hammer. You can do it using pliers, if you really want to, but you'll learn nothing about the right way to use either a hammer or pliers.
                      Learn SQL first. When you come to something that PL/SQL is good for, then you'll have a better foundation for learning PL/SQL.
                      The data are extensive
                      No matter how big your real table is, you can probably illustrate the problem with a small number of rows, perhaps 5 or 10.
                      and not mine, so I can't show them to anyone.
                      So make up some fake data. If the date January 29, 2013 gives away sensitive information about your business, then use July 29, 2009, or some other date, instead.
                      • 8. Re: Newbie to PL/SQL.
                        Karthick_Arp
                        984848 wrote:
                        The main problem is to read a varying unknown number of records and assign the values to arrays, as I have described.
                        Associative Array does not have any upper boundary, so you don't have to worry about the number of records each ID is going to return. And these collection types have pseudo columns like FIRST, LAST and COUNT which can by used to traverse through the array without knowing number of elements in the array.

                        You can read the document for more details [url http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collections.htm]Using PL/SQL Collections and Records

                        Said that i would still recommend you to stick with SQL and try to solve your problem. Considering the power of ORACLE supplied SQL, PL/SQL collection types are not something that need to be used frequently.
                        • 9. Re: Newbie to PL/SQL.
                          Nicosa-Oracle
                          984848 wrote:
                          Forget the date rules.
                          Forget the PL/SQL
                          +(unless there is definitely no way of doing it without, which you haven't proved us yet)+

                          Or get your question purely theorical such as : "how one affects entries/ to any-kind-of-PLSQL-structure-here when the total number is unknown ?"