Loading ...
Sorry, an error occurred while loading the content.

RE: [toad] procedure compile problem

Expand Messages
  • Mueller, Stephan
    Hi, what about showing the compilation error? You may find it either by browsing the schema (Database - Schema Browser - Tab-Procs - Expand Procedures -
    Message 1 of 38 , Nov 30, 2006
    • 0 Attachment
      Hi, what about showing the compilation error?
      You may find it either by browsing the schema
      (Database -> Schema Browser -> Tab-Procs -> Expand Procedures -> click
      your procedure -> click Errors-Tab)

      regards

      -----Original Message-----
      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of
      hstandley2003
      Sent: Thursday, November 30, 2006 3:46 PM
      To: toad@yahoogroups.com
      Subject: [toad] procedure compile problem

      I am not sure if this the right forumn but let me give it a shot.

      I am trying to create a proc and call it from crystal reports. I have
      tried some simple procs and of course you have to have a package as well
      for crystal to be able to use it.

      In any case if I try to create a proc that does more than simple stuff
      the proc will not compile. Fortunately TOAD let's me know where it dies
      but not why it dies.

      I am sending my proc so you can look at it and help me identify why it
      does not compile. If I just put the select statement in query analyzer
      it works fine. It just will not compile so I can call it from crystal.

      Any help would be greatly appreciated.

      Harry

      CREATE OR REPLACE PROCEDURE RPT_TUB_PROC ( DIVISION_CUR IN OUT
      RPT_TUB_PACKAGE.TEST_TYPE, DIVISION_PARAM IN VARCHAR2
      )

      AS

      BEGIN
      OPEN DIVISION_CUR FOR

      SELECT
      T.CUST_CODE,
      T.CUST_SHIPTO_CODE,
      T.SHIP_FROM_WHSE,
      TI.COLOR,
      T.TRANS_TYPE_CODE,
      C.NAME,
      T.TRANS_DATE,
      T.DOCUMENT_CODE,
      T.QTY,
      TI.TYPE_CODE,
      T.ITEM,
      T.DESCR,
      W.RPT_WHSE_CODE
      FROM
      TUBS T,
      CUSTOMER_SHIPTO C,
      TUB_ITEM TI,
      WAREHOUSE W

      WHERE
      W.RPT_WHSE_CODE = DIVISION_PARAM AND
      T.CUST_CODE = C.CUST_CODE AND
      T.CUST_SHIPTO_CODE =
      C.CUST_SHIPTO_CODE AND
      T.ITEM=TI.ITEM AND
      T.SHIP_FROM_WHSE=W.WHSE_CODE


      GROUP BY
      T.CUST_CODE;

      END RPT_TUB_PROC;
      /
    • Norman Dunbar
      Morning Roger, ... in the ... Yes. Remember this is not a cursor that is being passed but a REF cursor - basically, it s a memory address. When you close the
      Message 38 of 38 , Jan 9, 2007
      • 0 Attachment
        Morning Roger,

        >> Question Norm: Would that actually close the cursor that was opened
        in the
        >> called procedure itself?
        Yes. Remember this is not a cursor that is being passed but a REF
        cursor - basically, it's a memory address. When you close the cursor
        within the function/procedure, you are effectively closing the cursor
        that was passed into the function/procedure and not some arbitrary
        cursor defined within the function/procedure. Remember, the code looks
        like this :

        SQL> create or replace procedure norm(pCursor out sys_refcursor) as
        2 begin
        3 open pCursor for select to_char(sysdate,'dd/mm/yyyy hh24:mi:ss')
        from dual;
        4 exception
        5 when others then raise;
        6 end;
        7 /
        Procedure created.

        So the above code doesn't close the cursor, it assumes that it will be
        closed when called. A test proc to test the above is as follows :

        SQL> create or replace procedure test_norm as
        2 vTestCursor sys_refcursor;
        3 vResult varchar2(100);
        4 begin
        5 norm(vTestCursor);
        6 fetch vTestCursor into vResult;
        7 dbms_output.put_line(vResult);
        8 exception
        9 when others then raise;
        10 end;
        11 /
        Procedure created.

        The above test code doesn't close the cursor (vTestCursor) which is
        opened by the norm() procedure. Now, I think that PL/SQL will actually
        close an open cursor when you next try to open it (need to check) but, I
        also think that I should be explicit in my code - anyone reading it
        should be able to see what it is doing, so I would rewite the code as
        follows :

        SQL> create or replace procedure norm(pCursor out sys_refcursor) as
        2 begin
        3 if (pCursor%isopen) then
        4 close pCursor;
        5 end if;
        6 open pCursor for select to_char(sysdate,'dd/mm/yyyy hh24:mi:ss')
        from dual;
        7 exception
        8 when others then raise;
        9 end;
        10 /
        Procedure created.

        Now, the existing cusror will be closed whenever it is passed to the
        NORM() procedure. And, because the parameter is a SYS_REFCURSOR, that
        means that it is the passed in cursor which is being closed and not some
        temporary cursor named pCursor in the procedure itself.


        >> Considering scope, one would think that would close the cursor you
        used as a
        >> variable, but not the cursor that populated your variable.
        See above.


        >> Never mind, must be those brain cells that need to wake up from two
        weeks
        >> holidays.
        I have the same problem here - and I only had 10 days off ! :o)



        >> The calling procedure passes a cursor which is then used by the
        called
        >> procedure to populate. In that event it makes sense that closing it
        in the
        >> calling procedure would work.
        As ever, it depends. The NORM() procedure above could have been left as
        the originally written version (without the close) byut the TEST_NORM()
        procedure would have been rewritten as follows :

        SQL> create or replace procedure test_norm as
        2 vTestCursor sys_refcursor;
        3 vResult varchar2(100);
        4 begin
        5 if (vTestCursor%isopen) then
        6 close vTestCursor;
        7 end if;
        8 norm(vTestCursor);
        9 fetch vTestCursor into vResult;
        10 close vTestCursor;
        11 dbms_output.put_line(vResult);
        12 exception
        13 when others then
        14 close vTestCursor;
        15 raise;
        16 end;
        17 /
        Procedure created.

        Being a 'lazy' developer - and extremely bad typist - I like to reduce
        the amount of typing I have to do. I now need to code this IF test in
        every procedure/function/package that calls the NORM() procedure - so my
        most recent version of that (whihc closes the cursor if it is open) is
        better for my way of thinking.


        >> Not sure I'd want to use it though.
        No worries !

        >> The only real benefit I could see would
        >> be to be able to call the exact same cursor from two different
        procedures.
        Code reuse - a very good thing. Especially if you ever have to amend
        it. You fix it in one place and it 'works' for all callers.


        >> That raises it's own issues such as what if one procedure has a
        business
        >> rule change that would affect the cursor but the second one doesn't.

        Application error. Your business rules should be defined at the
        database and not at the application. This has the 'side effect;' of
        allowing the data to be used by any (other) application that is needed
        and not just by the one the creates it. Remember, data is the most
        important thing - not the application.


        >> It also raises the issue of: what if a developer that wants to use
        that cursor
        >> forgets to code in the close as the cursor is handled elsewhere.
        I think it will be closed when you next try to open it. But by having
        the close in the procedure/function that opens and loads it with data,
        you don't have to worry about developers closing it (or not). It is
        handled and handled in one place. Again, maintenance is easier.


        >> Ah well.... Ya live and try to learn ;)
        We ceratainly do! (or should!)


        Cheers,
        Norm. [TeamT]

        Norman Dunbar.
        Contract Oracle DBA.
        Rivers House, Leeds.

        Internal : 7 28 2051
        External : 0113 231 2051


        Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

        We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.

        We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

        If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
      Your message has been successfully submitted and would be delivered to recipients shortly.