Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Why desired records are not getting added?

PtrikhaJul 10 2014 — edited Jul 17 2014

Hi,

I ran this script and wanted to add records of Argentina World Cup players to the table :

wc_team_details:

/** Package to work with UTL_FILE to write a report to a file.

First run Create entities.sql

Sample run on World Cup Football Teams Names and related data

*/

-- @CreateEntities.sql

CREATE TABLE wc_team_names(wc_team_id NUMBER CONSTRAINT pk_wc_team_name_id PRIMARY KEY ,

wc_team_name VARCHAR2(75) CONSTRAINT nn_wc_team_name NOT NULL,

wc_team_continent VARCHAR2(50) CONSTRAINT nn_wc_continent_name NOT NULL );

CREATE INDEX idx_wc_team_nme ON wc_team_names(wc_team_name);

CREATE TABLE wc_team_details(wc_team_det_id NUMBER CONSTRAINT pk_wc_team_det_id PRIMARY KEY,

wc_player_name VARCHAR2(100)CONSTRAINT nn_wc_plyr_name NOT NULL,

wc_team_id NUMBER CONSTRAINT fk_wc_team_id references wc_team_names(wc_team_id)

);

CREATE INDEX idx_wc_team_det_nmes ON wc_team_details(wc_player_name) REVERSE;

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(1,'Argentina','South America');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(2,'Germany','Europe');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(3,'Brazil','South America');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(4,'Netherlands','Europe');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(5,'Mexico','North America');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(6,'Italy','Europe');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(7,'England','Europe');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(8,'South Korea','Asia');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(9,'Algeria','Africa');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(10,'Australia','Australasia');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(11,'Belgium','Europe');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(12,'Switzerland','Europe');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(13,'Portugal','Europe');

COMMIT;

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(14,'Greece','Europe');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(15,'Iran','Asia');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(16,'Japan','Asia');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(17,'Ivory Coast','Africa');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(18,'Nigeria','Africa');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(19,'Ghana','Africa');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(20,'Costa Rica','North America');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(21,'USA','North America');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(22,'Uruguay','South America');

COMMIT;

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(23,'Croatia','Europe');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(24,'Bosnia-Herzegovina','Europe');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(25,'Russia','Europe');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(26,'Colombia','South America');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(27,'France','Europe');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(28,'Cameroon','Africa');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(29,'Spain','Europe');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(30,'Chile','South America');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(31,'Ecuador','South America');

INSERT INTO wc_team_names(wc_team_id,wc_team_name,wc_team_continent) VALUES(32,'Honduras','North America');

COMMIT ;

/** Team Details Population  */

DECLARE

  TYPE playerNames IS TABLE OF VARCHAR2(75);

  v_counter number :=1;

  names playerNames := playerNames('Sergio Romero','Agustín Orión','Mariano Andújar','Ezequiel Garay','Hugo Campagnaro','Pablo Zabaleta','Martín Demichelis','Marcos Rojo','Federico Fernández','José María Basanta','Fernando Gago',

'Lucas Biglia','Ángel di María','Enzo Pérez','Maxi Rodríguez','Augusto Fernández','Javier Mascherano',

'Ricardo Álvarez','Gonzalo Higuaín','Lionel Messi','Rodrigo Palacio','Sergio Agüero','Ezequiel Lavezzi')

;

BEGIN

      FOR plnames IN names.FIRST .. names.LAST

LOOP

      INSERT INTO wc_team_details(wc_team_det_id ,

      wc_player_name ,

      wc_team_id )

      VALUES

   (v_counter,

   plnames,

   1);

   -- Increment the counter

   v_counter :=v_counter+1 ;

END LOOP;

      COMMIT ;

END;

/

However, I get this result:

anonymous block completed

WC_TEAM_DET_ID WC_PLAYER_NAME                                                                                       WC_TEAM_ID

-------------- ---------------------------------------------------------------------------------------------------- ----------

             1 1                                                                                                             1

             2 2                                                                                                             1

             3 3                                                                                                             1

             4 4                                                                                                             1

             5 5                                                                                                             1

             6 6                                                                                                             1

             7 7                                                                                                             1

             8 8                                                                                                             1

             9 9                                                                                                             1

            10 10                                                                                                            1

            11 11                                                                                                            1

            12 12                                                                                                            1

            13 13                                                                                                            1

            14 14                                                                                                            1

            15 15                                                                                                            1

            16 16                                                                                                            1

            17 17                                                                                                            1

            18 18                                                                                                            1

            19 19                                                                                                            1

            20 20                                                                                                            1

            21 21                                                                                                            1

            22 22                                                                                                            1

            23 23                                                                                                            1

23 rows selected

Why is this happening? Why has wc_player_name not having player names and is instead having player ids?

This post has been answered by David Berger on Jul 10 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 14 2014
Added on Jul 10 2014
6 comments
225 views