Q

Dynamic SQL, part 2

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

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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close