Home » SQL & PL/SQL » SQL & PL/SQL » Creating Packages with multiple Procedures (isqlplus)
Creating Packages with multiple Procedures [message #391981] Sun, 15 March 2009 10:24 Go to next message
jess1988
Messages: 1
Registered: March 2009
Junior Member
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;
Re: Creating Packages with multiple Procedures [message #391982 is a reply to message #391981] Sun, 15 March 2009 10:36 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
post results via CUT & PASTE from

SELECT * FROM USER_ERRORS

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

[Updated on: Sun, 15 March 2009 10:36]

Report message to a moderator

Re: Creating Packages with multiple Procedures [message #391995 is a reply to message #391981] Sun, 15 March 2009 18:34 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Don't use "create or replace procedure" within your package body, just use "procedure" as you did in the package specification. That is just the first thing that I noticed. There may be other problems.

Previous Topic: Change file
Next Topic: Outer Joins Concept
Goto Forum:
  


Current Time: Wed Dec 07 18:27:11 CST 2016

Total time taken to generate the page: 0.11774 seconds