SQL Server Simple Ad-hoc Query - Oracle Equivalent Needed
-
SQL Server Simple Ad-hoc Query - Equivalent needed in Oracle?
I am an experienced SQLServer developer tasked with retrieving data from an Oracle Database for a new project. I am needing to do a few adhoc queries against the Oracle DBMS and the only tool I have available to me is the TOAD freeware version. So far I have become frustrated at the things I am forced to do to perform a simple ad-hoc query against an Oracle DBMS, specifically with incorporating use of a variable in the query. I am not looking to start another SQLServer vs Oracle debate but just to find out from other folks if there is an equivalent script I can run in Oracle Syntax to perform in this case a smple adhoc query including a variable.
SQl Server query:
DECLARE @CategoryID int;
SET @CategoryID = 8
SELECT * FROM dbo.Products WHERE CategoryID = @CategoryID
The sample above is from the Northwind database, and I want to perform the same type of query against an Oracle DBMS that returns multiple rows (opposite of using INTO as I've run into) as does the above query. So far the only way I've read to do this is to define a REF CURSOR but have had no luck in getting to work, let alone get over my instinctual bad feelings on use of explicit cursors.
If anyone could provide the equivalent syntax in Oracle PL\SQL that would do the same thing as the SQLServer sample provided above I would greatly appreciate it.
PS. I first tried to use the database admin tools VS.Net 2003 to connect to and query the Oracle DBMS, has anybody else run into the apparent bug where the SQL parser, beside making your nicely formatted SQL very ugly, separates the colon and equal sign for the variable assignment in a DECLARE statement (i.e. 'DECLARE period := '200512') so that an error message is returned? Seems like I'm battling both Bill and Larry here.
Thanks Much,
Dennis
maddog -
In TOAD query window, enter the following:
SELECT * FROM Products WHERE CategoryID = 8
You can right click on upper left corner of results window and use Save As to export the results.
Generally ad hoc queries are done right in the SQL Editor in TOAD and not put into stored procedures.
You've discovered a big conceptual difference between Oracle and SQL Server stored procedures and you've gotten half-way to solving it! SQL Server stored procedures implicitly return result sets (maybe even cursors, internally) but with Oracle you have to be explicit because Procedures don't return anything (like most languages) while Functions do.
To return an arbitrary cursor from a procedure you need a package in Oracle, so do something like this:
CREATE OR REPLACE PACKAGE reportQueries AS
TYPE t_Cursor IS REF CURSOR;
PROCEDURE WeeklyIntake (tmpVar IN OUT t_Cursor );
PROCEDURE WeeklyIntake (startDate IN DATE, endDate IN Date, tmpVar IN OUT t_Cursor );
END reportQueries;
Then define the body of the package.
You could also define a fuction that returns a cursor (I think).
--Peter
-
Thanks Peter, I do appreciate the insight in the differences you provided on the use of stored procedures in Oracle vs SQLSever, however the real point of what I was after was that I don't want (nor do I have the permissions on the Oracle database I am connecting to) to create stored procedures or packages, but simply an on-the-fly query against an Oracle table with an embedded variable as illustrated in my original example. Can the REF CURSOR approach be used in this manner outside of a package or stored procedure? I dug up an old Oracle 7 manual and saw something called an 'anonymous block', I'm wondering if this construct can be used as an encapsulation for the REF CURSOR in the same way as a package & procedure.
Regards,
Dennis
maddog -
Anonymous blocks allow you to imbed PL/SQL, including its variables, but that won't get you the results of the query. They have to be assigned to another variable, but that will have scope only in the block.
You need a SQL*Plus variable (aka, column):
COLUMN STDATE NEW_VALUE _STDATE
SELECT to_char(sysdate-14, 'MM/DD/YYYY') STDATE FROM DUAL;
...
SELECT * FROM orders WHERE orderdate >= to_date('&_STDATE', 'MM/DD/YYYY')
Read up on SQL*Plus programming in the free on-line documentation at Oracle. There are also many Oracle forums on Oracle (http://www.oracle.com/technology/documentation/index.html).
--Peter
-
Thanks for the links Peter, I guess I'm once again forced to learn more than what I wanted to
but also more than a little dismayed that I need to jump through so many hoops to do something so simple.Regards,
maddog -
If you could elaborate a little more on your goal, I might offer more specific advice or scripting examples.
I made the transition from Oracle to SQL Server and had the same feeling.
Oracle is much more sophisticated, but has some legacy cruft. On the other hand, Microsoft's database is much more accessible and modern, but simplistic in some ways. Thankfully, the two are growing toward each other's strengths.
-
My goal can be boiled down as wanting to know the simplest way to script, using Oracle PL\SQL syntax, the example query in my first post (see below also) so that it runs as an adhoc query and returns a result set, while including a variable in the WHERE clause. In other words, take the query as it is, formatted for SQLServer, and convert it into Oracle PL\SQL syntax so that it does exactly the same thing. I cannot create packages or procedures as the Oracle DB role I am set up with has limited read-only permissions to a single view in the Oracle database.
SQLServer Query (using MS Query Analyzer):
DECLARE @CategoryID int;
SET @CategoryID = 8
SELECT * FROM dbo.Products WHERE CategoryID = @CategoryID
Oracle PL\SQL Query equivalent (using TOAD freeware):
???
Thanks Again,
Dennis
maddog -
For TOAD, you can use this syntax and TOAD will prompt you every time for the value of CATEGORYID. If you use a _single_ ampersand, TOAD will reuse a previous value once it has been set in a session.
SELECT * FROM products WHERE categoryID = &&_CATEGORYID
(The syntax using COLUMN is useful in SQL*Plus scripts.)
--Peter
-
The use of the single_ampersand in TOAD to prompt for the variables got me what I was after basically, it even persisted the variables between sessions which was a bonus. I recognize this is a feature specific to TOAD which realizes my goals in this case, but I will still keep the feelers out for the simplest way to do this with generic PL\SQL syntax (if possible).
Peter you have been a great help on this and I appreciate your taking the time to respond. It looks like I am good for now.
Thanks Again,
Dennis
maddog -
OK.
Remember just remember that PL/SQL != SQL*Plus != T-SQL.
The fact that MS SQL Server has one language/environment vs Oracle's two environments leads to confusion when going from one to the other.
-
I was looking to do precisely the same thing. However I still don't see a specific answer to the original question.
So does that mean it is not possible using Toad to create an ad-hoc query where you use a variable defined in the script as a filter in the query's where clause?
declare @x int; set @x = 10; select * from mytable where myfield=@x
thnx.
-
TOAD is an independent product. Therefore, you have to work with the functionality that is provided by that product.
That said, yes, you can perform the same thing as SQL Server T-SQL. Code your query as an anonymous PL/SQL block as follows:
DECLARE
v_x INTEGER;
BEGIN
v_x := 10;
SELECT * FROM mytable WHERE myfield = v_x;
END;
[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply