?
Solved

oracle loop

Posted on 2003-02-23
14
Medium Priority
?
499 Views
Last Modified: 2008-02-20
Hello

I try to make a loop with a cursor to obtain all occurences answering my select... but doesn't work. I get only occurence even when many should appear.
I start in programming with oracle , does anyine could help me ?

code
CREATE or REPLACE FUNCTION Affich_View1 (idparc PARCELLE.PARCELLE_Id_Parcelle%type)
RETURN varchar AS
     sortie VARCHAR(40);
     CURSOR CURS_Vue1 IS
          select distinct essence.essence_nom_latin
          from essence, composition, observation
          where essence.essence_id_essence = composition.composition_id_essence
               and composition.composition_id_observation = observation.observation_id_observation
               and observation.observation_id_parcelle = idparc;
BEGIN
     FOR tuple IN CURS_Vue1 LOOP
     sortie :=tuple.essence_nom_latin;
     return sortie;
     END LOOP;
END;
/
0
Comment
Question by:Japanflip
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 

Expert Comment

by:clawed
ID: 8003917
You have a return statement in the middle of a loop. That throws you out of the function.

Instead of your return statement, you should take the value of sortie at this point and do whatever you intended to do with it.





0
 

Author Comment

by:Japanflip
ID: 8004000
thank you for fast answer but how display many answers without return statement in the loop ?
0
 

Expert Comment

by:clawed
ID: 8004022
That depends on what exactly you are trying to do. Are you calling this from within another app? Or just standalone?

Standalone, you could change it to a procedure.

CREATE or REPLACE PROCEDURE Affich_View1 (idparc PARCELLE.PARCELLE_Id_Parcelle%type)
IS
    sortie VARCHAR(40);
    CURSOR CURS_Vue1 IS
         select distinct essence.essence_nom_latin
         from essence, composition, observation
         where essence.essence_id_essence = composition.composition_id_essence
              and composition.composition_id_observation = observation.observation_id_observation
              and observation.observation_id_parcelle = idparc;
BEGIN
    FOR tuple IN CURS_Vue1 LOOP
    sortie :=tuple.essence_nom_latin;
    DBMS_OUTPUT.PUT_LINE(sortie);
    END LOOP;
END;
/
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Japanflip
ID: 8004086
We tried before but doesn't work...
Sorry. Any other idea ?
0
 

Expert Comment

by:clawed
ID: 8004111
Let's get some more info.

What exactly are you trying to do here? What type of application? Is this being called from an external application, if so what? An asp page, perhaps?

What I posted would work under SQL Plus, or SQL Nav, or Toad, etc. as long as SERVEROUTPUT was on.

But you may be trying to do something else, and so far you haven't said what.
0
 

Author Comment

by:Japanflip
ID: 8004139
sorry, .... actually our function is being called by another page with following code. A user should be able to give a number which is identifier(id_parcelle) in a table(observation).The result should be all occurences found of essence_nom_latin for this parameter.
I don't know if it's very clear... but it should work something like.

/
set echo off;
set server output on;
set verify off;

Accept C number prompt  'Veuillez choisir le numero de parcelle dont vous souhaiter les informations : ';

select Affich_View1(&C) from dual;
/
0
 

Expert Comment

by:clawed
ID: 8004227
SERVEROUTPUT should be entered as one word. Don't know whether that's an issue here or not.

PL/SQL is not interactive. However, SQL+ is.

Your idea of a PL/SQL function to return all results in one call is not practical in this environment. You need a more interactive call.

It's been a while since I did this...
--------------------------
Accept C number prompt  'Veuillez choisir le numero de parcelle dont vous souhaiter les informations : ';

variable sortie VARCHAR(40);
variable cv REFCURSOR;

-- Assign bind variable value using PL/SQL

begin

:c := '&C';

end;
/

-- Run the query using the bind variables

begin

open :cv for

        select distinct essence.essence_nom_latin
        from essence, composition, observation
        where essence.essence_id_essence = composition.composition_id_essence
             and composition.composition_id_observation = observation.observation_id_observation
             and observation.observation_id_parcelle = :c;

end;
/
print cv;

0
 

Expert Comment

by:clawed
ID: 8004242
Actually, now that I think about it some more, you could use the procedure as I had it before, but in SQL+, instead of your select from dual, you would simply say:

exec Affich_View1(&C);

That would probably be easier.
0
 

Author Comment

by:Japanflip
ID: 8004272
unfortunately, we don't have choice to work on SQL+ rather than PL/SQL
I tried :
exec Affich_View1(&C);
but error appears :
ERREUR a la ligne 1 :
ORA-06550: Ligne 1, colonne 7 :
PLS-00905: l'objet DESBI19.AFFICH_VIEW1 n'est pas valide
ORA-06550: Ligne 1, colonne 7 :
PL/SQL: Statement ignored
0
 

Expert Comment

by:clawed
ID: 8004318
To my knowledge you can't use ACCEPT other than in SQL+. If you want interactivity, you need to use something else.

I don't know French, but it seems like the Procedure is invalid. (Exec is for PROCEDUREs, not FUNCTIONs, by the way).

Before executing it, try
ALTER PROCEDURE Affich_View1 COMPILE;

and see what you get.


0
 
LVL 8

Expert Comment

by:heskyttberg
ID: 8004839
Hi!

I'm not sure what you are trying to do here.
You talked about called from another page ?

If you just want to display the result on a web page, since you are building a webb application, instead of return sortie inside the loop do a htm.print(sortie).

You do need to print some other text too in order to get a functional webpage.

If you want to pass the result as an array to an external C or Java application do like this:

CREATE OR REPLACE PACKAGE pkg_myPackage AS
  TYPE refCursorType IS REF CURSOR;

  FUNCTION Affich_View1
    (idparc PARCELLE.PARCELLE_Id_Parcelle%type)
  RETURN refCursorType;
END pkg_query_data;
/

CREATE OR REPLACE PACKAGE BODY pkg_query_data AS

  FUNCTION Affich_View1
    (idparc PARCELLE.PARCELLE_Id_Parcelle%type)
  RETURN refCursorType AS
    crOutRefCrsr refCursorType;
  BEGIN
    OPEN crOutRefCrsr FOR
      select distinct e.essence_nom_latin
        from essence e, composition c, observation o
        where e.essence_id_essence = c.composition_id_essence
          and c.composition_id_observation = o.observation_id_observation
          and o.observation_id_parcelle = idparc;
    RETURN crOutRefCrsr;
  END Affich_View1;

END pkg_myPackage;
/



This would let you execute the query from another program handle the array that comes back loop through it and print the result anyway you want.

I have only used this in java apps so I don't know how you handle cursor type in C or Forms or anything else you might be using.

But there should be others that know.

Regards
/Hans - Erik Skyttberg
0
 

Author Comment

by:Japanflip
ID: 8004864
Thank you very much for all help. I'll logout.
Regards

Julien
0
 
LVL 5

Expert Comment

by:Tom Knowlton
ID: 9453696
Japanflip,
No comment has been added lately (218 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: Award points to Japanflip http:#0

Please leave any comments here within 7 days.

-- Please DO NOT accept this comment as an answer ! --

Thanks,

knowlton
EE Cleanup Volunteer
0
 

Accepted Solution

by:
YensidMod earned 0 total points
ID: 9525689
This question is PAQed and no points refunded (of 50)

YensidMod
Community Support Moderator
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question