Oracle Stored Procedure Call from iSQL PLUS Invalid Identifier

Question

I have created a procedure using the following code using iSQL Plus on Firefox. The procedure compiles successfully.

create or replace procedure get_staff  (
    product_no in varchar2,
    o_cursor out sys_refcursor)
is
begin
        open o_cursor for
        'select sr.name, sr.bonus from sales_staff sr inner join product p on p.sales_staff_id = sr.staff_id where product_no = ' || product_no ;
end;

I am trying to call this procedure using the following code

var rc refcursor
exec get_staff('A56',:rc)
print rc

I get the following error.

ERROR at line 1: 
ORA-00904: "A56": invalid identifier 
ORA-06512: at "AA2850.GET_STAFF", line 6 
ORA-06512: at line 1 
ERROR: 
ORA-24338: statement handle not executed 
SP2-0625: Error printing variable "rc"

score 1 · Accepted Answer · answered Jan 23 '13 at 07:36

in the case you have, there's no need for dynamic sql:

open o_cursor for
        select sr.name, sr.bonus 
          from sales_staff sr 
               inner join product p 
                       on p.sales_staff_id = sr.staff_id
         where p.product_no = product_no;

if you were using dynamic SQL then ideally you would in most cases want to bind:

open o_cursor for
        'select sr.name, sr.bonus 
          from sales_staff sr 
               inner join product p 
                       on p.sales_staff_id = sr.staff_id
         where p.product_no = :b1' using product_no;

failing that (edge cases, sometimes you want to avoid bind variables for skewed data), varchar2s need enclosing in quotes:

open o_cursor for
        'select sr.name, sr.bonus 
          from sales_staff sr 
               inner join product p 
                       on p.sales_staff_id = sr.staff_id
         where p.product_no = ''' ||product_no||'''';

but you should escape single quotes and validate that product_no has no semi colons etc (i.e. careful of SQL injection)

Oracle Stored Procedure Call from iSQL PLUS Invalid Identifier

1 Answers1