Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle dbms_sql tips

Oracle tips by Burleson

 


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



 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.