A reader, April 04, 2006 - 7:48 pm UTC
Thanks. It worked.
A reader, April 16, 2006 - 11:38 am UTC
DECLARE
TYPE num_tab_type is TABLE OF NUMBER NOT NULL
INDEX BY BINARY_INTEGER;
num_tab num_tab_type;
BEGIN
num_tab(1) := 20;
num_tab(2) := NULL;
END;
/
I am trying to assign null to an index by table which is declared as NOT NULL. I get the following error instead of the numeric or value error exception.
num_tab(2) := NULL;
*
ERROR at line 9:
ORA-06550: line 9, column 18:
PLS-00382: expression is of wrong type
ORA-06550: line 9, column 4:
PL/SQL: Statement ignored
Can you please let me know what is wrong in the above pl/sql block?
April 16, 2006 - 5:54 pm UTC
it is not allowed to take nulls - not null isn't "numeric or value error", it is the exception raised above.
always has been in plsql
Your definition?
Lars Stampe Villadsen, April 17, 2006 - 4:59 am UTC
DECLARE
TYPE num_tab_type is TABLE OF NUMBER NOT NULL
^
|
---------------------------------------------+
That mean that every element must be NOT NULL....
Lars
April 17, 2006 - 8:05 am UTC
I don't understand the point of your comment?
Sorry...
Lars Stampe Villadsen, April 17, 2006 - 1:38 pm UTC
Tom,
My comment was to the orginal "a reader" - simply by pointing out the definition in the block gave the error - not low level Oracle type handling.
Simply removing the "NOT NULL" in the type would execute the block...
Lars
April 17, 2006 - 8:19 pm UTC
ahh, got it...
A reader, April 17, 2006 - 4:18 pm UTC
If the NOT NULL constraint is removed, the block works without any errors. I know that I should not assign NULL to an index-by table which is declared as NOT NULL.
I was just checking to see if I would get "numeric or value error" exception when NULL is assigned to an index-by table declared as NOT NULL.
PLS-00382: expression is of wrong type
Dan, November 15, 2009 - 3:10 pm UTC
Tom:
Not able to make this work. could you tell me what i am doing wrong ?
Basically, i need to store the rowids in the sql collection and then use this to update the actual table using TABLE(CAST..). I tried this script before actual implementation.
ERROR at line 7:
ORA-06550: line 7, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 7, column 10:
PL/SQL: Statement ignored
ORA-06550: line 9, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 9, column 10:
PL/SQL: Statement ignored
ORA-06550: line 11, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 10:
SQL> @testrowid
SQL> drop type rowidtab
2 /
Type dropped.
SQL> create or replace type rowidtyp as object(row_id varchar2(18))
2 /
Type created.
SQL> create or replace type rowidtab as table of rowidtyp
2 /
Type created.
SQL>
SQL> set serveroutput on
SQL> declare
2 l_rowid_array rowidtab := rowidtab() ;
3
4 BEGIN
5
6 l_rowid_array.extend;
7 l_rowid_array(1) := 'AAEH7kAApAAD/3CAAD' ;
8 l_rowid_array.extend;
9 l_rowid_array(2) := 'AAEH7kAApAABRDsAAV' ;
10 l_rowid_array.extend;
11 l_rowid_array(3) := 'AAEH7kAApAABRDsAAV' ;
12 dbms_output.put_line('l_rowid_array.cnt :'||l_rowid_array.count) ;
13
14 END;
15 /
l_rowid_array(1) := 'AAEH7kAApAAD/3CAAD' ;
*
ERROR at line 7:
ORA-06550: line 7, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 7, column 10:
PL/SQL: Statement ignored
ORA-06550: line 9, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 9, column 10:
PL/SQL: Statement ignored
ORA-06550: line 11, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 10:
PL/SQL: Statement ignored
November 15, 2009 - 3:39 pm UTC
ops$tkyte%ORA10GR2> declare
2 l_rowid_array rowidtab := rowidtab() ;
3 BEGIN
4
5 l_rowid_array.extend;
6 l_rowid_array(1) := rowidtyp('AAEH7kAApAAD/3CAAD') ;
7 l_rowid_array.extend;
8 l_rowid_array(2) := rowidtyp('AAEH7kAApAABRDsAAV') ;
9 l_rowid_array.extend;
10 l_rowid_array(3) := rowidtyp('AAEH7kAApAABRDsAAV') ;
11 dbms_output.put_line('l_rowid_array.cnt :'||l_rowid_array.count) ;
12
13 END;
14 /
l_rowid_array.cnt :3
PL/SQL procedure successfully completed.
PLS-00382: expression is of wrong type
sunny gambhir, June 10, 2010 - 5:17 am UTC
i am trying to assign values to a object which is a part of table type
But it is giving me a error
PLS-00382: expression is of wrong type
CREATE OR REPLACE TYPE DateType IS OBJECT ( PayPeriod DATE );
/
CREATE OR REPLACE TYPE TableList IS TABLE OF DateType;
/
DECLARE
List1 TableList;
d date;
BEGIN
List1(1) :='01-jan-2010';
List1(2) :='01-feb-2010';
FOR c1 IN (SELECT payperiod FROM TABLE(CAST(List1 AS TableList))) LOOP
DBMS_OUTPUT.PUT_LINE(c1.payperiod);
END LOOP;
END;
i dont want to assign the values in one go in the table type
i want to do it one by one
June 10, 2010 - 2:21 pm UTC
list1(1) := dateType( to_date( '01-jan-2010', 'dd-mon-yyyy' ) );
would be the right way, you could just:
type datelist is table of datetype;
and just go
list1(1) := to_date( '01-jan-2010', 'dd-mon-yyyy' );
or, even
1 declare
2 list1 sys.odciDateList := sys.odciDateList();
3 begin
4 list1.extend;
5 list1(1) := to_date( '01-jan-2010' );
6* end;
ops$tkyte%ORA10GR2> /
PL/SQL procedure successfully complete
using the type we already provide (don't forget to EXTEND before using!)
PLS-00382: expression is of wrong type
sunny gambhir, June 10, 2010 - 11:35 pm UTC
i am using pl/sql table type not varray
That's why i cannot use extend
So provide a way to assign values in pl/sql table type
June 11, 2010 - 7:26 am UTC
ummm, no you are not
CREATE OR REPLACE TYPE DateType IS OBJECT ( PayPeriod DATE );
/
CREATE OR REPLACE TYPE TableList IS TABLE OF DateType;
/
DECLARE
List1 TableList;
that would be a SQL type.
You are confused, you are NOT using a plsql table type.
sajin mk, July 12, 2012 - 12:09 am UTC
create or replace procedure value_diff
as temps2 number(5);
temps number(5);
k number(5);
begin
for i in (select to_number(substr(f_file_name,18,5),9999)
from iuc_dat_msc001_rafp where f_file_name like '%RCPA1%'
ORDER BY to_date(substr(f_file_name,5,8),'yyyymmdd'))
loop
dbms_output.put_line(i);
temps2:=i;
temps:=i+1;
k:=temps2-temps;
if
k>1 then
dbms_output.put_line('the difference in rows ='|| k );
elsif
k<1 then
k:=abs(k);
dbms_output.put_line('the difference in rows ='|| k );
end if;
end loop;
end;
Compilation errors for PROCEDURE SCOTT.VALUE_DIFF
Error: PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
Line: 11
Error: PL/SQL: Statement ignored
Line: 11
Error: PLS-00382: expression is of wrong type
Line: 12
Text: temps2:=i;
Error: PL/SQL: Statement ignored
Line: 12
Error: PLS-00306: wrong number or types of arguments in call to '+'
Line: 13
Error: PL/SQL: Statement ignored
Line: 13
pls help i just cant figure out what is the problem and
how to rectify it.
July 12, 2012 - 6:08 pm UTC
"i" is a record, name the elements of the record and access them
ops$tkyte%ORA11GR2> create or replace procedure value_diff
2 as temps2 number(5);
3 temps number(5);
4 k number(5);
5
6 begin
7 for i in (select to_number(substr(f_file_name,18,5),9999) number_part
8 from iuc_dat_msc001_rafp
9 where f_file_name like '%RCPA1%'
10 ORDER BY to_date(substr(f_file_name,5,8),'yyyymmdd'))
11 loop
12 dbms_output.put_line(i.number_part);
13 temps2:=i.number_part;
14 temps:=i.number_part+1;
15 k:=temps2-temps;
16 if k!=0
17 then
18 dbms_output.put_line('the difference in rows ='|| abs(k) );
19 end if;
20
21 end loop;
22 end;
23 /
Procedure created.
sajin mk, July 13, 2012 - 4:50 am UTC
Thanks a lot.Atleast now it makes sense.
sajin mk, July 13, 2012 - 5:11 am UTC
Hii tom,i would like to create a table in which if there is a jump of more than two values in the sequence it should dislay the date and the total sum of values jumped for that particular date.
fno f_date
10799 12/31/2011
10944 12/31/2011
10945 12/31/2011
10946 12/31/2011
10947 12/31/2011
10948 12/31/2011
10949 12/31/2011
10950 12/31/2011
10951 12/31/2011
10952 12/31/2011
10748 12/31/2011
10809 12/31/2011
10810 12/31/2011
11014 1/1/2012
11015 1/1/2012
11016 1/1/2012
11017 1/1/2012
11018 1/1/2012
10971 1/1/2012
10972 1/1/2012
10978 1/1/2012
10979 1/1/2012
11003 1/1/2012
11004 1/1/2012
11005 1/1/2012
11006 1/1/2012
for ex.the output should look like this
fl_misd fl_date
410 12/31/2011
67 1/1/2012
July 16, 2012 - 3:04 pm UTC
no create
no inserts
no look
and what does this have in relation to the original question? perhaps a bit of searching would be a good thing to start with?