Oracle stored procedures, staging tables, dynamic SQL testing

--Create a result temp table at the transaction levelCreateGlobalTemporary TableTmp_yshy (C1varchar2( -), C2varchar2( -)) on Commit Deleterows;--Create a temporary table that stores SQL statements at the transaction levelCreateGlobalTemporary TableTmp_sql (C1varchar2(4000)) on Commit DeleteRows

Test table:

 --  create  table   T_user (NAME  varchar2  (20   number  )  

-- first build the package Create or Replace    as--  1: Check all users according to age  procedure  p_user_select (    av _age        number,        -- age    av_suc out    varchar2       -- Whether or not  the logo is successful   ); End Pack_user;

--Create a package bodyCreate or ReplacePackage Body Pack_user as --1: Check all users according to age procedureP_user_select (Av_age Number, Av_suc outvarchar2 )  asLv_sqlvarchar2(4000); beginAV_SUC:='T'; Lv_sql:='INSERT INTO Tmp_yshy (C1,C2)'||           'Select Name,age'||           'From T_user'||           'where age >'||Av_age; Insert  intoTmp_sql (C1)Values(Lv_sql||';'); Executeimmediate lv_sql; Exception whenOthers Then       rollback; AV_SUC:='F'||' '||To_char (Sqlcode)||' : '||SQLERRM; Dbms_output.put_line (AV_SUC); EndP_user_select;EndPack_user;

PL/SQL Developer Sqlwindow test:

DECLAREvarchar2 (begin pack_user.p_user_          Select (end;

Select *  from tmp_yshy; Select *  from Tmp_sql;

Oracle stored procedures, staging tables, dynamic SQL testing