Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Procedure Compilation error

    Running this script:

    create or replace procedure SP_LOAD_CHIP_STATUS_REPORT()
    as

    BEGIN
    Truncate table ISGCOSDEV.TBL_CHIP_CHIP_STATUS_REPORT REUSE STORAGE;

    insert into ISGCOSDEV.TBL_CHIP_CHIP_STATUS_REPORT
    (N_STATUS_REP_ID,
    C_SERVICE_REQ,
    ...--(columns removed)
    D_EST_COMPLETE_DATE,
    D_LAUNCH_DATE)
    select N_STATUS_REP_ID,
    C_SERVICE_REQ,
    ...--(columns removed)
    D_EST_COMPLETE_DATE,
    D_LAUNCH_DATE
    From ISGCOSDEV.STG_CHIP_CHIP_STATUS_REPORT;

    END;

    ...yields the warning "Procedure created with compilation errors."

    When I execute the procedure, I get this error:
    "object ISGCOSDEV.SP_LOAD_CHIP_STATUS_REPORT is invalid"

    ...but the internal code runs fine on its own.

    This has to be simple, but I cannot find the answer in my reference manuals. Thanks for your help.
    Last edited by blindman; 07-16-04 at 11:51.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    A couple of things:

    1) In a procedure, you cannot execute a TRUNCATE within a procedure.
    2) Your SELECT statement requires that you select INTO something.

    JoeB

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK with the truncate issue. Do I have to use DELETE instead?

    Regarding SELECT..INTO, why is that not an issue when I run the code stand-alone?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    You don't need to select into. Obviously, "insert into ...select" is fine. Also, after it fails to compile, type "show errors" to find out what is wrong.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It was the truncate statement. Thanks all!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If you need the truncate within the procedure, you can do it with EXECUTE IMMEDIATE:
    Code:
    ...
      execute immediate 'truncate table xxx ...';
    ...

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thanks for the tip. Are the any dangers using truncate, such as leaving the database in an unrecoverable state? What are the "best practices" in Oracle?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I'm not aware of any such issues - but then I'm not a DBA. However, you do need to be aware that TRUNCATE, being DDL, performs an implicit COMMIT - so it can't be rolled back afterwards, and any DML performed prior to the TRUNCATE will be committed also.

  9. #9
    Join Date
    Apr 2004
    Posts
    246
    You're not a DBA!?! I've always wondered what specific thing people think should define a "DBA" - passing the class maybe. I've been using Oracle for 18 years, and I call myself a DBA or developer, whatever the client wants, although I've been certified for anything. To me, the lines are too blurred to pigeon hole ourselves. And based on previous answers of yours, you know more about the internals of Oracle than most DBA's I've met.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Thanks for the compliment! Well, I have never in my career occupied the position of DBA, and I know precious little about the pure administration side of Oracle: backups, recovery, file management, (whatever else they do!), ...

    I do know more about database design concepts, which is also often considered to be the DBA's domain, and is often a total mystery to Oracle developers. And I have learned more in the last 2-3 years by trying to answer questions on this and other forums than in the previous 10+ years!

Posting Permissions

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