iSQLPlus creating packages with multiple procedures in it

55 pts.
Tags:
iSQL*Plus
Oracle development
Oracle SQL
SQLPlus
Hi, I am a bit stuck i need to create a package which has a number of different procedures in it. The procedures work on their own however whenever i try to put them into a package they then don't work and i get the error package created with complication errors, however when i type show errors into my iSQLPlus it doesn't show me what the errors are. This is the code i have been trying, however i have never had to make a package before so didn't really know what i was doing. Any help or suggestions would be great. Thanks set serveroutput on; CREATE OR REPLACE PACKAGE pack_add_users AUTHID CURRENT_USER IS PROCEDURE proc_add_new_csdba; PROCEDURE proc_add_new_csdeveloper; END pack_add_users; CREATE OR REPLACE PACKAGE BODY pack_add_users IS create or replace PROCEDURE proc_add_new_csdba AUTHID CURRENT_USER IS begin dbms_output.enable(20000); FOR VAR2 in 4..10 LOOP execute immediate 'create user csdba'||to_char (VAR2)||' identified by csdba'||to_char (VAR2); execute immediate 'grant create session to csdba'||to_char (VAR2); execute immediate 'grant cs_reps_role to csdba'||to_char (VAR2); execute immediate 'grant cs_admin_role to csdba'||to_char (VAR2); execute immediate 'grant cs_sales_role to csdba'||to_char (VAR2); execute immediate 'grant cs_clerk_role to csdba'||to_char (VAR2); execute immediate 'grant cs_manager_role to csdba'||to_char (VAR2); execute immediate 'grant cs_programmer_role to csdba'||to_char (VAR2); execute immediate 'grant cs_dba_role to csdba'||to_char (VAR2); END LOOP; end proc_add_new_csdba; create or replace procedure proc_add_new_developer AUTHID CURRENT_USER as begin dbms_output.enable(20000); FOR VAR2 in 4..10 LOOP execute immediate 'create user csdeveloper'||to_char (VAR2)||' identified by csdeveloper'||to_char (VAR2); execute immediate 'grant create session to csdeveloper'||to_char (VAR2); execute immediate 'grant cs_reps_role to csdeveloper'||to_char (VAR2); execute immediate 'grant cs_admin_role to csdeveloper'||to_char (VAR2); execute immediate 'grant cs_sales_role to csdeveloper'||to_char (VAR2); execute immediate 'grant cs_clerk_role to csdeveloper'||to_char (VAR2); execute immediate 'grant cs_manager_role to csdeveloper'||to_char (VAR2); execute immediate 'grant cs_programmer_role to csdeveloper'||to_char (VAR2); END LOOP; end proc_add_new_developer; END pack_add_users; show errors;

Answer Wiki

Thanks. We'll let you know when a new response is added.

This code has more than one error:

– You can’t define AUTHID for procedures or functions that you declare and define inside a PL/SQL package. In this case the AUTHID is defined for the whole package.
– Procedures inside a package should not be defined with the CREATE OR REPLACE command.
– In your package spec you defined a procedure ‘proc_add_new_<b>cs</b>developer’, but in the body, its name is ‘proc_add_new_developer’

Try this modified version:

CREATE OR REPLACE PACKAGE pack_add_users AUTHID CURRENT_USER IS
PROCEDURE proc_add_new_csdba;
PROCEDURE proc_add_new_csdeveloper;
END pack_add_users;
/

CREATE OR REPLACE PACKAGE BODY pack_add_users IS
PROCEDURE proc_add_new_csdba IS
begin
dbms_output.enable(20000);
FOR VAR2 in 4..10 LOOP
execute immediate ‘create user csdba’||to_char (VAR2)||’ identified by csdba’||to_char (VAR2);
execute immediate ‘grant create session to csdba’||to_char (VAR2);
execute immediate ‘grant cs_reps_role to csdba’||to_char (VAR2);
execute immediate ‘grant cs_admin_role to csdba’||to_char (VAR2);
execute immediate ‘grant cs_sales_role to csdba’||to_char (VAR2);
execute immediate ‘grant cs_clerk_role to csdba’||to_char (VAR2);
execute immediate ‘grant cs_manager_role to csdba’||to_char (VAR2);
execute immediate ‘grant cs_programmer_role to csdba’||to_char (VAR2);
execute immediate ‘grant cs_dba_role to csdba’||to_char (VAR2);
END LOOP;
end proc_add_new_csdba;

procedure proc_add_new_csdeveloper as
begin
dbms_output.enable(20000);
FOR VAR2 in 4..10 LOOP
execute immediate ‘create user csdeveloper’||to_char (VAR2)||’ identified by csdeveloper’||to_char (VAR2);
execute immediate ‘grant create session to csdeveloper’||to_char (VAR2);
execute immediate ‘grant cs_reps_role to csdeveloper’||to_char (VAR2);
execute immediate ‘grant cs_admin_role to csdeveloper’||to_char (VAR2);
execute immediate ‘grant cs_sales_role to csdeveloper’||to_char (VAR2);
execute immediate ‘grant cs_clerk_role to csdeveloper’||to_char (VAR2);
execute immediate ‘grant cs_manager_role to csdeveloper’||to_char (VAR2);
execute immediate ‘grant cs_programmer_role to csdeveloper’||to_char (VAR2);
END LOOP;
end proc_add_new_csdeveloper;

END pack_add_users;
/

show errors;

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Araghu
    How to register a package with multiple procedures.
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following