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

curious_mind

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.

CTAS.txt

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.

Hemendra.Singh

Please use anyone of Method to run SQL statement from UNIX env.

Method 1 -

vi run_cats.sh

#!/bin/bash

export ORACLE_SID=$db_sidl

export ORACLE_HOME=$db_homel

DT=`date +%d.%m.%s.%d.%m.%Y`

sql_log=/tmp/db_"$db_sidl"."$DT".log

sqlplus -s '/as sysdba ' << EOF > $sql_log

--Start Sql Statement below.

<< Please your code here >>

--End Sql Statement below.

exit;

EOF

exit;

nohup sh run_cats.sh &

Method 2 -

From Sys Locally.

nohup sqlplus "/as sysdba" @CTAS.sql &

From Non-sys Account Locally.

nohup sqlplus username/password @CTAS.sql &

From Remotely - 

nohup sqlplus username/[email protected] @CTAS.sql &

Pre-Check Before Execution-

1) Make sure ,you check the non-sys user have create table permission granted.

2) depend on data , check you must have enough space in tablespace.

3) enough space for archivelog mount point, in case of huge data volume ,archive generation might be high.

Regarding CD_VAL_VARCHAR2 , i am not clear what is the issue .

Could you please share me the error you getting due to this ?

Thanks,

Hemendra Singh

EdStevens-OC

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 Verreynne

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

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;

/

Hemendra.Singh

Please use anyone of Method to run SQL statement from UNIX env.

Method 1 -

vi run_cats.sh

#!/bin/bash

export ORACLE_SID=$db_sidl

export ORACLE_HOME=$db_homel

DT=`date +%d.%m.%s.%d.%m.%Y`

sql_log=/tmp/db_"$db_sidl"."$DT".log

sqlplus -s '/as sysdba ' << EOF > $sql_log

--Start Sql Statement below.

<< Please your code here >>

--End Sql Statement below.

exit;

EOF

exit;

nohup sh run_cats.sh &

Method 2 -

From Sys Locally.

nohup sqlplus "/as sysdba" @CTAS.sql &

From Non-sys Account Locally.

nohup sqlplus username/password @CTAS.sql &

From Remotely - 

nohup sqlplus username/[email protected] @CTAS.sql &

Pre-Check Before Execution-

1) Make sure ,you check the non-sys user have create table permission granted.

2) depend on data , check you must have enough space in tablespace.

3) enough space for archivelog mount point, in case of huge data volume ,archive generation might be high.

Regarding CD_VAL_VARCHAR2 , i am not clear what is the issue .

Could you please share me the error you getting due to this ?

Thanks,

Hemendra Singh