Re: HELP - PL/SQL using DBMS_SQL in Store Procedures doesn't work

From: Pavel Luzanov <pal_at_kpbank.ru>
Date: 1997/07/29
Message-ID: <01bc9bee$44a643c0$93c47cc1_at_pal.hq.kem>#1/1

Larry --
Check your privileges.
[Quoted] You must have SELECT privelege on w_wellwork_common, w_selected_wellwork tables granted directly:

   SQL> GRANT SELECT ON w_wellwork_common TO <user_name>;

             GRANT SELECT ON w_selected_wellwork TO <user_name>;
        or
             GRANT SELECT ON w_wellwork_common TO public;

Also, You must have CREATE TABLE privilege granted directly :

   SQL> GRANT CREATE TABLE TO <user_name>; or

   SQL> GRANT CREATE TABLE TO public;

You can not create table in PL/SQL with CREATE TABLE privilege granted via database role
(in many cases, this is RESOURCE role).
You must have direct access to all objects which called from PL/SQL. It is PL/SQL constraint, not SQL, therefore you may create tables directly in sqlplus.

---

Pavel Luzanov
Kuzbassprombank

Larry Jones <lljo_at_chevron.com> çàïèñàíî â ñòàòüþ <33DD12D9.2B49_at_chevron.com>...
...
> sqlstmt := 'CREATE TABLE ' || tbl_name || ' AS ' ||
> ' SELECT w_wellwork_common.* ' ||
> ' FROM w_wellwork_common, ' ||
> ' w_selected_wellwork ' ||

 ...
> DBMS_SQL.PARSE(csr, sqlstmt, DBMS_SQL.V7);
> rc := DBMS_SQL.EXECUTE(csr);

> 


> 		EXCEPTION

> 			WHEN OTHERS THEN

> 				errnum := -20002;

> 				errmsg := 'Create Error: ' || SQLERRM(SQLCODE);


> 		END;

 ...
> When I run the first bit of code (_at_TEMP.SQL) it creates the table as I
> need. When I run the
> TEST_PROC procedure, I get the following error:
> ================================ Start of Error
> ================================
> declare
> *
> ERROR at line 1:
> ORA-20002: Create Error: ORA-00942: table or view does not exist

> ORA-06512: at "LLJO.CREDIBILITY_TEMP2", line 53 > ORA-06512: at line 4 Received on Tue Jul 29 1997 - 00:00:00 CEST