| executing stored procedure [message #8258] |
Wed, 06 August 2003 09:32  |
Dave
Messages: 92
Registered: August 1999
|
Member |
|
|
Hi
I'm trying to run a stored procedure that I created:
begin
execute immediate 'drop table pwks_backup';
execute immediate 'create table pwks_backup as (select * from pwks_worksite)';
execute immediate 'drop table pwks_worksite';
execute immediate 'create table pwks_worksite as (select * from pwks_worksite@get_pwks)';
exception
when NO_DATA_FOUND then
rollback;
end;
I get the following error:
SQL> execute getpwks();
BEGIN getpwks(); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "PPS_OWNER.GETPWKS", line 4
ORA-06512: at line 1
I'm logged in as pps_owner, so I should be able to run the procedure. Also the sql statements work on their own. Any ideas as to what the problem is??
TIA
Dave
|
|
|
|
| Re: executing stored procedure [message #8261 is a reply to message #8258] |
Wed, 06 August 2003 11:07  |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
Most likely you are missing the direct grants for CREATE TABLE and DROP TABLE. Your SQL*Plus session may inherit these via a role, which is not enough in stored PL/SQL.
Is it absolutely necessary to drop and recreate these tables? Unless the definition is constantly changing you should be able to truncate them instead.
|
|
|
|
| Re: executing stored procedure [message #8276 is a reply to message #8261] |
Thu, 07 August 2003 04:11 |
Dave
Messages: 92
Registered: August 1999
|
Member |
|
|
Thanks very much for that. I have looked into it a little more and it looks like I could get away with truncate and copy. With that in mind, I have copied a script and adapted it so that I can run it as a stored procedure, but when executing it gives me this error:
SQL> execute copy_worksite
BEGIN copy_worksite; END;
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "PPS_OWNER.COPY_WORKSITE", line 54
ORA-06512: at line 1
line 54 is as follows:
execute immediate 'COPY FROM '|| v_user_name|| '/' || v_user_pass || '@' || v_db_name|| 'INSERT '|| v_table_name ||' USING SELECT * FROM '|| v_table_name;
I have run this line from sql*plus (translating the variables of course) and it works a treat.
Any thoughts on why pl/sql doesn't like it?
TIA
Dave
|
|
|
|
|
|
| Re: executing stored procedure [message #8280 is a reply to message #8279] |
Thu, 07 August 2003 04:44 |
Dave
Messages: 92
Registered: August 1999
|
Member |
|
|
Ah ha, so how do I get the contents of a table in a remote database into a local database using a stored procedure ??
or is it simply insert into ... select * from.....
|
|
|
|
| Re: executing stored procedure [message #8281 is a reply to message #8280] |
Thu, 07 August 2003 05:26 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
> or is it simply insert into ... select * from.....
Yes. You can also make use of direct-path INSERT with
INSERT /*+ APPEND */ INTO ... SELECT ...
if the table is defined NOLOGGING and the data does not need redo logging (i.e. if the database goes down due to power failure etc before the next backup, you don't need anything you loaded into a NOLOGGING table with INSERT /*+ APPEND */, because it won't be there...)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
