CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-apex
Strax
Strax

Reputation: 11

Unexpected results with IN OUT parameter in PL/SQL procedure

One of the tasks in my Databases class is to make a format phone procedure that uses a varchar2 in out parameter that is an unformatted phone number and prints a formatted version.

Ex:

Input: 8006330575

Output: (800)633-0575

The procedure that I wrote looks good to me and compiles, but when I try to call it I get an error message saying:

ORA-06550: line 2, column 14: PLS-00363: expression '8006330575'
cannot be used as an assignment target
ORA-06550: line 2, column 1:  PL/SQL: Statement ignored


1. BEGIN
2. format_phone('8006330575');
3. END;

I've tried messing around with input types but nothing seems to work. I also tried specifying the amount of characters in the varchar2 parameter in parentheses but when I do that the procedure won't even compile.

Here is the code:

create or replace PROCEDURE format_phone 
(p_num IN OUT VARCHAR2) IS

BEGIN 

p_num := '(' || SUBSTR(p_num, 1, 3) || ')' || SUBSTR(p_num, 4, 3)
          || '-' || SUBSTR(p_num, 7);
    
DBMS_OUTPUT.PUT_LINE(p_num);

END format_phone;

Upvotes: 0

Views: 1214

Answers (1)

user5683823
user5683823

Reputation:

Do you understand how OUT and IN OUT parameters work? You can't pass in a value for an OUT or IN OUT parameter. You must pass the name of a variable, which is defined and assigned a value outside the procedure and before the procedure is called. Also, you can only inspect the modified (OUT) value outside the procedure and after the procedure is executed (although within the procedure you may print its value, that's not the point of it being an OUT parameter).

Rather, the procedure should look like this (pretty much what you had, minus printing from within the procedure):

create or replace procedure format_phone (p_num in out varchar2) is
begin
  p_num := '(' || substr(p_num, 1, 3) || ')' || substr(p_num, 4, 3)
               || '-' || substr(p_num, 7);
end format_phone;
/

Then you use it in the outer context, like this. Notice the anonimous block in which I declare a variable and assign a value to it; I print it; then I execute the procedure, and print the value of the variable again. As expected, the value changed as required by the procedure.

One small but important point is that the variable, which I called num, can't be declared varchar2(10), even though that's what the input is. The reason is that the procedure will replace it with a longer string, which would not fit in varchar2(10). Care must be taken that the variable be able to accomodate the OUT value!

declare
  num varchar2(30);
begin
  num := '8006330575';
  dbms_output.put_line('Unformatted number: ' || num);
  -- Now call the procedure; the argument is the variable name, NUM
  format_phone(num);
  dbms_output.put_line('Formatted number: ' || num);
end;
/



Unformatted number: 8006330575
Formatted number: (800)633-0575


PL/SQL procedure successfully completed.

Upvotes: 3

Related Questions