CODEWITHSUNDEEP
sqloracle-databasestored-proceduresdynamic-sql
PriyalChaudhari
PriyalChaudhari

Reputation: 383

How to use create table and drop table DDL statements in stored proc in oracle.

Ihave created a proc like this 

create or replace PROCEDURE RMD_Tables  AS
Begin

execute immediate 'Drop table a608232_new_62K_master';

execute immediate 'create table a608232_new_62K_master as select A.* from 
a608232_MRD_DATA_62K_W_Account A inner join a608232_new_mids B
on A.MID = B.MID';

execute immediate'drop table a608232_web_abt_qlik_data';

execute immediate 'create table a608232_web_abt_qlik_data as select 
lv2.IP,SESSION_ID,PAGENAME,VSCHANNEL,VSSEARCHPHRASE,click_date,
EXTRACT(YEAR FROM click_date)"YEAR",EXTRACT(MONTH FROM 
click_date)"MONTH",EXTRACT(DAY FROM 
click_date)"DAY",wb.CLICK_HR,wb.CLICK_MIN,
RANK() OVER(PARTITION BY SESSION_ID ORDER BY SESSION_CLK_SEQ_NBR ASC)  
SESSION_RANK
from abc  wb
right outer join A608232.a608232_new_62K_master  lv2 on wb.IP = lv2.IP WHERE  
click_date >= TO_DATE(''25-SEP-2017'',''DD-MON-YYYY'') AND 
vschannel=''aaaa'' ' ;

END;

When I execute this procedure my first table is getting dropped/ After that I am getting this error msg 

Connecting to the database CWE_Nexedia.
ORA-01031: insufficient privileges
ORA-06512: at "A608232.RMD_TABLES", line 6
ORA-06512: at line 2
Process exited.
Disconnecting from the database CWE_Nexedia.

My first statement is getting executed after that i am facing issue. I am doing this becoz I want to schedule a job that will run everyday to update these tables.

Upvotes: 1

Views: 1547

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

First of all I suggest to use q'{}' to avoid duplicating '' for instance:

execute immediate q'{create table a608232_web_abt_qlik_data as select 
lv2.IP,SESSION_ID,PAGENAME,VSCHANNEL,VSSEARCHPHRASE,click_date,
EXTRACT(YEAR FROM click_date)"YEAR",EXTRACT(MONTH FROM 
click_date)"MONTH",EXTRACT(DAY FROM 
click_date)"DAY",wb.CLICK_HR,wb.CLICK_MIN,
RANK() OVER(PARTITION BY SESSION_ID ORDER BY SESSION_CLK_SEQ_NBR ASC)  
SESSION_RANK
from abc  wb
right outer join A608232.a608232_new_62K_master  lv2 on wb.IP = lv2.IP WHERE  
click_date >= TO_DATE('25-SEP-2017','DD-MON-YYYY') AND 
vschannel='aaaa' }' ;

Second:

insufficient privileges

It is pretty clear error message. You have to make sure you have priviliges to CREATE TABLE and read from specific tables.

You could also consider other options:

  • TRUNCATE TABLE and INSERT INTO
  • create materialized view and refresh it on daily basis

EDIT:

I have the previledges . When i ran create table separately I runs and creates table

When you use dynamic SQL inside stored procedure you execute it with rights of caller/invoker. I suspect that it is accout for reporting and has limited privileges.

Upvotes: 2

Related Questions