This discussion is archived
5 Replies Latest reply: Jun 21, 2013 1:40 PM by Jim Smith RSS

Error in procedure

Murray Sobol Newbie
Currently Being Moderated


I am trying to create this procedure:

create or replace
PROCEDURE fw_sav_query_detail
   (as_user_id                 IN  varchar2,
    as_name_and_address_id     IN  varchar2,
    as_settlement_type         IN  char,
    as_io_flag                 IN  char,
    as_ap_interface            IN  varchar2,
    as_payment_status          IN  varchar2,
    as_name_address_filter     IN  varchar2,
    as_location_id             IN  varchar2,
    an_settlement_nbr          IN  number,
    as_check_no                IN  varchar2,
    as_commodity_id            IN  varchar2,
    ad_from_date               IN  date,
    ad_to_date                 IN  date,
    as_reference_id            IN  varchar2,
    ad_ngr_due_date            IN  date,
    an_page_no                 IN  number,
    an_page_size               IN  number,
    result_set                 IN
                               OUT PagingUtility.PagedResultType,
    query                      IN  long,
    order_by                   IN  long
   )
AS
   lv_user_id                  varchar2(40);
   lv_name_and_address_id      varchar2(10);
   lc_settlement_type          char(1);
   lc_io_flag                  char(1);
   lv_ap_interface             varchar2(3);
   lv_payment_status           varchar2(10);
   lv_name_address_filter      varchar2(50);
   lv_location_id              varchar2(10);
   ln_settlement_nbr           number;
   lv_check_no                 varchar2(10);
   lv_commodity_id             varchar2(10);
   ld_from_date                date;
   ld_to_date                  date;
   lv_reference_id             varchar2(28);
   ld_ngr_due_date             date;
   ln_page_no                  number;
   ln_page_size                number;
   ln_session_nbr              number;
   initstmt                    long;
   endstmt                     long;
   startpos                    number;
   endpos                      number;
   rowstmt                     long;
   page_no                     number;
   page_size                   number;

BEGIN

   lv_user_id                  := as_user_id;
   lv_name_and_address_id      := as_name_and_address_id;
   lc_settlement_type          := as_settlement_type;
   lc_io_flag                  := as_io_flag;
   lv_ap_interface             := as_ap_interface;
   lv_payment_status           := as_payment_status;
   lv_name_address_filter      := as_name_address_filter;
   lv_location_id              := as_location_id;
   ln_settlement_nbr           := an_settlement_nbr;
   lv_check_no                 := as_check_no;
   lv_commodity_id             := as_commodity_id;
   ld_from_date                := ad_from_date;
   ld_to_date                  := ad_to_date;
   lv_reference_id             := as_reference_id;
   ld_ngr_due_date             := ad_ngr_due_date;
   ln_page_no                  := an_page_no;
   ln_page_size                := an_page_size;

   EXECUTE IMMEDIATE '
   CREATE global TEMPORARY TABLE tmpret
      (location_short_name         varchar2(10),
       vendor_name                 varchar2(50),
       commodity_id                varchar2(10),
       serial_nbr                  number,
       settlement_date             date,
       amount                      number(12,2),
       settlement_type             char(1),
       settlement_status           varchar2(10),
       query_mode                  varchar2(3),
       void_nbr                    number,
       paid_by                     varchar2(10),
       settlement_nbr              number,
       settlement_location         varchar2(10),
       gs_print_flag               char(1),
       check_void_flag             char(1),
       check_status                char(1),
       payment_nbr                 number,
       check_date                  date,
       intended_check_date         date,
       settlement_date_temp        date,
       null_date                   number,
       payment_method              varchar2(10),
       ngc_flag                    char(1),
       gs_export_status            char(1),
       quantity                    number(24,8),
       uom_code                    varchar2(10),
       no_of_decimals              number,
       currency_code               varchar2(10),
       currency_description        varchar2(50),
       exchange_rate               number(20,10),
       quantity_conv               float,
       no_of_decimals_conv         number,
       uom_conv_code               varchar2(10),
       vendor_id                   varchar2(10),
       reference_id                varchar2(28),
       third_party_nbr             number,
       third_party_void_flag       char(1),
       payment_count               number,
       ngr_due_date                date,
       payment_status              varchar2(10),
       email_date                  date,
       email_user_id               varchar2(40),
       base_currency_amount        number(20,5),
       status_code                 varchar2(10),
       check_no                    varchar2(10),
       advice_nbr                  number,
       printed_status              number,
       reconciliation_status       char(1),
       producer_statement_file_name varchar2(10)
      );
   ';

   IF (ln_page_no = 0) OR
      (ln_page_size = 0) THEN
       endpos   := (page_no * page_size);
       startpos := (endpos - page_size) + 1;

       initstmt := 'SELECT *
                      FROM (SELECT a.*,
                                   ROWNUM row_no
                              FROM (SELECT *
                                      FROM ('||query||'
                                     ORDER BY :ordr
                                   ),
                                   (SELECT COUNT(*) AS tot_rows
                                      FROM ('|| query ||')
                                   )
                                   )a
                           )
                     WHERE row_no BETWEEN :st AND :en';

       OPEN RESULT_SET for initstmt USING order_by,
                                          startpos,
                                          endpos;

   ELSE
       endpos   := (page_no * page_size);
       startpos := (endpos - page_size) + 1;

       initstmt := 'WITH group_data AS
                    (SELECT *
                       FROM (SELECT a.*,
                                    ROWNUM row_no
                               FROM (SELECT *
                                       FROM ('||query||'
                                      ORDER BY :ordr
                                    ),
                                    (SELECT COUNT(*) AS tot_rows
                                        FROM ('|| query ||')
                                    )
                                    )a
                            )
                      WHERE row_no BETWEEN :st AND :en';

       OPEN RESULT_SET for initstmt USING order_by,
                                          startpos,
                                          endpos;

   END IF;
END;

 

It compiles Ok but I get this error when I run it (debug mode):

Connecting to the database Oracle - D15.5.

Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE

Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '10.150.1.29', '4215' )

Debugger accepted connection from database on port 4215.

Exception breakpoint occurred at line 72 of FW_SAV_QUERY_DETAIL.pls.

$Oracle.EXCEPTION_ORA_911:

 

If I take the SQL that creates the temporary table out and run it in a separate session it creates the table.

 

Any assistance would be appreciated.

Murray

  • 1. Re: Error in procedure
    Blues Breaker Newbie
    Currently Being Moderated

    Not sure what the execute immediate is doing there. This is DDL stuff.

  • 2. Re: Error in procedure
    Jim Smith Expert
    Currently Being Moderated

    1)  This doesn't really have anything to do with SQL Developer.

    2) You shouldn't be creating a table in the middle of a procedure.  The whole point of global temporary tables is that they exist independently of the code and can be used by multiple sessions.

    3) execute immediate doesn't like ';' as  a statement terminator.

  • 3. Re: Error in procedure
    rp0428 Guru
    Currently Being Moderated

    Please mark the questin ANSWERED and repost it in the SQL and PL/SQL forum:

    https://forums.oracle.com/community/developer/english/oracle_database/sql_and_pl_sql

     

    You should expect a lot of negative feedback in that forum. There are so many things wrong with your code the responders there will have a field day.

     

    You shouldn't be using dynamic SQL to begin with, or be creating database objects dynamically since that means ALL queries in that code also have to be dynamic since the table doesn't exist yet.

     

    It also means the code will raise an exception the second time you execute it since you will be trying to create a table that will now already exist.

     

    And ROWNUM will not, and cannot, paginate result sets even using ORDER BY since there is no FIRST row in a table and other users may be inserting, updating or deleting rows that another user wants to paginate.

  • 4. Re: Error in procedure
    Murray Sobol Newbie
    Currently Being Moderated

    rp0428:

     

         The REAL requirement is to create a TEMPORARY table within the procedure, populate it and paginate the results.

         Trust me, the pagination stuff works when polulated with a result set.

     

         Is it possible to create a TEMPORARY table, NOT a GLOBAL TEMPORARY table within a procedure?

     

         The documentation I'm reading suggests that it is NOT possible.

     

    Murray

  • 5. Re: Error in procedure
    Jim Smith Expert
    Currently Being Moderated

    You don't need to create a temporary table in the procedure at all!

     

    Create the global temporary table outside the procedure and use it in the procedure like a normal table.  The contents of the table are private to you session.

     

    Go and read the documentation on temporary tables.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points