1 2 Previous Next 22 Replies Latest reply: Apr 15, 2012 4:01 PM by sb92075 RSS

    PL/SQL Procedure using cursor loops

    928040
      **
        • 1. Re: PL/SQL Procedure using cursor loops
          rp0428
          Welcome to the forum!
          When posting questions always provide your 4 digit Oracle version (results of SELECT * FROM V$VERSION).
          >
          INSERT INTO TABLE(SELECT QUOTES FROM Actor_Quotes WHERE row.ActorID = ActorID)
          >
          What table do you think that is trying to insert into? That is invalid syntax.
          I already answered this question - See my reply Posted: Mar 29, 2012 3:08 PM at
          Re: SQL PROCEDURE CURSOR LOOPS
          Maybe the two of you were classmates and didn't know it.
          • 2. Re: PL/SQL Procedure using cursor loops
            928040
            Ah yes! that question is very simular and one that I have managed to do, but the difference between that question and this one is this one uses a single parameter and should actually populate the data requested.

            Refering to that insert statement you aksed about I am trying to get it to insert the the select statement that is directly below that insert statement into the nested table.

            Sorry about my poor explanation but PL/SQL is really not my strong point but I am ready to learn

            Oracle version is: Oracle Database 10g Express Edition Release 10.2.0.1.0

            Edited by: 925037 on 02-Apr-2012 15:12
            • 3. Re: PL/SQL Procedure using cursor loops
              sb92075
              http://www.orafaq.com/forum/m/549347/136107/#msg_549347
              • 4. Re: PL/SQL Procedure using cursor loops
                928040
                @sb92075 Iv managed to do the one in that link but this one needs to actually populate the nested table with data using a single parameter where as the one in the link required no parameters.

                The problem I am having is getting the PL/SQL to work with a single parameter and to actually populate the nested table with data
                • 5. Re: PL/SQL Procedure using cursor loops
                  928040
                  Does anyone have any idea as to what I am doing wrong?
                  • 6. Re: PL/SQL Procedure using cursor loops
                    rp0428
                    Only what I already told you. You haven't specified the table that you want to insert into. An insert statement is of the form
                    INSERT INTO myTable (colA, colB, . . .) VALUES (1, 'cheese', . . .)
                    or
                    INSERT INTO myTable (colA, colB, . . .) SELECT (. . .) FROM otherTable
                    Here is an example of an insert into a nested table
                    http://www.orafaq.com/wiki/NESTED_TABLE
                    • 7. Re: PL/SQL Procedure using cursor loops
                      928040
                      Ok thanks for the link and examples. I understand those INSERT statements but mine seems alot harder than those in the examples.

                      This query achieves what I want:
                      SELECT Actor.ActorID, Movie.title, Movie.year, Role.roleName, Quote.quoteCHAR FROM Actor, Movie, Role, Quote, RoleQuote
                      WHERE Actor.actorID = Role.actorID AND Movie.movieID = Role.movieID AND Role.roleID = RoleQuote.roleID AND Quote.quoteID = RoleQuote.quoteID;

                      But I need to put this SELECT statement into the nested table within the PL/SQL statement, an insert for this is causing me a head ache to say the least!

                      Do you have any ideas?
                      • 9. Re: PL/SQL Procedure using cursor loops
                        rp0428
                        >
                        But I need to put this SELECT statement into the nested table within the PL/SQL statement, an insert for this is causing me a head ache to say the least!

                        Do you have any ideas?
                        >
                        Not if you are unwilling to read through the example I provided of how to insert into a nested table.
                        Good luck.
                        • 10. Re: PL/SQL Procedure using cursor loops
                          sb92075
                          925037 wrote:
                          Ok thanks for the link and examples. I understand those INSERT statements but mine seems alot harder than those in the examples.

                          This query achieves what I want:
                          SELECT Actor.ActorID, Movie.title, Movie.year, Role.roleName, Quote.quoteCHAR FROM Actor, Movie, Role, Quote, RoleQuote
                          WHERE Actor.actorID = Role.actorID AND Movie.movieID = Role.movieID AND Role.roleID = RoleQuote.roleID AND Quote.quoteID = RoleQuote.quoteID;

                          But I need to put this SELECT statement into the nested table within the PL/SQL statement, an insert for this is causing me a head ache to say the least!

                          Do you have any ideas?
                          data & tables should be Normalized & not contain duplicated data.
                          Forget about the Nested Table & just CREATE VIEW that contains the desired columns.


                          If/When NESTED table exists how do you keep it current when rows it the other tables change?
                          • 12. Re: PL/SQL Procedure using cursor loops
                            928040
                            Yes your right but I have to do it this way as it has been given to me as an assignment. Im afraid I have no choice but to do it using a nested table within a PL/SQL statement no matter what problems it may cause.
                            • 13. Re: PL/SQL Procedure using cursor loops
                              rp0428
                              >
                              cant find documentation on anything that complex
                              >
                              The documentation is in your own question
                              SELECT Actor.ActorID, Movie.title, Movie.year, Role.roleName, Quote.quoteCHAR FROM Actor, Movie, Role, Quote, RoleQuote 
                              You don't see any resemblence between the above and the below?
                              ACTOR_QUOTE_TYPE('Spiderman', '2000', 'Peter Parker', 'I can fly'))); 
                              Such as
                              SELECT Actor.ActorID, ACTOR_QUOTE_TYPE(Movie,title, . . .) FROM . . .
                              1 2 Previous Next