I am new to PL/SQL and am trying to do the following. Instead of this stored procedure:
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.
Fine Points Using dbms_sql
- It's okay if the query SELECTS more columns than you handle with calls to define_column. The extra columns are ignored.
- The second argument in define_column and column_value is the column number (as in "ORDER BY 3, 2"). You don't have to use the numbers in order, or use all of them.
- Oracle will try to convert SELECTed values to the data types specified in define_column. For example, add_up ('SELECT '' 1'' FROM table_z') would return the total number of rows in table_z. (Those are all apostrophes: your browser may make the two consecutive apostrophes look like a double-quote character.)
- Example of bind_variable: If the statement given to parse contains variables, such as ':x', then you must supply values for these by calling bind_variable before calling execute, e.g. dbms_sql.bind_variable (cursor_id, ':x', avg_salary); Using bind_variable may result in faster execution than incorporating the value into the parsed text.
- You can use dbms_sql instead of EXECUTE IMMEDIATE for non-queries (if you really want to).
- The value returned by execute is meaningless, except in INSERT, UPDATE or DELETE statements (in which case, it is the number of rows modified).