Correcting a stored procedure
ERROR at line 1: ORA-06550: line 1, column 7: PLS-00905: object NMPKG.sp_s_DetailFreightReport is invalid ORA-06550: line, column 7: PL/SQL: Statement ignored CREATE OR REPLACE PROCEDURE sp_s_DetailFreightReport ( STORE_NO IN VARCHAR2(10), FROM_DATE IN VARCHAR2(10), TO_DATE IN VARCHAR2(10)) IS BEGIN SELECT PM.SEND_FREQ_ADDR.NAME, SN.IDATA_SHIPMENTS.CARRIER, SN.IDATA_SHIPMENTS.PO_NUM, SN.IDATA_SHIPMENTS.S2NAME1, SN.IDATA_SHIPMENTS.S2ADD1, SN.IDATA_SHIPMENTS.S2ZIP, SN.IDATA_SHIPMENTS.FLEX41 FROM PM.SEND_FREQ_ADDR, SN.IDATA_SHIPMENTS WHERE PM.SEND_FREQ_ADDR.STORE_NO = STORE_NO AND SN.IDATA_SHIPMENTS.FLEX11 = PM.SEND_FREQ_ADDR.STORE_NO AND SN.IDATA_SHIPMENTS.SHIPDATE BETWEEN FROM_DATE AND TO_DATE ORDER BY SN.IDATA_SHIPMENTS.CARRIER, SN.IDATA_SHIPMENTS.PO_NUM END sp_s_DetailFreightReport; /
Any clues as to the cause of the error?
Your procedure is invalid and needs to be successfully compiled before you can execute it. First of all you need to SELECT the columns into variables (remember: this is a PL/SQL procedure and not SQL) that you need to define in the procedure.
Secondly, a semi-colon is required at the end of your SQL statement in the procedure.
Finally, please note that this procedure does not do anything at this time, other than executing the SQL statement and getting the data into the defined variables. You need to manipulate the data or send it outside the procedure with out parameters.
Another note, in the procedure below I have used table aliases T1 and T2. There is no need to use
CREATE OR REPLACE PROCEDURE sp_s_DetailFreightReport ( STORE_NO IN VARCHAR2(10), FROM_DATE IN VARCHAR2(10), TO_DATE IN VARCHAR2(10)) IS V_NAME PM.SEND_FREQ_ADDR.NAME%TYPE; V_ CARRIER SN.IDATA_SHIPMENTS.CARRIER; V_PO_NUM SN.IDATA_SHIPMENTS.PO_NUM; V_S2NAME1 SN.IDATA_SHIPMENTS.S2NAME1; V_S2ADD1 SN.IDATA_SHIPMENTS.S2ADD1; V_S2ZIP SN.IDATA_SHIPMENTS.S2ZIP; V_FLEX41 SN.IDATA_SHIPMENTS.FLEX41; BEGIN SELECT T1.NAME, T2.CARRIER, T2.PO_NUM, T2.S2NAME1, T2.S2ADD1, T2.S2ZIP, T2.FLEX41 INTO V_NAME, V_ CARRIER, V_PO_NUM, V_S2NAME1, V_S2ADD1, V_S2ZIP, V_FLEX41 FROM PM.SEND_FREQ_ADDR T1, SN.IDATA_SHIPMENTS T2 WHERE T1.STORE_NO = STORE_NO AND T2.FLEX11 = T1.STORE_NO AND T2.SHIPDATE BETWEEN FROM_DATE AND TO_DATE ORDER BY SN.IDATA_SHIPMENTS.CARRIER, SN.IDATA_SHIPMENTS.PO_NUM; END sp_s_DetailFreightReport;