OraFAQ Forum: SQL & PL/SQL » PL/SQL statement

Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL statement (Oracle 11g 11.1.0.7.0)

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

PL/SQL statement [message #606348]

Wed, 22 January 2014 10:50

SteveShephard
Messages: 41
Registered: August 2012

Member

Hi All,

I am trying to write a PL/SQL routine using the select into statement to run an internal command. However the problem I have is the query returns multiple values and therefore the whole statement errors (exact fetch returns move than requested number of rows)

Here is the kind of thing I am writing at the moment. I think I need to use a cursor and loop through but not sure where and how to put this into the statement?

DECLARE
   name_ VARCHAR2(100);

  BEGIN
select name into name_ from employee where name like = 'A%';
  MY_COMMAND.START(name_);
END;

Any help would be really appreciated.

Many thanks

Steve

Report message to a moderator

Re: PL/SQL statement [message #606349 is a reply to message #606348]

Wed, 22 January 2014 10:53

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Read cursor loop in:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Report message to a moderator

Re: PL/SQL statement [message #606350 is a reply to message #606348]

Wed, 22 January 2014 12:34

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

SteveShephard wrote on Wed, 22 January 2014 22:20

I think I need to use a cursor and loop through but not sure where and how to put this into the statement?
DECLARE
   name_ VARCHAR2(100);

  BEGIN
select name into name_ from employee where name like = 'A%';
  MY_COMMAND.START(name_);
END;

Per Oracle :
"The nice thing about the cursor FOR loop is that Oracle Database opens the cursor, declares a record by using %ROWTYPE against the cursor, fetches each row into a record, and then closes the loop when all the rows have been fetched (or the loop terminates for any other reason)."

So, you could try something like :

FOR indx IN (select * from employee where .....)
   LOOP
      MY_COMMAND.START(indx.column);
   END LOOP;

Report message to a moderator

Re: PL/SQL statement [message #606397 is a reply to message #606350]

Thu, 23 January 2014 05:19

SteveShephard
Messages: 41
Registered: August 2012

Member

Thanks Lalit that work great! However my command is an email (there are several parameters needed to run it). So with the use of this loop an email is sent containing the name in the message. Since it loops though the entire command multipe emails can be sent.

e.g. email one - Name contained within the email is John. Email two - Name contained within the email is Alan.

Ideally I would like one email containing both names John Alan.

Is it possible to add a loop into a single parameter within a command. Something similar to below:

BEGIN
MY_COMMAND.START
(
'PARAMETER_1',
'PARAMETER_2',
FOR indx IN (select NAME from EMPLOYEE where NAME like 'A%')
LOOP
indx.NAME,
END LOOP;
'PARAMETER_4',
'PARAMETER_5'
END;

Report message to a moderator

Re: PL/SQL statement [message #606398 is a reply to message #606397]

Thu, 23 January 2014 05:24

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Sounds like you want a loop to concatenate the names to pass.
You would put that loop before the procedure call.

Report message to a moderator

Re: PL/SQL statement [message #606399 is a reply to message #606398]

Thu, 23 January 2014 05:42

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Or you could concatenate the names with one of these methods in the select

Report message to a moderator

Re: PL/SQL statement [message #606401 is a reply to message #606399]

Thu, 23 January 2014 06:04

SteveShephard
Messages: 41
Registered: August 2012

Member

Thanks guys! Got it working with the wm_concat function.

Appreciate the help Smile

Report message to a moderator

Re: PL/SQL statement [message #606410 is a reply to message #606401]

Thu, 23 January 2014 07:24

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

As you are in 11g you should use LISTAGG instead.
WM_CONCAT is not documented (so not supported for your case) and requires that Oracle Workspace Manager is installed.

Report message to a moderator

Previous Topic:	How to use bind variables in cursors?
Next Topic:	Second max

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 13:57:49 CDT 2024