I wrote the test code is as follows:

 
The CREATE TABLE testlkkj AS SELECT * FROM All_Tables; 
SELECT * FROM testlkkj WHERE table_name='MGMT_PDP_METADATA'; 
The CREATE OR REPLACE PROCEDURE proc_test001 
AS 
Sql_tx VARCHAR2 (3000); 
Tb_name VARCHAR2 (200) :='MGMT_PDP_METADATA'; 
The BEGIN 
Sql_tx:=- 'delete from testlkkj where table_name=: v1'; 
'insert into testlkkj select * from all_tables where table_name=: v2'; 
The EXECUTE IMMEDIATE sql_tx USING tb_name; 
IF % % NOTFOUND SQL OR SQL NOTFOUND IS NULL 
THEN dbms_output. Put_line (' no statement is executed). 
END the IF; 
COMMIT; 
END proc_test001; 

The BEGIN 
Proc_test001; 
END; 

Expected, after execution testlkkj will add a record in the table, but after execution, print out "no statement is executed", check the testlkkj records in the table, do not add, but when sql_tx into after the delete statement commented above, after the execution testlkkj did delete a data,

Ask, what are the problems in the process of the storage, the insert statement but failed to perform the delete statement can normal execution again,

CodePudding user response:

Insert into testlkkj...
Is less a key values

CodePudding user response:

reference 1st floor lzhui1987 response:

insert into testlkkj...
Is less a keyword values

not to insert into tb1 select * from tb2 this don't need values

CodePudding user response:

Dynamic SQL access issues, I tested your SQL, in my local user who performs the stored procedure because of you, do not have permission to query MGMT_PDP_METADATA this form, I'll local authorization, execution, has recorded,

CodePudding user response:

='insert into testlkkj sql_tx: select * from all_tables where table_name=' '| | : v2 | | "';

Incoming variables: v2 is? Need to play outside, you can put the dynamic execution of the statement print it out and see what is executed statements,

CodePudding user response:

='insert into testlkkj sql_tx: select * from all_tables where table_name=' | | : v2.

CodePudding user response:

Dynamic statement when the class object access all_objects, must explicitly authorized;

CodePudding user response:

reference u010520724 reply: 3/f

dynamic SQL access related problems, I tested your SQL in my local, because the user who performs the stored procedure, you do not have permission to query MGMT_PDP_METADATA this form, I'll local authorization, execution, has recorded,

I under the same user, perform the following statement:
DECLARE tb_name VARCHAR (20) :='MGMT_PDP_METADATA';
Sql_tx VARCHAR2 (1000);
The BEGIN
='insert into testlkkj sql_tx: select * from all_tables where table_name=: 1'.
-- the EXECUTE IMMEDIATE 'insert into testlkkj select * from all_tables where table_name=: 1' USING tb_name;
The EXECUTE IMMEDIATE sql_tx USING tb_name;
IF % % NOTFOUND SQL OR SQL NOTFOUND IS NULL THEN
Dbms_output. Put_line (' no statement is executed).
END the IF;
COMMIT;
END;
Can successfully perform ah, also is a dynamic SQL, one is a stored procedure is not, can produce such a difference?

CodePudding user response:

- in 10 g test and no problem

SQL> Select * from testlkkj;

Not selected row

SQL> The CREATE OR REPLACE PROCEDURE proc_test001
2 AS
3 sql_tx VARCHAR2 (3000);
4 tb_name VARCHAR2 (200) :='EMP';
5 the BEGIN
6 sql_tx:=-- 'delete from testlkkj where table_name=: v1';
7 'insert into testlkkj select * from all_tables where table_name=: v2';
8 the EXECUTE IMMEDIATE sql_tx USING tb_name;
9 IF % % NOTFOUND SQL OR SQL NOTFOUND IS NULL
10 THEN dbms_output. Put_line (' no statement is executed).
11 END IF;
12 COMMIT;
13 END proc_test001;
14/

Process has been created,

SQL> Select * from testlkkj;

Not selected row

SQL> The BEGIN
2 proc_test001;
3 the END;
4/

PL/SQL procedure successfully completed,

SQL> Select * from testlkkj;

The OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
SCOTT EMP SYCH_NEW

CodePudding user response:

refer to 7th floor kingofluo response:

successfully performed, the same is dynamic SQL, a stored procedure is not, can produce such a difference?

Right, there is such a difference;

CodePudding user response:

The building Lord, compile your stored procedure can through the first,

Or can you try to
END proc_test001;
Change
END;
/

CodePudding user response:

references to the tenth floor u012557814 response:

the building Lord, compile your stored procedure can through the first,

Or can you try to
END proc_test001;
Change
END;
/

He is a dynamic SQL, if there are no other grammar question, can be compiled through, if take out the insert statement, compilation is not likely in the past,