SQL and PL/SQL Error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SQL and PL/SQL Error

  1. #1
    Join Date
    Jan 2001
    Location
    St. Louis. MO
    Posts
    150

    Angry

    Can anyboby tell me why the SQL below works in SQLPLUS. but when you put it in a procedure and try to compile it, it gives an error.

    Thanks,

    Ed

    --SQL
    insert into stationlstatistics
    (select e.* from stationestatistics e
    where (select l.tottripno
    from stationlstatistics l
    where l.stncarr = e.stncarr
    and l.stncity = e.stncity
    and l.stnst = e.stnst
    and l.totacthrs = e.totacthrs
    and l.trainchgfl = e.trainchgfl) is null and
    (select sum(l2.tottripno) from stationlstatistics l2
    where l2.stncarr = e.stncarr
    and l2.stncity = e.stncity
    and l2.stnst = e.stnst
    and l2.trainchgfl = e.trainchgfl ) < 41
    );

    --PL/SQL
    create or replace procedure test_proc as
    Begin
    insert into stationlstatistics
    (select e.* from stationestatistics e
    where (select l.tottripno
    from stationlstatistics l
    where l.stncarr = e.stncarr
    and l.stncity = e.stncity
    and l.stnst = e.stnst
    and l.totacthrs = e.totacthrs
    and l.trainchgfl = e.trainchgfl) is null and
    (select sum(l2.tottripno) from stationlstatistics l2
    where l2.stncarr = e.stncarr
    and l2.stncity = e.stncity
    and l2.stnst = e.stnst
    and l2.trainchgfl = e.trainchgfl ) < 41
    );
    COMMIT;
    end;
    /



  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    what errors you get :-?

  3. #3
    Join Date
    Jan 2001
    Location
    St. Louis. MO
    Posts
    150
    It's not allowing me to compile the PROC. I am getting this error. Is it not enough the Query works on SQLPLUS?

    5/9
    PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

    ( - + mod not null others
    avg
    count current exists max min prior sql stddev sum variance
    execute cast trim forall



    11/37
    PLS-00103: Encountered the symbol "NULL" when expecting one of the following:

    set

  4. #4
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Try using cursors & variables and select data into the variables for select statements nested in the main query.

  5. #5
    Join Date
    May 2001
    Location
    Chennai
    Posts
    57
    Use Cursors & Variables...

    Its not necessary that ur query working in SQL needs to work when put in a PROC...Sometimes it may need some minor changes to get it going...


Posting Permissions

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



Click Here to Expand Forum to Full Width