You gave the example

SQL> create package my_pkg as type foo is .....


I cant think of an example where you have to create a package as a type of something else,

elaborate the declaration of a package as a type of something else please.
 

ops$tkyte@ORA817DEV.US.ORACLE.COM> create package my_pkg
  2  as
  3          type foo is table of number index by binary_integer;
  4  end;
  5  /

Package created.

I was just using shorthand -- maybe it makes more sense with the line breaks.  

 

The fact that varchar2 have different limits in SQL and PL/SQL is a shame. Why not to equalize CLOB and PL/SQL varchar2? PL/SQL is not constrained by storage access efficiency concerns, after all. I hope this character string mess would be cleaned one day.

On optimistic note, Oracle has a single Date datatype, while other databases have many (and it's the same in SQL and PL/SQL!-).

when we declare a local variable that will
get value from a table column, we do
l_variable table_name.column_name%type;

Now let us say we have an object
create or replace type obj_type as object
(a varchar2(30),
b varchar2(10)
);

If we declare a local variable where we want to
store the value of "a" in the object type, do
we have a similar elegant way?

thanx!

Tom, can you, please, comment on Record vs. Object_Type.

My goal is to have a record type
(data object without methods)
available globally for data transfer between modules.

1. SQL: create type my_rec as record.....
Failed, apparently, it is not possible

2. PLSQL record in package specification:
CoR PACKAGE "RP" AS
TYPE foo IS RECORD (todayy date,numm number);END;

Works fine as RP.FOO, but I was told that pl/sql
memory cache can expell this definition under
heavy load and cause exception (???)

3. SQL: create type my_rec as object..... (with no methods)
Is it good aproarch here? will it be some overheat
versus pl/sql record : get/set operations, IN and OUT
passing, return data from function ?

4. Is there something else to consider?

Thanks a lot in advance

OK, I will consider "record" for our pure pl/sql
and forms project. Thanks again.

Hi Tom,
What can be the use of SQL Types?suppose if I create a sql
type as
sql>create type Names_t as table of varchar2(30);
and if I assign them the ename from EMP table,How can iterate over each value?Do the SQL Types have attributes like "count,exists,delete etc." when compared with PL/SQL
Types?Could you please clarify on this?
Thanks in advance.
Bye!

Tom

I have a procedure which uses record types:


PROCEDURE P_myproc

IS

cursor t_pat is select * from t1;

TYPE pat_array IS TABLE OF t_pat%ROWTYPE index by binary_integer;

l_source pat_array;
l_good pat_array;
l_bad pat_array;



BEGIN

open t_pat ;
loop

fetch t_pat bulk collect into l_source limit 1000;
l_good.delete;
l_bad.delete;


for i in 1 .. l_source.count
loop

if ( F_Format__Data ( l_source(i) ) = 1 )
then


l_good(l_good.count+1) := l_source(i);
else


l_bad(l_bad.count+1) := l_source(i);
end if;

end loop;



forall i in 1 .. l_good.count

insert into t2 values l_good(i);


exit when t_pat%notfound;
end loop;

close t_pat;

END ;



T1 and t2 have the same structure however I would like create an extra field on t2 so that I can set it to a value later on.
So I attempted to change the insert into t2 to specify the field names that the record type would be inserting into eg:


forall i in 1 .. l_good.count

insert into t2 (c1, c2, c3, ...etc ) values l_good(i);


(ie not including my extra field)



but this did not compile, giving me a ORA-00947: not enough values error. Is this not allowed when you assign record types in this way??
Can you only assign a record type to match the exact same table structure?? Is there anyway to achieve this?

(this is 9.2.0.5)

Thanks

Do you need anymore information for the above question?

Ok here is a test case:

drop table t1;
drop table t2;
create table t1 (c1 number, c2 number, c3 number);
create table t2 (c1 number, c2 number, c3 number);

insert into t1 values (1,1,1);
insert into t1 values (2,2,2);
insert into t1 values (3,3,3);
insert into t1 values (4,4,4);

declare

cursor t_pat is select * from t1;

TYPE pat_array IS TABLE OF t_pat%ROWTYPE index by binary_integer;

l_source pat_array;
l_good pat_array;
l_bad pat_array;



BEGIN

open t_pat ;
loop

fetch t_pat bulk collect into l_source limit 1000;
l_good.delete;
l_bad.delete;


for i in 1 .. l_source.count
loop

l_good(l_good.count+1) := l_source(i);
end loop;


forall i in 1 .. l_good.count

insert into t2 values l_good(i);


exit when t_pat%notfound;
end loop;

close t_pat;

END ;
/


This should work fine. Now if i add a column to t2:


drop table t2;
create table t2 (c1 number, c2 number, c3 number, c4 number);

But I still want to insert into t2 so I specify which fields my insert into t2 will insert into:


declare

cursor t_pat is select * from t1;

TYPE pat_array IS TABLE OF t_pat%ROWTYPE index by binary_integer;

l_source pat_array;
l_good pat_array;
l_bad pat_array;



BEGIN

open t_pat ;
loop

fetch t_pat bulk collect into l_source limit 1000;
l_good.delete;
l_bad.delete;


for i in 1 .. l_source.count
loop

l_good(l_good.count+1) := l_source(i);
end loop;


forall i in 1 .. l_good.count

insert into t2 (c1, c2, c3) values l_good(i);


exit when t_pat%notfound;
end loop;

close t_pat;

END ;
/


But I get this error:


TYPE pat_array IS TABLE OF t_pat%ROWTYPE index by binary_integer;
*
ERROR at line 5:
ORA-06550: line 32, column 37:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 32, column 9:
PL/SQL: SQL Statement ignored


So I'm asking why this is? Is this syntax not supported?
Is there no way to do this?

referencing this one:

forall i in 1 .. l_good.count
insert into (select c1, c2, c3 from t2) values l_good(i);

1) what does the "(select c1, c2, c3 from t2)" do effectively? i think, this will only define a (new) valid record structure at parse time?

2) in our article, you wrote that this should not be used as the insert could get wrong if another one recreates the table with another column sequence. is there any way to run this without this explicit declaration?

regards,
markus

hi tom,

CREATE TYPE laps_t AS OBJECT
(client NUMBER, support NUMBER, editeur NUMBER, integrateur NUMBER, tempo NUMBER );
/

CREATE TABLE delay (reference NUMBER, etat VARCHAR2(20), date_chgt DATE,laps LAPS_T);

CREATE OR REPLACE TRIGGER UpdateCpt
AFTER UPDATE ON ICOUR
FOR EACH ROW

DECLARE

valeur NUMBER;
reference NUMBER;
etat VARCHAR2(20);

BEGIN
reference := :OLD.reference;
etat := :OLD.etat;
valeur := 10;

EXECUTE IMMEDIATE 'UPDATE delay d SET d.date_chgt = sysdate,
d.laps.support = valeur WHERE d.reference = reference AND d.etat = etat ';
END ;
/
trigger created

update icour set etat = 'Enregistrement' where reference = 20562;
ERROR at line 1:
ORA-00904: invalid column name
ORA-06512: at "SCOTT.UPDATECPT", line 12
ORA-04088: error during execution of trigger 'SCOTT.UPDATECPT'


I cannot find where the problem is :(
If I replace in the trigger definition d.laps.support = valeur by d.laps.support = 10, this works fine.

any ideas?

Thanks for your help,
Paul