Home » SQL & PL/SQL » SQL & PL/SQL » Publishing PL/SQL as web service
Publishing PL/SQL as web service [message #215491] Mon, 22 January 2007 08:57 Go to next message
inm_jon
Messages: 3
Registered: January 2007
Junior Member
Hi there,

I've run into a bit of a problem when creating a few stored procedures to be published as web services. I've created one which takes in an ID number and returns the correct row. That works fine, but I hit problems when I run some SQL that return muliple rows. Thusly:

PROCEDURE GET_QUERIES
		  (
		  q_name OUT VARCHAR,
		  q_desc OUT VARCHAR,
		  q_cat OUT VARCHAR,
		  q_pobject OUT BLOB,
		  q_id OUT NUMBER,
		  q_query OUT VARCHAR
		  ) IS
   BEGIN
   		
		SELECT Name, Description, Category, PObject, ID, QUERY_STRING 
                INTO q_name, q_desc, q_cat, q_pobject, q_id, q_query
		FROM query;
			
   END;


It's basically the same as my previous procedure, but with the WHERE clause removed. I know that the problem is that it's trying to retrieve many rows, but my code only allows for one, but I can't work out how to modify this so it passes all the rows to be published in the web service. Any kind soul out there with a solution?

Many thanks.

[Edited to drop maximum line length]

[Updated on: Mon, 22 January 2007 11:23] by Moderator

Report message to a moderator

Re: Publishing PL/SQL as web service [message #215492 is a reply to message #215491] Mon, 22 January 2007 09:02 Go to previous messageGo to next message
inm_jon
Messages: 3
Registered: January 2007
Junior Member
I mean, I'm *guessing* that I'll need to use arrays instead of single variable to pass the results into, but I can't get my head round how that's work nor how I'd go about coding it.
Re: Publishing PL/SQL as web service [message #215498 is a reply to message #215492] Mon, 22 January 2007 10:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How are you exposing this data? Are you using a PSP (Pl/sql Server Page)?
Re: Publishing PL/SQL as web service [message #215507 is a reply to message #215498] Mon, 22 January 2007 11:15 Go to previous messageGo to next message
inm_jon
Messages: 3
Registered: January 2007
Junior Member
JRowbottom wrote on Mon, 22 January 2007 10:04
How are you exposing this data? Are you using a PSP (Pl/sql Server Page)?


No, I'm connecting directly to the database through JDeveloper and exposing the relevant package as a Web Service directly, hence why I can't use a Ref Cursor type, as I can't package that as a web service.
Re: Publishing PL/SQL as web service [message #215512 is a reply to message #215507] Mon, 22 January 2007 11:25 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you get if you define a record to match your data set, and table of that record (either in a package spec, or as SQL types) and then do a SELECT... BULK COLLECT INTO... in your procedure and then return this table of results?

I've no experience with JDeveloper, so I'm firing blind here.
Previous Topic: How to suppress a line in a report
Next Topic: how -ve scale is used in number datatype??
Goto Forum:
  


Current Time: Mon Dec 05 08:41:18 CST 2016

Total time taken to generate the page: 0.18183 seconds