How to make a sql function and test it at the same sheet?

Asked Aug 27 '15 at 12:28

Active Aug 27 '15 at 13:14

Viewed 86 times

I'm trying to make an sql function (or procedure) in sql developer and test in at the same sheet. I've got some error message. This is my code:

CREATE or replace function myProc (trace_items_ids IN arrayofnumbers)
RETURN tableof2numbers
is 
tr          tableof2numbers := tableof2numbers();

BEGIN
select object_id, parent_id
bulk collect into tr
from objects;
return tr;
END;

select * from TABLE(myProc(arrayofnumbers(1,2)))

and I've got an error :

ORA-00955. 00000 - "name is already used by an existing object" Cause:
Action:

How can I test my function?

edited Aug 27 '15 at 13:14

asked Aug 27 '15 at 12:28

Sergei Podlipaev

1,331
1
14
34

1

Apparently there is already some object (but not a function) in the database with that name. `select object_name, object_type from user_objects where object_name = 'MYPROC'` will show you what it is. – Aug 27 '15 at 12:48
No, I've created function MYPROC2 and it have the same error. – Sergei Podlipaev Aug 27 '15 at 13:00
So what does the `select` return? – Aug 27 '15 at 13:01
It returns my function. Now I have a compilation error : PLS-00103: Encountered the symbol "SELECT" – Sergei Podlipaev Aug 27 '15 at 13:04
You need to terminate the `create procedure` with a `/`: See here: http://stackoverflow.com/a/10207695/330315 – Aug 27 '15 at 13:05
I had some errors in my pl/sql function. Now I have no problems, thank you. – Sergei Podlipaev Aug 27 '15 at 14:01
It works, when first I write function declaration and then I do a query select. I use 2 sheets and perform it separately. – Sergei Podlipaev Aug 28 '15 at 12:09

How to make a sql function and test it at the same sheet?

0 Answers0