Post by Thomas Ky » Fri, 05 Sep 1997 04:00:00

roles are never enabled during the execution of a procedure.

Try this:

SQL> set role none;
SQL> create view foo as select * from dual;

If you can do it in plus with no roles you can do it in a procedure.  If you
can't, you must have the privelege from a role and hence dbms_sql won't be able
to do it either.

You probably have the "create view" privelege from the DBA role.  Grant the
privelege directly to the owner of the procedure and it'll work.

On Thu, 04 Sep 1997 21:28:32 +0200, Dietrich Mueller

>Hi there,
>need some help regarding the DBMS_SQL package
>I want to dynamically create Views using this package. For a starter I
>wrote the following code:

>Create or replace Procedure create_view(view_name IN varchar2)
> is
>cursor_id Integer;
>tab_name varchar2(30);
>create_statement varchar2(100);
>   cursor_id:=dbms_sql.open_cursor;
>   create_statement:='Create or Replace View '||view_name||' As Select
>tname from tab';
>   dbms_output.Put_Line('create_statement: '||create_statement);
>   dbms_sql.parse(cursor_id,create_statement,dbms_sql.v7);
>   --dbms_sql.define_column(cursor_id,1,tab_name);
>   dbms_sql.close_cursor(cursor_id);
>   dbms_output.Put_Line('View '||view_name||' wurde erstellt');
>end create_view;

>The procedure is compilied without errors.
>But when I execute the procedure from SQL*PLUS like

>SQL> execute create_view('test_view');

>the following error messages appear:

>ERROR at line 1:
>ORA-01031: insufficient privileges
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
>ORA-06512: at "SYS.DBMS_SQL", line 25
>ORA-06512: at "DMUELLER.CREATE_VIEW", line 10
>ORA-06512: at line 1

>First I thought that I might need more privileges so I ran the script as
>DBA  but the same error message kept poping up.
>Then  I tried to define the column on the Select but that didn't work
>At the moment I'm stuck and I only got the following ideas;
> * Statements like that can't be handeld that way because of a mixture
>of DML and DDL
> * if that error_message is right (which I really doubt) then it could
>be a passwordfile problem (because the database has set the parameter
>     passwordfile=none ) (allthough this idea seems not very logical to
> * ????
>Oracle7 Server Release  running on AIX 4.x

>Any ideas?
>Any hints/help very much appreciated

> thanks a lot in advance!!!



Thomas Kyte

Oracle Government
Bethesda MD

http://govt.us.oracle.com/    -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation


1. Oracle DBMS_SQL Package Equivalent

Is there any equivalent of Oracle's DBMS_SQL
Package to handle "dynamic sql generation and
execution situation"?

DBName : db1            Procedure : p1        one
output parameter
DBName : db2            Procedure : p1        one
output parameter
DBName : db3            Procedure : p1        one
output parameter
DBName : db4            Procedure : p1        one
output parameter

DBName : db5            Procedure : p2        one
input and output parameter each

The procedure p2 of db5 has to call p1 procedure
from the required database(that is p2 will receive
the db name as input parameter and dynamically
call p1).

In oracle using DBMS_SQL, I am able to handle the
situation as below:

1.Get a handle to the cursor.
2.Parse the query (wherein I am concatinating the
schema name, procedure name p1, and the bind
output variable)using this handle.
3.Bind the variable of step 2 to a local variable.

4. Execute the procedure parsed procedure.
5. Retrieve the value of the output parameter of
p1 procedure
6. Assign this parameter to the output parameter
of p2 procedure.

Can any one please tell me how to handle this
situation is SQL Server (any version).

Thanks in Adnace,

2. FW: awk function question

3. JDBC, Oracle Stored Procedure and DBMS_SQL.VARCHAR_TABLE Type

4. ::..Senior Oracle Database Administrator needed in Portland, Oregon..:: jwdr10005


6. Automation error... using MSCAL.OCX ????

7. Problem Using DBMS_SQL in trigger.

8. Best method to send rows to a remote db, without full replication

9. Single quatation when using dbms_Sql package gives ORA-error

10. DBMS_SQL Dynamically creating views.

11. Does Dynamic SQL (DBMS_SQL) performance lacks?