Results 1 to 9 of 9

Thread: Package - help!

  1. #1
    Join Date
    Jun 2009
    Location
    Croatia, Zagreb
    Posts
    12

    Unanswered: Package - help!

    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!
    Noli turbare circulos meos.

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    I don't mind looking for logical problems, but sifting through code looking for a missing semicolon is work for a machine.

    Grab a copy of SQL Developer and edit the code using that. It will highlight syntax errors for you, so you can look up the statements in the documentation to see what you're doing wrong. If you get error messages you don't understand, post them here, and then we can help you out.

  3. #3
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Plz, can you tell me where is the syntaks problem in it?
    FiZor,

    there is more than one ...

    1.) You cannot call DDL commands directly out of a pl/sql block. So in your KREIRANJE_TABLICE_POPIS_MOBITELA - procedure you must use

    Code:
    execute immediate 'CREATE TABLE POPIS_MOBITELA(ID_BRAND CHAR(4) PRIMARY KEY, IME_BRANDA VARCHAR2(30))';
    instead of just typing the DDL statement.

    2.) Since this is PL/SQL and not SQL you have to do something with the result set in your PRIKAZI_REZULTAT - procedure (SQL is a set oriented language and PL/SQL is a procedural language).

    In your example, your SELECT statement will retrieve 7 records. What do you want to do with them ?
    Provided, you want to output them, this procedure should read:
    Code:
    PROCEDURE PRIKAZI_REZULTAT AS
    
      -- define a cursor for the SELECT - Statement
      cursor  cMobilCur      is SELECT * FROM POPIS_MOBITELA;
    
      -- define a type for a collection corresponding to the resultset type
      type    tMobilArr      is table of cMobilCur%rowtype index by pls_integer;
    
      -- define a variable to hold the result of the query
      vMobilArr tMobilArr;
    
    BEGIN
      open  cMobilCur;  -- open the cursor
      fetch cMobilCur bulk collect into vMobilArr;  -- populate the variable
      close cMobilCur;  -- we are done with the SELECT by now
      -- now lets process the records
      if vMobilArr.COUNT > 0 then
         for i in vMobilArr.FIRST..vMobilArr.LAST loop
            -- to perform output from within PL/SQL you have to use the
            -- dbms_output package
            dbms_output.put_line('ID_BRAND: ' || vMobilArr(i).ID_BRAND || '   IME_BRANDA: ' || vMobilArr(i).IME_BRANDA);
         end loop;
      end if;
    END PRIKAZI_REZULTAT;
    And don't forget to enable the serveroutput when printing from PL/SQL:

    Code:
    SQL> set serverout on
    SQL> execute KREIRANJE_TABLICE_POPIS_MOBITELA.PRIKAZI_REZULTAT;
    ID_BRAND: 0001   IME_BRANDA: Sony Ericsson
    ID_BRAND: 0002   IME_BRANDA: Nokia
    ID_BRAND: 0003   IME_BRANDA: Samsung
    ID_BRAND: 0004   IME_BRANDA: LG
    ID_BRAND: 0005   IME_BRANDA: HTC
    ID_BRAND: 0006   IME_BRANDA: Blackberry
    ID_BRAND: 0007   IME_BRANDA: NEC
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Last edited by magicwand; 05-20-10 at 06:44. Reason: typos fixed
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  4. #4
    Join Date
    Jun 2009
    Location
    Croatia, Zagreb
    Posts
    12
    Thank you very much, you helped a lot. I will correct that
    Last edited by FiZor; 05-20-10 at 06:31.
    Noli turbare circulos meos.

  5. #5
    Join Date
    Jun 2009
    Location
    Croatia, Zagreb
    Posts
    12
    Now, i changed my code, is this ok?
    CREATE OR REPLACE
    PACKAGE POPIS_MOB IS
    PROCEDURE KR_POPISMOB;
    PROCEDURE UPDATE_BRAND;
    PROCEDURE PRIKAZI_REZULTAT;
    END POPIS_MOB;

    CREATE PACKAGE BODY POPIS_MOB IS

    PROCEDURE KR_POPISMOB 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');
    EXECUTE IMMEDIATE 'CREATE TABLE POPIS_MOBITELA(ID_BRAND CHAR(4) PRIMARY KEY, IME_BRANDA VARCHAR2(30));'
    END KR_POPISMOB;

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

    PROCEDURE PRIKAZI_REZULTAT AS
    CURSOR POPMOB IS SELECT * FROM POPIS_MOBITELA; -- definira kursor za selektiranje popisa
    TYPE TMOBILAR IS TABLE OF POPMOB%ROWTYPE INDEX BY PLS_INTEGER;

    VMOBIL TMOBIL;

    BEGIN
    OPEN POPMOB; -- otvara kursor
    FETCH POPMOB BULK COLLECT INTO VMOBIL; --puni varijablu
    CLOSE POPMOB; -- gotov select
    IF VMOBIL.COUNT > 0 THEN
    FOR i IN VMOBIL.FIRST..VMOBIL.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('Šifra branda: ' || VMOBIL(i).ID_BRAND || ' Ime branda: ' || VMOBIL(i).IME_BRANDA ||);
    END LOOP;
    END IF;
    END PRIKAZI_REZULTAT;

    END POPIS_MOB;
    Noli turbare circulos meos.

  6. #6
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    is this ok?
    I'm sure, you will find the 3 remaining errors by yourself ...
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Get rid of the create table command and move the execute immediate into it's position.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not sure the creation of the table even needs to be in the package, anyway. I can't really come up with a good reason you would want to do that, anyway, unless this is some sort of temporary table.

  9. #9
    Join Date
    Jun 2009
    Location
    Croatia, Zagreb
    Posts
    12
    It is just practicing example. I am new at procedures, functions, learning logic.
    Noli turbare circulos meos.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •