英文:
how to resolve:- PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin end function pragma procedure
问题
需要通过添加一列来更改表格,我们如何编写正确的语法来在包内更改表格?可以使用过程或函数吗?
我尝试使用过程,并且遇到了以下错误:
CREATE OR REPLACE PACKAGE BODY temp IS
PROCEDURE prc_proc_add_column(
table_name IN user_tab_columns.TABLE_NAME%TYPE,
p_return OUT NUMBER,
p_message OUT VARCHAR2
) IS
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE table_name ADD col_name NUMBER';
p_return := 0;
p_message := 'Succesful';
EXCEPTION WHEN OTHERS THEN p_return := SQLCODE;
p_message := SQLERRM;
END prc_proc_add_column;
然后遇到了以下错误:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of
the following: begin end function pragma procedure
英文:
Need to alter the table by adding one column how can we write correct syntax for altering the table inside the package? it can be with procedure or function?
I tried with procedure and getting this error
CREATE OR REPLACE PACKAGE BODY temp IS
PROCEDURE prc_proc_add_column(
table_name IN user_tab_columns.TABLE_NAME%TYPE,
p_return OUT NUMBER,
p_message OUT VARCHAR2
) IS
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE table_name ADD col_name NUMBER';
p_return := 0;
p_message := 'Succesful';
EXCEPTION WHEN OTHERS THEN p_return := SQLCODE;
p_message := SQLERRM;
END prc_proc_add_column;
then getting this error:-
> PLS-00103: Encountered the symbol "end-of-file" when expecting one of
> the following: begin end function pragma procedure
答案1
得分: 0
Barbaros已经评论了您发布的代码在语法方面存在问题。
当然,您可以这样做,但通常来说,动态修改数据模型不是最佳选择。
既然您在这里,为什么不修改代码,以便您不仅可以提供表名,还可以提供列名和其数据类型呢?
请注意,在DDL语句上使用动态SQL时,您必须将名称连接到语句中;您不能使用绑定变量(尽管您没有使用...),而且通常来说,如果您能防止可能的SQL注入,那也不会有害。
这是一个示例。
包规范:
CREATE OR REPLACE PACKAGE temp
IS
PROCEDURE prc_proc_add_column (
p_table_name IN user_tab_columns.table_name%TYPE,
p_column_name IN user_tab_columns.column_name%TYPE,
p_datatype IN user_tab_columns.data_type%TYPE,
p_return OUT NUMBER,
p_message OUT VARCHAR2);
END;
包体:
CREATE OR REPLACE PACKAGE BODY temp
IS
PROCEDURE prc_proc_add_column (
p_table_name IN user_tab_columns.table_name%TYPE,
p_column_name IN user_tab_columns.column_name%TYPE,
p_datatype IN user_tab_columns.data_type%TYPE,
p_return OUT NUMBER,
p_message OUT VARCHAR2)
IS
l_str VARCHAR2 (200);
BEGIN
l_str :=
'ALTER TABLE '
|| DBMS_ASSERT.sql_object_name (p_table_name)
|| ' ADD '
|| DBMS_ASSERT.simple_sql_name (p_column_name)
|| ' '
|| DBMS_ASSERT.simple_sql_name (p_datatype);
EXECUTE IMMEDIATE l_str;
p_return := 0;
p_message := '成功';
EXCEPTION
WHEN OTHERS
THEN
p_return := SQLCODE;
p_message := SQLERRM;
END prc_proc_add_column;
END;
测试:
CREATE TABLE test
(
id NUMBER
);
DECLARE
l_ret VARCHAR2 (200);
l_msg VARCHAR2 (200);
BEGIN
temp.prc_proc_add_column ('test',
'salary',
'number',
l_ret,
l_msg);
DBMS_OUTPUT.put_line (l_ret || ': ' || l_msg);
END;
在测试部分,您可以看到如何使用此代码来添加列。
英文:
Barbaros already commented what is wrong with code you posted, as far as syntax is concerned.
Sure, you can do it, but - generally speaking - it isn't the best idea to modify data model dynamically.
When you're here, why wouldn't you modify that code so that you could provide not only table name, but also column name and its datatype?
Note that - when using dynamic SQL on DDL statements - you have to concatenate names into the statement; you can't use bind variables (not that you did ...), and - again generally speaking - it wouldn't hurt if you take care about possible SQL injection.
Here's an example.
Package specification:
SQL> CREATE OR REPLACE PACKAGE temp
2 IS
3 PROCEDURE prc_proc_add_column (
4 p_table_name IN user_tab_columns.table_name%TYPE,
5 p_column_name IN user_tab_columns.column_name%TYPE,
6 p_datatype IN user_tab_columns.data_type%TYPE,
7 p_return OUT NUMBER,
8 p_message OUT VARCHAR2);
9 END;
10 /
Package created.
Package body:
SQL> CREATE OR REPLACE PACKAGE BODY temp
2 IS
3 PROCEDURE prc_proc_add_column (
4 p_table_name IN user_tab_columns.table_name%TYPE,
5 p_column_name IN user_tab_columns.column_name%TYPE,
6 p_datatype IN user_tab_columns.data_type%TYPE,
7 p_return OUT NUMBER,
8 p_message OUT VARCHAR2)
9 IS
10 l_str VARCHAR2 (200);
11 BEGIN
12 l_str :=
13 'ALTER TABLE '
14 || DBMS_ASSERT.sql_object_name (p_table_name)
15 || ' ADD '
16 || DBMS_ASSERT.simple_sql_name (p_column_name)
17 || ' '
18 || DBMS_ASSERT.simple_sql_name (p_datatype);
19
20 EXECUTE IMMEDIATE l_str;
21
22 p_return := 0;
23 p_message := 'Succesful';
24 EXCEPTION
25 WHEN OTHERS
26 THEN
27 p_return := SQLCODE;
28 p_message := SQLERRM;
29 END prc_proc_add_column;
30 END;
31 /
Package body created.
Testing:
SQL> CREATE TABLE test
2 (
3 id NUMBER
4 );
Table created.
SQL> DECLARE
2 l_ret VARCHAR2 (200);
3 l_msg VARCHAR2 (200);
4 BEGIN
5 temp.prc_proc_add_column ('test',
6 'salary',
7 'number',
8 l_ret,
9 l_msg);
10 DBMS_OUTPUT.put_line (l_ret || ': ' || l_msg);
11 END;
12 /
PL/SQL procedure successfully completed.
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
SALARY NUMBER
SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论