Results 1 to 3 of 3

Thread: PL/SQL errors

  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Unanswered: PL/SQL errors

    CREATE TABLE INSTRUCTOR
    ( instructor_id NUMBER(10) NOT NULL,
    instructor_name VARCHAR2(20) NOT NULL,
    instructor_surname VARCHAR2(20),
    instructor_adress VARCHAR2(50),
    instructor_title VARCHAR2(50),
    instructor_zip number(10),
    CONSTRAINT instructor_pk PRIMARY KEY(instructor_id)
    );

    CREATE TABLE SECTION
    ( section_id NUMBER(10) NOT NULL,
    student_id NUMBER(10),
    course_id NUMBER(10),
    CONSTRAINT section_pk PRIMARY KEY(section_id)
    );

    CREATE TABLE COURSE
    ( course_id NUMBER(10) NOT NULL,
    course_name VARCHAR2(20) NOT NULL,
    course_duration NUMBER(20),
    course_leader VARCHAR2(20),
    instructor_id NUMBER(10),
    CONSTRAINT course_pk PRIMARY KEY(course_id)
    );


    CREATE TABLE STUDENT
    ( student_id NUMBER(10) NOT NULL,
    student_name VARCHAR2(20) NOT NULL,
    student_surname VARCHAR2(20) NOT NULL,
    JMBG NUMBER(13) NOT NULL,
    student_adress CHAR(1),
    enrollment VARCHAR(20),
    CONSTRAINT student_pk PRIMARY KEY (student_id)
    );

    CREATE OR REPLACE PACKAGE pck_student AS no_data EXCEPTION;


    FUNCTION get_section_info (p_course_no NUMBER)RETURN VARCHAR2;

    PROCEDURE do_ispisi_instruktore (p_zip NUMBER);

    CREATE OR REPLACE PROCEDURE do_ispisi_instruktore (p_zip NUMBER) IS
    BEGIN
    SELECT instructor_id, instructor_name, instructor_surname, instructor_adress,
    instructor_zip
    FROM INSTRUCTOR
    WHERE instructor_zip = p_zip;
    --EXCEPTION
    -- WHEN no_data THEN
    --RETURN ‘Nema trazenog podatka’;
    END do_ispisi_instruktore;

    select * from instructor
    CREATE OR REPLACE FUNCTION get_section_info (p_course_no NUMBER) RETURN VARCHAR2 IS

    info NUMBER;

    BEGIN
    SELECT COUNT(*)
    INTO info
    FROM * SECTION
    WHERE course_id = p_course_no;

    IF info IS NULL THEN
    RETURN ‘Ne postoji’;
    ELSE
    RETURN ‘Postoji’;
    END IF;
    END get_section_info;
    END;
    BEGIN
    SELECT COUNT(*)
    FROM STUDENT
    WHERE enrollment = '1';

    END;


    END pck_student;

    declare
    i number;
    begin
    for i in 4..7 loop
    insert into student(student_id,student_name,student_surname,jm bg,enrollment)
    values (i,'kuzma','f','12','0');
    end loop;
    end;
    select * from student
    declare
    rezultat char (20 char);
    begin
    rezultat := get_section_info(1);
    end;
    select get_section_info(1) from section


    ORA-24344: success with compilation error

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You didn't pay enough attention to packages' syntax. First, create a package specification and name all program units in there. Then, create a package body that contains actual code for these program units. Something like this:
    Code:
    create or replace package my_pkg as
      function fun_1 return number;
      procedure prc_2;
    end my_pkg;
    
    create or replace package body my_pkg as
      function fun_1 return number is
        begin
          return (0);
        end fun_1;
    
      procedure prc_2 is
        begin
          null;
        end prc_2;
    end my_pkg;
    You don't use CREATE PROCEDURE nor CREATE FUNCTION within the package (body; not to mention its specification). You can't use "dummy" SELECT statements within the package (such as your SELECT * FROM INSTRUCTOR).

    Do some reading: PL/SQL User's Guide and Reference, Application Developer's Guide - Fundamentals.

  3. #3
    Join Date
    Nov 2011
    Posts
    2
    thank you

Posting Permissions

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