How to Execute a .sql file(having CTAS) in the Unix environment — oracle-tech

    Forum Stats

  • 3,701,852 Users
  • 2,239,498 Discussions
  • 7,835,577 Comments

Discussions

How to Execute a .sql file(having CTAS) in the Unix environment

curious_mindcurious_mind Posts: 242 Bronze Badge

Hi All,

I have my requirement where i have to executed the attached block(.sql file) having CTAS into it from my KSH file. my script will get fail in production because in production the user which is executing this script will not have "create table" privilege.

also in the attached script, it is picking up some partition codes dynamically(called CD_VAL_VARCHAR2 in the attached script) so considering this also please suggest how to get my requirement fulfilled.


Thanks in advance.

Best Answer

Answers

  • EdStevens-OCEdStevens-OC Posts: 44 Silver Trophy
    edited November 19

    Many sites block attachements/ links. Many forum participants refuse to open them for the same reason others block them. There is no reason not to include code directly in your message.


    Bottom line for your question - the oracle db user that executes the script must have the necessary privileges to execute the commands in the script. This is basic Database Security 101. It has nothing to do with being run from a ksh script.

  • Billy VerreynneBilly Verreynne Posts: 27,730 Red Diamond

    Make the script a package or procedure in the schema that needs to own the table, and has the create table priv.

    Grant the user who will run ksh script, execute rights on the package or procedure created.

    And perhaps look at Ada language standards as detailed in https://en.wikibooks.org/wiki/Ada_Style_Guide - PL/SQL is an Ada language implementation.

    It is a proper and rich programming language. Not some uppercase mad hotchpot of scripting, SQL, macros, and what not. Treat it with respect and it will do an excellent job, outperforming other languages used for Oracle client/SQL programming.

    curious_mind
  • curious_mindcurious_mind Posts: 242 Bronze Badge

    This is the script which i have mentioned above, here the statement starting with "SELECT CD_VAL_VARCHAR2" is extracting the partition codes dynamically from the database so how come i can include the same in stored proc/package?


    alter session set current_schema=SEODS01;

    set serveroutput on;

    --drop table CL_GRP_DTL_TMP;

    Declare

     v_ag         varchar2(20);

     v_cap        varchar2(20);

     v_grp        varchar2(20);   

     v_ca         varchar2(20);

     v_cg         varchar2(20);

     v_prs        varchar2(20);

     V_CLP        VARCHAR2(20);

     V_CEAP    VARCHAR2(20);

     V_CEAS    VARCHAR2(20);

     V_TVR        VARCHAR2(20);

     V_SSR        VARCHAR2(20);

     V_CM         VARCHAR2(20);

     V_FG         VARCHAR2(20);

     V_FPG        VARCHAR2(20);

     V_FP         VARCHAR2(20);

     V_GFP        VARCHAR2(20);

     v_cgd        VARCHAR2(20);

     v_cvv        varchar2(100);

     v_crt_tb       varchar2(10000);

     v_part_code     VARCHAR2(20);

     v_part_flg      NUMBER(38);

     prt_cde       varchar2(20);

     

    BEGIN

     SELECT  cd_val_desc, decode(cd_val_int_1,0,1,1,0)

     INTO   v_part_code, v_part_flg

     FROM   cd_val_dyn

     WHERE  cd_typ    = 'CL_GRP_DTL'

     AND   cd_cat_typ  = 'CGA'

     AND   cd_subcat_typ = 'VIEW DYN METADATA';


    prt_cde := v_part_code || '_' || v_part_flg;


     SELECT CD_VAL_VARCHAR2

      , MAX(CASE WHEN cd_typ = 'ACCT_GRP' THEN CD_VAL_TXT  END) AS AG

      , MAX(CASE WHEN cd_typ = 'CIF_ACCT_PRX' THEN CD_VAL_TXT  END) AS CAP

      , MAX(CASE WHEN cd_typ = 'GRP' THEN CD_VAL_TXT  END) AS GRP 

      , MAX(CASE WHEN cd_typ = 'CRS_AUM' THEN CD_VAL_TXT  END) AS CA

      , MAX(CASE WHEN cd_typ = 'CL_GRP' THEN CD_VAL_TXT  END) AS CG 

      , MAX(CASE WHEN CD_TYP = 'PERS' THEN CD_VAL_TXT  END) AS PRS 

      , MAX(CASE WHEN CD_TYP = 'CL_PRFL' THEN CD_VAL_TXT  END) AS CLP

      , MAX(CASE WHEN CD_TYP = 'CASH_EDGE_ACCT_PRFL' THEN CD_VAL_TXT  END) AS CEAP

      , MAX(CASE WHEN CD_TYP = 'CASH_EDGE_ACCT_SUM' THEN CD_VAL_TXT  END) AS CEAS

      , MAX(CASE WHEN CD_TYP = 'TOT_VIEW_RGST' THEN CD_VAL_TXT  END) AS TVR

      , MAX(CASE WHEN CD_TYP = 'SECR_SITE_RGST' THEN CD_VAL_TXT  END) AS SSR

      , MAX(CASE WHEN CD_TYP = 'CL_MEET' THEN CD_VAL_TXT  END) AS CM

      , MAX(CASE WHEN CD_TYP = 'FP_GRP' THEN CD_VAL_TXT  END) AS FPG

      , MAX(CASE WHEN CD_TYP = 'FP_PRLM_GOAL' THEN CD_VAL_TXT  END) AS FPGO

      , MAX(CASE WHEN CD_TYP = 'FACE_PRFL' THEN CD_VAL_TXT  END) AS FP

      , MAX(CASE WHEN CD_TYP = 'GRP_FACE_PRFL' THEN CD_VAL_TXT  END) AS GFP

      , MAX(CASE WHEN CD_TYP = 'CL_GRP_DTL' THEN CD_VAL_TXT  END) AS CGD

      INTO v_cvv,v_ag,v_cap,v_grp,v_ca,v_cg,v_prs,v_clp,v_ceap,v_ceas,v_tvr,v_ssr,v_cm,v_fg,v_fpg,v_fp,v_gfp,v_cgd

      FROM CD_VAL_DYN WHERE cd_typ IN

      ('ACCT_GRP', 'CIF_ACCT_PRX','GRP','CRS_AUM','CL_GRP', 'PERS','CL_PRFL','CASH_EDGE_ACCT_PRFL',

      'CASH_EDGE_ACCT_SUM','TOT_VIEW_RGST','SECR_SITE_RGST','CL_MEET','FP_GRP','FP_PRLM_GOAL',

      'FACE_PRFL','GRP_FACE_PRFL','CL_GRP_DTL')

      AND cd_subcat_typ IN ( 'VIEW DYN METADATA', 'VIEW STAT METADATA')

      GROUP BY cd_val_varchar2;

       

    --end;   

    V_CRT_TB:=  'CREATE TABLE CL_GRP_DTL_TMP

             (EODS_GRP_ID,EODS_CL_ID,PRTT_CDE,FACE_PRFL_EODS_DSTR_ID,GRP_ALT_ID not null,GRP_ALT_ID_CNTX_CDE not null,CL_ALT_ID not null,

              CL_ALT_ID_CNTX_CDE not null,FACE_PRFL_DSTR_ALT_ID,FACE_PRFL_DSTR_ALT_ID_CNTX_CDE,CL_LAST_NME,CL_FIRST_NME,

      CL_MID_NME,CL_BTH_DTE,CL_DECSD_CDE,CL_EMPL_STAT_CDE,CL_MRTL_STAT_CDE,PRM_CL_GRP_IND,GRP_FP_ACCT_IND,

              GRP_ACTV_ACCT_IND,GRP_FP_IND,GRP_PRLM_GOAL_IND,CL_HSLD_INCM_AMT,CRS_AUM_AMT,CL_TOT_VIEW_AST_AMT,

              CL_RGST_FOR_SECR_SITE_IND,CL_RGST_FOR_TOT_VIEW_IND,CL_NXT_MEET_DTE,CRTE_PGM not null,CRTE_TSTP not null,UPDT_PGM,UPDT_TSTP,

          CONSTRAINT CL_GRP_DTL_TMP PRIMARY KEY (FACE_PRFL_EODS_DSTR_ID,EODS_GRP_ID, EODS_CL_ID,PRTT_CDE) 

              ) PARTITION BY HASH (EODS_GRP_ID) PARTITIONS 8 

              AS

    With

     GRP_ELEG as

     (Select ag.eods_grp_id, sum(case when cif.prod_ctg_cde = ''013'' then 1 ELSE 0 end) CNT_FP, 

      sum(case when cif.prod_ctg_cde <> ''013'' then 1 ELSE 0 end) CNT_OTH

      FROM acct_grp ag, cif_acct_prx cif

      WHERE ag.eods_acct_id = cif.eods_acct_id

      AND  cif.ACCT_STAT_CDE in (''01'',''02'')

      AND  ag.ACCT_GRP_END_DTE is null

      AND  AG.PRTT_CDE = ''' || v_ag || '''

      AND  cif.prtt_cde = ''' || v_cap || '''

      group by ag.eods_grp_id),


     GRP_LIST as

     (Select cl.cl_alt_id,cl.cl_alt_id_cntx_cde,gp.eods_grp_id, cg.eods_cl_id, p.CL_LAST_NME, p.CL_FIRST_NME, p.CL_MID_NME, p.BTH_DTE CL_BTH_DTE,         p.DECSD_CDE CL_DECSD_CDE,CLP.EMPL_STAT_CDE CL_EMPL_STAT_CDE, P.MRTL_STAT_CDE CL_MRTL_STAT_CDE,FP.face_prfl_id,

     clp.ANNL_INCM_AMT CL_HSLD_INCM_AMT, aum.AUM_VAL_AMT CRS_AUM_AMT,dstr.dstr_alt_id,dstr.dstr_alt_id_cntx_cde

     FROM Grp     gp

     ,  crs_aum   aum

     ,  cl_grp   cg

     ,  pers    p

     ,  cl_prfl   clp

     ,  face_prfl fp

     ,  grp_face_prfl gfp

     ,  cl_alt_id cl

     ,  dstr_alt_id dstr

     WHERE gp.GRP_CTG_CDE = ''001''

     AND cg.EODS_GRP_ID = gp.EODS_GRP_ID

     AND cg.CL_GRP_ROLE_END_DTE is null

     AND cg.CL_ROLE_IN_GRP_TYP_CDE = ''01''

     AND aum.EODS_GRP_ID(+) = gp.EODS_GRP_ID

     AND p.eods_cl_id = cg.eods_cl_id

     AND cg.eods_cl_id = cl.eods_cl_id

     AND clp.eods_cl_id(+) = cg.eods_cl_id 

     AND fp.face_prfl_id=gfp.face_prfl_id

     AND cg.eods_grp_id = gfp.eods_grp_id

     AND fp.eods_dstr_id= dstr.eods_dstr_id

     AND GP.PRTT_CDE  = ''' || v_grp || '''

     AND AUM.PRTT_CDE(+)= ''' || v_ca || '''

     AND CG.PRTT_CDE  = ''' || v_cg || '''

     AND P.PRTT_CDE   = ''' || v_prs || '''

     AND clp.prtt_cde(+)= ''' || v_clp || '''

     AND fp.prtt_cde = ''' || v_fp || '''

     AND gfp.prtt_cde = ''' || v_gfp || '''),


     TV_BAL as

     (Select eods_cl_id, sum(sum.CURR_BAL_AMT) CL_TOT_VIEW_AST_AMT

     FROM cash_edge_acct_prfl AP 

     ,  cash_edge_acct_sum SUM

     WHERE AP.acct_id = SUM.acct_id

     AND AP.PRTT_CDE  =  ''' || v_ceap || '''

     and sum.prtt_cde =  ''' || v_ceas || '''

     and (

     (ACCT_TYP_cde = ''DDA'' AND EXTN_ACCT_TYP_cde IN (''DDA'', ''CMA''))

      OR

     (ACCT_TYP_cde = ''INS'' AND EXTN_ACCT_TYP_cde IN (''ALI'', ''WLI'', ''ULI'', ''TLI'') )

      OR

     (ACCT_TYP_cde = ''OAA'' AND EXTN_ACCT_TYP_cde IN (''CDA'') )

      OR

     (ACCT_TYP_cde = ''SDA'' AND EXTN_ACCT_TYP_cde IN (''SDA'', ''MMA'')))

     group by eods_cl_id),


     PRM as

     (Select b.eods_cl_id, c.eods_grp_id, c.grp_alt_id,c.grp_alt_id_cntx_cde,                                           ''001'' || lpad( min(to_number(substr(c.grp_alt_id,4))) over(partition by b.eods_cl_id),10,''0'') PRM_GRP

     FROM cl_grp a, pers b, grp_alt_id c

     where a.eods_cl_id = b.eods_cl_id

     and a.eods_grp_id = c.eods_grp_id

    and a.prtt_cde = ''' || v_cg || '''

    and b.prtt_cde = ''' || v_prs || ''')


    select

    cast(b.EODS_GRP_ID as NUMBER(*,0)), cast(b.EODS_CL_ID AS NUMBER(*,0)),CAST('''||prt_cde||''' AS VARCHAR2(10)) PRTT_CDE,

    cast(b.FACE_PRFL_ID as NUMBER(*,0)) FACE_PRFL_EODS_DSTR_ID,CAST(i.grp_alt_id as VARCHAR2(30)),cast(i.grp_alt_id_cntx_cde as VARCHAR2(50)) ,

    cast(b.cl_alt_id as VARCHAR2(30)),cast(b.cl_alt_id_cntx_cde as VARCHAR2(50)),cast(b.dstr_alt_id as VARCHAR2(30)) face_prfl_dstr_alt_id,

    cast(b.dstr_alt_id_cntx_cde as VARCHAR2(50)),cast(b.CL_LAST_NME as VARCHAR2(100)), cast(b.CL_FIRST_NME as VARCHAR2(30)), 

    cast(b.CL_MID_NME as VARCHAR2(30)),cast(b.cl_bth_dte as DATE), cast(b.cl_decsd_cde as CHAR(1)), cast(b.cl_empl_stat_cde as VARCHAR2(20)),

    cast(b.cl_mrtl_stat_cde as VARCHAR2(20)),

    cast((case when i.GRP_ALT_ID=i.PRM_GRP then ''Y'' else ''N'' end) as CHAR(1)) PRM_CL_GRP_IND,

    cast((case when a.CNT_FP>0 then ''Y'' else ''N'' end) as CHAR(1)) as GRP_FP_ACCT_IND, 

    cast((case when a.CNT_OTH>0 then ''Y'' else ''N'' end) as CHAR(1)) as GRP_ACTV_ACCT_IND, 

    cast(decode(g.eods_grp_id,NULL, ''N'', ''Y'') as CHAR(1)) GRP_FP_IND, 

    cast(decode(h.eods_grp_id,NULL, ''N'', ''Y'') as CHAR(1)) GRP_PRLM_GOAL_IND,

    cast(b.CL_HSLD_INCM_AMT as NUMBER(15,2)), cast(b.CRS_AUM_AMT as NUMBER(15,2)), cast(c.CL_TOT_VIEW_AST_AMT as NUMBER(15,2)), 

    cast(decode(e.ENRL_STAT_CDE,''ACTIVE'',''Y'',''N'') as CHAR(1)) CL_RGST_FOR_SECR_SITE_IND, 

    cast(decode(d.ENRL_STAT_CDE,''ACTIVE'',''Y'',''N'') as CHAR(1)) CL_RGST_FOR_TOT_VIEW_IND, 

    cast(CL_NXT_MEET_DTE as DATE) CL_NXT_MEET_DTE, CAST(''BATCH'' as VARCHAR2(50)) CRTE_PGM, cast(sysdate as date)CRTE_TSTP,

    cast(''Batch'' as VARCHAR2(50)) Updt_pgm, cast(sysdate as date) updt_tstp

     FROM GRP_ELEG a, GRP_LIST b , TV_BAL c, tot_view_rgst d, secr_site_rgst e, cl_meet f,

     (SELECT DISTINCT EODS_GRP_ID FROM FP_GRP) G,

     (select distinct eods_grp_id from fp_prlm_goal where upper(PRLM_GOAL_STAT_CDE) in (''CAPTURED'',''SENTTOFPTOOL'')) h,prm i

     WHERE a.eods_grp_id(+) = b.eods_grp_id

     AND c.eods_cl_id(+) = b.eods_cl_id

     AND d.EODS_CL_ID (+) = b.eods_cl_id

     AND e.EODS_CL_ID (+) = b.eods_cl_id

     AND f.EODS_CL_ID (+) = b.eods_cl_id

     AND b.eods_grp_id=g.eods_grp_id(+) 

     AND b.eods_grp_id=h.eods_grp_id(+)

     AND i.eods_cl_id = b.eods_cl_id

     AND i.eods_grp_id = b.eods_grp_id

     AND d.prtt_cde(+) = ''' || v_tvr || '''

     AND e.prtt_cde(+) = ''' || v_ssr || '''

     AND F.PRTT_CDE(+) = ''' || v_cm  || '''';


    execute immediate V_CRT_TB;

    --dbms_output.put_line(V_CRT_TB);


    END;

    /

Sign In or Register to comment.