Re: DBMS_SQL usage for DDL execution
Date: 1996/12/12
Message-ID: <32afa2d5.5497705_at_dcsun4>#1/1
On Thu, 12 Dec 1996 07:36:37 GMT, openshaw_at_airmail.net (Mike Openshaw) wrote:
>We are trying to establish some routine to grant and revoke roles
>using DBMS_SQL. We've run into problem with the Bind_Variable
>statement. Does anyone out there have experience using DBMS_SQL in
>this manner? Examples we've seen include only DML, but those authors
>swear DDL is also supported (but give no examples). I'll be posting
>this in the tools group also to cover all bases.
>
bind variables can be used where a constant can be used.
bind variables can never be used where a sql identifier *has* to be used.
for example:
select * from emp where ename = X
X could be a constant (for example 'KING') or a SQL Identifier (for example
ename).
In this case, X could be a bind variable since a constant could be used.
Likewise:
select * from X where ename = 'foo'
X could only be sql identifier here. You could not say: select * from 'EMP' where ename = 'foo'
You can say:
select * from emp where ename = 'foo'
In DDL, there are no bind variables. All things are sql identifiers or keywords. You cannot use bind variables in create, grant, alter, drop, etc. You must use full statements.
You can issue ddl very easily in pl/sql. Lets say you wanted to be able to grant a role to a user (or revoke) in pl/sql... You could:
create or replace procedure execute_immediate( stmt in varchar2 ) as
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
begin
dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
rows_processed := dbms_sql.execute(exec_cursor);
dbms_sql.close_cursor( exec_cursor );
exception
when others then
if dbms_sql.is_open(exec_cursor) then
dbms_sql.close_cursor(exec_cursor);
end if;
raise;
end;
/
declare
role_name varchar2(25) default 'xxx';
username varchar2(25) default 'bob';
begin
execute_immediate( 'grant ' || role_name || ' to ' || username );
end;
/
Since you can't use bind variables, you must build a string that contains the
command and execute the string.
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Thu Dec 12 1996 - 00:00:00 CET