Home » SQL & PL/SQL » SQL & PL/SQL » Error with Pipelined Table Funtion in PL/SQL
Error with Pipelined Table Funtion in PL/SQL [message #144286] Tue, 25 October 2005 15:58 Go to next message
jyanta
Messages: 7
Registered: October 2005
Junior Member
Well I am a little stumped with this. The error I am getting is this:
ORA-22905: cannot access rows from a non-nested table item.

This is what I am trying to run...


create or replace package body TT_PAW as
Procedure Clean_Split_Values(p_String in out varchar2, p_del in varchar2)
as
c_List R_Cursor;
v_plat varchar2(4000);
sOutTemp varchar2(4000);
begin
if (nvl(length(trim(p_del from p_String)),0)>0) then
-- tt_paw.Get_Split_Values(p_String,p_del,c_List);
open c_List for select * from table(split(p_String, p_del));
loop
fetch c_List into v_plat;
exit when c_List%notfound;
sOutTemp := sOutTemp || v_plat || ',';
end loop;
sOutTemp:= TRIM (',' FROM sOutTemp);
close c_List;
else
sOutTemp:='';
end if;
p_String:= ','||sOutTemp||',';
end;

split is a funtion I created that looks like this:
FUNCTION split
(
p_list varchar2,
p_del varchar2 :=','
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767);
l_TestString varchar2(32767);
begin
l_TestString := substr(p_list,1,1);
if l_TestString = ',' then
l_list := ltrim(p_list,p_del);
else
l_list := p_list;
end if;
l_TestString := substr(p_list,length(p_list),1);
if l_TestString = ',' then
l_list := rtrim(l_list,p_del);
end if;
loop
l_idx := instr(l_list,p_del);
if l_idx >0 then
pipe row(substr(l_list,1,l_idx-1));
dbms_output.put_line(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
dbms_output.put_line(l_list);
else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;

With Split_TBL as a type:
type split_tbl as table of varchar2(4000)

What could I be doing wrong and can you give me a hint on how to fix this.

Justin
Re: Error with Pipelined Table Funtion in PL/SQL [message #144287 is a reply to message #144286] Tue, 25 October 2005 16:13 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
What version of Oracle are you on?

But anyway, try using TABLE(CAST(split(..) AS split_tbl)) instead, and see if it works. If it does, then Oracle is unable to work out what type it is. Have you created it as a database type, i.e. CREATE OR REPLACE TYPE split_table AS TABLE OF VARCHAR2(4000), or is it defined in a package?

Regards
Re: Error with Pipelined Table Funtion in PL/SQL [message #144288 is a reply to message #144287] Tue, 25 October 2005 16:18 Go to previous messageGo to next message
jyanta
Messages: 7
Registered: October 2005
Junior Member
Sorry it is Oracle 10g. And I have the one process in a package that calls a function that is not in the package. The type is also not in the package. It is a global type.

I just tried using the cast but I get the error:
ORA-00905: Missing Keyword

[code]
open c_List for select * from table(cast(split(p_String, p_del))as split_tbl);
[code]

[Updated on: Tue, 25 October 2005 16:25]

Report message to a moderator

Re: Error with Pipelined Table Funtion in PL/SQL [message #144290 is a reply to message #144288] Tue, 25 October 2005 16:31 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
open c_List for
  select * from table(cast(split(p_String, p_del) as split_tbl));


Parentheses mods.
Re: Error with Pipelined Table Funtion in PL/SQL [message #144291 is a reply to message #144290] Tue, 25 October 2005 16:35 Go to previous messageGo to next message
jyanta
Messages: 7
Registered: October 2005
Junior Member
Thanks that is really bad. I think I was so wrapped into the problem I forgot to check my ().

Tested it out and it works. Thank you so much.

[Updated on: Tue, 25 October 2005 16:44]

Report message to a moderator

Re: Error with Pipelined Table Funtion in PL/SQL [message #144294 is a reply to message #144286] Tue, 25 October 2005 16:51 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello.

Did the CAST work then? I've been trying to replicate your problem on my 10.2 instance, but can't, see what I'm doing here that may be different to what you've done :
SQL> CREATE OR REPLACE TYPE split_tbl AS TABLE OF VARCHAR2(4000)
  2  /

Type created.

SQL> CREATE OR REPLACE
  2  FUNCTION split
  3  (
  4  p_list varchar2,
  5  p_del varchar2 :=','
  6  ) return split_tbl pipelined
  7  is
  8  l_idx pls_integer;
  9  l_list varchar2(32767);
 10  l_TestString varchar2(32767);
 11  begin
 12  l_TestString := substr(p_list,1,1);
 13  if l_TestString = ',' then
 14  l_list := ltrim(p_list,p_del);
 15  else
 16  l_list := p_list;
 17  end if;
 18  l_TestString := substr(p_list,length(p_list),1);
 19  if l_TestString = ',' then
 20  l_list := rtrim(l_list,p_del);
 21  end if;
 22  loop
 23  l_idx := instr(l_list,p_del);
 24  if l_idx >0 then
 25  pipe row(substr(l_list,1,l_idx-1));
 26  l_list := substr(l_list,l_idx+length(p_del));
 27  else
 28  pipe row(l_list);
 29  exit;
 30  end if;
 31  end loop;
 32  return;
 33  end split;
 34  /

Function created.

SQL> CREATE OR REPLACE PACKAGE test_pack
  2  AS
  3    PROCEDURE x;
  4  END test_pack;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY test_pack
  2  AS
  3    TYPE r_cursor IS REF CURSOR;
  4  
  5    PROCEDURE x
  6    IS
  7      curs   r_cursor;
  8      out_put  VARCHAR2(1);
  9    BEGIN
 10      OPEN curs FOR SELECT * FROM TABLE(split('x,y,z'));
 11  
 12      LOOP
 13        FETCH curs INTO out_put;
 14        EXIT WHEN curs%NOTFOUND;
 15        dbms_output.put_line('OUTPUT : ' || out_put);
 16      END LOOP;
 17    END x;
 18  
 19  END test_pack;
 20  /

Package body created.

SQL> EXEC test_pack.x;
OUTPUT : x
OUTPUT : y
OUTPUT : z

PL/SQL procedure successfully completed.
Re: Error with Pipelined Table Funtion in PL/SQL [message #144820 is a reply to message #144294] Fri, 28 October 2005 09:11 Go to previous messageGo to next message
jyanta
Messages: 7
Registered: October 2005
Junior Member
Yes the cast did work. After I put the cast in everything work smoothly. Thanks again for everyone's help.

Justin

P.S. Looking at what you did I found out that if you pass variables to the function then you need to cast the cursor comming out of the function. But if you are just calling a function and not passing any variable then it works fine without casting.

[Updated on: Fri, 28 October 2005 09:13]

Report message to a moderator

Re: Error with Pipelined Table Funtion in PL/SQL [message #236590 is a reply to message #144820] Thu, 10 May 2007 05:33 Go to previous messageGo to next message
krisjai
Messages: 7
Registered: May 2007
Location: cyprus
Junior Member
Hi,

I used the same split function in my code but it was not working even i cast it. it is saying package created with compilation errors.

for main in ( select *
from ITEMS I
where i.item_id in (select * from table(cast(split(p_List) as split_tbl))) .....
......


I am using oracle 9i. can you suggest me what to do???


Thanks in advance,

Jai
Re: Error with Pipelined Table Funtion in PL/SQL [message #236595 is a reply to message #236590] Thu, 10 May 2007 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you did like Martin (mchadder) did (that is just copy and paste your screen).
Read How to format your posts first.

Regards
Michel
Re: Error with Pipelined Table Funtion in PL/SQL [message #236616 is a reply to message #236595] Thu, 10 May 2007 06:59 Go to previous messageGo to next message
krisjai
Messages: 7
Registered: May 2007
Location: cyprus
Junior Member
Hi,

I used the following split function and i am getting compilation error at
select * from table(cast(split(p_main_item_id) as split_tbl))
. Can anybody suggest me.


type split_tbl is table of varchar2(32767);
function split(p_List varchar2,p_del varchar2 := ',') return split_tbl pipelined is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_List;
    l_value    varchar2(32767);
begin
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row(substr(l_list,1,l_idx-1));
            l_list := substr(l_list,l_idx+length(p_del));

        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end split;
procedure build_list(p_List varchar2)
     curr_ap_id product_type;
  BEGIN
      for main in ( select * 
                    from ITEM_LIST i
                    where  i.item_id in (select * from table(cast(split(p_main_item_id) as split_tbl)))                          ) loop



I am using oracle 9i

Thanks in advance,
Jai
Re: Error with Pipelined Table Funtion in PL/SQL [message #236629 is a reply to message #236616] Thu, 10 May 2007 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see any compilation error in what you posted.

Regards
Michel
Re: Error with Pipelined Table Funtion in PL/SQL [message #236632 is a reply to message #236629] Thu, 10 May 2007 07:39 Go to previous messageGo to next message
krisjai
Messages: 7
Registered: May 2007
Location: cyprus
Junior Member
i am getting this error..

Warning: Package Body created with compilation errors.

If i replace

select * from table(cast(split(p_List) as split_tbl))
with
select * from dual
It is executed successfully. I used the above code just to check where is error. I found the error is at the calling part of split function.

Thanks,
Jai

[Updated on: Thu, 10 May 2007 07:40]

Report message to a moderator

Re: Error with Pipelined Table Funtion in PL/SQL [message #236633 is a reply to message #236632] Thu, 10 May 2007 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you see Martin (mchadder) post?
This is a copy and paste of his screen.
There are the whole code, line numbers, returns from SQL*Plus and so on.
So do the same thing.
Is this too difficult?
And if you just get "Package Body created with compilation errors" add "show errors" or "show errors package body <your package name>".

Regards
Michel
Re: Error with Pipelined Table Funtion in PL/SQL [message #236694 is a reply to message #236633] Thu, 10 May 2007 11:35 Go to previous messageGo to next message
krisjai
Messages: 7
Registered: May 2007
Location: cyprus
Junior Member
create or replace type split_tbl is table of varchar2(32767);

create or replace function split1(p_List varchar2,p_del varchar2 := ',') return split_tbl pipelined is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_List;
    l_value    varchar2(32767);
begin
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row(substr(l_list,1,l_idx-1));
            l_list := substr(l_list,l_idx+length(p_del));
        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end split1;

create or replace procedure printList(p_List varchar2) IS
  BEGIN
      create table test_jk as select * from table(cast(split1(p_List)as split_tbl));
      
 end printList;


I just compiled it and didn't executed the procedure yet. I am getting the below error.

Type created.
Function created.
Warning: PROCEDURE created with compilation errors.
PLS-00103: CREATE symbol encountered

[Updated on: Thu, 10 May 2007 11:36]

Report message to a moderator

Re: Error with Pipelined Table Funtion in PL/SQL [message #236704 is a reply to message #236694] Thu, 10 May 2007 12:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I just wanted you posted that:

SQL> create or replace type split_tbl is table of varchar2(32767);
  2  /

Type created.

SQL> create or replace function split1(p_List varchar2,p_del varchar2 := ',') return split_tbl pipelined is
  2      l_idx    pls_integer;
  3      l_list    varchar2(32767) := p_List;
  4      l_value    varchar2(32767);
  5  begin
  6      loop
  7          l_idx := instr(l_list,p_del);
  8          if l_idx > 0 then
  9              pipe row(substr(l_list,1,l_idx-1));
 10              l_list := substr(l_list,l_idx+length(p_del));
 11          else
 12              pipe row(l_list);
 13              exit;
 14          end if;
 15      end loop;
 16      return;
 17  end split1;
 18  /

Function created.

SQL> create or replace procedure printList(p_List varchar2) IS
  2    BEGIN
  3        create table test_jk as select * from table(cast(split1(p_List)as split_tbl));
  4        
  5   end printList;
  6  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE PRINTLIST:
LINE/COL
---------------------------------------------------------------------------------
ERROR
------------------------------------------------------------------------------------------------------------
3/7
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

   begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe


Was it so difficult?????

Then we have the line numbers, the line number where the error happened and the full error message.
Then we can work.

You don't want us to help you, don't you?
Yes, you would like, so post what we asked. Simply that, without trying to think.


Now, you can't use create statement like this.
What do you really want to achieve?
Please answer clearly and completly if you want some help.

Regards
Michel
Re: Error with Pipelined Table Funtion in PL/SQL [message #236850 is a reply to message #236704] Fri, 11 May 2007 02:35 Go to previous messageGo to next message
krisjai
Messages: 7
Registered: May 2007
Location: cyprus
Junior Member
Sorry for troubling you. Thanks for the reply.

I want to send a list of numbers as a string with comma(,) seperated to a procedure and create a table with those values. Later i use this temporary table for other purpose.

For this purpose i used split function. But when i create a temporary table it was giving error.

How can I create a table with the values comes from split function.

Thanks,
Jai
Re: Error with Pipelined Table Funtion in PL/SQL [message #236853 is a reply to message #236850] Fri, 11 May 2007 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not just use:
SQL> var p_list varchar2(30)
SQL> exec :p_list := 'a,b,c';

PL/SQL procedure successfully completed.

SQL> create table t (id varchar2(20));

Table created.

SQL> insert into t select * from table(cast(split1(:p_List)as split_tbl));

3 rows created.

SQL> select * from t;
ID
--------------------
a
b
c

3 rows selected.

Regards
Michel
Re: Error with Pipelined Table Funtion in PL/SQL [message #236867 is a reply to message #236853] Fri, 11 May 2007 03:19 Go to previous messageGo to next message
krisjai
Messages: 7
Registered: May 2007
Location: cyprus
Junior Member
when i run each type and procedure invidually it is working.But, when i create a package then it is giving errors. see the errors below:

SQL> edit
Wrote file afiedt.buf

  1  CREATE OR REPLACE package body items_pkg as
  2   type split_tbl is table of varchar2(32767);
  3   function split1(p_List varchar2,p_del varchar2 := ',') return split_tbl pi
pelined is
  4      l_idx    pls_integer;
  5      l_list    varchar2(32767) := p_List;
  6      l_value    varchar2(32767);
  7  begin
  8      loop
  9          l_idx := instr(l_list,p_del);
 10          if l_idx > 0 then
 11              pipe row(substr(l_list,1,l_idx-1));
 12              l_list := substr(l_list,l_idx+length(p_del));
 13          else
 14              pipe row(l_list);
 15              exit;
 16          end if;
 17      end loop;
 18      return;
 19  end split1;
 20   procedure printList(p_List varchar2) IS
 21    BEGIN
 22        insert into test_jk select * from table(split1(p_List));
 23   end printList;
 24* end items_pkg;
SQL> /

Warning: Package Body created with compilation errors.

SQL> show errors;
Errors for PACKAGE BODY ITEMS_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
22/7     PL/SQL: SQL Statement ignored
22/47    PLS-00231: function 'SPLIT1' may not be used in SQL
22/47    PL/SQL: ORA-00904: : invalid identifier

[Updated on: Fri, 11 May 2007 03:22]

Report message to a moderator

Re: Error with Pipelined Table Funtion in PL/SQL [message #236881 is a reply to message #236867] Fri, 11 May 2007 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't define it as a stand-alone function so SQL does not see it.
You have to prefix your function name with the package name.

Regards
Michel
Re: Error with Pipelined Table Funtion in PL/SQL [message #236898 is a reply to message #236881] Fri, 11 May 2007 04:14 Go to previous message
krisjai
Messages: 7
Registered: May 2007
Location: cyprus
Junior Member
Hi Michel,

It is working now. I forgot to declare the signature of the function in package. I just created the function in package body and didn't put the sugnature in package. See the below code and it is working now.

Thanks alot.

create or replace package items_pkg as
	type split_tbl is table of varchar2(32767);
	function split1(p_List varchar2,p_del varchar2 := ',')return split_tbl pipelined;
	procedure printList1(p_List varchar2);
end items_pkg;


CREATE OR REPLACE package body items_pkg is


 function split1(p_List varchar2,p_del varchar2 := ',') return split_tbl pipelined is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_List;
    l_value    varchar2(32767);
begin
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row(substr(l_list,1,l_idx-1));
            l_list := substr(l_list,l_idx+length(p_del));
        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end split1;

 procedure printList1(p_List varchar2) IS
  BEGIN
	
      insert into test_jk select * from table(cast(split1(p_List)as split_tbl));
      
 end printList;
end items_pkg;
Previous Topic: Doubt in alphanumeric vaidation
Next Topic: 10G (Group by - Order By) doubt
Goto Forum:
  


Current Time: Sun Dec 04 10:45:11 CST 2016

Total time taken to generate the page: 0.16212 seconds