Stored Procedure

Question

Stored Procedure

rahulsony111

Hall of Fame

Points: 3865
More actions
December 14, 2009 at 8:24 am

#139984

Can anyone explain what this stored procedure is doing
create or replace PROCEDURE DWP00030_GRANT_ODI_PRIVS
AS
v_start_dt date;
v_sp_name varchar2(30);
v_count number;
v_err_msg varchar2(200);
t_count number;
t_name varchar2(200);
cursor v_cursor is
SELECT 'grant all on ' || table_name || ' to DWR_ETL_HWRITER' STMNT,table_name tab_name
FROM user_tables
WHERE table_name like 'E$%'
or table_name like 'I$%'
or table_name like 'C$%'
or table_name like 'J$%';
sql_stmt VARCHAR2(200);
V_STMNT NUMBER(20);
BEGIN
-- store in variable to update the same row later
v_start_dt:=sysdate;
v_sp_name:='DWP00030_GRANT_ODI_PRIVS';
insert into dwt10430_sp_log
values(v_sp_name,v_start_dt,null,'started');
--commit;
-- create a list of ODI tables
FOR sql_stmts IN v_cursor LOOP
SELECT nvl(count(1),0) into t_count
FROM user_tables
WHERE table_name =sql_stmts.tab_name;
IF t_count<>1 THEN
NULL;
ELSE
EXECUTE IMMEDIATE sql_stmts.STMNT;
END IF;
END LOOP;
update dwt10430_sp_log
set (end_dt, status) = (select sysdate,'finished' from dual)
where sp_name=v_sp_name
and start_dt=v_start_dt;
commit;
EXCEPTION
WHEN OTHERS THEN
v_err_msg := substr(SQLERRM, 1, 200);
insert into dwt10440_sp_log_detail
values(v_sp_name,sysdate,v_err_msg);
insert into dwt10440_sp_log_detail
values(v_sp_name,sysdate,DBMS_UTILITY.format_error_backtrace);
update dwt10430_sp_log
set (end_dt, status) = (select sysdate,'ended with errors' from dual)
where sp_name=v_sp_name
and start_dt=v_start_dt;
commit;
RAISE_APPLICATION_ERROR(-20001, v_sp_name);
END;
I want to know about the code in Highlighted

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

That doesn't look like T-SQL. Not sure what it is, but I doubt it's from an MS SQL database.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

GSquared (12/14/2009)
That doesn't look like T-SQL. Not sure what it is, but I doubt it's from an MS SQL database.

<<from dual>> Oracle?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rahulsony111 Hall of Fame Points: 3865 More actions · Answer 3

I am sorry then.

I dont know which technology is this

So this is oracle pl/sql, right?

Thanks guys for info.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

That's Oracle, not MS SQL Server. You're probably better off asking on an Oracle forum what it does.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass