I am new to PL/SQL and am trying to do the following. Instead of this stored procedure:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
CREATE OR REPLACE PROCEDURE prc_get_serverid ( p_dbid IN NUMBER, p_svrid OUT NUMBER ) AS BEGIN SELECT serverid INTO p_svrid FROM tbldatabases WHERE DATABASEID = p_dbid; END;
I would like to have something like this, where I can pass the SQL statement:
CREATE OR REPLACE PROCEDURE prc_get_serverid ( p_sqlstmt IN VARCHAR2, p_dbid IN NUMBER, p_svrid OUT NUMBER ) AS BEGIN OPEN p_sqlstmt USING p_dbid, psvrid; END; ... sqlstmt := 'SELECT serverid INTO :X FROM tbldatabases WHERE databaseid = :Y'; prc_get_serverid (sqlstmt, svrid, dbid);
I know the syntax is all wrong, but I am having trouble figuring out the best way to do this.
The buzzword you're looking for is dynamic SQL: a routine that builds and executes a SQL statement at run time is performing dynamic SQL. Dynamic SQL is a silver bullet that can do magical things. With dynamic SQL you can write a single function that returns the answer to any query! It can do anything, for a price. The price is both your time as a developer and the user's time when the program runs. The price is usually so high that you solve the problem some other way.
If you're new to PL/SQL, you may not be aware of some simpler techniques that might help you. Within PL/SQL:
- creating cursors with parameters
- passing cursors as parameters to procedures
In regular SQL:
- CASE statements
- scalar subquery expressions
- temporary tables
Or in PL/SQL behind the scenes:
- policies (dmbs_rls package)
So if you're "having trouble figuring out the best way to do this," you should start with clearly stating what you're trying to do. Is it look up a value from a table? A join can do that. Do you need to look up a value in any one of three tables, to be determined at run time? Use CASE. Do you need to determine which of several databases you're on? SYS_CONTEXT may give the answer. Are you really trying to get the result from a SQL statement that is completely unknown before run time? Then you may really have to use dynamic SQL, but if that's the case, why are you calling the procedure "prc_get_serverid"?
That's the end of the sermon. Now for the answer to your question in part 2.
For a wonderful story involving a silver bullet and how hard it is to make one, see the fairly self-contained chapter "The great bear of Gurlita Cliff" in Selma Lagerlöf's novel The story of Gösta Berling.
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.