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.
This was first published in September 2003