RE: Creating a simple stored procedure

> Bob,
>
> where does the table mytab exist? - probably not in the same
> account as where you are creating the sp. in this case, you
> need to do two things. change the sp to say:
>
> update {schema}.mytab where {schema} is where the table exists.
>
> secondly, from {schema} you need to : grant select, insert,
> update, delete on mytab to {user} where {user} is where the sp exists.
>
> good luck!
>
> Tom Mercadante
> Oracle Certified Professional

Tom, thanks for the reply (and thanks to everyone else as well) The problem was that I was not prefexing the table_name with the owner. The table did exist in the account I was logged in under but I had to add the owner for the procedure to reconise it.

I had the necessary permissions in place

I was logged on as the owner of the owner of the tables... I should have known to prefix with the table_name
(I actually thought I tried that....)

So this works:

create or replace procedure closeActivity  AS

BEGIN      
          UPDATE OWNER.TABLE_NAME
        SET
          STATUS='O'
          WHERE STATUS='I' ;
          commit;

Then set this in a job:

variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;   dbms_job.submit(:jobno, 'OWNER.closeActivity;', trunc(sysdate+1), 'trunc(SYSDATE+1)', TRUE, :instno);
  commit;
end;
/

I was getting extremely frustrated .....

Thanks for the clairity
bob

>
> -----Original Message-----
> Sent: Thursday, January 30, 2003 10:31 AM
> To: Multiple recipients of list ORACLE-L
>
>
> How can I create this simple stored procedure
>
> Obiviously I don't have much experience but I would not think
> this being as difficult as it appears
>
> I want to run an update on a table (eventually from a job) I
> just need to get the sp working... Any examples Ive seen are
> much too complex for this simple task
>
> CREATE OR REPLACE PROCEDURE XYZ
> AS
> BEGIN
> UPDATE myTab SET STATUS = 'a' WHERE STATUS = 'b' ;
> END XYZ;
>
> This is telling me I need to declare myTab... Ive tried every
> iteration of declaring... But its not happening So, I present
> my delimma to my knowledgable friends @fatcity!
>
> {about to go insane}
> bob
> --

>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bob Metelsky
  INET: bmetelsky_at_cps92.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).