CODEWITHSUNDEEP
plsqlplsqldeveloper
user544079
user544079

Reputation: 16629

pl /sql procedure execution giving an error

My stored proc is defined as

create or replace procedure TEST(
     name  IN table1.col_name%type,
     price IN table1.col_price%type
)
is
begin
    update table1  t set t.name =name where t.price = price;
commit;
end TEST;

I am trying to execute it as

exec TEST(name => 'John', price => 1000);

However, it gives invalid SQL error. What am i missing here?

Upvotes: 1

Views: 101

Answers (2)

David Aldridge
David Aldridge

Reputation: 52346

I really don't understand the variable prefixing approach. Oracle don't do it with their own API's, and it would be extraordinarily irritating if they did. It always seems like a workaround, rather than a fix.

For me the fix is to namespace the variables with the procedure name. It keeps the argument names "clean" and makes your code 100% proof against capture:

create or replace procedure TEST(
  name  IN table1.col_name%type,
  price IN table1.col_price%type)
is
begin
  update table1 t
  set    name    = test.name
  where  t.price = price;
commit;
end TEST;

Lots more info on capture here.

Upvotes: 0

stvsmth
stvsmth

Reputation: 3878

Your input parameter %type statements claim the column names are col_name and col_price. But that is not how you refer to them in your stored procedure (name and price).

Bad things can happen when you name variables after column names. AskTom recommends a limited convention of variable naming conventions:

  • local variables start with L_
  • parameters start with P_
  • global package variables start with G_

That link has a good general discussion on PL/SQL naming conventions. I personally just use V_ for most variables (aside from indexes and other obvious things), but that's just me.

Lastly, the col_ in the column names seem redundant; simply use name and price as column names.

So, that said, I think this does what you want:

create table table1 (
    name    varchar2(30),
    price   number
);

create or replace procedure TEST(
     p_name  IN table1.name%type,
     p_price IN table1.price%type
)
is
begin
    update table1 
       set name = p_name 
     where price = p_price;
    commit;
end TEST;
/
insert into table1 values ('John', 500);
commit;

select * from table1;

exec TEST(p_name => 'Bob', p_price => 500);

select * from table1;

-- Clean up test artifacts
drop procedure test;
drop table table1;

Giving the output:

table TABLE1 created.
PROCEDURE TEST compiled
1 rows inserted.
committed.
NAME                                PRICE
------------------------------ ----------
John                                  500 

anonymous block completed
NAME                                PRICE
------------------------------ ----------
Bob                                   500 

procedure TEST dropped.
table TABLE1 dropped.

Upvotes: 2

Related Questions