Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Problem (Oracle 9.2.0.1)
PL/SQL Problem [message #376173] Tue, 16 December 2008 07:17 Go to next message
avik2009
Messages: 61
Registered: November 2008
Member

create or replace PROCEDURE Get_count
   (
    p_txt_string       IN  varChar2,
    rc1 out sys_refcursor
    	)
   IS
       v_str      varchar2(255);
       v_cnt      Number;
       v_position        number;
	   o_word_cnt      array_1;
       
   BEGIN
  
     /* if , is not at end then append */
     IF substr(p_txt_string,LENGTH(p_txt_string),1) != ',' THEN
          v_str := p_txt_string||',';
      ELSE
          v_str := p_txt_string;
      END IF;   

      v_cnt := 1;
      v_position := INSTR(v_str,',',1,v_cnt);

      WHILE v_position <> 0 LOOP   
        BEGIN
            SELECT count(*)
				INTO word_cnt(v_cnt)
            FROM my_dictionary_tbl
            WHERE word_txt = GET_WORD(v_str,',',v_cnt))
||                                  '                              ',1,50);
                 
        v_cnt := v_cnt + 1;
        v_position := INSTR(v_str,',',1,v_cnt);
      END LOOP;
	  
	  OPEN RC1 FOR SELECT * FROM TABLE( CAST(o_word_cnt

   END Get_count;   
   
   
select word_txt from my_dictionary_tbl;

word_txt
________

NERO
AVG
WINDOWS
VISTA


EXPECTED OUTPUT: 
WHEN I PASS   'AVG,NERO,WINDOWS,ABN'
THE RESULT SHOULD BE ( FOR MATCHING VALUES 1, ELSE 0)
1 
1
1
0 



    

Calling procedure:

DECLARE
   p_txt_string varchar2(30);
   a      NUMBER;
   cv_c1  sys_refcursor;
BEGIN
   GET_COUNT ('AVG,NERO,WINDOWS,ABN',cv_c1);
   LOOP
      FETCH cv_c1 INTO a;
      EXIT WHEN cv_c1%NOTFOUND;
      DBMS_OUTPUT.put_line(A);
   END LOOP;
   --CLOSE cv_c1;
END;

When we tried got error.

[Updated on: Tue, 16 December 2008 08:02] by Moderator

Report message to a moderator

Re: PL/SQL Problem [message #376178 is a reply to message #376173] Tue, 16 December 2008 07:28 Go to previous messageGo to next message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
The code of your Get_count procedure does not compile. There is an error at line 29 - seems there is some missing text.

Thanks,
Jim
Re: PL/SQL Problem [message #376182 is a reply to message #376173] Tue, 16 December 2008 07:34 Go to previous messageGo to next message
karthick_arp@yahoo.com
Messages: 6
Registered: November 2006
Location: India
Junior Member

A different approach with a function returning a table type.

SQL> create table temp(col varchar2(10))
  2  /

Table created.

SQL> insert into temp values('NERO')
  2  /

1 row created.

SQL> insert into temp values('AVG')
  2  /

1 row created.

SQL> insert into temp values('WINDOWS')
  2  /

1 row created.

SQL> insert into temp values('VISTA')
  2  /

1 row created.

SQL> create type mytbl as table of number(10)
  2  /

Type created.


SQL> create or replace function myfn (pstr in varchar2) return mytbl
  2  as
  3     ltbl mytbl;
  4  begin
  5     select sum(decode(col,null,0,1))
  6       bulk collect into ltbl
  7       from (select level no, regexp_substr(str,'[^,]+', 1, level) list
  8               from (select pstr str
  9                       from dual)
 10                    connect by level <= nvl( length( regexp_replace( str, '[^,]+', null ) ), 0 ) + 1)
 11               left join temp
 12                 on col = list
 13             group by no
 14             order by no;
 15
 16             return ltbl;
 17  end;
 18  /

Function created.

SQL> select * from table(myfn('AVG,NERO,WINDOWS,ABN'))
  2  /

COLUMN_VALUE
------------
           1
           1
           1
           0

SQL>
Re: PL/SQL Problem [message #376183 is a reply to message #376182] Tue, 16 December 2008 07:44 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
I want to pass the string as input parameter through procedure and regexp_substr is not working
Re: PL/SQL Problem [message #376193 is a reply to message #376183] Tue, 16 December 2008 07:59 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
Any suggestion to modify the procedure to get expected output will be appreciated.

GET_WORD procedure is used to select word one by one and to check whether its in my_dictionary_tbl table or not
Re: PL/SQL Problem [message #376197 is a reply to message #376193] Tue, 16 December 2008 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want to pass the string as input parameter through procedure and regexp_substr is not working

You MUST tell and show us it does not work not just saying it does not work.

Quote:
Any suggestion to modify the procedure to get expected output will be appreciated.

Procedures splitting a list string into its component have been posted so many times trhat you should not any problem to find them.

Regards
Michel
Re: PL/SQL Problem [message #376209 is a reply to message #376197] Tue, 16 December 2008 08:34 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
ORA-06531: Reference to uninitialized collection.
Re: PL/SQL Problem [message #376213 is a reply to message #376209] Tue, 16 December 2008 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Barbara answered to this error less than 24 hours ago.
I answered to this error less than 2 days ago.

What is the relation with this error and "regexp_substr".
Post (copy and paste) your code and SQL*Plus session and post it formatted.

Regards
Michel

[Updated on: Tue, 16 December 2008 08:45]

Report message to a moderator

Re: PL/SQL Problem [message #376223 is a reply to message #376213] Tue, 16 December 2008 08:54 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
@Michel
I got that error when I tried to execute the procedure.
Re: PL/SQL Problem [message #376224 is a reply to message #376223] Tue, 16 December 2008 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post SQL*Plus session that creates the procedure and call it.

Regards
Michel
Re: PL/SQL Problem [message #376226 is a reply to message #376224] Tue, 16 December 2008 09:03 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
I have no sqlplus installed in my machine. I am using SQL Developer and this is what I got:

ORA-06531: Reference to uninitialized collectionORA-06512: at line 9 00000 - "Reference to uninitialized collection"*Cause: An element or member function of a nested table or varraywas referenced (where an initialized collection is needed)without the collection having been initialized.*Action: Initialize the collection with an appropriate constructoror whole-object assignment.

[Updated on: Tue, 16 December 2008 09:03]

Report message to a moderator

Re: PL/SQL Problem [message #376227 is a reply to message #376226] Tue, 16 December 2008 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I still don't have your code.
Post it formatted.

Regards
Michel
Re: PL/SQL Problem [message #376270 is a reply to message #376227] Tue, 16 December 2008 14:36 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
I have already post the code at the ebgining.Anyway here it is..


 Create or replace type array_1 is table of number;

CREATE OR REPLACE
PROCEDURE Get_count
  (
    p_txt_string IN VARCHAR2,
    rc1 OUT sys_refcursor )
IS
  v_str      VARCHAR2(255);
  v_cnt      NUMBER;
  v_position NUMBER;
  o_word_cnt array_1;
BEGIN
  /* if , is not at end then append */
  IF SUBSTR(p_txt_string,LENGTH(p_txt_string),1) != ',' THEN
    v_str                                        := p_txt_string||',';
  ELSE
    v_str := p_txt_string;
  END IF;
  v_cnt            := 1;
  v_position       := INSTR(v_str,',',1,v_cnt);
  WHILE v_position <> 0
  /* Get word one by one and compare with my_dictionary_tbl
  if found 1 else 0 for each word element
   */
  LOOP
    BEGIN
       SELECT COUNT(*)
         INTO word_cnt(v_cnt)
         FROM my_dictionary_tbl
        WHERE word_txt = GET_WORD(v_str,',',v_cnt)) ||' ',1,50);
v_cnt                 := v_cnt + 1;
v_position            := INSTR(v_str,',',1,v_cnt);
END LOOP;
OPEN RC1 FOR select * from table(cast (o_word_cnt  as ARRAY_1));
END Get_count;


Calling procedure:
/* wants to store in a variable of datatype number */
DECLARE
   txtstr varchar2(30);
   a      NUMBER;
   cv_c1  sys_refcursor;
BEGIN
   GET_COUNT ('AVG,NERO,WINDOWS,ABN',cv_c1);

   LOOP
      FETCH cv_c1 INTO a;
      EXIT WHEN cv_c1%NOTFOUND;
            DBMS_OUTPUT.put_line(a);
   END LOOP;
   --CLOSE cv_c1;
END;




Additional information:
select word_txt from my_dictionary_tbl;

word_txt
________

NERO
AVG
WINDOWS
VISTA


EXPECTED OUTPUT:
WHEN I PASS 'AVG,NERO,WINDOWS,ABN'
THE RESULT SHOULD BE ( FOR MATCHING VALUES 1, ELSE 0)
1
1
1
0

Error received when tried to execute:

Error report:
ORA-06531: Reference to uninitialized collection
ORA-06512: at "AVIK08.GET_COUNT", line 29
ORA-06512: at line 7
06531. 00000 -  "Reference to uninitialized collection"
*Cause:    An element or member function of a nested table or varray
           was referenced (where an initialized collection is needed)
           without the collection having been initialized.
*Action:   Initialize the collection with an appropriate constructor
           or whole-object assignment.


Thanks

[Updated on: Tue, 16 December 2008 14:37]

Report message to a moderator

Re: PL/SQL Problem [message #376275 is a reply to message #376173] Tue, 16 December 2008 15:20 Go to previous messageGo to next message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
The code does not compile... there is an error in this line:
WHERE word_txt = GET_WORD(v_str,',',v_cnt)) ||' ',1,50);

Re: PL/SQL Problem [message #376287 is a reply to message #376270] Tue, 16 December 2008 17:27 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Make the changes:

o_word_cnt array_1:=array_1();


Begin
/* add the below line */
o_word_cnt.extend(100);

IF SUBSTR(p_txt_string,LENGTH(p_txt_string),1) != ',' THEN
..



Regards,
Oli
Previous Topic: BFILE
Next Topic: unable to send mail more then 100ids
Goto Forum:
  


Current Time: Thu Dec 08 21:58:05 CST 2016

Total time taken to generate the page: 0.11524 seconds