| About insert data into a table which created in pl/sql [message #36105] |
Mon, 05 November 2001 21:53  |
Sunny
Messages: 25
Registered: November 2001
|
Junior Member |
|
|
In a PL/SQL block, I use DBMS_UTILITY.EXEC_DDL_STATEMENT to create a table, then I want to insert data into the table in the same block, it show errors!
The example code is:
begin
dbms_utility.exec_ddl_statement(
'create table test2(t1 varchar2(10))');
insert into test2 values('111');
dbms_utility.exec_ddl_statement(
'drop table test2)');
end;
the error msg:
insert into test2 values('111');
*
ORA-06550: line 4, column 13:
PLS-00201: identifier 'TEST2' must be declared
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored
Is anything wrong?
Thank you.
----------------------------------------------------------------------
|
|
|
|
| Re: About insert data into a table which created in pl/sql [message #36106 is a reply to message #36105] |
Mon, 05 November 2001 22:29  |
Malli
Messages: 10
Registered: November 2001
|
Junior Member |
|
|
Hi Sunny!
Your insert statement in pl/sql block should also be use dbms_utility package.
ur insert should be as follows.
dbms_utility.exec_dml_statement('insert into test2 values(''111'')');
i think it may solve ur problem.
malli.
----------------------------------------------------------------------
|
|
|
|
| Re: About insert data into a table which created in pl/sql [message #39535 is a reply to message #36105] |
Mon, 22 July 2002 12:32 |
sn008
Messages: 7
Registered: July 2002
|
Junior Member |
|
|
It does not work in this case:
declare
v_cust_id CHAR(5) := &v_cust_id;
v_cust_rpid CHAR(2) := &v_cust_rpid;
v_cust_ln VARCHAR2(10) := &v_cust_ln;
v_cust_fn VARCHAR2(10) := &v_cust_fn;
v_cust_at CHAR(1) := &v_cust_at;
v_cust_ssn VARCHAR2(12) := &v_cust_ssn;
v_cust_gid CHAR(2) := &v_cust_gid;
v_cust_adate DATE := to_date('&v_cust_adate','dd-Mon-yyyy-HH24:MI:SS');
BEGIN
dbms_utility.exec_dml_statement('Insert into customer
(CUSTID,CRPID,LASTNAME,FIRSTNAME,ACTIVE,SSN,GID,ACTDATE)
VALUES (v_cust_id,v_cust_rpid,v_cust_ln,v_cust_fn,v_cust_at,v_cust_ssn,
v_cust_gid,v_cust_adate)');
END;
/
value for v_cust_id: 10006
2: v_cust_id CHAR(5) := &v_cust_id;
2: v_cust_id CHAR(5) := 10006;
value for v_cust_rpid: 20
3: v_cust_rpid CHAR(2) := &v_cust_rpid;
3: v_cust_rpid CHAR(2) := 20;
value for v_cust_ln: Le
4: v_cust_ln VARCHAR2(10) := &v_cust_ln;
4: v_cust_ln VARCHAR2(10) := Le;
value for v_cust_fn: Khai
5: v_cust_fn VARCHAR2(10) := &v_cust_fn;
5: v_cust_fn VARCHAR2(10) := Khai;
value for v_cust_at: Y
6: v_cust_at CHAR(1) := &v_cust_at;
6: v_cust_at CHAR(1) := Y;
value for v_cust_ssn: 089-90-9202
7: v_cust_ssn VARCHAR2(12) := &v_cust_ssn;
7: v_cust_ssn VARCHAR2(12) := 089-90-9202;
value for v_cust_gid: NULL
8: v_cust_gid CHAR(2) := &v_cust_gid;
8: v_cust_gid CHAR(2) := NULL;
value for v_cust_adate: 22-Jul-2002-12:37:11
9: v_cust_adate DATE := to_date('&v_cust_adate','dd-Mon-yyyy-HH24:MI:SS');
9: v_cust_adate DATE := to_date('22-Jul-2002-12:37:11','dd-Mon-yyyy-HH24:MI:SS');
Error:
v_cust_ln VARCHAR2(10) := Le;
*
ERROR at line 4:
ORA-06550: line 4, column 27:
PLS-00201: identifier 'LE' must be declared
ORA-06550: line 4, column 11:
PL/SQL: Item ignored
ORA-06550: line 5, column 27:
PLS-00201: identifier 'KHAI' must be declared
ORA-06550: line 5, column 11:
PL/SQL: Item ignored
ORA-06550: line 6, column 22:
PLS-00201: identifier 'Y' must be declared
ORA-06550: line 6, column 11:
PL/SQL: Item ignored
ORA-06550: line 11, column 14:
PLS-00302: component 'EXEC_DML_STATEMENT' must be declared
ORA-06550: line 11, column 1:
PL/SQL: Statement ignored
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
