Its asked to create a table to maintain details by using a stored procedure. Do I need to use a procedure to create the table or do I need to use a procedure to insert values?

1. Create a table to maintain company details by using a stored procedure by passing company id and name as input parameters.

   Company ID	Name
   COMP_A	Company A
   COMP_B	Company B

CodePudding user response：

Creating a table from PL/SQL is possible - you'd need dynamic SQL for that. But, you should avoid it because that's simply bad practice: create table at SQL level, then write a procedure which will manipulate data stored within:

create table some_name (id number, name varchar2(20));

create or replace procedure p_some_proc (par_id in number, par_name in varchar2)
is
begin
  insert into some_name (id, name) values (par_id, par_name);
end;
/

begin
  p_some_proc(par_id => 1, par_name => 'Littlefoot');
end;
/

If you'd want to do create table in a procedure, then you'd

create or replace procedure p_some_proc as
begin
  execute immediate 'create table some_name (id number, name varchar2(20))';
end;
/

begin
  p_some_proc;
end;
/

Note that such a procedure would fail every time you call it if table already exists so you'd have to handle the exception. Not that it is impossible, but - that's just not the way to do it.

CodePudding user response：

Do I need to use a procedure to create the table or do I need to use a procedure to insert values?

The homework question is phrased awfully and you should confirm with your instructor what their intentions are.

Best-practice would be to:

1. Create the table with a CREATE TABLE statement first.
2. Create the procedure with a CREATE PROCEDURE statement and within the procedure's body INSERT the values into the table.
3. Use an anonymous PL/SQL block to call the procedure with the values you need to insert.

While it is technically possible to use a CREATE TABLE statement inside EXECUTE IMMEDIATE inside a procedure it is bad practice and, if you did that, then you would also need to wrap the INSERT statements inside their own EXECUTE IMMEDIATE statements (otherwise the procedure will not compile as you are trying to reference a table that does not exist at the procedure's compile time). When you start to do that then it cascades and everything has to be contained in dynamically called SQL statements and you lose the benefits of all the compile-time type checking and all your errors will happen at run-time (which becomes a nightmare to debug).