How to copy table using the procedure that gets the table's name to copy and name of the new ta-CodePudding

Running the following code

create or replace procedure copy_table(
        from_table in out varchar2,
        new_table_name in out varchar2
    ) is v varchar(4000);
begin
    v :='create table new_table_name as select * from from_table';
    execute immediate v;
end copy_table;

begin
copy_table(lalala, new_table);
end;

I got the error

begin
copy_table(lalala, new_table);
end;
Error report -
ORA-06550: line 2, column 12:
PLS-00357: Table,View Or Sequence reference 'LALALA' not allowed in this context
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

How to correctly call the procedure since I alredy have "lalala" table? And will my procedure work for coping the existing table and create a new one? Or the code is wrong?

CodePudding user response：

You have passed the procedure parameters as strings while they must be passed as variables. Also your procedure parameters must be IN only instead of IN OUT. So your updated code would be -

create or replace procedure copy_table(
        from_table in varchar2,
        new_table_name in varchar2
    ) is v varchar(4000);
begin
    v :='create table '|| new_table_name ||' as select * from '|| from_table;
    execute immediate v;
end copy_table;

Demo.