Since you are on Oracle here is my SQL Function to do just what you are
trying to do.

CREATE OR REPLACE FUNCTION GET_SQL_RESULT (strSQL VARCHAR2, strSeparator
CHAR := (Chr(13)) ) RETURN CLOB IS
   ResultList CLOB;
   TYPE RefCurTyp IS REF CURSOR;
   cv RefCurTyp;
   strTemp VARCHAR2(3999);
BEGIN
   ResultList := NULL;
   OPEN cv FOR strSQL;
   LOOP
      FETCH cv INTO strTemp;
      EXIT WHEN cv%NOTFOUND;
      IF ResultList is not NULL THEN
         IF strTemp is not NULL THEN
            ResultList := ResultList || strSeparator || trim(strTemp);
         END IF;
      ELSE
         ResultList := trim(strTemp);
      END IF;
   END LOOP;
   CLOSE cv;
   RETURN trim(ResultList);
EXCEPTION
   WHEN OTHERS THEN
   RETURN NULL;   
END Get_SQL_Result;
/
 

You pass in the SQL you want to run and optionally the separator between
the answers.  The only catch is your SQL can only return 1 field (so you
have to concat your fields together).

Example call:
1st Action: Set zTempSQL = "SELECT SchemaID || ' ' || Name From ARSCHEMA
Order by SchemaID"
2nd Action: SQL Set Fields:  Select Get_SQL_Result('$zTempSQL$') from
DUAL

Fred


-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Brittain, Mark
Sent: Friday, June 13, 2008 2:09 PM
To: [email protected]
Subject: Get all results from a SQL call

Hi All & TGIF,

I need to do a SQL query to a database outside of Remedy and gets all of
the contact records for a customer and drop them into a field in a
ticket. Of course I can get the first or display a list if there are
multiple. There has to be a way to get all records that meet the
criteria.

ARS 6.3 patch 20
Oracle 9.2
SunOS 5.9

Any ideas?

Thanks
Mark

Mark Brittain
Remedy Developer
NaviSite ESM Operations
315-453-2912 x5418 (Office)
315-317-2897 (Cell)


This e-mail is the property of NaviSite, Inc. It is intended only for
the person or entity to which it is addressed and may contain
information that is privileged, confidential, or otherwise protected
from disclosure. Distribution or copying of this e-mail, or the
information contained herein, to anyone other than the intended
recipient is prohibited.

________________________________________________________________________
_______
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum
Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"

_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"

Reply via email to