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 Go to next message
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 #299389 is a reply to message #299388] Mon, 11 February 2008 16:32 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
You need to explain the function my_fun and how its beign called as part of this sql stack.

Re: why i cannot do it;PLS-00231" Function 'my_fun' cannot be used in this SQL" [message #299390 is a reply to message #299388] Mon, 11 February 2008 16:43 Go to previous messageGo to next message
pizdek
Messages: 5
Registered: February 2008
Junior Member
sory for ambiguity 'my_fun' i mean 'process_inv'
so it`s called from
here
SELECT * BULK COLLECT INTO invoices_temp
FROM TABLE(process_inv(CURSOR(SELECT customer,sum_f FROM TABLE(invoices_s))) );

ThX
Re: why i cannot do it;PLS-00231" Function 'my_fun' cannot be used in this SQL" [message #299393 is a reply to message #299390] Mon, 11 February 2008 17:02 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
This:-

EXIT WHEN curs%NOTFOUND;

Should be

EXIT WHEN v_curs%NOTFOUND;
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #299604 is a reply to message #299388] Tue, 12 February 2008 11:38 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
I'm afraid I'm still not clear on what you're after. 'Please try reviewing your posts from the point of view of someone who isn't you, and you might realise that there's a certain lack of clarity missing'.

Complete code samples (including in this case a valid package header/body ) make it much easier for people to help you. Unformatted code snippets on the other hand, just make people skip on to the next thread. If your problem is with one line, then simply comment that line out, and make sure the rest compiles before posting it. Remember Code tags are your friend Smile

Michael
Re: why i cannot do it;PLS-00231" Function 'my_fun' cannot be used in this SQL" [message #299626 is a reply to message #299454] Tue, 12 February 2008 14:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
Deciphering your post was like putting together a jigsaw puzzle. It would have been nice if you had posted all the pieces in their proper order inside the package. Please see the demonstration below. I believe the differences between what you are trying to do and my example below are:

1. I created a tab_cur as table of record_cur and declared invoices_temp as tab_cur type.

2. I used CAST (invoices AS invoices_tab) instead of just invoices in the select.

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  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 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  	   -- do something:
 12  	     result.sum_f := result.sum_f * 10;
 13  	   PIPE ROW (invoices (result.customer, result.sum_f));
 14  	 END LOOP;
 15  	 CLOSE v_curs;
 16  	 RETURN;
 17    END process_inv;
 18    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;
 31  END your_pkg;
 32  /

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:     20
----------------------------------------
customer:  3
sum_f:     40
----------------------------------------

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 #299648 is a reply to message #299388] Tue, 12 February 2008 18:30 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
Barbara,

I've gotta say, you're a more generous soul than I am (or at least was last night Smile) ...

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
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
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 Go to previous messageGo to next message
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 Smile

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 " Smile)

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 #299956 is a reply to message #299388] Wed, 13 February 2008 18:59 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
Barbara,

Thank you muchly for taking the time to do up those examples, and help clear things up for me.

Much appreciated.

Michael
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
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 Go to previous messageGo to next message
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 #300604 is a reply to message #299388] Sat, 16 February 2008 10:55 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
> i've got problems with wait events
If you say so.
I don't see any problem, so I can't guess what solution you need.
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 Go to previous message
Barbara Boehmer
Messages: 8623
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.



Previous Topic: Read/Write text files
Next Topic: Access data fom multiple schemas in a single query
Goto Forum:
  


Current Time: Sat Dec 03 09:52:57 CST 2016

Total time taken to generate the page: 0.08169 seconds