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.
Dynamic SQL with EXECUTE IMMEDIATE
To execute SQL statements other than SELECT statements you can use the EXECUTE IMMEDIATE command in PL/SQL.
EXECUTE IMMEDIATE 'CREATE TABLE ' || table_name || '_' || TRIM (TO_CHAR (n, '99990')) || ' AS SELECT * FROM ' || table_name;
Note that the only semicolon is the one ending the PL/SQL statement itself: do not include a semicolon at the end of the string containing the SQL statement.
For SELECT statements it's much harder: you have to use the dbms_sql supplied package.
Dynamic SQL with dbms_sql
The original way to do dynamic SQL in Oracle involves a package called dbms_sql. This is still the only way to do dynamic SELECT statements. The routines in dbms_sql include:
- open_cursor to start. The integer returned by this function will be used in all subsequent calls to dbms_sql routines dealing with this query.
- parse to provide the SQL statement
- bind_variable (0 or more times) to provide values for parameters in the SQL statement.
- define_column(1 or more times) to set up SELECTed columns
- execute to run the query
- fetch_rows (1 or more times) to return a row of results
- column_value (1 or more times for each call to fetch_rows) to retrieve values
- close_cursor to finish
Here's an example. If you knew that the query was only going to SELECT one row, you wouldn't need a LOOP, but I included it here to show how the more general case works.
-- add_up takes one argument, a SQL statement that SELECTs a -- single number, and returns that number. -- If the result set has more than one row, add_up -- returns the sum of those numbers. -- If all the numbers are NULL, add_up returns NULL. CREATE OR REPLACE FUNCTION add_up ( in_sql_text IN VARCHAR2 -- SQL statement to execute ) RETURN NUMBER IS cursor_id PLS_INTEGER; -- set by open_cursor return_val NUMBER; -- total to be returned dummy_cnt PLS_INTEGER; -- 0 returned by execute this_val NUMBER; -- Number from current row BEGIN cursor_id := dbms_sql.open_cursor; dbms_sql.parse ( cursor_id, in_sql_text, dbms_sql.native -- "language" (version) ); dbms_sql.define_column ( cursor_id, 1, this_val ); dummy_cnt := dbms_sql.execute (cursor_id); LOOP IF dbms_sql.fetch_rows (cursor_id) = 0 THEN EXIT; END IF; dbms_sql.column_value ( cursor_id, 1, this_val ); IF this_val IS NOT NULL THEN return_val := this_val + NVL ( return_val, 0 ); END IF; END LOOP; dbms_sql.close_cursor (cursor_id); RETURN return_val; END;
This was first published in October 2003