This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Apr 15, 2012 2:01 PM by sb92075 RSS

PL/SQL Procedure using cursor loops

928040 Newbie
Currently Being Moderated
**
  • 1. Re: PL/SQL Procedure using cursor loops
    rp0428 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    http://www.orafaq.com/forum/m/549347/136107/#msg_549347
  • 4. Re: PL/SQL Procedure using cursor loops
    928040 Newbie
    Currently Being Moderated
    @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 Newbie
    Currently Being Moderated
    Does anyone have any idea as to what I am doing wrong?
  • 6. Re: PL/SQL Procedure using cursor loops
    rp0428 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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?
  • 8. Re: PL/SQL Procedure using cursor loops
    928040 Newbie
    Currently Being Moderated
    **
  • 9. Re: PL/SQL Procedure using cursor loops
    rp0428 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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?
  • 11. Re: PL/SQL Procedure using cursor loops
    928040 Newbie
    Currently Being Moderated
    **
  • 12. Re: PL/SQL Procedure using cursor loops
    928040 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 . . .
  • 14. Re: PL/SQL Procedure using cursor loops
    928040 Newbie
    Currently Being Moderated
    **
1 2 Previous Next

Legend

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