OraFAQ Forum: Forms » PL/SQL assigning SELECT statement to variable (merged)

Home » Developer & Programmer » Forms » PL/SQL assigning SELECT statement to variable (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PL/SQL assigning SELECT statement to variable (merged) [message #418433]

Sat, 15 August 2009 04:38

siddiqui88
Messages: 18
Registered: July 2009
Location: Pakistan

Junior Member

can i do this. but its not work plzz its urgent.

DECLARE
PlateExists VARCHAR2(20);
PlateExists := (SELECT COUNT(*) FROM MyTable WHERE KiteID = 'A1025')
BEGIN
IF PlateExists = 0 THEN
INSERT INTO MyTable values('A1025', '0', 304);
END IF;
--other code here
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;

plzz tel me how can i assigning select statement to a variable and how can i use it in the body plzz reply

Report message to a moderator

Re: PL/SQL assigning SELECT statement to variable [message #418436 is a reply to message #418433]

Sat, 15 August 2009 04:42

delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City

Senior Member

Use SELECT INTO statement in BEGIN section.

regards,
Delna

Report message to a moderator

Re: PL/SQL assigning SELECT statement to variable [message #418437 is a reply to message #418433]

Sat, 15 August 2009 04:59

siddiqui88
Messages: 18
Registered: July 2009
Location: Pakistan

Junior Member

how if

declare
v1 varchar2(200);
v1:= select * from emp;
begin
select v1 into :emp.empno, ....
end;

is it ok
or its work

Report message to a moderator

Re: PL/SQL assigning SELECT statement to variable [message #418438 is a reply to message #418433]

Sat, 15 August 2009 05:03

delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City

Senior Member

Are you asking? Or just informing?
And if it works, then fine. No worries.

But, I think, it wont work.

regards,
Delna

Report message to a moderator

Re: PL/SQL assigning SELECT statement to variable [message #418439 is a reply to message #418433]

Sat, 15 August 2009 05:06

siddiqui88
Messages: 18
Registered: July 2009
Location: Pakistan

Junior Member

thanks 4 reply

Report message to a moderator

Re: PL/SQL assigning SELECT statement to variable [message #418440 is a reply to message #418433]

Sat, 15 August 2009 05:14

delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City

Senior Member

While posting your question next time, don't forget to format it properly.

In your current problem, do as follow

DECLARE
   <variable declaration>;
BEGIN
   SELECT <column_name> INTO <variable name> FROM <table_name>;
   ...
END;

regards,
Delna

Report message to a moderator

Re: PL/SQL assigning SELECT statement to variable [message #418441 is a reply to message #418433]

Sat, 15 August 2009 05:47

siddiqui88
Messages: 18
Registered: July 2009
Location: Pakistan

Junior Member

this is the code on when_button_press plzz help me to solve this problem.

declare
v1 varchar2(200):= 'select :main.empno_op from emp';

CURSOR CHK IS
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP WHERE 'EMPNO'||v1||':MAIN.EMPNO' OR ':MAIN.EMPNO' IS NULL;

BEGIN

OPEN CHK;
LOOP
FETCH CHK INTO :SEARCH.EMPNO,:SEARCH.ENAME,:SEARCH.JOB, :SEARCH.MGR, :SEARCH.HIREDATE, :SEARCH.SAL, :SEARCH.COMM, :SEARCH.DEPTNO;
EXIT WHEN CHK%NOTFOUND;
NEXT_RECORD;
END LOOP;
CLOSE CHK;

GO_BLOCK('MAIN');
END;

and form is attached plzz view it then u remember what iam going to do.