Dynamic SQL of stored procedures in Oracle packages

--- Create a package
Create or replace package test_pkg is
G_public_flag varchar2 (1 );

Function test_function (p_param1 varchar2,
P_param2 varchar2) return varchar2;
Function hello_function (p_param3 varchar2) return varchar2;
Procedure ff (p1 varchar2 );
End;
 

-- Package body
Create or replace package body test_pkg is
G_private_flag varchar2 (1): = 'y ';
 

Function test_function (p_param1 varchar2,
P_param2 varchar2) return varchar2
Is
Val varchar2 (10): = 'hello ';

Begin
Dbms_output.put_line ('HELO ');
Return val;
End;

Function hello_function (p_param3 varchar2) -- The parameters must be consistent with those in the header.
Return varchar2 is
Val varchar2 (10): = 'hello ';
Begin
Dbms_output.put_line ('World ');
Return val;
End;
Procedure ff (p1 varchar2) is
Flag number;
Pp1 VARCHAR2 (10): = 'P ';
P2 VARCHAR2 (10): = 'P ';
P3 VARCHAR2 (10): = 'P ';
P4 VARCHAR2 (10): = 'P ';
Begin
Select count (*) into flag from all_all_tables where table_name = 'logtable1 ';
Dbms_output.put_line (flag );
If (flag = 0) then
Execute immediate
'Create TABLE logtable1 (userid VARCHAR2 (10), logdate VARCHAR2 (10), exception_id VARCHAR2 (10), exception_msg VARCHAR2 (10 ))';
End if;
Execute immediate 'insert into logtable1 values (: 1,: 2,: 3,: 4) 'using '1', '1', '2', '3 '; -- Dynamic inserts only
End;
End;

---- Test
Declare
Begin
Dbms_output.put_line (test_pkg.test_function ('1', '1 '));
Dbms_output.put_line (test_pkg.hello_function ('1 '));
Test_pkg.ff ('dd ');
End;
Select * from logtable1;
Drop table logtable1