Home » SQL & PL/SQL » SQL & PL/SQL » Error using dbms_sql package
Error using dbms_sql package [message #188101] Thu, 17 August 2006 02:25 Go to next message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
I need to execute a procedure stored in wmadmin schema that updates some tables in other user schema. At this moment i dont't know the name of the schema whoose tables will be updated so i need to consider skema as a parameter in my procedure
My procedure is:

create or replace procedure SetYearMonth(Year IN INTEGER , Month IN INTEGER, SKEMA IN VARCHAR2) is

TYPE TABS IS TABLE OF VARCHAR2(20);
IDX NUMBER(2):=1;
STR VARCHAR2(500);
TABEL TABS := TABS('A',
'B',
'C'
);
tab1 VARCHAR2(30);
tbl VARCHAR2(30);
crs number;
n number;
begin


FOR IDX IN TABEL.FIRST ..TABEL.LAST LOOP
tab1 := SKEMA || '.' || TABEL(IDX);
crs := dbms_sql.open_cursor;
dbms_sql.parse(crs,'UPDATE :TBL set start_year=:an, start_month=:month',2);
dbms_sql.bind_variable(crs,'tbl',tab1);
dbms_sql.bind_variable(crs,'Year',Year);
dbms_sql.bind_variable(crs,'Month',Month);
n:=dbms_sql.execute(crs);
dbms_Sql.close_Cursor(crs);

END LOOP;
commit;
end SetYearMonth;

when i try to execute the procedure i get the following error:
ORA-00903: invalid table name

i have used also "EXECUTE IMMEDIATE" statement but i got the same error....

Can anyone give me a hint???

Thanks...
Re: Error using dbms_sql package [message #188103 is a reply to message #188101] Thu, 17 August 2006 02:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your code is erroring for exactly the same reason that
SELECT count(*)
FROM :table_name
would error - there is no table at the point where the query is parsed.
If you rewrite your code as:
FOR IDX IN TABEL.FIRST ..TABEL.LAST LOOP
tab1 := SKEMA || '.' || TABEL(IDX);
crs := dbms_sql.open_cursor;
dbms_sql.parse(crs,'UPDATE '||tab1||' set start_year=:an, start_month=:month',2);

Or even just use
FOR IDX IN TABEL.FIRST ..TABEL.LAST LOOP
tab1 := SKEMA || '.' || TABEL(IDX);
execute immediate 'UPDATE '||tab1||' set start_year=:an, start_month=:month' using p_an, p_month;

Then it should work.
Re: Error using dbms_sql package [message #188106 is a reply to message #188103] Thu, 17 August 2006 02:44 Go to previous messageGo to next message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
sorry but it doesn't work neither...i issued the "EXECUTE IMMEDIATE" statement but the error still persists...

SQL>execute setYearMonth(2005,5,'q');

ORA-00942: table or view does not exist

The user q exists and also the tables to be updated exist in his skema...

SQL> desc q.a;
Name Type Nullable Default Comments
---------------- ------------ -------- ------- --------
CODIOART1 NUMBER(20)
CODIOART NUMBER(20)
CONT NUMBER(20) Y
DIFERENTE NUMBER(20) Y
CHELTUIELI NUMBER(20) Y
START_YEAR NUMBER(4)
START_MONTH NUMBER(2)
Re: Error using dbms_sql package [message #188124 is a reply to message #188106] Thu, 17 August 2006 03:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I notice that it fixes the problem you reported, in that the ORA-903 error has gone.

You have remembered to grant UPDATE/SELECT on these tables to the user creating this procedure explicitly rather than via a role, haven't you?
Re: Error using dbms_sql package [message #188126 is a reply to message #188124] Thu, 17 August 2006 03:53 Go to previous messageGo to next message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
The user creating this procedure has DBA role and if i try to update explicitly the tables from the procedure owner's schema it works...

SQL>UPDATE q.a set start_year=2005, start_month=1

19 rows updated

[Updated on: Thu, 17 August 2006 03:53]

Report message to a moderator

Re: Error using dbms_sql package [message #188132 is a reply to message #188126] Thu, 17 August 2006 04:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So the correct answer to my question would be 'No, I have not explicitly granted the permissions you mentioned'

Try running
BEGIN
  execute immediate 'UPDATE q.a set start_year=2005, start_month=1';
END;
and tell me what happens.
Re: Error using dbms_sql package [message #188138 is a reply to message #188132] Thu, 17 August 2006 04:23 Go to previous messageGo to next message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
I granted explicitly that privileges and now it works...Thanks for your advice...
Anyway it is a little weird to me because the owner of the procedure has DBA role....
Re: Error using dbms_sql package [message #286147 is a reply to message #188138] Thu, 06 December 2007 10:39 Go to previous messageGo to next message
tcox
Messages: 2
Registered: December 2007
Location: Portland, OR
Junior Member
This is correct behavior. The DBA role is irrelevant here - as described in OracleŽ Database Application Developer's Guide - Fundamentals 10g Release 2 (10.2):

Quote:

Privileges to Create Procedures and Functions To create a standalone procedure or function, or package specification or body, you must meet the following prerequisites:

You must have the CREATE PROCEDURE system privilege to create a procedure or package in your schema, or the CREATE ANY PROCEDURE system privilege to create a procedure or package in another user's schema.

Note:

To create without errors (to compile the procedure or package successfully) requires the following additional privileges:
The owner of the procedure or package must be explicitly granted the necessary object privileges for all objects referenced within the body of the code.


The owner cannot obtain required privileges through roles.

Emphasis added.

So there you have it. You must have 'SELECT' (or whatever is appropriate for your object type and what your code does) granted directly to the code owner.
Re: Error using dbms_sql package [message #286167 is a reply to message #286147] Thu, 06 December 2007 11:26 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you really register to post this answer to a post that is over a year old?
Well, welcome to the forum then!
Wink
Previous Topic: dbms_stats.gather_table_stats in stored procedure
Next Topic: Listing triggers
Goto Forum:
  


Current Time: Tue Dec 06 04:52:20 CST 2016

Total time taken to generate the page: 0.11817 seconds