# Oracle & SQL+ Question **URL:** \ **Category:** Factual Questions\ **Created:** [September 28, 2000, 1:59pm UTC](https://boards.straightdope.com/t/oracle-sql-question/34866 "2000-09-28T13:59:02Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Starbury](https://avatars.discourse-cdn.com/v4/letter/s/f04885/32.png) [@Starbury](https://boards.straightdope.com/u/Starbury)\ **Post date:** [September 28, 2000, 1:59pm UTC](https://boards.straightdope.com/t/oracle-sql-question/34866/1 "2000-09-28T13:59:02Z")
Most of my experience with databases has been with SQL Server, and I’m working on a stored procedure for Oracle. Basically, I need to return a recordset/table (which will then be used in an ASP Page with ADO). In SQL Server, I could just write a simple SELECT statement and that would be returned automatically. However, I can’t find an easy way to do this with Oracle. The only way I’ve been able to do it so far is to create a Package, which defines a variable ‘AS TABLE’. Then, within the Package, create a Procedure that then loops through one column of the table and assigns it to the variable. My problem with this is that it requires a TABLE variable for each column of the DB’s table (and I need to return about 5 columns). Does anyone know of an easier/more efficient way to do this? ---
**Author:** ![Padeye](https://avatars.discourse-cdn.com/v4/letter/p/a9a28c/32.png) [@Padeye](https://boards.straightdope.com/u/Padeye)\ **Post date:** [September 28, 2000, 5:41pm UTC](https://boards.straightdope.com/t/oracle-sql-question/34866/2 "2000-09-28T17:41:23Z")
Starbury, this is right up my alley but I want to make sure I understand what you’re trying to do. However you may not like the answer as Oracle doesn’t have a provision for returning multiple values as Sybase stored procedures can. If you give me a better context for what you want to accomplish I might be able to help. Drop me a line directly if you want. ---
**Author:** ![Starbury](https://avatars.discourse-cdn.com/v4/letter/s/f04885/32.png) [@Starbury](https://boards.straightdope.com/u/Starbury)\ **Post date:** [September 28, 2000, 6:16pm UTC](https://boards.straightdope.com/t/oracle-sql-question/34866/3 "2000-09-28T18:16:25Z")
> [@](#): > > \*Originally posted by Padeye \* > **However you may not like the answer as Oracle doesn’t have a provision for returning multiple values … If you give me a better context for what you want to accomplish I might be able to help** Unfortunately, this is what I’m discovering with Oracle. Essentially, I need to display the contents of a multi-column table. Under normal circumstances (using SQL Server) I would just set up a stored procedure that would have: > [@](#): > > “SELECT ColumnA, ColumnB, ColumnC > FROM TableA > WHERE ColumnA = SomeValue” Ideally, I would like the ‘SomeValue’ to be a parameter that is passed in. However, that’s not what my concern is. I am looking for some way to just transfer the results of this entire SELECT statement into _one_ output parameter. From that point, I would just have the ASP page loop the Recordset Object and print it out to the client. From what I have seen, this is apparently not possible through Oracle…the only solution I have come up with so far is to create a Table variable for each column (which I am still working on…we’ll see if that works). I was hoping that SQL+ provided some way around this. Well, if there is any help you could give me, I would appreciate it! ---
**Author:** ![Padeye](https://avatars.discourse-cdn.com/v4/letter/p/a9a28c/32.png) [@Padeye](https://boards.straightdope.com/u/Padeye)\ **Post date:** [September 28, 2000, 8:18pm UTC](https://boards.straightdope.com/t/oracle-sql-question/34866/4 "2000-09-28T20:18:05Z")
I think that’s exactly the situation out powerbuilder developers hit and there was no elegant way out of it. Oracle works differently in its own forms. You can execute PL/SQL in the application rather than the server like this. Select tabcol\_a, tabcol\_b, etc. into :screen\_widget\_a, :screen\_widget\_b from table where yada = :yada / The only time you can omit the “into” clause is running straight command line SQLplus. I still do a fair amount of automation that way but it’s not allowed in stored procedures. ---
**Author:** ![Starbury](https://avatars.discourse-cdn.com/v4/letter/s/f04885/32.png) [@Starbury](https://boards.straightdope.com/u/Starbury)\ **Post date:** [September 28, 2000, 8:31pm UTC](https://boards.straightdope.com/t/oracle-sql-question/34866/5 "2000-09-28T20:31:43Z")
> [@](#): > > \*Originally posted by Padeye \* > \*\* > Oracle works differently in its own forms. You can execute PL/SQL in the application rather than the server like this. > > Select tabcol\_a, tabcol\_b, etc. > into :screen\_widget\_a, :screen\_widget\_b > from table > where yada = :yada > \*\* This is how I am going to have to do it. It just seems unneccessarily inefficient…oh well. Thanks for you help, though.