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.
Requires Free Membership to View
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;
Click for the fine points in part 3.
This was first published in October 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation