Oracle stored procedure error > ora-24344: the reason for success with compilation error, execute immediate to execute dynamic SQL

Oracle stored procedures are as follows:

create or replace procedure pro_demo
is
begin
   select * from k_cicpa;
end

Error message:
> ORA-24344: success with compilation error
In SQL, the stored procedure exEC displays the query results normally, as shown below.

--drop PROCEDURE pro_demo
create  procedure pro_demo
as
begin
   select * from k_cicpa;
end

 exec pro_demo;


This is the first time I have been exposed to Oracle stored procedures
Reason for error:
Oracle stored procedures cannot be written in such a way as to write simple SELECT… The from… This is different from SQL Server

--Either write
select field into variable from student;
--either written as
execute immediate 'select * from student;'
--either store with a cursor

process is in the form of selet col into v_col from tb
 
--Change it to look like this.

 create or replace procedure p1
 as
  v_sql varchar2(4000);
 begin
   v_sql:='select * from xs';
   execute immediate v_sql;
 end;

Execute IMMEDIATE: Used in a stored procedure. Dynamic execution of SQL statements, such as using local dynamic SQL to execute DDL statements dynamically based on table names entered by the user and parameters such as field names, field types, etc.

create or replace procedure proc_test  
(  
table_name in varchar2, 
field1 in varchar2,  
datatype1 in varchar2, 
field2 in varchar2, 
datatype2 in varchar2 
) as  
str_sql varchar2(500);  
begin  
str_sql:='create table '||table_name||'('||field1||' '||datatype1||','||field2||' '||datatype2||')';  
execute immediate str_sql;   
exception  
when others then  
null;  
end ;

 

Read More: