1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Package error- help

Discussion in 'SQL PL/SQL' started by FiZor, May 20, 2010.

  1. FiZor

    FiZor Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Croatia, Zagreb
    Greetings folks!
    I am learning procedures, functions and packages.
    For practice, I use NAVICAT for Oracle and SQL Developer. I need little help with one package so, source is here:

    CREATE OR REPLACE
    PACKAGE VJEZBA4 IS
    PROCEDURE KREIRANJE_TABLICE_POPIS_MOBITELA;
    PROCEDURE UPDATE_BRAND;
    PROCEDURE PRIKAZI_REZULTAT;
    END VJEZBA4;

    CREATE PACKAGE BODY VJEZBA4 IS

    PROCEDURE KREIRANJE_TABLICE_POPIS_MOBITELA AS
    BEGIN
    CREATE TABLE POPIS_MOBITELA(ID_BRAND CHAR(4) PRIMARY KEY, IME_BRANDA VARCHAR2(30));
    INSERT INTO POPIS_MOBITELA(ID_BRAND, IME_BRANDA)
    VALUES ('0001', 'Sony Ericsson');
    INSERT INTO POPIS_MOBITELA(ID_BRAND, IME_BRANDA)
    VALUES ('0002', 'Nokia');
    INSERT INTO POPIS_MOBITELA(ID_BRAND, IME_BRANDA)
    VALUES ('0003', 'Samsung');
    INSERT INTO POPIS_MOBITELA(ID_BRAND, IME_BRANDA)
    VALUES ('0004', 'LG');
    INSERT INTO POPIS_MOBITELA(ID_BRAND, IME_BRANDA)
    VALUES ('0005', 'HTC');
    INSERT INTO POPIS_MOBITELA(ID_BRAND, IME_BRANDA)
    VALUES ('0006', 'Blackberry');
    INSERT INTO POPIS_MOBITELA
    VALUES ('0007', 'NEC');
    END KREIRANJE_TABLICE_POPIS_MOBITELA;

    PROCEDURE UPDATE_BRAND AS
    BEGIN
    UPDATE POPIS_MOBITELA
    SET IME_BRANDA = 'IMATE'
    WHERE ID_BRAND = '0007';
    END UPDATE_BRAND;

    PROCEDURE PRIKAZI_REZULTAT AS
    BEGIN
    SELECT * FROM POPIS_MOBITELA;
    END PRIKAZI_REZULTAT;
    END VJEZBA4;


    Plz, can you tell me where is the syntaks problem in it? Thank you very much in advance!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    DDl cannot directly be executed in PL/SQL (create table ...); such actions are restricted to dynamic sql:

    Code (SQL):
     
    ...
    sqltext varchar2(4000);
    ...
    BEGIN
           sqltext := 'CREATE TABLE POPIS_MOBITELA(ID_BRAND CHAR(4) PRIMARY KEY, IME_BRANDA VARCHAR2(30))';
           EXECUTE immediate sqltext;
           ,,,
     
    Also 'direct' selects aren't allowed outside of a cursor:

    Code (SQL):
     
    PROCEDURE PRIKAZI_REZULTAT AS
    cursor get_popis IS
    SELECT * FROM POPIS_MOBITELA;

    BEGIN
         FOR popis_rec IN get_popis loop
                 dbms_output.put_line(popis_rec.id_brand||'  '||popis_rec.imd_branda);
         END loop;
    END PRIKAZI_REZULTAT;
    END VJEZBA4;
    /

     
    These are the errors I see in your code.
     
  3. FiZor

    FiZor Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Croatia, Zagreb
    Thank you very much. I am new at procedures, learning so i make cardinal mistakes. Thank again for your help!
     
  4. You can Write as follwoing......

    CREATE or replace PACKAGE BODY VJEZBA4 IS

    PROCEDURE KR AS
    BEGIN
    -- create out side other wise use the drop tbale
    --EXECUTE IMMEDIATE 'CREATE TABLE POPIS_MOBITELA(ID_BRAND CHAR(4) PRIMARY KEY , IME_BRANDA VARCHAR2(30))';

    INSERT INTO POPIS_MOBITELA(ID_BRAND, IME_BRANDA)
    VALUES ('0001', 'Sony Ericsson');
    INSERT INTO POPIS_MOBITELA(ID_BRAND, IME_BRANDA)
    VALUES ('0002', 'Nokia');
    INSERT INTO POPIS_MOBITELA(ID_BRAND, IME_BRANDA)
    VALUES ('0003', 'Samsung');
    INSERT INTO POPIS_MOBITELA(ID_BRAND, IME_BRANDA)
    VALUES ('0004', 'LG');
    INSERT INTO POPIS_MOBITELA(ID_BRAND, IME_BRANDA)
    VALUES ('0005', 'HTC');
    INSERT INTO POPIS_MOBITELA(ID_BRAND, IME_BRANDA)
    VALUES ('0006', 'Blackberry');
    INSERT INTO POPIS_MOBITELA
    VALUES ('0007', 'NEC');

    null;
    END KR;

    PROCEDURE UPDATE_BRAND AS
    BEGIN
    --UPDATE POPIS_MOBITELA
    --SET IME_BRANDA = 'IMATE'
    --WHERE ID_BRAND = '0007';
    null;
    END UPDATE_BRAND;

    PROCEDURE PRIKAZI_REZULTAT AS
    BEGIN
    -- use select into
    --SELECT * FROM POPIS_MOBITELA;

    null;
    END PRIKAZI_REZULTAT;
    END VJEZBA4;
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    The following code will be fine.

    PROCEDURE UPDATE_BRAND AS
    BEGIN
    UPDATE POPIS_MOBITELA
    SET IME_BRANDA = 'IMATE'
    WHERE ID_BRAND = '0007';
    END UPDATE_BRAND;

    Anyway , It would be helpful if you go through the basics of Oracle packages.


    Package Link1


    Package Link2
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    With more than one record in the popis_mobitela table 'select into' won't work; it requres either a cursor loop or a bulk collect.
     
  7. FiZor

    FiZor Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Croatia, Zagreb
    Thank you very much zargon!