Using Oracle dbms_sql
This has been the first ever created
innovative solution for building dynamic SQL
statements and PL/SQL blocks in the Oracle
version 7 prior to the native method which
is the widely accepted method due to its
less coding complexity and easy
understandability unlike its predecessor.
This API was enhanced to support
collections in the version 8i and has
groomed itself through the successive
releases up to 12c. Nevertheless, the focus
of dynamic scripting has been shifted to
NATIVE DYNAMIC SQL from its availability in
the Oracle version 9i and the DBMS_SQL API
is available only for backward
compatibility.
DBMS_SQL API has one
additional feature, which is still not
available in the native method. The API
supports managing the dynamic statements
when the number of columns or the data types
of the returned columns are unknown during
the run time that cannot be done through
NATIVE DYNAMIC SQL.
This
dbms_sql API
supports various procedures and functions
for processing the dynamic statements that
are explained below.
PL/SQL dbms_sql describe_columns2
PL/SQL dbms_sql describe_columns
PL/SQL dbms_sql method 4
PL/SQL dbms_sql execute_and_fetch
PL/SQL dbms_sql column_value and variable_value
PL/SQL dbms_sql define_column
PL/SQL dbms_sql execute
PL/SQL dbms_sql parse
PL/SQL dbms_sql column_value
PL/SQL dbms_sql bind_variable
PL/SQL dbms_sql open_cursor
We started this
chapter with an introduction to the dynamic
SQL processing technique along with their
different types. In the next section, we
toured through the NATIVE DYNAMIC SQL method
of dynamic statement processing along with
their latest enhancements with numerous real
time examples. We have also gained a better
understanding of the DBMS_SQL API that is
not commonly used compared to the native
method along with their secret weapon, the
Method 4 Type processing technique in the
later section with appropriate examples.
In the next chapter, we will learn
about the advanced SQL concepts like regular
expressions and XML programming in Oracle.
The Oracle docs note the following a bout
the dbms_sql package noting that the
DBMS_SQL package provides an interface to
use dynamic SQL to parse any data
manipulation language (DML) or data
definition language (DDL) statement from
within PL/SQL.
A common request from
users is the ability to change their
password. Unfortunately, Oracle does not
allow a user to change an account password
without having the ALTER USER privilege.
This privilege also allows users to change
information other than the password and for
any user. So, unless you want to grant this
level of access to users, the DBA has the
responsibility of assigning and changing
passwords.
Let’s use the DBMS_SQL package to create
a Change_Password() function, which the DBA
can compile and make accessible to all
users. This function can be called from a
system’s front end, usually via a button on
a form or a menu item, to allow the user to
change a password (and only a password). The
Change_Password() function is shown in
Listing 9.3.
CREATE OR REPLACE
FUNCTION
Change_Password (vUsername IN varchar2,
vPassword IN
varchar2)
RETURN integer
IS
iCursorID integer;
vCommand varchar2
(80);
iReturned integer;
xMISSING_PARAMETER
EXCEPTION;
BEGIN
IF (vUserName IS
NULL OR vPassword IS NULL) THEN
RAISE
xMISSING_PARAMETER;
END IF;
vCommand := 'ALTER
USER ' ||
vUsername ||
'identified by '
||
vPassword;
iCursorID :=
DBMS_SQL.Open_Cursor;
DBMS_SQL.Parse (iCursorID,
vCommand,
DBMS_SQL.v7);
iReturned :=
DBMS_SQL.Execute (iCursorID);
DBMS_SQL.Close_Cursor (iCursorID);
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END
Change_Password;
/
This
function must be run by a user who has the
ALTER USER privilege. In this
implementation, the function allows any
user’s password to be changed. This
particular implementation assumes that you
will pass only the login name of the current
user as a parameter.
The function
first builds the proper SQL*Plus command and
stores it in the vCommand string. The
function then calls functions and procedures
in the DBMS_SQL package to parse and execute
the command.
This
function requires a username and password to
be passed as parameters. If the user’s
password is successfully changed, the
function returns 1. Otherwise, the function
returns 0.
Here are some examples how
dbms_sql can be used.
Example 1:
CREATE OR REPLACE
PROCEDURE "SYSTEM"."ORA$_SYS_REP_AUTH" as
i integer;
x integer;
begin
i:=dbms_sql.open_cursor;
dbms_sql.parse(i,'GRANT
SELECT ON SYSTEM.repcat$_repschema TO SYS '
||
'WITH GRANT
OPTION',dbms_sql.v7);
x:=dbms_sql.execute(i);
dbms_sql.parse(i,'GRANT
SELECT ON SYSTEM.repcat$_repprop TO SYS ' ||
'WITH GRANT
OPTION',dbms_sql.v7);
x:=dbms_sql.execute(i);
dbms_sql.parse(i,'GRANT
SELECT ON SYSTEM.def$_aqcall TO SYS ' ||
'WITH GRANT
OPTION',dbms_sql.v7);
x:=dbms_sql.execute(i);
dbms_sql.parse(i,'GRANT
SELECT ON SYSTEM.def$_calldest TO SYS ' ||
'WITH GRANT
OPTION',dbms_sql.v7);
x:=dbms_sql.execute(i);
dbms_sql.parse(i,'GRANT
SELECT ON SYSTEM.def$_error TO SYS ' ||
'WITH GRANT
OPTION',dbms_sql.v7);
x:=dbms_sql.execute(i);
dbms_sql.parse(i,'GRANT
SELECT ON SYSTEM.def$_destination TO SYS '
||
'WITH GRANT
OPTION',dbms_sql.v7);
x:=dbms_sql.execute(i);
dbms_sql.close_cursor(i);
exception when
others then
if
dbms_sql.is_open(i) then
dbms_sql.close_cursor(i);
cur:=DBMS_SQL.OPEN_CURSOR;
sql_com:='ALTER
SYSTEM FLUSH SHARED_POOL';
DBMS_SQL.PARSE(cur,sql_com,dbms_sql.v7);
row_proc:=DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
OPEN get_time;
FETCH get_time
INTO todays_date;
INSERT INTO
dba_running_stats VALUES
(
'Flush of Shared
Pool',1,35,todays_date,0
);
COMMIT;
END IF;
END flush_it;
For more information on dbms_sql view
the following articles:
Using the DBMS_SQL Package
DBMS_SQL Package
|
and include the URL for the page.
