Home » SQL & PL/SQL » SQL & PL/SQL » why i cannot do it;PLS-00231" Function 'my_fun' cannot be used in this SQL"
| why i cannot do it;PLS-00231" Function 'my_fun' cannot be used in this SQL" [message #299388] |
Mon, 11 February 2008 16:13  |
pizdek
Messages: 5
Registered: February 2008
|
Junior Member |
|
|
134 PLS-00231: Function 'my_fun' cannot be used in this SQL
134 PL/SQL: ORA-00904: invalid identifier
133 PL/SQL: SQL Statement ignored ryone
helo everyone,
I got this message when i try to compile my code.
I show you what i`am trying to do, if you can help me somehow i will be grateful
so,
CREATE OR REPLACE TYPE invoices AS OBJECT (
customer NUMBER,
sum_f NUMBER
)
CREATE OR REPLACE TYPE invoices_tab aS TABLE OF invoices
whith this code seems to be a problem:
SELECT * BULK COLLECT INTO invoices_temp
FROM TABLE(process_inv(CURSOR(SELECT customer,sum_f FROM TABLE(invoices_s))) );
where invoices_temp & invoices_s both are of invoices_tab type
and invoices_s is
my process_inv looks like that:
FUNCTION process_inv(v_curs cursor_typ)
RETURN invoices_tab
PIPELINED AS --PARALLEL_ENABLE(PARTITION v_kursor BY ANY) AS
result record_cur;
BEGIN
LOOP
FETCH v_curs INTO result;
EXIT WHEN curs%NOTFOUND;
--do something
PIPE ROW(invoices(result.customer,result.sum_f));
END LOOP;
RETURN ;
END;
where
TYPE record_cur iS RECORD (
customer NUMBER,
sum_f NUMBER );
TYPE cursor_typ iS REF CURSOR RETURN record_cur;
please give me a hint what`s wrong with this code.
ThX
|
|
|
|
|
|
|
|
|
|
| Re: why i cannot do it;PLS-00231" Function 'my_fun' cannot be used in this SQL" [message #299398 is a reply to message #299388] |
Mon, 11 February 2008 18:22  |
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
|
Member |
|
|
I'll be honest, having read this a couple of times, I'm not exactly sure what this code is meant to be doing, and going through it is not made any easier by your presentation. Might I suggest that you post complete pieces of code which make it easier for others to reproduce what you're up to :
ie | Quote: | Post a test case: create table and insert statements.
Also post the result you want with these data.
Always post your Oracle version (4 decimals).
Read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
|
etc, etc ..
So whilst I'm not clear if any of these snippets are from packaged code or not (Can ref cursor types be declared outside a package? - guess I should know the answer to that), I'll say this. The way I've normally used pipeline functions would be with a declaration like this ..
CREATE OR REPLACE PACKAGE test_stuff
IS
type invoice IS record(customer NUMBER, sum_f NUMBER);
type invoice_cur IS ref CURSOR RETURN invoice;
type invoice_tab IS TABLE OF invoice;
FUNCTION process_inv(v_curs invoice_cur) RETURN invoice_tab pipelined;
END;
Which (with the appropriate matching body), would allow for
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> SELECT *
2 FROM TABLE(test_stuff.process_inv(CURSOR
3 (SELECT 100, 200
4 FROM dual)))
5 ;
CUSTOMER SUM_F
---------- ----------
100 200
SQL>
However I'll hold off talking about the package body, till perhaps you can clear up what exactly you're trying to do, and I know if I'm on the completely wrong track ...
Regards,
Michael
|
|
|
|
| Re: why i cannot do it;PLS-00231" Function 'my_fun' cannot be used in this SQL" [message #299454 is a reply to message #299388] |
Tue, 12 February 2008 02:09 |
pizdek
Messages: 5
Registered: February 2008
|
Junior Member |
|
|
so, all what i`am trying to do is to copy one collection to another one, but during this process i want do some operations on each element
my record type and ref cursor both are in the same package body
as process_inv
my select :
SELECT * BULK COLLECT INTO invoices_temp
FROM TABLE(process_inv(CURSOR(SELECT customer,sum_f FROM TABLE(invoices_s))) );
[B]
is invoked from another procedure from the same package[B]
my first question is: is this costruction, i mean this select
correct? it`s just bit different from that what you present
in a place of CURSOR(SELECT 100,200 FROM dual)))
i`ve got (CURSOR(SELECT customer,sum_f FROM TABLE(invoices_s)))
is it possible to do nested select * from TABLE(...
|
|
|
|
|
|
|
|
|
|
| Re: why i cannot do it;PLS-00231" Function 'my_fun' cannot be used in this SQL" [message #299663 is a reply to message #299648] |
Tue, 12 February 2008 22:14 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
| moshea wrote on Tue, 12 February 2008 16:30 | Barbara,
I've gotta say, you're a more generous soul than I am (or at least was last night :)) ...
Just a quickie on some code the OP and yourself have used ie:
PIPE ROW (invoices (result.customer, result.sum_f));
I've never used a object type exactly like that in this sortof situation, and so am unclear on the exact purpose (except perhaps to show it can be done).
In the snippet I'd done up, I'd gone for a more vanilla
| Quote: | LOOP
FETCH v_curs INTO invoice_rec;
EXIT WHEN v_curs%NOTFOUND;
invoice_rec.sum_f := invoice_rec.sum_f + 10000;
-- Note I just use the original rec type here
-- rather than instanstiating a new object.
pipe ROW(invoice_rec);
END LOOP;
|
And I guess my question is just, whether or not the two are equivalent in this example. Or does copying the values into a new object change the behaviour in any way.
Cheers
Michael
|
Michael,
Whenever something appears to be a simplification of a more complex problem, I figure there may be reasons for doing certain things that way and try to only change the minimum amount of code necessary to make it work.
There is no problem with your method if your cursor in the select statement is selecting from a SQL table, such as:
SELECT * BULK COLLECT INTO invoices_temp
FROM TABLE (process_inv (CURSOR (SELECT 1, 2 FROM DUAL UNION ALL SELECT 3, 4 FROM DUAL)));
However, if you are selecting from another pl/sql table, such as:
SELECT * BULK COLLECT INTO invoices_temp
FROM TABLE (process_inv (CURSOR (SELECT customer, sum_f FROM TABLE (invoices_s))));
the select will fail, unless you cast it as a sql type:
SELECT * BULK COLLECT INTO invoices_temp
FROM TABLE (process_inv (CURSOR (SELECT customer, sum_f FROM TABLE (CAST (invoices_s AS invoices_tab)))));
and if you are going to cast it as a sql type, and your function is going to return that same sql type, then you also need to:
PIPE ROW (invoices (result.customer, result.sum_f));
I have provided some examples below. The first one is what I think you are suggesting. The next two fail due to lack of types to match the nested TABLE clause in the cursor.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE your_pkg AS
2 TYPE record_cur IS RECORD
3 (customer NUMBER,
4 sum_f NUMBER);
5 TYPE tab_cur IS TABLE OF record_cur;
6 TYPE cursor_typ IS REF CURSOR RETURN record_cur;
7 FUNCTION process_inv
8 (v_curs cursor_typ)
9 RETURN tab_cur PIPELINED;
10 PROCEDURE your_proc;
11 END your_pkg;
12 /
Package created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY your_pkg AS
2 FUNCTION process_inv
3 (v_curs cursor_typ)
4 RETURN tab_cur PIPELINED
5 IS
6 invoice_rec record_cur;
7 BEGIN
8 LOOP
9 FETCH v_curs INTO invoice_rec;
10 EXIT WHEN v_curs%NOTFOUND;
11 invoice_rec.sum_f := invoice_rec.sum_f + 10000;
12 PIPE ROW (invoice_rec);
13 END LOOP;
14 CLOSE v_curs;
15 RETURN;
16 END process_inv;
17 PROCEDURE your_proc
18 IS
19 invoices_temp tab_cur := tab_cur();
20 invoices_s tab_cur := tab_cur();
21 BEGIN
22 invoices_s.EXTEND;
23 invoices_s (invoices_s.LAST).customer := 1;
24 invoices_s (invoices_s.LAST).sum_f := 2;
25 invoices_s.EXTEND;
26 invoices_s (invoices_s.LAST).customer := 3;
27 invoices_s (invoices_s.LAST).sum_f := 4;
28 FOR i IN 1 .. invoices_s.LAST LOOP
29 DBMS_OUTPUT.PUT_LINE ('customer: ' || invoices_s(i).customer);
30 DBMS_OUTPUT.PUT_LINE ('sum_f: ' || invoices_s(i).sum_f);
31 DBMS_OUTPUT.PUT_LINE ('----------------------------------------');
32 END LOOP;
33 SELECT * BULK COLLECT INTO invoices_temp
34 FROM TABLE (process_inv (CURSOR (SELECT 1, 2 FROM DUAL UNION ALL SELECT 3, 4 FROM DUAL)));
35 FOR i IN 1 .. invoices_temp.LAST LOOP
36 DBMS_OUTPUT.PUT_LINE ('customer: ' || invoices_temp(i).customer);
37 DBMS_OUTPUT.PUT_LINE ('sum_f: ' || invoices_temp(i).sum_f);
38 DBMS_OUTPUT.PUT_LINE ('----------------------------------------');
39 END LOOP;
40 END your_proc;
41 END your_pkg;
42 /
Package body created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> /
Package body created.
SCOTT@orcl_11g> EXEC your_pkg.your_proc
customer: 1
sum_f: 2
----------------------------------------
customer: 3
sum_f: 4
----------------------------------------
customer: 1
sum_f: 10002
----------------------------------------
customer: 3
sum_f: 10004
----------------------------------------
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY your_pkg AS
2 FUNCTION process_inv
3 (v_curs cursor_typ)
4 RETURN tab_cur PIPELINED
5 IS
6 invoice_rec record_cur;
7 BEGIN
8 LOOP
9 FETCH v_curs INTO invoice_rec;
10 EXIT WHEN v_curs%NOTFOUND;
11 invoice_rec.sum_f := invoice_rec.sum_f + 10000;
12 PIPE ROW (invoice_rec);
13 END LOOP;
14 CLOSE v_curs;
15 RETURN;
16 END process_inv;
17 PROCEDURE your_proc
18 IS
19 invoices_temp tab_cur := tab_cur();
20 invoices_s tab_cur := tab_cur();
21 BEGIN
22 invoices_s.EXTEND;
23 invoices_s (invoices_s.LAST).customer := 1;
24 invoices_s (invoices_s.LAST).sum_f := 2;
25 invoices_s.EXTEND;
26 invoices_s (invoices_s.LAST).customer := 3;
27 invoices_s (invoices_s.LAST).sum_f := 4;
28 FOR i IN 1 .. invoices_s.LAST LOOP
29 DBMS_OUTPUT.PUT_LINE ('customer: ' || invoices_s(i).customer);
30 DBMS_OUTPUT.PUT_LINE ('sum_f: ' || invoices_s(i).sum_f);
31 DBMS_OUTPUT.PUT_LINE ('----------------------------------------');
32 END LOOP;
33 SELECT * BULK COLLECT INTO invoices_temp
34 FROM TABLE (process_inv (CURSOR (SELECT customer, sum_f FROM TABLE (invoices_s))));
35 FOR i IN 1 .. invoices_temp.LAST LOOP
36 DBMS_OUTPUT.PUT_LINE ('customer: ' || invoices_temp(i).customer);
37 DBMS_OUTPUT.PUT_LINE ('sum_f: ' || invoices_temp(i).sum_f);
38 DBMS_OUTPUT.PUT_LINE ('----------------------------------------');
39 END LOOP;
40 END your_proc;
41 END your_pkg;
42 /
Package body created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> /
Package body created.
SCOTT@orcl_11g> EXEC your_pkg.your_proc
customer: 1
sum_f: 2
----------------------------------------
customer: 3
sum_f: 4
----------------------------------------
BEGIN your_pkg.your_proc; END;
*
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete
ORA-06512: at "SCOTT.YOUR_PKG", line 33
ORA-06512: at line 1
SCOTT@orcl_11g> CREATE OR REPLACE TYPE invoices AS OBJECT
2 (customer NUMBER,
3 sum_f NUMBER);
4 /
Type created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE TYPE invoices_tab aS TABLE OF invoices;
2 /
Type created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY your_pkg AS
2 FUNCTION process_inv
3 (v_curs cursor_typ)
4 RETURN tab_cur PIPELINED
5 IS
6 invoice_rec record_cur;
7 BEGIN
8 LOOP
9 FETCH v_curs INTO invoice_rec;
10 EXIT WHEN v_curs%NOTFOUND;
11 invoice_rec.sum_f := invoice_rec.sum_f + 10000;
12 PIPE ROW (invoice_rec);
13 END LOOP;
14 CLOSE v_curs;
15 RETURN;
16 END process_inv;
17 PROCEDURE your_proc
18 IS
19 invoices_temp tab_cur := tab_cur();
20 invoices_s tab_cur := tab_cur();
21 BEGIN
22 invoices_s.EXTEND;
23 invoices_s (invoices_s.LAST).customer := 1;
24 invoices_s (invoices_s.LAST).sum_f := 2;
25 invoices_s.EXTEND;
26 invoices_s (invoices_s.LAST).customer := 3;
27 invoices_s (invoices_s.LAST).sum_f := 4;
28 FOR i IN 1 .. invoices_s.LAST LOOP
29 DBMS_OUTPUT.PUT_LINE ('customer: ' || invoices_s(i).customer);
30 DBMS_OUTPUT.PUT_LINE ('sum_f: ' || invoices_s(i).sum_f);
31 DBMS_OUTPUT.PUT_LINE ('----------------------------------------');
32 END LOOP;
33 SELECT * BULK COLLECT INTO invoices_temp
34 FROM TABLE (process_inv (CURSOR (SELECT customer, sum_f FROM TABLE (CAST (invoices_s AS invoices_tab)))));
35 FOR i IN 1 .. invoices_temp.LAST LOOP
36 DBMS_OUTPUT.PUT_LINE ('customer: ' || invoices_temp(i).customer);
37 DBMS_OUTPUT.PUT_LINE ('sum_f: ' || invoices_temp(i).sum_f);
38 DBMS_OUTPUT.PUT_LINE ('----------------------------------------');
39 END LOOP;
40 END your_proc;
41 END your_pkg;
42 /
Package body created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> /
Package body created.
SCOTT@orcl_11g> EXEC your_pkg.your_proc
customer: 1
sum_f: 2
----------------------------------------
customer: 3
sum_f: 4
----------------------------------------
BEGIN your_pkg.your_proc; END;
*
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete
ORA-06512: at "SCOTT.YOUR_PKG", line 33
ORA-06512: at line 1
SCOTT@orcl_11g>
|
|
|
|
| Re: why i cannot do it;PLS-00231" Function 'my_fun' cannot be used in this SQL" [message #299803 is a reply to message #299388] |
Wed, 13 February 2008 04:33 |
pizdek
Messages: 5
Registered: February 2008
|
Junior Member |
|
|
i'm sorry that my code was not formated properly,
next time i will remeber about that, thx for advice 
Thank You all BArbara,Michael,Coleing very much for a help
special thanks to Barbana.
Barbara you are Great, I love you
I fall at Yours feet i say "oo Thank You " )
but i have to say that i'm just a bit confused
I do not know what's the difference between plsql type as declared in a package and types created using create or replace type at the context of my problem
two questions:) :
PROCEDURE your_proc
19 IS
20 invoices_temp tab_cur;
21 invoices_s invoices_tab := invoices_tab (invoices (1, 2), invoices (3, 4));
22 BEGIN
23 SELECT * BULK COLLECT INTO invoices_temp
24 FROM TABLE (process_inv (CURSOR (SELECT customer, sum_f FROM TABLE (CAST (invoices_s AS invoices_tab)))));
25 FOR i IN 1 .. invoices_temp.LAST LOOP
26 DBMS_OUTPUT.PUT_LINE ('customer: ' || invoices_temp(i).customer);
27 DBMS_OUTPUT.PUT_LINE ('sum_f: ' || invoices_temp(i).sum_f);
28 DBMS_OUTPUT.PUT_LINE ('----------------------------------------');
29 END LOOP;
30 END your_proc;
1.I understand that if invoices_temp were of invoices_tab type
it would work tha same correct way , yes ??
2.I filled my invoices_s in this way
| Quote: |
SELECT invoices(customer_id, NULL)
BULK COLLECT INTO invoices_s
FROM customers
WHERE customer_id BETWEEN p_max AND p_min;
|
so is it correrct if i use my select where sum_f is null
| Quote: | SELECT * BULK COLLECT INTO invoices_temp
34 FROM TABLE (process_inv (CURSOR (SELECT customer, sum_f FROM TABLE (cast(invoices_s as invoices_tab) ))));
|
|
|
|
|
|
|
| Re: why i cannot do it;PLS-00231" Function 'my_fun' cannot be used in this SQL" [message #300214 is a reply to message #299803] |
Thu, 14 February 2008 11:01 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
There are differences between SQL tables, SQL objects, and PL/SQL collections. In some cases, Oracle can do implicit conversions. In other cases, it cannot and you have to CAST.
When I first saw your problem, I assumed that there was a reason for the nested TABLE. However, now that I see the whole problem, I can see that there isn't. You are making it more complicated than it needs to be. You should just use a cursor that selects directly from the customers table, as Michael O'Shea suggested. It eliminates the need for any objects or casting. Please see the demonstration below.
SCOTT@orcl_11g> CREATE TABLE customers
2 (customer_id NUMBER)
3 /
Table created.
SCOTT@orcl_11g> INSERT INTO customers VALUES (1)
2 /
1 row created.
SCOTT@orcl_11g> INSERT INTO customers VALUES (2)
2 /
1 row created.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE your_pkg AS
2 TYPE record_cur IS RECORD
3 (customer NUMBER,
4 sum_f NUMBER);
5 TYPE tab_cur IS TABLE OF record_cur;
6 TYPE cursor_typ IS REF CURSOR RETURN record_cur;
7 FUNCTION process_inv
8 (v_curs cursor_typ)
9 RETURN tab_cur PIPELINED;
10 PROCEDURE your_proc
11 (p_min IN customers.customer_id%TYPE,
12 p_max IN customers.customer_id%TYPE);
13 END your_pkg;
14 /
Package created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY your_pkg AS
2 FUNCTION process_inv
3 (v_curs cursor_typ)
4 RETURN tab_cur PIPELINED
5 IS
6 result record_cur;
7 BEGIN
8 LOOP
9 FETCH v_curs INTO result;
10 EXIT WHEN v_curs%NOTFOUND;
11 result.sum_f := result.customer * 10;
12 PIPE ROW (result);
13 END LOOP;
14 CLOSE v_curs;
15 RETURN;
16 END process_inv;
17 PROCEDURE your_proc
18 (p_min IN customers.customer_id%TYPE,
19 p_max IN customers.customer_id%TYPE)
20 IS
21 invoices_temp tab_cur := tab_cur();
22 invoices_s tab_cur := tab_cur();
23 BEGIN
24 SELECT * BULK COLLECT INTO invoices_temp
25 FROM TABLE
26 (process_inv
27 (CURSOR
28 (SELECT customer_id, NULL
29 FROM customers
30 WHERE customer_id BETWEEN p_min AND p_max)));
31 FOR i IN 1 .. invoices_temp.LAST LOOP
32 DBMS_OUTPUT.PUT_LINE ('customer: ' || invoices_temp(i).customer);
33 DBMS_OUTPUT.PUT_LINE ('sum_f: ' || invoices_temp(i).sum_f);
34 DBMS_OUTPUT.PUT_LINE ('----------------------------------------');
35 END LOOP;
36 END your_proc;
37 END your_pkg;
38 /
Package body created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC your_pkg.your_proc (1, 2)
customer: 1
sum_f: 10
----------------------------------------
customer: 2
sum_f: 20
----------------------------------------
PL/SQL procedure successfully completed.
SCOTT@orcl_11g>
[Updated on: Thu, 14 February 2008 11:25] Report message to a moderator |
|
|
|
| Re: why i cannot do it;PLS-00231" Function 'my_fun' cannot be used in this SQL" [message #300227 is a reply to message #299803] |
Thu, 14 February 2008 11:38 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Just in case there is some reason for the nested TABLE and objects and such that isn't evident in the portion that you posted, here is an example incorporating your population of invoices_s the hard way.
SCOTT@orcl_11g> CREATE TABLE customers
2 (customer_id NUMBER)
3 /
Table created.
SCOTT@orcl_11g> INSERT INTO customers VALUES (1)
2 /
1 row created.
SCOTT@orcl_11g> INSERT INTO customers VALUES (2)
2 /
1 row created.
SCOTT@orcl_11g> CREATE OR REPLACE TYPE invoices AS OBJECT
2 (customer NUMBER,
3 sum_f NUMBER);
4 /
Type created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE TYPE invoices_tab aS TABLE OF invoices;
2 /
Type created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE your_pkg AS
2 TYPE record_cur IS RECORD
3 (customer NUMBER,
4 sum_f NUMBER);
5 TYPE tab_cur IS TABLE OF record_cur;
6 TYPE cursor_typ IS REF CURSOR RETURN record_cur;
7 FUNCTION process_inv
8 (v_curs cursor_typ)
9 RETURN invoices_tab PIPELINED;
10 PROCEDURE your_proc
11 (p_min IN customers.customer_id%TYPE,
12 p_max IN customers.customer_id%TYPE);
13 END your_pkg;
14 /
Package created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY your_pkg AS
2 FUNCTION process_inv
3 (v_curs cursor_typ)
4 RETURN invoices_tab PIPELINED
5 IS
6 result record_cur;
7 BEGIN
8 LOOP
9 FETCH v_curs INTO result;
10 EXIT WHEN v_curs%NOTFOUND;
11 result.sum_f := result.customer * 10;
12 PIPE ROW (invoices (result.customer, result.sum_f));
13 END LOOP;
14 CLOSE v_curs;
15 RETURN;
16 END process_inv;
17 PROCEDURE your_proc
18 (p_min IN customers.customer_id%TYPE,
19 p_max IN customers.customer_id%TYPE)
20 IS
21 invoices_temp tab_cur;
22 invoices_s invoices_tab := invoices_tab ();
23 BEGIN
24 SELECT invoices(customer_id, NULL)
25 BULK COLLECT INTO invoices_s
26 FROM customers
27 WHERE customer_id BETWEEN p_min AND p_max;
28 SELECT * BULK COLLECT INTO invoices_temp
29 FROM TABLE (process_inv (CURSOR (SELECT customer, sum_f FROM TABLE (CAST (invoices_s AS invoices_tab)))));
30 FOR i IN 1 .. invoices_temp.LAST LOOP
31 DBMS_OUTPUT.PUT_LINE ('customer: ' || invoices_temp(i).customer);
32 DBMS_OUTPUT.PUT_LINE ('sum_f: ' || invoices_temp(i).sum_f);
33 DBMS_OUTPUT.PUT_LINE ('----------------------------------------');
34 END LOOP;
35 END your_proc;
36 END your_pkg;
37 /
Package body created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> /
Package body created.
SCOTT@orcl_11g> EXEC your_pkg.your_proc (1, 2)
customer: 1
sum_f: 10
----------------------------------------
customer: 2
sum_f: 20
----------------------------------------
PL/SQL procedure successfully completed.
SCOTT@orcl_11g>
|
|
|
|
| Re: why i cannot do it;PLS-00231" Function 'my_fun' cannot be used in this SQL" [message #300593 is a reply to message #299388] |
Sat, 16 February 2008 06:30 |
pizdek
Messages: 5
Registered: February 2008
|
Junior Member |
|
|
helo
But in my case it's necessary to use 3 or more plsql tables or objects.
please tell me what is more efficient
select from collection to collection and process some fields
or to select from table (one more time ) to collection and process:
| Quote: |
1.select bulk collect into collection_2 from
table(function_to_process(cursor(select col_1,col_2 from
table(cast(collection_1 as customers_tab)))))
2.select bulk collect into collection_2
from table(function_to_process(cursor(select col_1,col_2 from table)
|
each time i try copy from one collection to another using pipelined function
or even using procedure and passing my collection
as IN OUT NOCOPY parametr i've got problems with wait events
my unction is suffering from latch free and buffer busy wait
|
|
|
|
|
|
| Re: why i cannot do it;PLS-00231" Function 'my_fun' cannot be used in this SQL" [message #300607 is a reply to message #300593] |
Sat, 16 February 2008 11:18 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
| pizdek wrote on Sat, 16 February 2008 04:30 | helo
But in my case it's necessary to use 3 or more plsql tables or objects.
please tell me what is more efficient
select from collection to collection and process some fields
or to select from table (one more time ) to collection and process:
| Quote: |
1.select bulk collect into collection_2 from
table(function_to_process(cursor(select col_1,col_2 from
table(cast(collection_1 as customers_tab)))))
2.select bulk collect into collection_2
from table(function_to_process(cursor(select col_1,col_2 from table)
|
each time i try copy from one collection to another using pipelined function
or even using procedure and passing my collection
as IN OUT NOCOPY parametr i've got problems with wait events
my unction is suffering from latch free and buffer busy wait
|
For the portion of the problem that you have presented, as previously stated, the method that you listed above as 2 is the simplest. I have now seen where your data comes from, but not what processing needs to be done or what you do with the data after processing. I suspect that you are making things more complicated than they need to be. There is probably no need to copy from one collection to another to do processing. Just do your processing on the one collection, then return that collection.
|
|
|
|
Goto Forum:
Current Time: Sun Jan 11 21:41:28 CST 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
