Getting PLS-00103: Encountered the symbol "/" error in creating packages

Question

Getting error Error(8,1): PLS-00103: Encountered the symbol "/"

Tried inserting / symbol at the end of package definition gives error -Error(9,1): PLS-00103: Encountered the symbol "CREATE" What I want is to do validation in CreateShipment and store result in array of validation and pass array to procedure 2 where I'll do the insert

create or replace package WSH_Delivery_Detail_Shipment as

type Result IS VARRAY(8) OF INTEGER;
PROCEDURE CreateShipment(p_delivery_detail_interface_id IN WSH_DEL_DETAILS_INTERFACE.DELIVERY_DETAIL_INTERFACE_ID%TYPE,p_status OUT String) ;
PROCEDURE CreateShipmentLines(v_result IN Result);

END WSH_Delivery_Detail_Shipment;
/
CREATE OR REPLACE package body WSH_Delivery_Detail_Shipment as

PROCEDURE CreateShipment(
p_delivery_detail_interface_id IN WSH_DEL_DETAILS_INTERFACE.DELIVERY_DETAIL_INTERFACE_ID%TYPE)

IS

CURSOR wddi_cur IS SELECT * FROM WSH_DEL_DETAILS_INTERFACE WHERE DELIVERY_DETAIL_INTERFACE_ID=p_delivery_detail_interface_id;
wddi_record WSH_DEL_DETAILS_INTERFACE%ROWTYPE;
type Result IS VARRAY(8) OF INTEGER;
v_result result:=result();
l_uniqueRecords INTEGER;
l_organizationId INTEGER;
l_actionType INTEGER;
l_orderType INTEGER;
l_customerNumber INTEGER;
l_orderQuantity INTEGER;
l_orderquantityUom INTEGER;
l_updateAction INTEGER;
orgId INTEGER;


BEGIN

OPEN wddi_cur;

LOOP

FETCH wddi_cur into wddi_record;/* Validation2 : Check if Organization Id exists and store Organization_Id corresponding to Organization_Code*/
EXIT when wddi_cur%NOTFOUND;

BEGIN
select Organization_Id INTO l_organizationId from inv_org_parameters where Organization_Code=wddi_record.Organization_Code;
EXCEPTION
WHEN no_data_found THEN
l_organizationId:=0;
END;

IF (l_organizationId > 0) then
orgId:=l_organizationId;
l_organizationId:=1;
END IF;

/*validaion1 : Check for uniqueness of record with Organization_Code,Organization_Code,Organization_Id,SalesOrderNumber,SalesOrderLineNumber as unique */
SELECT COUNT(*) INTO l_uniqueRecords FROM WSH_DELIVERY_DETAILS WHERE SALES_ORDER_NUMBER=wddi_record.SALES_ORDER_NUMBER AND SALES_ORDER_LINE_NUMBER=wddi_record.SALES_ORDER_LINE_NUMBER
AND Organization_Id=orgId;

/*Validation3 : Check ActionType should be either CREATE, UPDATE, CANCEL */
IF (wddi_record.Line_Action_Type = 'CREATE' or wddi_record.Line_Action_Type = 'UPDATE' or wddi_record.Line_Action_Type = 'CANCEL') THEN
l_actionType:=1;
ELSE
l_actionType:=0;
END IF; 

/* validation 4: Check OrderType should be a valid Order Type Lookup*/
select COUNT(1) INTO l_orderType from fnd_lookups where Lookup_Code = wddi_record.Source_Line_Type AND lookup_type='ORA_WSH_ORDER_LINE_TYPE';

/*Validation5 : CustomerNumber should be a valid Party Number based on OrderType*/
select COUNT(1) INTO l_customerNumber from HZ_PARTIES where Party_id=wddi_record.Ship_To_Party_Id;

/*Validation6 : OrderQuantity should be greater than 0 when ActionType is CREATE. When ActionType is UPDATE then OrderQuantity 0 is treated as CANCEL*/
IF(wddi_record.Line_Action_Type = 'CREATE' and wddi_record.SRC_REQUESTED_QUANTITY IS NULL or wddi_record.SRC_REQUESTED_QUANTITY <= 0 ) THEN
l_orderQuantity:=0;
else 
l_orderQuantity:=1;
END IF;

/*Validation7 : OrderQuantityUOM should be a valid UOM Code in Units of Measure table*/
select COUNT(1) INTO l_orderquantityUom from inv_units_of_measure where UOM_CODE=wddi_record.SRC_REQUESTED_QUANTITY_UOM;

/*Validation8 : UPDATE action is allowed when Shipment Line Released Status is not Shipped or Interfaced.
RELEASED_STATUS != R
*/
select COUNT(1) INTO l_updateAction from  WSH_DELIVERY_DETAILS where wddi_record.Line_Action_Type = 'UPDATE' AND SALES_ORDER_NUMBER=wddi_record.SALES_ORDER_NUMBER AND SALES_ORDER_LINE_NUMBER=wddi_record.SALES_ORDER_LINE_NUMBER
AND source_shipment_number=wddi_record.Source_Shipment_Number
AND source_shipment_id=wddi_record.Source_Shipment_Id
AND Organization_Id=orgId
AND RELEASED_STATUS != 'R';


/*1 - Pass
0-Fail
*/

v_result:=result(l_uniqueRecords,l_organizationId,l_actionType,l_orderType,l_customerNumber,l_orderQuantity,l_orderquantityUom,l_updateAction);

/*PROCEDURE CreateShipmentLines(v_result);*/

FOR i in 1.. 8 LOOP
DBMS_OUTPUT.PUT_LINE(v_result(i));
END LOOP;

END LOOP;
CLOSE wddi_cur;
END CreateShipment;

/*create or replace procedure CreateShipmentLines(v_result IN res)

END CreateShipmentLines;*/
END WSH_Delivery_

Detail_Shipment;

Posting incomplete code doesn't help much, and it is difficult to debug such a code. That one won't compile; procedure declaration in package spec and body should match (in your example, they don't). If you declare a procedure in specification, it must exist in body (in your case, it doesn't). `Error(8,1)` means that the error is in line 8, column 1 (which is the first `/` we see), but - there's nothing wrong about it - it does terminate PL/SQL procedures. I'd suggest you to post reproducible code, i.e. help us help you. — Littlefoot, Jan 01 '19 at 09:25
@iminiki removing / gives error- Error(9,1): PLS-00103: Encountered the symbol "CREATE" — rahul bhandari, Jan 01 '19 at 11:35
Yes, but - you didn't put much effort in it. It is incomplete and, apparently, you didn't carefully read what I previously wrote. — Littlefoot, Jan 01 '19 at 11:56
@Littlefoot I'm not clear what you meant in above comment.Please can u be bit more specific — rahul bhandari, Jan 01 '19 at 12:16
Sure. This: `END WSH_Delivery_` is invalid ("didn't put much effort in it"). Package spec still has two procedures declared, while the body has only one. Not to mention that we don't have your tables in order to compile code you posted, so including `CREATE TABLE` statements would be a plus. — Littlefoot, Jan 01 '19 at 12:21
@Littlefoot Thanks for the above suggestion.I had commented the second procedure untill now but even after uncommenting it the issue is same.Although there's no use of Table right now but as for your reference:https://docs.oracle.com/en/cloud/saas/supply-chain-management/18c/oedsc/WSH_DEL_DETAILS_INTERFACE-tbl.html — rahul bhandari, Jan 01 '19 at 12:30
You're welcome. Also, thank you for the link. However, I'm not going to create such a table manually which means that you have a problem and I can't reproduce it. Although not a real "answer", I'm posting code which, actually, compiles and shows how that should be done. Instead of NULL procedure's bodies, put your own code in there. Do it step by step, test frequently. — Littlefoot, Jan 01 '19 at 13:33
This article resolved the issue: https://stackoverflow.com/questions/20280148/getting-error-while-executing-package — rahul bhandari, Jan 01 '19 at 15:07

Littlefoot · Answer 1 · 2019-01-01T13:50:01.270

From a comment I posted previously:

Although not a real "answer", I'm posting code which, actually, compiles and shows how that should be done. Instead of NULL procedures' bodies, put your own code in there. Do it step by step, test frequently.

SQL> CREATE TABLE wsh_del_details_interface(
  2    delivery_detail_interface_id   NUMBER
  3  );

Table created.

SQL> CREATE OR REPLACE PACKAGE wsh_delivery_detail_shipment AS
  2    TYPE result IS
  3      VARRAY(8)OF INTEGER;
  4    PROCEDURE createshipment(
  5      p_delivery_detail_interface_id   IN  wsh_del_details_interface.delivery_detail_interface_id%TYPE,
  6      p_status                         OUT STRING
  7    );
  8
  9    PROCEDURE createshipmentlines(
 10      v_result IN   result
 11    );
 12  END wsh_delivery_detail_shipment;
 13  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY wsh_delivery_detail_shipment AS
  2    PROCEDURE createshipment(
  3      p_delivery_detail_interface_id   IN  wsh_del_details_interface.delivery_detail_interface_id%TYPE,
  4      p_status                         OUT STRING
  5    )
  6    AS
  7    BEGIN
  8      NULL;
  9    END;
 10
 11    PROCEDURE createshipmentlines(
 12      v_result IN   result
 13    )
 14    AS
 15    BEGIN
 16      NULL;
 17    END;
 18  END wsh_delivery_detail_shipment;
 19  /

Package body created.

SQL>

Getting PLS-00103: Encountered the symbol "/" error in creating packages

1 Answers1