Home » SQL & PL/SQL » SQL & PL/SQL » Passing multiple value (Oracle 9.2.0.1)
Passing multiple value [message #377336] Mon, 22 December 2008 08:01 Go to next message
avik2009
Messages: 61
Registered: November 2008
Member

I want to pass the string 'SYS','SYSTEM' as V1 into the procedure
CREATE PROCEDURE TEST_DUMMY
( v1 IN VARCHAR2,
  RC1 OUT SYSREFCURSOR)
  IS
  BEGIN
  OPEN RC1 FOR 
  SELECT COUNT(*) INTO v1 where USERNAME IN (v1);
  END;
  /
Re: Passing multiple value [message #377339 is a reply to message #377336] Mon, 22 December 2008 08:02 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Search for "varying in-list" in this forum. You will find loads of answers.

Regards

Raj
Re: Passing multiple value [message #377340 is a reply to message #377336] Mon, 22 December 2008 08:03 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member

CREATE PROCEDURE TEST_DUMMY
( v1 IN VARCHAR2,
  RC1 OUT SYSREFCURSOR)
  IS
  BEGIN
  OPEN RC1 FOR 
  SELECT COUNT(*) INTO v1 from ALL_USERS where USERNAME IN (v1);
  END;
  /

Re: Passing multiple value [message #377345 is a reply to message #377339] Mon, 22 December 2008 08:12 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
Cant it be possible without varrying list actually my original query is a complex query. Can you please provide a solution for this? It will help me to understand the use of it
Re: Passing multiple value [message #377348 is a reply to message #377336] Mon, 22 December 2008 08:20 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
From the front end I am passing a string: 'SYS','SYSTEM'

Why would it not work if I pass it as

where username in (v1);

Re: Passing multiple value [message #377353 is a reply to message #377348] Mon, 22 December 2008 08:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It won't work because IN looks for a comma separated list of items, not a string containing a comma separated list of items.

It's exactly the same reason that
DECLARE
  v_table_name  varchar2(30) := 'DUAL';
  v_count       pls_integer;
BEGIN
  SELECT count(*)
  INTO   v_count
  FROM   v_table_name;
END;
/

Doesn't work - there is a findamental differentce between an object specified as part of a query and the contents of a string.
Re: Passing multiple value [message #377358 is a reply to message #377353] Mon, 22 December 2008 08:51 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
But the in parameter (v1) that is being passed from front end holds the value
as 'SYS','SYSTEM' (both quote and delimeter are passed


Then why cant be?


Re: Passing multiple value [message #377360 is a reply to message #377336] Mon, 22 December 2008 08:54 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
I meant I just want to replace the string value
'SYS','SYSTEM' by V1



where col1 in ('SYS','SYSTEM')



where col1 in (v1)




From front end the string being passed to V1 is 'SYS','SYSTEM'


Or take it this way..
v1:='SYS','SYSTEM'
BEGIN
..
OPEN RC1 FOR
SELECT * FROM ALL_USERS WHERE NAME IN(V1);



* I have no Oracle installed in my machine at this moment.So, the reason why I am unable to check as of now.

[Updated on: Mon, 22 December 2008 08:57]

Report message to a moderator

Re: Passing multiple value [message #377362 is a reply to message #377360] Mon, 22 December 2008 08:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your string's value is not
'SYS','SYSTEM'

Your string's value is
''SYS','SYSTEM''


Search for 'Varying Inlist' to see ways of solving this problem.
Re: Passing multiple value [message #377366 is a reply to message #377360] Mon, 22 December 2008 09:16 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

* I have no Oracle installed in my machine at this moment.So, the reason why I am unable to check as of now.


If you want, you could create an account in apex.oracle.com and test your queries. Only thing to bear in mind is it is running on Oracle 11g.

Regards

Raj
Re: Passing multiple value [message #377368 is a reply to message #377362] Mon, 22 December 2008 09:48 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
Then cant we remove firstand last occurance of ' instead of varying in-list


[Updated on: Mon, 22 December 2008 09:49]

Report message to a moderator

Re: Passing multiple value [message #377370 is a reply to message #377368] Mon, 22 December 2008 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JRowbottom's answer was just a way of speaking, an image not the real thing.
Your string variale is a single string.
IN (<list>) wait for a list of values or variables.
When you put your variable inside IN there is no substitution in the query string/text. You put ONE variable so there is ONE value.

Regards
Michel
Re: Passing multiple value [message #377377 is a reply to message #377336] Mon, 22 December 2008 10:37 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
SQL>
CREATE OR REPLACE type str2tblType
AS  TABLE OF VARCHAR2(4000) 
/
Type created.
SQL>
CREATE OR replace2
FUNCTION str2tbl3
  (
    p_str     IN VARCHAR2,
     p_delim IN VARCHAR2 DEFAULT ',' )
  RETURN str2tblType as l_str LONG DEFAULT p_str || p_delim;
   l_n NUMBER;
 l_data str2tblType  := str2tblType();
   begin11 loop12 l_n := instr( l_str, p_delim );
  
  EXIT
WHEN (NVL(l_n,0) = 0);
   l_data.extend;
   l_data(l_data.count) := ltrim(rtrim(SUBSTR(l_str,1,l_n-1)));
   l_str                := SUBSTR( l_str, l_n            +1 );

END LOOP;

RETURN l_data;

END;
/

How or what needs to be modified to make the procedure( the one that I posted in my previous post) work?

[Updated on: Mon, 22 December 2008 10:37]

Report message to a moderator

Re: Passing multiple value [message #377378 is a reply to message #377336] Mon, 22 December 2008 10:43 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
CREATE PROCEDURE TEST_DUMMY
( v1 IN VARCHAR2,
  RC1 OUT SYSREFCURSOR)
  IS
  BEGIN
  OPEN RC1 FOR 
  SELECT COUNT(*) INTO v1 from ALL_USERS where USERNAME IN (v1);
  END;
  /


In the above how can I replace with the function. Please help!
Re: Passing multiple value [message #377381 is a reply to message #377378] Mon, 22 December 2008 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does your ugly function do?
Can you explain it?
(Also I hope you don't really code as you posted otherwise I pity those who will come after you.)

Regards
Michel
Re: Passing multiple value [message #377392 is a reply to message #377381] Mon, 22 December 2008 12:41 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
Is it too ugly?
SQL> exec :txt := 'SYS, SYSTEM'
PL/SQL procedure successfully completed.
SQL> select *  from TABLE( cast( str2tbl(:txt) as str2TblType ) )  
/


COLUMN_VAL
----------
SYS
SYSTEM

Is anything wrong in below procedure?

CREATE PROCEDURE TEST_DUMMY
( v1 IN VARCHAR2,
  RC1 OUT SYSREFCURSOR)
  IS
  BEGIN
  OPEN RC1 FOR 
  SELECT COUNT(*)  from ALL_USERS where USERNAME IN (select *  from TABLE( cast( str2tbl(v1) as str2TblType ) ));
  END;
  /

[Updated on: Mon, 22 December 2008 12:43]

Report message to a moderator

Re: Passing multiple value [message #377393 is a reply to message #377392] Mon, 22 December 2008 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it too ugly?

In how it is written (or posted), yes.

So now you know that your function returns a "table", how do you use the content of a table in "IN"?

Regards
Michel
Re: Passing multiple value [message #377396 is a reply to message #377393] Mon, 22 December 2008 14:07 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
Oh sorry the code was not formatted!

SQL>
CREATE OR REPLACE type str2tblType AS  TABLE OF VARCHAR2(4000) 
/
Type created.
SQL>
CREATE OR replace FUNCTION str2tbl3
  (  p_str     IN VARCHAR2,
     p_delim IN VARCHAR2 DEFAULT ',' )
  RETURN str2tblType as l_str LONG DEFAULT p_str || p_delim;
   
   l_n NUMBER;
   l_data str2tblType  := str2tblType();
 begin
   loop
      l_n := instr( l_str, p_delim );
  
     EXIT WHEN (NVL(l_n,0) = 0);
     l_data.extend;
     l_data(l_data.count) := ltrim(rtrim(SUBSTR(l_str,1,l_n-1)));
     l_str  := SUBSTR( l_str, l_n +1 );

   END LOOP;

RETURN l_data;

END;
/

Re: Passing multiple value [message #377399 is a reply to message #377396] Mon, 22 December 2008 14:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So now you know that your function returns a "table", how do you use the content of a table in "IN"?

Regards
Michel
Re: Passing multiple value [message #377410 is a reply to message #377392] Mon, 22 December 2008 20:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
SYS_REFCURSOR not SYSREFCURSOR and make sure your function names match. You have created your function as str2tbl3 and are calling str2tbl.

[Updated on: Mon, 22 December 2008 20:09]

Report message to a moderator

Re: Passing multiple value [message #377446 is a reply to message #377410] Tue, 23 December 2008 01:51 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Barbara , the "3" comes from a bad copy and paste, it is the line number, as well as the "2" after "create and replace2" in the previous post.
This is what happens when people fakes their output.

Regards
Michel
Previous Topic: Insufficient Previleges
Next Topic: Serialize an existing table
Goto Forum:
  


Current Time: Sun Dec 04 17:02:04 CST 2016

Total time taken to generate the page: 0.07223 seconds