Q
Problem solve Get help with specific problems with your technologies, process and projects.

Dynamic SQL, part 3

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.

Continued from part 2.

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).


This was last published in October 2003

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close