Forum Stats

  • 3,874,265 Users
  • 2,266,717 Discussions
  • 7,911,796 Comments

Discussions

Why desired records are not getting added?

Ptrikha
Ptrikha Member Posts: 12 Blue Ribbon
edited Jul 17, 2014 8:40AM in SQL & PL/SQL

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?

Tagged:
PtrikhaSven W.KayK

Best Answer

  • David Berger
    David Berger Member Posts: 345 Silver Badge
    edited Jul 10, 2014 9:02AM Answer ✓

    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

    Ptrikha

Answers

  • David Berger
    David Berger Member Posts: 345 Silver Badge
    edited Jul 10, 2014 9:02AM Answer ✓

    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

    Ptrikha
  • SomeoneElse
    SomeoneElse Member Posts: 14,867 Silver Crown

         VALUES

       (v_counter,

       plnames,

       1);

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

    PtrikhaSomeoneElse
  • RogerT
    RogerT Member Posts: 1,860 Gold Trophy

    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

    RogerT
  • Ptrikha
    Ptrikha Member Posts: 12 Blue Ribbon

    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
    KayK Member Posts: 1,740 Bronze Crown
    edited Jul 14, 2014 3:09AM

    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

    Sven W.
  • Ptrikha
    Ptrikha Member Posts: 12 Blue Ribbon

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

    KayK
This discussion has been closed.