Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am writing a procedure, independently the queries run fine with no errors.. But, when I run it as a procedure I get errors. Given below is my procedure

    create or replace
 PROCEDURE xyz(inuserid IN NUMBER,
                                    invType IN varchar2,
                                    curDN out sys_refcursor,
                                    curDNDetail out sys_refcursor,
                                    curgenDN out sys_refcursor,
                                    curgenDNDetail out sys_refcursor) AS
     varstocks_id  varchar2;                                 
   BEGIN
    IF invType = 'OUTDN' THEN
     OPEN curDN for
  SELECT DISTINCT a.orders_id,
                  a.location
                  || Decode(Length(a.financial_year), 1, '0'
                                                         || a.financial_year,
                                                      a.financial_year)
                  || '-'
                  || a.order_no AS orderNo,
                  b.display_name  AS requestor,
                  a.order_date
  FROM   orders a,
         users b,
         users c
  WHERE  a.initiator_id = b.users_id
         AND a.first_users_id = c.users_id
         AND a.status_id > 80
         AND dispatch_note = 1
         AND stocks_id IN (SELECT DISTINCT a.stocks_id
                           FROM   users_stocks a,
                                  stocks b
                           WHERE  a.stocks_id = b.stocks_id
                                  AND b.nmfg_site = 1

                                  AND a.orders_id NOT IN (SELECT orders_id
                                                          FROM   dispatch_note)
                                  AND users_id = inuserid)
  ORDER  BY orders_id DESC;
  open curDNDetail for
  SELECT b.orders_id,
         c.incoterms,
         d.shipment_mode,
         b.hawb,
         b.location
         || Decode(Length(b.financial_year), 1, '0'
                                                || b.financial_year,
                                             b.financial_year)
         || '-'
         || b.order_no AS my_order      FROM   orders b,
         incoterms c,
         shipment_mode d
  WHERE  b.prba = c.sap_prba
         AND b.shipment_mode_id = d.shipment_mode_id;  

    elsif invType = 'GENDN' THEN

    SELECT default_shipping_location
    INTO   varstocks_id
    FROM   (SELECT DISTINCT stocks_id AS default_shipping_location       
            FROM   users_stocks
            WHERE  users_id =inuserid
            UNION
            SELECT default_shipping_location 
            FROM   users a
            WHERE  a.users_id = inuserid)T1;  
     open curgenDN for
    SELECT DISTINCT a.orders_id   AS orders_id,
                    a.location
                    || Decode(Length(a.financial_year), 1, '0'
                                                           || a.financial_year,
                                                        a.financial_year)
                    || '-'
                    || a.order_no AS orderNo,
                    b.dn_no,
                    Trim(Substr(c.display_name, 0, Instr(c.display_name, '(') - 1))
                    || CASE
                         WHEN d.display_name <> c.display_name THEN ' / '
                                                                    || Trim(
                         Substr(d.display_name, 0, Instr(d.display_name, '(') - 1))
                         ELSE ''
                       END        AS requestor
    FROM   orders a,
           dn_customs_inv_orders b,
           dispatch_note e,
           users c,
           users d
    WHERE  a.orders_id = b.orders_id
           AND ( a.status_id > 80 )
           AND a.dispatch_note = 1
           AND a.initiator_id = c.users_id
           AND b.orders_id = e.orders_id
           AND a.first_users_id = d.users_id
           AND a.stocks_id IN ( varstocks_id )--used the variable initialized by             above select statement
    ORDER  BY orders_id DESC; 
     OPEN curDNDetail for
    SELECT b.orders_id,
           c.incoterms,
           d.shipment_mode,
           b.hawb,
           To_char(a.delivery_date, 'dd-mm-yyyy')    AS delivery_date,
           a.fwd_no,
           a.fwd_name,
           a.fwd_address,
           a.package_type,
           a.no_of_package_type,
           a.gross_weight,
           a.dimension,
           a.dn_no,
           To_char(a.dn_creation_date, 'dd-mm-yyyy') AS dn_creation_date
    FROM   orders b,
           incoterms c,
           shipment_mode d,
           dn_customs_inv_orders a
    WHERE  b.prba = c.sap_prba
           AND b.shipment_mode_id = d.shipment_mode_id
           AND b.orders_id = a.orders_id;  
 END IF;
  END xyz;

Given below are the errors which I get.

          Error(7,15): PLS-00215: String length constraints must be in range (1 .. 32767)

Thanks in advance.

share|improve this question
2  
Show us the complete error message (run show errors to see the details) –  a_horse_with_no_name Feb 6 at 8:44
    
Hi , I have added the errors. Please go through. –  Rajesh Feb 6 at 9:05
    
I have edited the question with fewer errors, which might be easy to solve for masters out there. –  Rajesh Feb 6 at 9:22
    
You have a missing semi-colon the 3rd line from the end. Wan't Error(115,48): PL/SQL: ORA-00933: SQL command not properly ended with the exact line number a bit of a hint? :P –  FreshPhilOfSO Feb 6 at 9:25
2  
varstocks_id varchar2; - the data type needs a length. Again, it gives the line number.... –  FreshPhilOfSO Feb 6 at 10:00
show 2 more comments

closed as off-topic by Marian, Paul White, RolandoMySQLDBA, Max Vernon, Mark Storey-Smith Feb 7 at 13:04

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Marian, Paul White, RolandoMySQLDBA, Max Vernon, Mark Storey-Smith
If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

up vote 3 down vote accepted

Several errors:

varstocks_id  varchar2;                                 

The VARCHAR2() datatype requires a data length.

.. as indicated by the error message Error(7,15): PLS-00215: String length constraints must be in range (1 .. 32767), which tells you the error is on line 7.

The 3rd from last line:

AND b.orders_id = a.orders_id

requires a semi-colon at the end, as indicated by the error message:

Error(115,48): PL/SQL: ORA-00933: SQL command not properly ended

... which tells you that the SQL command that ends on line number 115 is not properly ended.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.