Home » SQL & PL/SQL » SQL & PL/SQL » IF condition on PLSQL
IF condition on PLSQL [message #341304] Mon, 18 August 2008 04:55 Go to next message
kmkumar24
Messages: 69
Registered: January 2007
Location: Singapore
Member
Hello,

I am trying to write IF condition inside the cursor, but it is giving error. Can any one provide the exact statement for this?

IF l_USER_ID=(Select * from OS_AGENCY_USER where USER_ID = FOS.USER_ID) then
insert into PV_USERPREF (USERID,NAME,VALUE)
values ((Select USERID from PV_USER where USERNAME = OS_AGENCY_USER.USER_ID),'USER_TYPE','PUBLIC');

ELSE
insert into PV_USERPREF (USERID,NAME,VALUE)
values ((Select USERID from PV_USER where USERNAME = OS_AGENCY_USER.USER_ID),'USER_TYPE','ORGANISATION');

END IF;

ERROR is:

IF l_USER_ID=(Select * from OS_AGENCY_USER where USER_ID = FOS.USER_ID) then
*
ERROR at line 55:
ORA-06550: line 55, column 14:
PLS-00405: subquery not allowed in this context
ORA-06550: line 55, column 1:
PL/SQL: Statement ignored

Thanks in Advance
Kumar
Re: IF condition on PLSQL [message #341305 is a reply to message #341304] Mon, 18 August 2008 05:06 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Store result of the SELECT statement into a variable, and then use this variable in the IF statement (IF l_user_id = newly_created_variable THEN ...).
Re: IF condition on PLSQL [message #341459 is a reply to message #341305] Mon, 18 August 2008 22:00 Go to previous messageGo to next message
kmkumar24
Messages: 69
Registered: January 2007
Location: Singapore
Member
As I am new to plsql could you let me know how to assign it to the variable. This is my querry

select * from os_agency_user,os_user where os_agency_user.user_id=os_user.user_id;

Thanks

Kumar
Re: IF condition on PLSQL [message #341461 is a reply to message #341304] Mon, 18 August 2008 22:05 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above


>As I am new to plsql could you let me know how to assign it to the variable.
To what does "it" refer?


v_name := 'assign string to variable';
Re: IF condition on PLSQL [message #341464 is a reply to message #341304] Mon, 18 August 2008 23:15 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> As I am new to plsql could you let me know how to assign it to the variable.

As you are new to PL/SQL, you shall learn it from a senior colleague, attend an Oracle PL/SQL course, or, if nothing available, search and study the documentation, found e.g.online on http://tahiti.oracle.com/.

PL/SQL User's Guide and Reference
Chapter 6 Performing SQL Operations from PL/SQL
Querying Data with PL/SQL
Selecting At Most One Row: SELECT INTO Statement
Re: IF condition on PLSQL [message #341471 is a reply to message #341464] Mon, 18 August 2008 23:55 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some are quite reluctant from reading documentation; from his previous post:
Michel Cadot wrote on Wed, 30 July 2008 12:21
As for your previous topic:
Michel Cadot wrote on Wed, 30 July 2008 11:59
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals
SQL Reference, CREATE PROCEDURE

Regards
Michel




Previous Topic: how to create file with spanish characters using SPOOL
Next Topic: can some body explin what this pragma does RESTRICT_REFERENCES ?
Goto Forum:
  


Current Time: Mon Dec 05 11:13:33 CST 2016

Total time taken to generate the page: 0.08302 seconds