-
How can I test a procedure with SQL plus. What is the command?
ex:
CREATE OR REPLACE PROCEDURE add_status
(status_code CHAR,
status_desc_e VARCHAR2,
status_desc_f VARCHAR2
) IS
cSQL_Statement VARCHAR2(200);
BEGIN
cSQL_Statement := 'INSERT INTO E_SIA_STATUS ' ||
' VALUES(:code, :desc_e, :desc_f)';
EXECUTE IMMEDIATE cSQL_Statement
USING status_code, status_desc_e, status_desc_f;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20101,
'Error in procedure Add_status.');
END add_status;
/
How execute this procedure wtih SQL plus?
Thanks in advance
-
> exec add_status('A','desc status - e','desc status - f');
or
> exec owner_name.add_status('A','desc status - e','desc status - f');
-
an alternative that won't require you to recompile every time is to create an anonymous block instead:
declare
status_code char (1) := 'G';
status_desc_e varchar2(20) := 'ok';
status_desc_f varchar2(20) := 'bad';
csql_statement varchar2(200);
begin
. . .
exception
. . .
end;
/
--Simply type it all in at the command prompt
