April 14, 2008

Oracle scripts

-- Create table
create table Owner1.Table_Name1
(
Col11 NUMBER(5) not null,
Col12 NUMBER(3),
Col13 VARCHAR2(10)
);
create table Owner1.Table_Name2
(
Col21 NUMBER(5) not null,
Col22 NUMBER(3),
Col23 VARCHAR2(10)
);
-- Add comments to the table
comment on table Owner1.Table_Name1 is ‘Test Table1.';

-- Create/Recreate primary, unique and foreign key constraints
alter table Owner1.Table_Name1
add constraint PK_Col11 primary key (Col11);
alter table Owner1.Table_Name1
add constraint FK_ Col12 foreign key (Col12)
references Owner1.Table_Name2 (Col22)
deferrable;

-- Create/Recreate check constraints
alter table Owner1.Table_Name1
add constraint NN_ Table_Name1_ Col11
check ("Col11" IS NOT NULL);

-- Grant/Revoke object privileges
grant select, references on Owner1.Table_Name1 to Owner2 with grant option;
grant select on Owner2.Table_Name2 to PUBLIC;

-- create or replace function
create or replace function F_Function(istr1 varchar2,
inum1 number)
return varchar2 is
-- return number is
-- Declare variables
num1 number;
str1 varchar(30);
str2 varchar(30);
begin
str1 := istr1;
If
/* Code1 */
return str1 ;
else
/* Code1 */
end if;
return str2;
end F_Function;
-- CREATE OR REPLACE PROCEDURE
CREATE OR REPLACE PROCEDURE P_PROC1as
-- Cursor to get REQUIRED INFO FROM BASE TABLES
Cursor C_CURSOR
is select t1.col1
from table1 t1, table2 t2
where t1.col1=t2.col2;
val number;
begin
OPEN C_CURSOR ;
loop
-- To get next value
fetch C_CURSOR into val;
EXIT WHEN C_CURSOR%NOTFOUND;
insert into t2(col2)
select col1
from t1
where t1.col1=val;
end loop;
CLOSE C_CURSOR;
end;

No comments: