Home » SQL & PL/SQL » SQL & PL/SQL » varray in in-list function
|
|
Re: varray in in-list function [message #287643 is a reply to message #287638] |
Thu, 13 December 2007 00:53   |
annu-agi
Messages: 238 Registered: July 2005 Location: Karachi
|
Senior Member |
 
|
|
thanks michale for early reply
i doestnt understand why it happins first ..because there is no errors and this query is working fine in sql plus then why dont in reports ..
and the next see what tom says
You can use a bind variable in an IN but I don't think it'll do what you want. I believe
you want a string that has the value:
1, 2, 3
for example (a single variable) to be used in a query like:
select * from t where x in ( :that_variable );
and have all rows returned such that x is in the set of 1, 2, 3. What would happen
though is that every row in T such that x = "1, 2, 3" would be returned (if it worked the
other way -- you would never be able to IN on something with commas in it).
One 'trick' that works very well on Oracle8.0 and up is to use an object type that is a
nested table type and a simple parse routine that returns this nested table type given a
string input. What I mean is best explained via an example:
ops$tkyte@8i> create or replace type myTableType as table of number;
2 /
Type created.
ops$tkyte@8i> create or replace function str2tbl( p_str in varchar2 ) return myTableType
2 as
3 l_str long default p_str || ',';
4 l_n number;
5 l_data myTableType := myTabletype();
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 l_data.extend;
11 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
12 l_str := substr( l_str, l_n+1 );
13 end loop;
14 return l_data;
15 end;
16 /
Function created.
ops$tkyte@8i>
ops$tkyte@8i> select * from all_users
2 where user_id in ( select *
3 from THE ( select cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual ) )
4 /
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM 5 20-APR-99
how can i use this procedure as a reference in my query
while my report query is
select a.d_code, a.name, B.CODE STAT_CD ,B.DESCRIPT STAT_NM, c.code ROUTE_CD, C.DESCRIPT ROUTE_NM,
A.VISIT1,A.VISIT2,A.VISIT3,A.VISIT4,A.VISIT5,A.VISIT6,A.VISIT7,A.PHONE, A.ADDRESS1||' '||
A.ADDRESS2||' '||A.ADDRESS3
from
(SELECT * FROM DEALER_INFO WHERE (VISIT1||VISIT2||VISIT3||VISIT4||VISIT5||VISIT6||VISIT7) IS NOT NULL ) a,
(select CODE, DESCRIPT from sld_info WHERE KEYFIELD='T') B,
(select CODE, STATION, DESCRIPT from sld_info WHERE KEYFIELD='R') c
where
B.CODE=SUBSTR(D_cODE,1,2) AND
B.CODE=:p_station_Cd AND
c.station=B.CODE AND
C.CODE=SUBSTR(D_CODE,3,6) AND
C.CODE IN (:p_route_Cd)
ORDER BY 1
any suggusions
regards
anwer
|
|
|
|
Re: varray in in-list function [message #287718 is a reply to message #287653] |
Thu, 13 December 2007 04:43   |
annu-agi
Messages: 238 Registered: July 2005 Location: Karachi
|
Senior Member |
 
|
|
thank you michale
i have done the same as you told me .. but in the end i m getting
1 select a.d_code, a.name, B.CODE STAT_CD ,B.DESCRIPT STAT_NM, c.code ROUTE_CD, C.DESCRIPT ROUTE_NM,
2 A.VISIT1,A.VISIT2,A.VISIT3,A.VISIT4,A.VISIT5,A.VISIT6,A.VISIT7,A.PHONE, A.ADDRESS1||' '||
3 A.ADDRESS2||' '||A.ADDRESS3
4 from
5 (SELECT * FROM DEALER_INFO WHERE (VISIT1||VISIT2||VISIT3||VISIT4||VISIT5||VISIT6||VISIT7) IS NOT NULL ) a,
6 (select CODE, DESCRIPT from sld_info WHERE KEYFIELD='T') B,
7 (select CODE, STATION, DESCRIPT from sld_info WHERE KEYFIELD='R') c
8 where
9 B.CODE=SUBSTR(D_cODE,1,2) AND
10 B.CODE='01' AND
11 c.station=B.CODE AND
12 C.CODE=SUBSTR(D_CODE,3,6) AND
13 C.CODE IN (select * from table(str2tbl(&p_route_Cd)))
14* ORDER BY 1
SQL> /
Enter value for p_route_cd: 'KHA11A, KHA11B, KHA11C, KHA11D, KHA11E, KHA11F, KHA11G'
old 13: C.CODE IN (select * from table(str2tbl(&p_route_Cd)))
new 13: C.CODE IN (select * from table(str2tbl('KHA11A, KHA11B, KHA11C, KHA11D, KHA11E, KHA11F, KHA11G')))
select a.d_code, a.name, B.CODE STAT_CD ,B.DESCRIPT STAT_NM, c.code ROUTE_CD, C.DESCRIPT ROUTE_NM,
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
And if try to run it again then i give me another error
SQL> /
Enter value for p_route_cd: 'KHA11A, KHA11B, KHA11C, KHA11D, KHA11E, KHA11F, KHA11G'
old 13: C.CODE IN (select * from table(str2tbl(&p_route_Cd)))
new 13: C.CODE IN (select * from table(str2tbl('KHA11A, KHA11B, KHA11C, KHA11D, KHA11E, KHA11F, KHA11G')))
select a.d_code, a.name, B.CODE STAT_CD ,B.DESCRIPT STAT_NM, c.code ROUTE_CD, C.DESCRIPT ROUTE_NM,
*
ERROR at line 1:
ORA-01041: internal error. hostdef extension doesn't exist
SQL>
by the way you can check that while making this routine i didnt get any error
SQL> create or replace type myTableType as table of number;
2
3
4 .
SQL>
SQL>
SQL> /
Type created.
SQL> create or replace function str2tbl( p_str in varchar2 ) return myTableType
2 as
3 l_str long default p_str || ',';
4 l_n number;
5 l_data myTableType := myTabletype();
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 l_data.extend;
11 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
12 l_str := substr( l_str, l_n+1 );
13 end loop;
14 return l_data;
15 end;
16 .
SQL> /
Function created.
any suggustions
|
|
|
|
|
|
Re: varray in in-list function [message #287739 is a reply to message #287735] |
Thu, 13 December 2007 06:00   |
annu-agi
Messages: 238 Registered: July 2005 Location: Karachi
|
Senior Member |
 
|
|
sorry to bothering you again
but this time i m getting this error in reports
ora-24323: value not allowed
select a.d_code, a.name, B.CODE STAT_CD ,B.DESCRIPT STAT_NM, c.code ROUTE_CD, C.DESCRIPT ROUTE_NM,
now my report query is like this
select a.d_code, a.name, B.CODE STAT_CD ,B.DESCRIPT STAT_NM, c.code ROUTE_CD, C.DESCRIPT ROUTE_NM,
A.VISIT1,A.VISIT2,A.VISIT3,A.VISIT4,A.VISIT5,A.VISIT6,A.VISIT7,A.PHONE, A.ADDRESS1||' '||
A.ADDRESS2||' '||A.ADDRESS3
from
(SELECT * FROM DEALER_INFO WHERE (VISIT1||VISIT2||VISIT3||VISIT4||VISIT5||VISIT6||VISIT7) IS NOT NULL ) a,
(select CODE, DESCRIPT from sld_info WHERE KEYFIELD='T') B,
(select CODE, STATION, DESCRIPT from sld_info WHERE KEYFIELD='R') c
where
B.CODE=SUBSTR(D_cODE,1,2) AND
B.CODE=:p_station_Cd AND
c.station=B.CODE AND
C.CODE=SUBSTR(D_CODE,3,6) AND
c.code IN (select * from table(cast(str2tbl(:p_route_Cd) as myTableType)))
ORDER BY 1
i tried to solve it by myself but it wont work .. after entering this change my report builder is stuck and after some mins. i have to close it down and restart my report builder again .. other wise this error continiously come on screen till you restaret your r-builder
any suggustions
|
|
|
|
Re: varray in in-list function [message #287913 is a reply to message #287754] |
Fri, 14 December 2007 00:59   |
annu-agi
Messages: 238 Registered: July 2005 Location: Karachi
|
Senior Member |
 
|
|
hi experts once again
ok forget my table and data .. lets do it with SCOTT.emp
see that i create table type and functions
SQL> create or replace type myTableType as table of number;
2 .
SQL> /
Type created.
SQL> ed
Wrote file afiedt.buf
1 create or replace function str2tbl( p_str in varchar2 ) return myTableType
2 as
3 l_str long default p_str || ',';
4 l_n number;
5 l_data myTableType := myTabletype();
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 l_data.extend;
11 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
12 l_str := substr( l_str, l_n+1 );
13 end loop;
14 return l_data;
15* end;
SQL> /
Function created.
now check the the results
1 select * from all_users
2 where user_id in ( select *
3* from THE ( select cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual ) )
SQL> /
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM 5 04-SEP-01
SQL>
now try with JOBS
1 select * from emp where job in
2 (
3 select * from table
4 (cast
5 (str2tbl
6 ('CLERK, MANAGER, SALESMAN')
7* as myTableType)))
SQL> /
select * from emp where job in
*
ERROR at line 1:
ORA-01722: invalid number
it should be because of tabletype is number datatype .. so we create varchar2(200) now our type type is MYTABLETYPE1 and function is STR2TBL1
SQL> create or replace type myTableType1 as table of varchar2(200);
2 /
Type created.
1 create or replace function str2tbl1( p_str in varchar2 ) return myTableType1
2 as
3 l_str long default p_str || ',';
4 l_n number;
5 l_data myTableType1 := myTabletype1();
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 l_data.extend;
11 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
12 l_str := substr( l_str, l_n+1 );
13 end loop;
14 return l_data;
15* end;
SQL> /
Function created.
chek the result in varchar2
1* select * from emp where job in(select * from table(cast(str2tbl1('CLERK, SALESMAN') as myTableType1)))
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7900 JAMES CLERK 7698 03-DEC-81 950 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
8 rows selected.
now the same thing i create for my USER SCHEMA because my in-lint is also charector based this time i named myTableType2 and
STR2TBL2
see the coding
1* create or replace type myTableType2 as table of varchar2(200)
SQL> /
Type created.
SQL> ed
Wrote file afiedt.buf
1 create or replace function str2tbl2( p_str in varchar2 ) return myTableType2
2 as
3 l_str long default p_str || ',';
4 l_n number;
5 l_data myTableType2 := myTabletype2();
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 l_data.extend;
11 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
12 l_str := substr( l_str, l_n+1 );
13 end loop;
14 return l_data;
15* end;
SQL>
SQL> /
Function created.
and now my results is
1* select * from sld_info where code in(select * from table(cast(str2tbl2('CLF01K, CLF01P') as myTableType2)))
SQL> /
SP2-0642: SQL*Plus internal error state 2133, context 0:0:0
Unsafe to proceed
SP2-0642: SQL*Plus internal error state 2221, context 4294967295:4:0
Unsafe to proceed
SQL>
This is due an incorrect NLS_DATE_FORMAT setting in either -
SYSTEM ENVIRONMENT SETTINGS
USER ENVIRONMENT SETTINGS
Or
REGISTRY SETTINGS:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\<ORACLE_HOME> (NLS_DATE_FORMAT)
If a date other than the normal DD-MON-RR , DD/MM/YY, etc..then the
above occurs.
Set the NLS_DATE_FORMAT date to a correct format and all Oracle
executables or utilities will work correctly again.
You can either set your env by manually adjusting the ORACLE_HOME and
ORACLE_SID variables (set ORACLE_HOME...) or, use the Oracle home
switching
feature that should have been installed with the software of either
Oracle
home...
I check my registry and my oracle home nsl lang is AMERICAN_AMERICA.WE8MSWIN1252
NOW WHAT TO DO NEXT .. how to get out of this problem .. and you can see this whole procedure works in scott and with emp but not with my table ..
regards
Anwer
|
|
|
|
|
|
|
|
|
|
Re: varray in in-list function [message #287946 is a reply to message #287938] |
Fri, 14 December 2007 01:41   |
annu-agi
Messages: 238 Registered: July 2005 Location: Karachi
|
Senior Member |
 
|
|
thanks for encourgment for asking questions rather then ignore...
what do you mean my
Don't you know the use of "where" clause, of "order by" clause and of formatting; of course yes, so use it please.
i cant understand what do you want
SQL> select * from sld_info where code in(select * from table(cast(str2tbl2('CLF01K, CLF01P') as myTableType2)))
2 order by 1
3 .
SQL> /
SP2-0642: SQL*Plus internal error state 2133, context 0:0:0
Unsafe to proceed
SP2-0642: SQL*Plus internal error state 2221, context 4294967295:4:0
Unsafe to proceed
SQL>
by the way
my session parameters are
SQL> select * from NLS_SESSION_PARAMETERS ;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
[mod-edit]Corrected code tag, sorry.
[Updated on: Fri, 14 December 2007 01:44] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
Re: varray in in-list function [message #287974 is a reply to message #287971] |
Fri, 14 December 2007 03:18  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
1. A table is something that stores a set of data. A table of varchar2 contains a set of strings
2. cast changes or here precises the type of a variable
Regards
Michel
|
|
|
Goto Forum:
Current Time: Sat Feb 08 07:56:11 CST 2025
|