CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Tunnelblick
Tunnelblick

Reputation: 147

Oracle SQL: Stored procedure - object invalid

I was just about to create a store procedure and it worked so far. (For learning purposes I want to hand a credit card number over to a stored procedure which should return the associated customer identification number.) But when I wanted to test this procedure using 

BEGIN CC_TO_CID(:p1, :p2); 
END;

(the input data was submitted via a dialogue of my SQL IDE) it just returned:

SQL Error [6550][65000]: ORA-06550: Row 1, Column 7:
PLS-00905 Object xyz.CC_TO_CID is invalid
ORA-06550: Row 1, Column 7:
PL/SQL: Statement ignored

This basically means that my stored procedure isn't well formatted but I really don't have any clue.

My stored procedure: 

CREATE OR REPLACE PROCEDURE CC_TO_CID(in_cc_nr IN NUMBER(16,0), out_cid OUT NUMBER) IS
BEGIN
SELECT PM.CUSTOMER_ID INTO cid FROM "2_PAYMENT_M" PM, 
"2_CREDITCARD" CC
WHERE CC.CC_NR=in_cc_nr AND CC.PAYMENT_M_NR=PM.PAYMENT_M_NR;
END;

My table structure with some test data:

Table: "2_CREDITCARD"
       CC_NR         PAYMENT_M_NR       NAME       CVV         EXPIRES        
 ------------------ -------------- -------------- ----- --------------------- 
  5307458270409047              1   Haley Harrah    52   2019-11-01 00:00:00  



Table: "2_PAYMENT_M"
  PAYMENT_M_NR   CUSTOMER_ID         CREATED         TRANSACTION_LIMIT  
 -------------- ------------- --------------------- ------------------- 
             1           100   2018-01-21 00:00:00               1.000

Thanks in advance! I appreciate any useful hints.

Upvotes: 1

Views: 6304

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

You will have seen an error when you compiled the procedure, but it would probably have been quite generic. Your client may support show errors, or you can query the user_errors view to see the details.

You can’t give a size or precision restriction for the data type of a formal parameter to a function or procedure, so NUMBER(10,0) should just be NUMBER; and you have got the name of the argument wrong in your into clause.

CREATE OR REPLACE PROCEDURE CC_TO_CID(in_cc_nr IN NUMBER, out_cid OUT NUMBER) IS
BEGIN
  SELECT PM.CUSTOMER_ID
  INTO out_cid
  FROM "2_PAYMENT_M" PM
  JOIN "2_CREDITCARD" CC
  ON CC.PAYMENT_M_NR=PM.PAYMENT_M_NR
  WHERE CC.CC_NR=in_cc_nr;
END;

I’ve switched to ANSI join syntax because... well, just because. Untested as I don’t have your tables; if it still gets errors then check user_errors again.

Upvotes: 2

Related Questions