Home » SQL & PL/SQL » SQL & PL/SQL » varray in in-list function
varray in in-list function [message #287633] Thu, 13 December 2007 00:27 Go to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

dear experts

Once again i m here and again stuck in one problem .. i already ask this question some weeks ago and get answer and link of asktom.oracle .. but its a lengthy procedure and cant understand. But i hope somebuddy explane me why it is happening.

see the code
select * from emp where job in ('CLERK','MANAGER','SALEMAN');

its fine working .
now make a report on developer 6i r2 with this query its also work fine. but when i use parameter for this in-list like e.g.
:P_JOB and passing all the values in this parameter it doesnt work .
why ?? thats the question .. well report doesnt give any error. i tried by concatination and also tried with chr() charector function but nothing works ..

somebody told me to save your result set in a table and work join condition . Its also a good solution but for that i have to write ip-address too and this report doesnt work if the multiple instances of program are running.

what is the solution .. and why is this happens .. can anybody tell me the reason and solution also. Due to this i am stuck in no. of reports



thanks in advance

regards
anwer
Re: varray in in-list function [message #287638 is a reply to message #287633] Thu, 13 December 2007 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you don't understand the detailled explainations from T. Kyte in the link I posted, I can't see what we can say.

By the way, what don't you understand? Where are you stuck? What did you understand so far?

Regards
Michel
Re: varray in in-list function [message #287643 is a reply to message #287638] Thu, 13 December 2007 00:53 Go to previous messageGo to next message
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 #287653 is a reply to message #287643] Thu, 13 December 2007 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you want to use str2tbl function (there are other ways showed in AskTom thread: varying elements in IN list), you can write (in the current syntax):
C.CODE IN (select * from table(str2tbl(:p_route_Cd)))

Regards
Michel
Re: varray in in-list function [message #287718 is a reply to message #287653] Thu, 13 December 2007 04:43 Go to previous messageGo to next message
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 #287722 is a reply to message #287718] Thu, 13 December 2007 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
:variable is NOT the same thing as &variable.

If you use SQL*Plus and "&variable" you don't need str2tbl function just put "&variable" (without ") in the IN clause.

Regards
Michel
Re: varray in in-list function [message #287729 is a reply to message #287722] Thu, 13 December 2007 05:02 Go to previous messageGo to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

in report i m getting error

ORA-22905: cannot access rows from a non-nested table item 
c.code IN ((select * from ==> table(str2tbl(:p_route_Cd)))



anay suggusions
Re: varray in in-list function [message #287735 is a reply to message #287729] Thu, 13 December 2007 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why do you have 2 ((...))?

In 9i, you have to cast:
c.code IN (select * from table(cast(str2tbl(:p_route_Cd) as myTableType))


Regards
Michel
Re: varray in in-list function [message #287739 is a reply to message #287735] Thu, 13 December 2007 06:00 Go to previous messageGo to next message
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 #287754 is a reply to message #287739] Thu, 13 December 2007 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ORA-24323: value not allowed
 *Cause:  A null value or a bogus value was passed in for a mandatory
          parameter.
 *Action: Verify that all mandatory parameters are properly initialized.

I don't know where you got this error. Are you sure it comes from the query?
Use SQL*Plus and copy and paste the execution.

Regards
Michel
Re: varray in in-list function [message #287913 is a reply to message #287754] Fri, 14 December 2007 00:59 Go to previous messageGo to next message
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 #287918 is a reply to message #287913] Fri, 14 December 2007 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
As you stated, yuo have to set NLS_DATE_FORMAT to a correct value. And is your?
Also check NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_TIME_FORMAT, NLS_TIME_TZ_FORMAT.
What s your Oracle version for client and server with 4 decimals?

Regards
Michel
Re: varray in in-list function [message #287921 is a reply to message #287918] Fri, 14 December 2007 01:14 Go to previous messageGo to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

my oralce version details are
SQL*Plus: Release 9.0.1.3.0 - Production on Fri Dec 14 12:10:03 2007

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

by the way check another trick i create my table in scott same as in my user and then i tried this query and it work see the results

create table sld_info as (select * from igloo.sld_info where code<>'**-kahasd');

  1* select * from sld_info where code in(select * from table(cast(str2tbl1('CLF01K, CLF01P') as myTableType1)))
SQL> /

CODE       CAT_ID     STATION    DESCRIPT                                 K S_CODE     PHONE    N_I_C         LIC_NO       
---------- ---------- ---------- ---------------------------------------- - ---------- -------- ------------- -------------
CLF01K                01         SEA SIDE FISH HOUSE                      R                                                     
CLF01P                01         CLIFTON BLOCK 1&2&3&4                    R                                                     

SQL> 



now what to do .. it means there is somthing strange with my USER derfination .. kindly help me to setup this

Re: varray in in-list function [message #287923 is a reply to message #287921] Fri, 14 December 2007 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You still don't post the nls parameters I requested.

Regards
Michel
Re: varray in in-list function [message #287931 is a reply to message #287923] Fri, 14 December 2007 01:24 Go to previous messageGo to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

Confused
where do i find all this NLS information ..can you tell me


regards

Anwer
Re: varray in in-list function [message #287934 is a reply to message #287931] Fri, 14 December 2007 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
nls_session_parameters view.
Better ask if you don't know rather than ignoring the question.

Regards
Michel
Re: varray in in-list function [message #287935 is a reply to message #287931] Fri, 14 December 2007 01:27 Go to previous messageGo to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

SQL> select * from nls_DATABASE_PARAMETERS ;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
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
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 9.0.1.1.1

20 rows selected.
Re: varray in in-list function [message #287938 is a reply to message #287935] Fri, 14 December 2007 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Don't you know the use of "where" clause, of "order by" clause and of formatting; of course yes, so use it please.

9.0 is the most buggy version Oracle ever released. You should upgrade.

Regards
Michel
Re: varray in in-list function [message #287946 is a reply to message #287938] Fri, 14 December 2007 01:41 Go to previous messageGo to next message
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 #287947 is a reply to message #287946] Fri, 14 December 2007 01:45 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
What do you see when you issue a 'break' command in SQL*Plus? It's a long shot.

MHE
Re: varray in in-list function [message #287949 is a reply to message #287947] Fri, 14 December 2007 01:50 Go to previous messageGo to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

SQL> break
SP2-0015: no break(s) defined
Re: varray in in-list function [message #287954 is a reply to message #287949] Fri, 14 December 2007 01:59 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Bummer. I told you: a long shot. Sad. Have you checked Metalink (Oracle's support website)?

MHE
Re: varray in in-list function [message #287955 is a reply to message #287946] Fri, 14 December 2007 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

i cant understand what do you want

I only ask for a couple of parameters and you posted the whole stuff => use "where"
"order by" is for reading purpose, it is easier to find an information if it is sorted above all when you post 20 unformatted lines.
That's all.

Now you fell into a bug, I don't see anything we can do.
Try to find the differences between your session and SCOTT's one.

Regards
Michel
Re: varray in in-list function [message #287971 is a reply to message #287955] Fri, 14 December 2007 03:07 Go to previous messageGo to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

thank you very much experts michale and maher ..
i really appricaite what all you have done and try to find out solutions .. Now i m stuck in a bug , but i m releexed that its not a part of my job so i will refer this case to my DBA .but any way .. i have learn lots of new things .. now i just wanted to thanks to you and just wanted to know the explanations for


1- what tabletype do and use .. why we use ..
2- what is cast do and what are the use of cast


regards

Anwer
Re: varray in in-list function [message #287974 is a reply to message #287971] Fri, 14 December 2007 03:18 Go to previous message
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
Previous Topic: Transer sql query into oracle function
Next Topic: Count and Rownum calcualtion for a query fetch
Goto Forum:
  


Current Time: Sat Feb 08 07:56:11 CST 2025