I currently have the ability to execute a sql statement, return the info, and use reader to parse the information. I would thought to use a SPROC to do this but, am having issues. When defining the sproc, how should I return the structure of the table? Return each and every column?
example:
CREATE PROCEDURE sp_test1 (id int,
test1 varchar2(15),
test3 varchar3(15))
BEGIN
SELECT id, test1, test3 FROM test_table
END;
If you wanted to use a stored procedure, you would normally return a cursor, i.e.
CREATE OR REPLACE PROCEDURE sp_test1( p_cursor OUT SYS_REFCURSOR )
AS
BEGIN
OPEN p_cursor
FOR SELECT id, test1, test3
FROM test_table;
END;
Assuming that PowerShell knows what to do with the cursor handle that is returned, that should work. On the other hand, if all you are doing is encapsulating a query, you may want to use a view in Oracle rather than a stored procedure. Your application can then issue simple selects against the view.
