Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00103 Error while compiling a package
PLS-00103 Error while compiling a package [message #580237] Thu, 21 March 2013 16:08 Go to next message
praveen240881
Messages: 36
Registered: February 2012
Member
Hello

When i try to compile the below procedure i get the error message:
PLS-00103: Encountered the symbol 'YYYY' when expecting one of the follow values

CREATE OR REPLACE PROCEDURE PROC_PURGE_JOB_LOGS(p_year IN date) /*AUTHID current_user*/ is
    CURSOR c_job_logs IS
      SELECT job_id,
             module_id,
             log_type,
             log_text,
             error_no,
             created_date,
             created_by
        FROM job_logs;

    c_job_logs_rec job_logs%ROWTYPE;

    v_sql varchar2(32767);
    v1 date;

  begin

    execute immediate 'create table joblogstemp as select * from job_logs where 1=0';

    open c_job_logs;
    loop
      fetch c_job_logs
        into c_job_logs_rec;
      exit when c_job_logs%notfound;

      v_sql := 'insert into joblogstemp
(job_id,
    module_id,
    log_type,
    log_text,
    error_no,
    created_date,
    created_by)
    values
    (''' || c_job_logs_rec.job_id || ''',' ||
               c_job_logs_rec.module_id || ',' || c_job_logs_rec.log_type || ',' ||
               c_job_logs_rec.log_text || ',''' || c_job_logs_rec.error_no ||
               ''',''' || c_job_logs_rec.created_date || ''',' ||
               c_job_logs_rec.created_by || ')';
      execute immediate v_sql;
    end loop;
    close c_job_logs;

    execute immediate 'truncate table job_logs';
    execute immediate 'create table joblogsarchive as select * from joblogstemp where 1=0';
   e[color=red]xecute immediate 'insert into joblogsarchive (select * from joblogstemp where(created_date,'YYYY') <= 2010');[/color]
   [color=red]execute immediate 'insert into job_logs (select * from joblogstem where to_char(created_date,'YYYY') > 2010');[/color]
    execute immediate 'drop table joblogstemp';
    commit;
  end PROC_PURGE_JOB_LOGS;
Re: PLS-00103 Error while compiling a package [message #580239 is a reply to message #580237] Thu, 21 March 2013 16:22 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 413
Registered: July 2003
Location: WPB, FL
Senior Member
You forgot one of the TO_CHAR() in your statement:
. . .  om joblogstemp where   (created_date,'YYYY') <= 2010' ...
- - - - - - - - - - Here ^^^^^

[Updated on: Fri, 22 March 2013 01:37] by Moderator

Report message to a moderator

Re: PLS-00103 Error while compiling a package [message #580254 is a reply to message #580237] Fri, 22 March 2013 00:40 Go to previous messageGo to next message
zssultana
Messages: 3
Registered: March 2013
Junior Member
execute immediate 'insert into joblogsarchive (select * from joblogstemp where(created_date,'YYYY') <= 2010');


USE TO_CHAR FUNCTION HERE.
Re: PLS-00103 Error while compiling a package [message #580256 is a reply to message #580237] Fri, 22 March 2013 01:32 Go to previous messageGo to next message
dariyoosh
Messages: 513
Registered: March 2009
Location: Iran / France
Senior Member
You can also use EXTRACT in order to get the year.

Regards,
Dariyoosh
Re: PLS-00103 Error while compiling a package [message #580258 is a reply to message #580256] Fri, 22 March 2013 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Correction... you SHOULD use EXTRACT in order to get the year.

Regards
Michel
Re: PLS-00103 Error while compiling a package [message #580270 is a reply to message #580258] Fri, 22 March 2013 02:55 Go to previous messageGo to next message
dariyoosh
Messages: 513
Registered: March 2009
Location: Iran / France
Senior Member
Michel Cadot wrote on Fri, 22 March 2013 07:38
Correction... you SHOULD use EXTRACT in order to get the year.

Regards
Michel



That being said, I don't really like the following lines in his code
. . .
execute immediate 'create table joblogstemp as select * from job_logs where 1=0';
. . .
execute immediate 'drop table joblogstemp';

Well, maybe it's just a matter of personal taste, but I don't really like to see DDL inside EXECUTE IMMEDIATE. If this procedure is something that is used for log information, then I may assume that it is run almost every day/night. As a result joblogstemp would actually be a required table like other permanent schema elements. Personally I prefer to define it properly in a dedicated sql DDL script file like other tables and simply do a TRUNCATE or DELETE FROM every time the procedure is called.

Regards,
Dariyoosh
Re: PLS-00103 Error while compiling a package [message #580271 is a reply to message #580270] Fri, 22 March 2013 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If only it would be the only bad thing. This is clearly a code directly convert from SQL Server (or Sybase) to Oracle.
Actually the whole code is bad for an Oracle database.
1/ Yes, you correct, tables should not be created in PL/SQL application procedure
2/ Using execute immediate to build INSERT statements without bind variables is really the worst thing to do
3/ And this table is, in the end, useless in Oracle, all this can be done in one or a couple of SQL statements (and a code without comments is just something you can thrash).

Regards
Michel

[Updated on: Fri, 22 March 2013 03:15]

Report message to a moderator

Re: PLS-00103 Error while compiling a package [message #580278 is a reply to message #580271] Fri, 22 March 2013 03:41 Go to previous message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
As far as I can see the procedure should be a single insert/select statement followed by a single delete statement.
Which'll be less code, more efficient and less bug prone than what's been written.
Previous Topic: Exception: ORA-01438: value larger than specified precision allowed for this column
Next Topic: insert into table A with Select query on B and C
Goto Forum:
  


Current Time: Thu Apr 17 19:34:58 CDT 2014

Total time taken to generate the page: 0.11183 seconds