may this be useful
DECLARE
A NUMBER;
CURSOR CUR1 IS
SELECT * FROM EMP WHERE DEPTNO=&D AND ENAME='&N'
FOR UPDATE OF SAL NOWAIT;
BEGIN
FOR I IN CUR1 LOOP
IF I.SAL > 3000 THEN
UPDATE EMP
SET SAL= I.SAL*1.2
WHERE CURRENT OF CUR1;
END IF;
A := I.SAL;
DBMS_OUTPUT.PUT _LINE(A);
END LOOP;
...
subashsavji
Last Activity: Jun 26 '08, 10:14 AM
Joined: Jan 28 '08
Location:
-
AOL is technical module.
But you choose FinanceLeave a comment:
-
copy and paste TNSNAME.ORA file where you installed d2k.Leave a comment:
-
may be this is helpful .. how to use decode in plsql by using sql only
cursor c1 is
SELECT LORRY_NO, TCS_NO,tcs_date ,branch_branch_ code
FROM CT_TCS where ac_year_code=:a c_year_code
tcs_date between :vtcs1 and :vtcs2
and branch_branch_c ode=:vbran
and lorry_no=decode (:lry,'ALL',lor ry_no,:lry)
ORDER BY LORRY_NO,TCS_NO
begin
open c1
fetch c1..........Leave a comment:
-
This may helpful to you
[code=oracle]
UPDATE employees e
SET department_name =
(SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id );
//
UPDATE employees
SET salary = (SELECT employees.salar y + rewards.pay_rai se
FROM rewards
WHERE employee_id =
employees.emplo yee_id
AND payraise_date =
(SELECT MAX(payraise_da te)...Leave a comment:
-
this may be helpful to you
CREATE OR REPLACE PROCEDURE sal_status
(p_filedir IN VARCHAR2, p_filename IN VARCHAR2)
IS
v_filehandle UTL_FILE.FILE_T YPE;
CURSOR emp_info IS
SELECT ename, sal, deptno
FROM emp
ORDER BY deptno;
v_newdeptno emp.deptno%TYPE ;
v_olddeptno emp.deptno%TYPE := 0;
BEGIN
v_filehandle := UTL_FILE.FOPEN (p_filedir, p_filename,'w') ;...Leave a comment:
-
I think, i am not sure
when we use script means within inverted commas that means both compilation and execution happen at a time. so we can not pass value like this.
try this
[code=oracle]
CREATE OR REPLACE PROCEDURE procc
(p_item IN number,p_rows_d el OUT varchar2)
IS
cursor_name INTEGER;
a varchar2(200);
BEGIN
a:='CREATE TABLE Temp_BlokID_Val ues...Leave a comment:
-
one of the best example to avoid mutation problem
[code=oracle]
Create table CUG
drop table CUG cascade constraints;
create table CUG (
id_cug number(12) not null primary key,
id_B number(12) not null,
type number(1),
foreign key (id_B) references CUG (id_cug)
on delete cascade);
--Next we create a temporary table to avoid...Leave a comment:
-
[code=oracle]
function CF_1Formula return date is
begin
if :hiredate is null then
:cp_1:= :hiredate+30;
return (:cp_1);
else
return(:hiredat e);
end if;
exception
when others then
srw.message(100 0,sqlerrm);
end;
//
[/code]
Note :- You will get result null because you want to add 30 into null date value so anything...Leave a comment:
-
one of the good example to avoid mutation
[code=oracle]
Create table CUG
drop table CUG cascade constraints;
create table CUG (
id_cug number(12) not null primary key,
id_B number(12) not null,
type number(1),
foreign key (id_B) references CUG (id_cug)
on delete cascade);
Next we create a temporary table to avoid the "Mutating...Leave a comment:
-
may be this helpful to
better you declare that date item as varchar2 while displaying convert it using To_char
function....Leave a comment:
-
[code=oracle]
select a.dly_ref_no,a. cns_no,a.cns_da te,a.cnee_code, a.branch_branch _code,a.ranban
where
a.ac_year_code= b.ac_year_code and
a.branch_branch _code=b.branch_ branch_code and
a.branch_branch _code=decode('c olumn_value','A LL',a.branch_br anch_code,'colu mn_value') and
group by a.dly_ref_no,a. cns_no,a.cns_da te,a.cnee_code, a.branch_branch _code,a.ranban
[/code]...Leave a comment:
-
[code=oracle]
create type obj as object(x number)
/
create table objtable of obj
/
declare
type obj1 is table of obj;
z obj1:=obj1(obj( 1),obj(2),obj(3 ),obj(4));
type obj2 is varray(5) of integer;
x obj2:=obj2(1,2, 3);
begin
forall i in z.first..z.last
insert into objtable values(z(i));--Inserts the values in table of objects...Leave a comment:
-
[code=oracle]
create table demo
( id int primary key,
theClob clob
)
/
create or replace directory my_files as 'C:\';
/
CREATE OR REPLACE procedure load_a_file( p_id in number,
p_filename in varchar2 )
as
l_clob clob;
l_bfile bfile;
begin
insert into demo values...Leave a comment:
-
if you want to use LOB to load file then
example of LOB is already posted in this forum. plz. find it out....Leave a comment:
-
“The ORA-01033: ORACLE initialization or shutdown in progress” error happens when the database is not open. After you create the control file issue the command:
“alter database open”
That should stop the ORA-01033 error and make the database available for general use.
//
When trying to connect got the error
ORA-01033: ORACLE initialization or shutdown in progress
...Leave a comment:
-
may be useful
the error messages succeeding the ORA-02068 will
indicate the type/nature of problem that lead to the severe error.
In addition, you may want to check the alert log of the remote
database and the udump directory.
As an aside, you can also close any existing db links before using a
db link. Not a real issue with Oracle to Oracle, but a problem when
dealing with...Leave a comment:
-
Thank you............ ............... .....Leave a comment:
-
//
this example might be helpful to you
CREATE OR REPLACE PROCEDURE query_emp
(p_id IN emp.empNO%TYPE,
p_name OUT emp.Ename%TYPE,
p_salary OUT emp.sal%TYPE,
p_comm OUT emp.comm%TYPE)
IS
BEGIN
SELECT Ename, sal, comm
INTO p_name, p_salary, p_comm
FROM emp
WHERE empNO = p_id;
DBMS_OUTPUT.PUT _LINE(P_ID);...Leave a comment:
-
[code=oracle] CREATE OR REPLACE TRIGGER NEW before UPDATE ON emp1 FOR each ROW
DECLARE
PRAGMA autonomous_tran saction;
BEGIN
DELETE FROM emp11 WHERE ename = :old.ename ;
INSERT INTO emp11 SELECT * FROM emp1 WHERE ename=:old.enam e ;
COMMIT;
END;
update emp1
set ename='parag'
where ename='taj';
[/code]
its working and showing.Leave a comment:
No activity results to display
Leave a comment: