Home » SQL & PL/SQL » SQL & PL/SQL » Comparing comma seperated variable in IN clause
Comparing comma seperated variable in IN clause [message #260901] Tue, 21 August 2007 04:19 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi,
I want to check a variable for IN clause which are comma seperated.. something like...

DECLARE


	lv_US_instance 		VARCHAR2(1000) := '''himdb'',''dyntest''';
	lv_APJ_instance 	VARCHAR2(1000) := '''xdb'',''ydb''';

        lv_instance  VARCHAR2(100);
BEGIN

	SELECT instance_name INTO lv_instance FROM v$instance;

	dbms_output.put_line(lv_US_instance );
	
	IF lv_instance IN (lv_US_instance) THEN
		DBMS_OUTPUT.PUT_LINE('@US_ob_alter_table.sql');
	ELSIF lv_instance IN (lv_APJ_instance) THEN
		DBMS_OUTPUT.PUT_LINE('@APJ_ob_alter_table.sql');
	ELSE
		DBMS_OUTPUT.PUT_LINE('Else part');
	END IF;
	
END;
/


Here the lv_instance can be either "himdb" or "dyntest" and I want to check this for the variable? Its always going in ELSE part..

Regards
Himanshu

[Updated on: Tue, 21 August 2007 04:20]

Report message to a moderator

Re: Comparing comma seperated variable in IN clause [message #260911 is a reply to message #260901] Tue, 21 August 2007 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AskTom: varying elements in IN list

Regards
Michel
Re: Comparing comma seperated variable in IN clause [message #260944 is a reply to message #260901] Tue, 21 August 2007 05:33 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Do I need to create a persistent TYPE in the database? I tried using it by declaring it in the block but it gives error. When I create persistent TYPE in database through CREATE TYPE, then it works...

DECLARE
	
	TYPE string_list_t IS TABLE OF VARCHAR2(1000);
	v_value_list  string_list_t := string_list_t( 'himdb','dyntest' );
	DUMMY CHAR(10);
	lv_instance VARCHAR2(100);
	
BEGIN	
	
	SELECT instance_name INTO lv_instance FROM v$instance;
	
	SELECT 'X' INTO DUMMY
	FROM DUAL
	WHERE lv_instance IN (SELECT * FROM TABLE (cast(v_value_list  AS string_list_t )));
	
	IF  DUMMY = 'X' THEN
		DBMS_OUTPUT.PUT_LINE('success');
	ELSE
		DBMS_OUTPUT.PUT_LINE('fail');
	END IF;
END;
/

 WHERE lv_instance IN (SELECT * FROM TABLE (cast(v_value_list  AS string_list_t )));
                                                                  *
ERROR at line 14:
ORA-06550: line 14, column 67:
PL/SQL: ORA-00902: invalid datatype
ORA-06550: line 12, column 2:
PL/SQL: SQL Statement ignored
Re: Comparing comma seperated variable in IN clause [message #260949 is a reply to message #260944] Tue, 21 August 2007 05:40 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
himang wrote on Tue, 21 August 2007 12:33
Do I need to create a persistent TYPE in the database?

Yes, you do. You are switching to the SQL engine in your SELECT statement.

MHE
Previous Topic: cascade
Next Topic: %Rowtype
Goto Forum:
  


Current Time: Sat Dec 03 08:01:51 CST 2016

Total time taken to generate the page: 0.11338 seconds