PL/SQL HELP (call another procedure?)

[babsjoy]

Can I create a PL/SQL procedure which then calls another PL/SQL procedure (needs parameters). I have been looking and trying different commands but none of them seem to work with out this error or some other error.

PLS-00201: identifier 'MAILUSERS' must be declared

MAILUSERS is the procedure in the database schema I am trying to execute.

It is a PL/SQL procedure which determines whether an e-mail reminder must be sent due to non-approval within 24 hours. This procedure then must call the mail procedure so an email can be sent and the database be updated with the date/time the email was sent.

Thanks for your help in advance...

 

[carp]

Yes. It should look something like:

PROCEDURE my_calling_proc IS
BEGIN
.
.
.
IF (some_condition_is_true) THEN
mailusers(your_argument_list_here);
END IF;
.
.
.
END;

 

[SantaMufasa]

Babsjoy,

Adding to Carp's correct response, the MAILUSERS procedure must exist in either of the following forms:

1) Locally-defined procedure:
PROCEDURE my_calling_proc IS
     procedure mailusers (your_argument_list_here) is
     begin...
     end;
BEGIN
.
.
.
IF (some_condition_is_true) THEN
   mailusers(your_argument_list_here);
END IF;
.
.
.
END;
 
or 
2) Database procedure:
CREATE OR REPLACE procedure mailusers (your_argument_list_here) is
     begin...
     end;
/

Which of the two methods did you use to define your mailusers procedure?

Dave
Sandy, Utah, USA @ 21:21 GMT, 14:21 Mountain Time

 

[babsjoy]

I am using a procedure that was created by someone else:

Here is the beginning of the procedure up to the BEGIN.

CREATE OR REPLACE PROCEDURE mailusers(
P_EMAIL_ADDRESS IN ASIDBA.APR_SECURITY_TABLE.AST_EMAIL%TYPE,
P_REQUISITION_NUMBER IN ASIDBA.APR_APPROVAL_TABLE.AAT_REQUISITION_NUMBER%TYPE,
P_AMENDMENT_NUMBER IN ASIDBA.APR_APPROVAL_TABLE.AAT_AMENDMENT_NUMBER%TYPE,
P_REQ_YEAR IN ASIDBA.APR_APPROVAL_TABLE.AAT_REQUISITION_YR%TYPE,
P_UID IN ASIDBA.APR_APPROVAL_TABLE.AAT_APPROVER_UID%TYPE,
P_CATEGORY IN ASIDBA.APR_APPROVAL_TABLE.AAT_CATEGORY%TYPE,
P_SUBJECT_LINE IN VARCHAR2 )

IS
conn UTL_SMTP.CONNECTION;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
mesg VARCHAR2( 4000 );
usraddr VARCHAR2( 100 );
addrlist varchar2(50); --addresslist_tab;
addrcnt BINARY_INTEGER:= 0;
tz varchar2(25);
--emailname VARCHAR2( 30 );
--emailaddress varchar2(18) := '@NAVY.MIL';

 

[SantaMufasa]

Babsjoy,

If "mailuser" is a "procedure that was created by someone else", then to confirm, "mailuser" resides in the SAME SCHEMA as the Oracle user which you logged into to run your calling (outside) procedure, right?

Along with that confirmation, could you please copy and paste (in a reply) your calling procedure's code and the error message so we can see context?

Thanks,

Dave
Sandy, Utah, USA @ 21:37 GMT, 14:37 Mountain Time

 

[babsjoy]

We are using the TOAD utility. I am creating and testing procedure under my schema first before they get stored under the database schema (ASIDBA) my schema (JOYCEBA)

Here is my procedure code:

CREATE OR REPLACE PROCEDURE JOYCEBA.APRP100_EMAIL_REMINDER

IS

v_filehandle_log UTL_file.file_type;
BEGIN
v_filehandle_log :=UTL_file.FOPEN('e:\tasi\data','apr_email_reminder.log','w');
UTL_file.PUT_LINE(v_filehandle_log,'Begin EMAIL REMINDER Process');



DECLARE


TYPE db_view_fields is record(
requisition_number varchar2(15),
amendment_number varchar2(3),
requisition_yr varchar2(4),
approver_uid varchar2(12),
category varchar2(25));

db_view_rec db_view_fields;

email_address varchar2(45);
uid varchar2(12);


-- DEFINE FIELDS
v_email_count integer :=0;


--CURSOR

CURSOR email_reminder_fields IS
Select aat_requisition_number,aat_amendment_number,
aat_requisition_yr,aat_approver_uid,aat_category
FROM joyceba.v_apr_email_reminder;



BEGIN

OPEN email_reminder_fields;

LOOP
FETCH email_reminder_fields
INTO db_view_rec;
EXIT WHEN email_reminder_fields%NOTFOUND;

IF SQL%FOUND THEN


-- no update of email sent field since the actual procedure thats sends email will do this
-- IF CATEGORY = 'P' then look for e-mail address in control_points_table
-- otherwise look in security table


IF db_view_rec.category = 'P' THEN
SELECT acp_cp_distribution_list
INTO email_address
FROM
asidba.apr_control_points_table
WHERE acp_category =
db_view_rec.category;

uid:= db_view_rec.approver_uid;

asidba.MAILUSERS(email_address,
db_view_rec.requisition_number,
db_view_rec.amendment_number,
db_view_rec.requisition_yr,
uid,
db_view_rec.category 'APR approval reminder email');

-- ELSE
-- SELECT ast_email,ast_uid
-- INTO email_address,uid
-- FROM
--asidba.apr_requisition_table,asidba.apr_security_table
-- WHERE art_originator_uid =
---ast_uid;
--


END IF;


v_email_count := v_email_count + 1;


END IF;


END LOOP;

UTL_file.PUT_LINE(v_filehandle_log,
'Count of email reminders:'||to_char(v_email_count));
CLOSE email_reminder_fields;




EXCEPTION

WHEN TIMEOUT_ON_RESOURCE THEN
UTL_file.PUT_LINE(v_filehandle_log, 'Procedure TIMED OUT');
WHEN INVALID_CURSOR THEN
UTL_file.PUT_LINE(v_filehandle_log, 'Invalid cursor operation');
WHEN NOT_LOGGED_ON THEN
UTL_file.PUT_LINE(v_filehandle_log, 'Procedure could not connect to Oracle');
WHEN UTL_FILE.invalid_path THEN
UTL_file.PUT_LINE(v_filehandle_log, ' UTL_file error:invalid patH');
WHEN UTL_FILE.invalid_filehandle THEN
UTL_file.PUT_LINE(v_filehandle_log, ' UTL_file error:invalid filehandle');
WHEN UTL_FILE.write_error THEN
UTL_file.PUT_LINE(v_filehandle_log, ' UTL_file error: unable to write to output file');
WHEN OTHERS THEN
IF SQL%NOTFOUND THEN
UTL_file.PUT_LINE(v_filehandle_log,'NO data found, file created');
END IF;
UTL_file.PUT_LINE(v_filehandle_log, ' UTL_file ERROR unknown');

END;
UTL_file.PUT_LINE(v_filehandle_log,'EMAIL REMINDER process completed');
UTL_file.FClose(v_filehandle_log);


END;

 

[SantaMufasa]

Babsjoy,

Could you please execute as JOYCEBA (and post the results of) the following query:

select owner,object_name from all_objects where object_name = 'MAILUSERS';

Thanks,

Dave
Sandy, Utah, USA @ 22:28 GMT, 15:28 Mountain Time

 

[carp]

Babsjoy -
You have changed your story! You originally said the procedure was in the same schema. Now you are saying it is in a different schema. This changes things!

Assuming the mailusers is in the asidba schema and you are logged into joyceba, then you might try this:

First, make sure you can "see" the procedure:
desc asidba.mailusers

If you get an error message, then you may need to have asidba grant you execute privilege on mailusers. However, if you get a description of the procedure, then you can make a minor change to your code:

PROCEDURE my_calling_proc IS
BEGIN
.
.
.
IF (some_condition_is_true) THEN
asidba.mailusers(your_argument_list_here);
END IF;
.
.
.
END;

and it should work.

 

[SantaMufasa]

Carp,

Yes, Babsjoy needs to confirm visibility/execute privs on the "asidba.mailusers" procedure and, in the absence of synonyms, qualify any reference to "mailusers" as "asidba.mailusers". But, unless I am mistaken, she has done that in her code above.

I do, however, see a coding mistake in Babsjoy's code, which, as written, definitely creates and error message (perhaps the one she is receiving). There is no comma between the last two arguments in the procedure call:
"...asidba.MAILUSERS(email_address,db_view_rec.requisition_number,db_view_rec.amendment_number,db_view_rec.requisition_yr,uid,db_view_rec.category [missing comma]'APR approval reminder email');"

Babsjoy, could you fix the comma and re-try please? If that still results in an error, then please post the results of my earlier query:

select owner,object_name from all_objects where object_name = 'MAILUSERS';

Thanks,

Dave
Sandy, Utah, USA @ 23:08 GMT, 16:08 Mountain Time

 

[carp]

Great gadfry daniel, you're right! She DID take care of that, didn't she? Must be time to go home for the night.

 

[babsjoy]

Sorry I was not able to respond sooner but I could not get access to the site yesterday for some reason. It would not surprise me if this was a privilege problem. God knows thats been a problem all along. Eventhough we are using a development/test area the DBA gives privileges when needed or necessary. I have your answers below. I was signed into SQL*PLUS using my username/password. I issued the select statement in TOAD also under my schema. Hope this helps...

SQL> desc asidba.mailusers;
PROCEDURE asidba.mailusers
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_EMAIL_ADDRESS VARCHAR2 IN
P_REQUISITION_NUMBER VARCHAR2 IN
P_AMENDMENT_NUMBER NUMBER(3) IN
P_REQ_YEAR NUMBER(4) IN
P_UID VARCHAR2 IN
P_CATEGORY VARCHAR2 IN
P_SUBJECT_LINE VARCHAR2 IN


SQL*PLUS:

SQL> SELECT OWNER,OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_NAME = 'MAILUSERS';

OWNER OBJECT_NAME
------------------------------ ------------------------------
ASIDBA MAILUSERS


TOAD:

select owner,object_name from all_objects where object_name = 'MAILUSERS'
OWNER OBJECT_NAME
------------------------------ ------------------------------
ASIDBA MAILUSERS
1 row selected

 

[babsjoy]

Well the problem has been resolved. It was a privilege problem.

FROM DBA:
You needed select (as well as execute) to the mailusers procedure …….

Sorry about that but the error didn't give me an indication it could be a privilege problem. Did it ?

PLS-00201: identifier 'ASIDBA.MAILUSERS' must be declared

I guess as a beginner you learn these things to gain experience.

Thanks again for all your help ... You were great...

 

[SantaMufasa]

Babsjoy,

There is a reason Oracle gave you the error it did; it's to protect information assets from hacking. Had you been a hacker, and Oracle said something like, "You don't have permission to exectute this procedure", then it has just disclosed a truckload of information to you (the hacker) that it shouldn't have disclosed...Most importantly, a) you are getting close to hacking this procedure, and b) the only thing preventing you from hacking success is a permission problem. If you do not have the proper permissions on an object, then as far as Oracle is concerned, to you, 'ASIDBA.MAILUSERS' doesn't exist and "PLS-00201: identifier 'ASIDBA.MAILUSERS' must be declared" if you want it to work.

Dave
Sandy, Utah, USA @ 18:03 GMT, 11:03 Mountain Time