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


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