Skip to Main Content
  • Questions
  • Creating and Executing a stored procedure that dynamically builds a table getting ORA-06550: PLS-00103

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Terry .

Asked: July 13, 2016 - 5:59 pm UTC

Last updated: June 23, 2019 - 6:45 pm UTC

Version: 11.1.0

Viewed 10K+ times! This question is

You Asked

Receiving ORA-06550: PLS-00103 error when trying to execute a procedure that dynamically creates a table.

I have created a pl/sql script to dynamically create a table:
declare
l_tablename varchar2(30) := 'TEST_3_'||to_char(sysdate, 'YYYYMMDD');
l_from_table varchar2(30):= 'abc.pxr95ef'; --function in parm3

begin
execute immediate 'create table ' ||l_tablename ||' as select * from '|| l_from_table;
end;
/

When I run this script from TOAD, it works great.

I then created a procedure using the pl/sql script above, which created successfully:
CREATE OR REPLACE procedure asu_p_build_table_name(beg VARCHAR2,statements varchar2,sourc varchar2)
AS
l_tablename varchar2(30) := asu_p_build_table_name.beg||to_char(sysdate, 'YYYYMMDD');
l_from_table varchar2(30):= asu_p_build_table_name.sourc;
l_get_data varchar2(1000) := asu_p_build_table_name.statements;
begin
execute immediate 'create table '||l_tablename||l_get_data||l_from_table;
end;
/

My problem is that when I try to execute the procedure:
execute asu_f_build_table_names('FTEST1_', ' as select * from ', 'abc.pxr95ef;');
I get the following error:
ORA-06550: line 1, column 101:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge


and Connor said...

SQL> CREATE OR REPLACE procedure asu_p_build_table_name(beg VARCHAR2,statements varchar2,sourc varchar2)
  2  AS
  3  l_tablename varchar2(30) := asu_p_build_table_name.beg||to_char(sysdate, 'YYYYMMDD');
  4  l_from_table varchar2(30):= asu_p_build_table_name.sourc;
  5  l_get_data varchar2(1000) := asu_p_build_table_name.statements;
  6  begin
  7  dbms_output.put_line('create table '||l_tablename||l_get_data||l_from_table);
  8  execute immediate 'create table '||l_tablename||l_get_data||l_from_table;
  9  end;
 10  /

Procedure created.

SQL>
SQL>
SQL> set serverout on
SQL> execute asu_p_build_table_name('FTEST1_', ' as select * from ', 'abc.pxr95ef;');
create table FTEST1_20160714 as select * from abc.pxr95ef;
BEGIN asu_p_build_table_name('FTEST1_', ' as select * from ', 'abc.pxr95ef;'); END;

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "MCDONAC.ASU_P_BUILD_TABLE_NAME", line 8
ORA-06512: at line 1


The semi-colon at the end is *not* SQL, its a signal to TOAD (or SQL Plus) that you can now *run* this command. Hence you do not need it for PLSQL.

SQL> set serverout on
SQL> execute asu_p_build_table_name('FTEST1_', ' as select * from ', 'dual');
create table FTEST1_20160714 as select * from dual

PL/SQL procedure successfully completed.


Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Build dynamic table

Terry Kelly, July 15, 2016 - 12:58 pm UTC

Worked like a champ,,, thank you so much!!
Connor McDonald
July 16, 2016 - 4:39 am UTC

glad we could help.

(Just tread carefully when dynamically building *anything*... you can get into sql injection territory)

compilation error

Dash, June 19, 2019 - 8:29 pm UTC

CREATE OR REPLACE TRIGGER orders_bffr_before_delete
BEFORE DELETE ON nufast.eai_orders_bffr
FOR EACH ROW

BEGIN

-- Insert record into audit table
INSERT INTO nufast.eai_orders_bffr_audit
(order_key,
order_type,
premise_key,
cis_account,
compl_date,
order_status,
delete_date)
VALUES
(:old.order_key,
:old.order_type,
:old.premise_key,
:old.cis_account,
:old.compl_date,
:old.order_status,
SYSDATE);

END orders_bffr_before_delete;

/



getting the error :Error during Execute
S1000(6550)[Oracle][ODBC][Ora]ORA-06550: line 5, column 10:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

;

Connor McDonald
June 23, 2019 - 6:45 pm UTC

Running it in SQL Developer? If so, remove the whitespace lines.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library