Databases
StackOverflow2013
Postsdbo
POPl/SQL procedure with single Parameter
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPl/SQL procedure with single Parameter
    primarykey
    data
    text
    <p>I have create the following tables...</p> <pre><code>CREATE TABLE Actor (Actor_ID CHAR(5), lastName CHAR(24), firstName CHAR(24), / CREATE TABLE Movie (movieID CHAR(3) , title CHAR(36), year NUMBER, / CREATE TABLE Role (roleID CHAR(5), roleName CHAR(36), actorID CHAR(5), movieID CHAR(3)) / CREATE TABLE Quote (quoteID CHAR(4), quoteCHAR CHAR(255)) / CREATE TABLE RoleQuote (roleID CHAR(5), quoteID CHAR(4)) / Then i created this schemas.... CREATE TYPE ACTOR_QUOTE_TYPE AS OBJECT ( Movie_Title CHAR(36), Year NUMBER, Role CHAR(36), Quote CHAR(255) ) / CREATE TYPE AQ_NT AS TABLE OF ACTOR_QUOTE_TYPE / CREATE TABLE ACTOR_QUOTES ( ACTORID CHAR(5), QUOTES AQ_NT ) NESTED TABLE QUOTES STORE AS ACTOR_QUOTES_NT / </code></pre> <p>I need to create a procedure with a single parameter(ACTORID is procedure parameter) and insert all the quotes in all the movies for any ACTORID, into the row(s) (an actor may have many movies and many quotes, some may have no quotes!) of the QUOTES nested table in the ACTOR_QUOTES table for any ACTORID.</p> <p>How do i do it ?</p> <p>Thanks</p> <p>So far i tried this, i am not sure it is correct or not.</p> <pre><code>CREATE OR REPLACE PROCEDURE Populate_Movies_Quote AS CURSOR Quote_cursor (ActorID in CHAR) IS SELECT ActorID, Quote, Movie_Title from Actor_Quotes, AQ_NT where Quotes.ActorID=ActorID; BEGIN FOR row IN Quote_cursor LOOP INSERT INTO ACTOR_QUOTES (ActorID, quotes) values (row.ActorID, AQ_NT(Actor_Quote_Type)); END LOOP; END Populate_Movies_Quote ; / Show erros LINE/COL ERROR -------- ----------------------------------------------------------------- 4/1 PL/SQL: SQL Statement ignored 4/55 PL/SQL: ORA-04044: procedure, function, package, or type is not allowed here 6/1 PL/SQL: Statement ignored 6/10 PLS-00306: wrong number or types of arguments in call to 'QUOTE_CURSOR' </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USShaan
    1. USShaan
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPl/SQL procedure with single Parameter
      1. This table or related slice is empty.
      1. VTModeratorReview
    2. VO
      singulars
      1. POPl/SQL procedure with single Parameter
      1. This table or related slice is empty.
      1. VTModeratorReview
    1. CO[What have you tried so far?](http://mattgemmell.com/2008/12/08/what-have-you-tried). I'd also recommend [ask].
      singulars
      1. POPl/SQL procedure with single Parameter
      1. USBen
    2. COIf this is homework, please tag it as such
      singulars
      1. POPl/SQL procedure with single Parameter
      1. USA.B.Cade
    3. COMore or less duplicate of http://stackoverflow.com/questions/9946383/pl-sql-procedure-using-cursor-loops-and-single-parameter and http://stackoverflow.com/questions/9929058/sql-procedure-cursor-loops and http://stackoverflow.com/questions/9928363/pl-sql-cursor-loops If this is an assignment you guys should learn to search or (even better) just talk to each other.
      singulars
      1. POPl/SQL procedure with single Parameter
      1. USBurhan Ali
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload