SQL PROCEDURE CURSOR LOOPS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL PROCEDURE CURSOR LOOPS

Author

Topic

swfc4lyf
Starting Member

2 Posts

Posted - 2012-03-29 : 15:02:28

I have create the following tables and types....

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
/

I need to create an.....

A PL/SQL procedure called INIT_ACTOR_QUOTES with no parameters that:

Reads ALL the ACTORIDs from the ACTOR table and INSERTs them into the ACTORID attribute for each row the ACTOR_QUOTES table (the tables have the same cardinality) and at the same time INSERTs the following initial values into the first row only of the QUOTES nested table into each row of the ACTOR_QUOTES table;

(Movie_Title, Year, Role, Quote) are set respectively to (' ',NULL ,' ', ' ')

Also and at the same time immediately after each INSERT use DELETE to delete ALL the rows from the nested table in each row belonging to each ACTORID in the ACTOR_QUOTES table.

I'm getting a compilation error with the code....

CREATE OR REPLACE PROCEDURE INIT_ACTOR_QUOTES
AS
CURSOR actorID_cursor IS
SELECT actorID FROM Actor;
BEGIN
FOR row IN actorID_cursor LOOP
INSERT INTO ACTOR_QUOTES (ACTORID) VALUES (actorID);
INSERT INTO actor_Quotes_NT VALUES ('', NULL, '', '');
DELETE FROM actor_Quotes_NT WHERE ACTORID=actorID;
END LOOP;
END INIT_ACTOR_QUOTES ;
/
Warning: Procedure created with compilation errors.
SQL> show errors;
Errors for PROCEDURE INIT_ACTOR_QUOTES:

LINE/COL ERROR
-----------------------------------------------------------------
7/1 PL/SQL: SQL Statement ignored
7/44 PL/SQL: ORA-00984: column not allowed here
8/1 PL/SQL: SQL Statement ignored
8/13 PL/SQL: ORA-22812: cannot reference nested table column's storage table
9/1 PL/SQL: PL/SQL: SQL Statement ignored
7/9 PL/SQL: ORA-00925: missing INTO keyword
9/13 PL/SQL: ORA-22812: cannot reference nested table column's storage table
SQL>

Any help would be appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-29 : 15:27:58

seems like you're using Oracle

This is MS SQL Server forum

you may be better off posting it in some oracle forums like www.orafaq.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

swfc4lyf
Starting Member

2 Posts

Posted - 2012-03-29 : 15:34:53

my mistake, already posted in there too!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources