Why desired records are not getting added?

Ptrikha

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?

David Berger

Hello User252311

You have to reference your Index-by Table.

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, names(plnames), 1);
     
       -- Increment the counter
      v_counter :=v_counter+1 ;
    END LOOP;
    COMMIT;
END;
/

I hope it helps.

Regards, David

David Berger

Hello User252311

You have to reference your Index-by Table.

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, names(plnames), 1);
     
       -- Increment the counter
      v_counter :=v_counter+1 ;
    END LOOP;
    COMMIT;
END;
/

I hope it helps.

Regards, David

SomeoneElse

     VALUES

   (v_counter,

   plnames,

   1);

For your second value, I think you meant names(plnames).

RogerT

Because:

FOR plnames IN names.FIRST .. names.LAST

names.FIRST gives you the INDEX of the first element of the array names whereas LAST gives you the INDEX of the last element of the array.

So you should change your insert to: from

INSERT INTO wc_team_details(wc_team_det_id ,

      wc_player_name ,

      wc_team_id )

      VALUES

   (v_counter,

   plnames,

   1);

to

INSERT INTO wc_team_details(wc_team_det_id ,

      wc_player_name ,

      wc_team_id )

      VALUES

   (v_counter,

   names(plnames),

   1);

and then:

have a look at "BULK OPERATIONS" as a next step

hth

Ptrikha

Yes this works:

WC_TEAM_DET_ID WC_PLAYER_NAME                                                                                       WC_TEAM_ID

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

             1 Sergio Romero                                                                                                 1

             2 Agustín Orión                                                                                                 1

             3 Mariano Andújar                                                                                               1

             4 Ezequiel Garay                                                                                                1

             5 Hugo Campagnaro                                                                                               1

             6 Pablo Zabaleta                                                                                                1

             7 Martín Demichelis                                                                                             1

             8 Marcos Rojo                                                                                                   1

             9 Federico Fernández                                                                                            1

            10 José María Basanta                                                                                            1

            11 Fernando Gago                                                                                                 1

            12 Lucas Biglia                                                                                                  1

            13 Ángel di María                                                                                                1

            14 Enzo Pérez                                                                                                    1

            15 Maxi Rodríguez                                                                                                1

            16 Augusto Fernández                                                                                             1

            17 Javier Mascherano                                                                                             1

            18 Ricardo Álvarez                                                                                               1

            19 Gonzalo Higuaín                                                                                               1

            20 Lionel Messi                                                                                                  1

            21 Rodrigo Palacio                                                                                               1

            22 Sergio Agüero                                                                                                 1

            23 Ezequiel Lavezzi                                                                                              1

23 rows selected

KayK

Hi David,

sorry to say this, but there is an error in your data: We are number 1 in the world

Thanks to Mario Götze

regards
Kay

Ptrikha

This question was put up before the Fifa World Cup 2014 final.