Q

Correcting a stored procedure

When I execute the following stored procedure, I get these errors:
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 . . in the SQL. Just the column name prefixed with the table alias and a dot will suffice. Also, try to use synonyms for table references from different schema. This makes the code more portable. For example, you should have a synonym (private or public depending on business requirements) SEND_FREQ_ADDR for PM.SEND_FREQ_ADDR and IDATA_SHIPMENTS for SN.IDATA_SHIPMENTS and so on.

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;

This was first published in June 2004

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close