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?
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.
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:
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!
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.