Max. number of characters in the Command Window

[F. van Boven]

Hi,

we've discovered that the command windows has got a maximum number of characters, close to 2000.

This is discovered when trying to create a (huge) Stored Procedure.

Is this correct? Ans will it remain this way, or can it be increased?

 

[ScottMattes]

Why use the Command Window, the Program Window is MUCH, MUCH better for that.

 

[Marco Kalter]

There is indeed a maximum number of characters per line. For programming/readibility reasons I would recommend to use shorter lines. There should never be a reason for a line with 2000 characters.

 

[F. van Boven]

Tha'ts just what I wanted to know...

I added a Stored Procedure using the command line. One with more then 2000 characters

 

[F. van Boven]

update:

We want to execute a script (created by powerdesigner) to add all our views/functions/procedures to the database.

For this we use the Command window. Is there a better way to execute scripts with statements longer than 2000 characters?

 

[ScottMattes]

In the Command Window, switch to the Edit tab, open the file you want to execute and run Edit / PLSQL Beautifier from the menu. Then save the results back to the file (or better, a different file) and use that.

 

[mike]

If the beautifier doesn't parse lines that long, here are some things I would try in this order. Maybe the beautifier does, I don't know.

1. There is always good old SQL Plus that I would think would work just fine for executing a script like this.

2. Instead of opening the file and pressing F8, maybe just execute it in the command window with @filename.sql like you would using SQL Plus instead?

3. Open the file in a text editor and manually break it up a bit.

4. Call into work sick on the day this script needs to be run and later ask what worked. OK - maybe not .

Mike

 

[F. van Boven]

I'll try the @filename thingy...

Running the beautifier or breaking up is not really an option since the procedures are written in our own layout. I'd rather not break up this code.

Is there a reason the command windows supports only up to 2000 characters? Why not increase the size of the lines?

 

[F. van Boven]

*KICK*

Retry:
Is there a reason the command windows supports only up to 2000 characters? Why not increase the size of the lines?

We currently are not ably to succesfully create some Views/Procedures using the scripts created by PowerDesigner...

Running the Beatifier, of removing all spaces that can be missed (What we do currently) is no option, because we want to keep the code readable (We have our Coding Standard, and like to add (lots of) comments to it).

 

[Marco Kalter]

It's a limitation of the editor component that is used. It's the limit of one single line though, not for the complete text. Lines of more than 2000 characters are unreadable, so it doesn't seem like an unreasonable limit.

 

[F. van Boven]

The code is definitly readable, because we use blank lines and comments...

Example :

create or replace view van_vw_orderstopack as
select distinct
  o.ORDERID,
  o.ORDERDATETIME,
  c.FULLNAME,
  o.PAYMENTMETHODID,
  p.PAYMENTMETHOD,
  t.TOTALINVAT,
  s.SHOPID,
  s.SHOP
from
  VAN_ORDER o -- Driving table
  inner join VAN_SHOPOUTLET so on so.SHOPOUTLETID = o.SHOPOUTLETID -- Needed for shop name
  inner join VAN_SHOP s on s.SHOPID = so.SHOPID
  inner join VAN_VW_ORDERTOTALAMOUNT t on t.ORDERID = o.ORDERID -- Needed to check if order is paid
  inner join VAN_VW_CUSTOMER c on c.CUSTOMERID = o.CUSTOMERID -- For customer name
  inner join VAN_PAYMENTMETHOD p on p.PAYMENTMETHODID = o.PAYMENTMETHODID -- For payment method description
  inner join VAN_VW_ORDERPAYMENTCHECKSTATE sc on sc.ORDERID = o.ORDERID -- To check if all payment checks are done
where
  -- Must not be deleted
  o.DELETED = VAN_CONST.BOOLEAN_FALSE and

  -- Must be allowed to be packed
  o.ALLOWPACKING = VAN_CONST.BOOLEAN_TRUE and

  -- Must be shipped by carrier
  o.SHIPMENTMETHODID = VAN_CONST.SHIPMENTMETHOD_CARRIER and

  -- Must not already being packed
  o.ORDERID not in (select ORDERID from VAN_PACKBATCHSHIPMENTORDER) and

  -- Must be fully paid or a COD, credit
  (  (o.PAYMENTMETHODID in (select PAYMENTMETHODID from VAN_VW_CASHONDELIVPAYMENTMETH)) or
     (o.PAYMENTMETHODID in (select PAYMENTMETHODID from VAN_VW_CREDITPAYMENTMETH)) or
     (t.TOTALINVAT - t.TOTALPAID <= 0) or
     ( (select a.TOTALINVAT - a.TOTALPAID from VAN_VW_INVOICEORDERTOTALAMOUNT a where a.ORDERID = o.ORDERID) <= 0) ) and

  -- VAT number must be validated and is valid
  (  (case when (c.COUNTRYID = VAN_CONST.COUNTRY_BE) and (o.VATFREE = VAN_CONST.BOOLEAN_TRUE) then
        VAN_CONST.BOOLEAN_TRUE
      else
        VAN_CONST.BOOLEAN_FALSE
      end = VAN_CONST.BOOLEAN_TRUE and c.VATNUMBERVALID = VAN_CONST.BOOLEAN_TRUE) or
     (case when (c.COUNTRYID = VAN_CONST.COUNTRY_BE) and (o.VATFREE = VAN_CONST.BOOLEAN_TRUE) then
        VAN_CONST.BOOLEAN_TRUE
      else
        VAN_CONST.BOOLEAN_FALSE
      end = VAN_CONST.BOOLEAN_FALSE)
  ) and

  -- Must be allowed to ship on this date
  (  (o.SHIPDATE <= sysdate) or
     (o.SHIPDATE is null)  ) and

  -- All payment checks must be OK
  ( (sc.CHECKOK =  sc.CHECKCOUNT) or (sc.ORDERID is null) ) and

  -- Orders with orderlines containing "Stockproducts"
  ( (select
       count(*)
     from
       VAN_ORDERLINEITEM oli
       inner join VAN_ORDERLINE ol on ol.ORDERLINEID = oli.ORDERLINEID
       inner join VAN_PRODUCT p on oli.PRODUCTID = p.PRODUCTID
     where
       ol.ORDERID = o.ORDERID and
       p.STOCKPRODUCT = VAN_CONST.BOOLEAN_TRUE)
  ) > 0 and

  -- Quantity of products ordered must be greater than the quantity delivered
  (  (select
        nvl(sum( (select
                    sum(oli.QUANTITY)
                  from
                    VAN_ORDERLINEITEM oli
                  where
                    oli.ORDERLINEID = dol.ORDERLINEID) ), 0)
      from
        VAN_ORDER do
        inner join VAN_ORDERLINE dol on dol.ORDERID = do.ORDERID
      where
        do.ORDERID = o.ORDERID) > -- Ordered
     (select
        nvl(sum( (select
                    sum(dl.QUANTITY)
                  from
                    VAN_DELIVERYLINE dl
                    inner join VAN_ORDERLINEITEM oli on oli.ORDERLINEITEMID = dl.ORDERLINEITEMID
                  where
                    oli.ORDERLINEID = dol.ORDERLINEID) ), 0)
      from
        VAN_ORDER do
        inner join VAN_ORDERLINE dol on dol.ORDERID = do.ORDERID
      where
        do.ORDERID = o.ORDERID) -- Delivered
  )

(PS: Please no comments about the actual SQL statement...)

 

[Marco Kalter]

It is only now that I understand the problem. The issue is caused by the fact that an empty line terminates the input, just like in SQL*Plus. You can either remove these empty lines, or replace them with a line with an empty comment, or use the SQL Window to edit views.