CODEWITHSUNDEEP
phporacle-databasecodeigniter
Saswat
Saswat

Reputation: 12836

Package created with compilation error

I am using Oracle 10g express edition with PHP codeigniter.

I am creating a package like this:-

CREATE OR REPLACE PACKAGE currentMovie
    IS
        PROCEDURE 
        currentMovieList (cdate IN VARCHAR2, movieList OUT SYS_REFCURSOR) 
        IS
          BEGIN
            OPEN movieList FOR
            SELECT * FROM tbl_movie, tbl_language WHERE tbl_movie.language_id = tbl_language.language_id AND TO_DATE(cdate, 'YYYY-MM-DD hh24:mi:ss') BETWEEN movie_sdate AND movie_edate;
          END currentMovieList
    END currentMovie;
/

I am getting a warning: Package created with compilation error

But when I am crating the procedure separately like this:-

CREATE OR REPLACE PROCEDURE 
        currentMovieList (cdate IN VARCHAR2, movieList OUT SYS_REFCURSOR) 
    IS
        BEGIN
            OPEN movieList FOR
            SELECT * FROM tbl_movie, tbl_language WHERE tbl_movie.language_id = tbl_language.language_id AND TO_DATE(cdate, 'YYYY-MM-DD hh24:mi:ss') BETWEEN movie_sdate AND movie_edate;
        END;
/

I am getting no error. The procedure is getting saved fine.

What am I doing wrong?

Upvotes: 0

Views: 1166

Answers (1)

atokpas
atokpas

Reputation: 3351

First you need to define the procedure in a package as shown below. We just define it on the PACKAGE. The body part goes to PACKAGE BODY.

SQL> CREATE OR REPLACE PACKAGE currentMovie AS 
   PROCEDURE currentMovieList (cdate IN VARCHAR2, movieList OUT SYS_REFCURSOR); 
END currentMovie; 
/    

Package created.

Then you need to use CREATE PACKAGE BODY statement to create the body of a stored package.

SQL> CREATE OR REPLACE PACKAGE BODY currentMovie
    IS
        PROCEDURE 
        currentMovieList (cdate IN VARCHAR2, movieList OUT SYS_REFCURSOR) 
        IS
          BEGIN
            OPEN movieList FOR
            SELECT * FROM tbl_movie, tbl_language WHERE tbl_movie.language_id = tbl_language.language_id AND TO_DATE(cdate, 'YYYY-MM-DD hh24:mi:ss') BETWEEN movie_sdate AND movie_edate;
          END currentMovieList;
    END currentMovie;
/

Package body created.

Upvotes: 2

Related Questions