Home » SQL & PL/SQL » SQL & PL/SQL » creating views in PL/SQL
| creating views in PL/SQL [message #8031] |
Tue, 22 July 2003 16:07  |
Rohini
Messages: 13
Registered: December 2001
|
Junior Member |
|
|
Below is my stored proc that creates a view at nearly the end (*** highlight the exec immediate statement below). The proc works to the point of creating the correct "Create or Replace As ..." SQL statement(I can dump it in the temp table and run it to create the view) BUT will not create the actual view.
Instead I get the error code 33 and NO View as the view name.
What is the problem?
(p_Ent_ID IN varchar2,
p_summary IN varchar2, -- ('5M','15M','30M' OR '60M')
p_req_view_type IN varchar2, -- (value is 'AGTAPP')
po_view_name OUT varchar2,
po_error_code OUT number)
IS
v_Enterprise_App_Id varchar2(2000);
v_Ent_Agt_ID varchar2(2000);
v_summary varchar2(4) := p_summary;
v_req_view_type varchar2(12) := p_req_view_type;
v_table_name varchar2(40);
v_table_name1 varchar2(40);
TYPE ContCurTyp IS REF CURSOR;
cont_cv ContCurTyp;
cont_rec varchar2(5000);
sql_stmt VARCHAR2(1000);
v_tot_cont number;
v_cnt number;
v_common varchar2(5000) := ' ';
v_view_name varchar2(50);
create_view varchar2(30000);
view_name varchar2(200);
v_contributor varchar2(2000) := ' ';
e_error_code number;
p_view_name varchar2(40) := 'NO VIEW';
e_cont_error EXCEPTION;
e_view_exists EXCEPTION;
e_some_error EXCEPTION;
v_union Number;
v_union1 Number;
BEGIN
/* Convert given input summary value '5M' to '05M' */
po_view_name := p_view_name;
IF v_summary = '5M' THEN
v_summary := '05M';
END IF;
/* Based on Ent. Agt. Id's or Ent. Application Id's total contributor count is calculated */
IF V_REQ_VIEW_TYPE = 'AGTAPP' THEN
GETENTERPRISEID(p_Ent_ID,v_Ent_Agt_ID,e_Error_Code);
IF e_Error_Code > 0 THEN
RAISE e_some_error;
END IF;
/*Changed below stmt to reflect new resource_superset_type_number on DM rel 2SP2 */
--cres_superset.resource_superset_type_number between 1 and 2 and
/* Deleted below 4 stmts from sql below */
--cs.node_id = n.node_id and
--nt.node_type_id = n.node_type_id and
--dc.vendor_initials not in (''EGAN'',''KANA'') and
--nt.data_contributor_id = dc.data_contributor_id'
-- dc.vendor_initials not in (''EGAN'',''KANA'') and
-- nt.data_contributor_id = dc.data_contributor_id
/* */
sql_stmt := 'select count(distinct dc.vendor_initials)
from
call_resource_superset cres_superset,
call_resource_set2superset c2s,
call_resource_set cs,
node n,
node_type nt,
data_contributor dc
where ('||v_Ent_Agt_ID||') and
cres_superset.resource_superset_type_number in (1,2,5) and
cres_superset.call_resource_superset_id = c2s.call_resource_superset_id and
c2s.call_resource_set_id = cs.call_resource_set_id and
dc.uuid in (''060efdcc-a421-11d2-9327-00c04fa37ab5'',''4e53b197-960f-44ea-b1c4-2b5647ed85ab'',''6b456f75-ac8e-11d5-b066-00b0d07901f1'')'
;
END IF;
OPEN cont_cv FOR sql_Stmt;
FETCH cont_cv INTO v_tot_cont;
CLOSE cont_cv;
IF (v_tot_cont = 0) then
e_error_code := 30;
RAISE e_cont_error;
END IF;
/* Based on Ent. Agt. or Ent. Application Id's find out existing contributors */
v_cnt := 1;
IF V_REQ_VIEW_TYPE = 'AGTAPP' THEN
sql_stmt := 'select distinct dc.vendor_initials
from
call_resource_superset cres_superset,
call_resource_set2superset c2s,
call_resource_set cs,
node n,
node_type nt,
data_contributor dc
where ('||v_Ent_Agt_ID||') and
cres_superset.resource_superset_type_number in (1,2,5) and
cres_superset.call_resource_superset_id = c2s.call_resource_superset_id and
c2s.call_resource_set_id = cs.call_resource_set_id and
dc.uuid in (''060efdcc-a421-11d2-9327-00c04fa37ab5'',''4e53b197-960f-44ea-b1c4-2b5647ed85ab'',''6b456f75-ac8e-11d5-b066-00b0d07901f1'')
order by 1';
END IF;
OPEN cont_cv FOR sql_stmt;
LOOP
v_contributor := ' ';
FETCH cont_cv INTO cont_rec;
EXIT WHEN cont_cv%NOTFOUND;
-- process record
/* Check the availability of tables */
IF (cont_rec = 'ASPT' AND V_REQ_VIEW_TYPE = 'AGTAPP' ) THEN
v_table_name := (RTRIM('S_ASPT_ACD_AGENT_APPLIC_'||v_summary));
v_table_name := check_table(v_table_name);
END IF;
IF (cont_rec = 'AV' AND V_REQ_VIEW_TYPE = 'AGTAPP' ) THEN
v_table_name1 := (RTRIM('S_AV_ACD_AGT_APPLIC_SPLIT_'||v_summary));
v_table_name1 := check_table(v_table_name1);
END IF;
/* Based on contributor construct SELECT statement */
/* SELECT statement for Agent by application report with Aspect switch */
IF (cont_rec = 'ASPT' AND V_REQ_VIEW_TYPE = 'AGTAPP' AND v_table_name != 'NOTFOUND') THEN
v_contributor := 'SELECT
UTC_TIME_ID,
UTC_DATE_ID,
CALL_SEGMENT_TYPE_ID,
CALL_RESOURCE_SET_ID,
BUSINESS_APPLICATION_ID,
CALL_RESOURCE_ID,
CALL_DIRECTION_CODE,
LOCAL_DATE_ID,
LOCAL_TIME_ID,
a.NODE_ID NODE_ID,
NUMBER_CALLS,
TALK_TIME,
HOLD_TIME,
RING_TIME,
WRAPUP_TIME,
QUEUE_TIME,
MAX_WAIT,
WAITING_TIME,
CALL_WORK_TIME,
NUMBER_OF_TIMES_ON_HOLD,
WITHIN_SERVICE_LEVEL,
(HOLD_TIME + TALK_TIME + CALL_TYPE_TIME + SELECT_TIME)
IN_CALL_LEN,
(RING_TIME + QUEUE_TIME + HOLD_TIME + TALK_TIME + DIAL_TIME +
NETWORK_TIME) OUT_CALL_LEN,
(RING_TIME + HOLD_TIME + TALK_TIME + QUEUE_TIME) MSG_DURATION,
b.WT_WRAPUP,
b.WT_AVAIL
FROM S_ASPT_ACD_AGENT_APPLIC_'||v_summary||' a,
ASPECT_ACD_CONFIGURATION b
WHERE a.NODE_ID = b.NODE_ID';
/* Added to compensate for different contributors w/different summary interval tables */
/* only adds UNION ALL if >1 contributor table of same summary interval actually found */
IF v_common = ' ' THEN
v_common := v_common || v_contributor;
ELSE
v_common := v_common ||' UNION ALL '|| v_contributor;
END IF;
/* End add */
END IF;
/* SELECT statement for Agent by application report with Avaya switch */
IF (cont_rec = 'AV' AND V_REQ_VIEW_TYPE = 'AGTAPP' AND v_table_name1 != 'NOTFOUND') THEN
v_contributor := 'SELECT
UTC_TIME_ID,
UTC_DATE_ID,
CONTACT_SEGMENT_TYPE_ID CALL_SEGMENT_TYPE_ID,
CONTACT_RESOURCE_SET_ID CALL_RESOURCE_SET_ID,
SPLIT_BUSINESS_APPLICATION_ID BUSINESS_APPLICATION_ID,
CONTACT_RESOURCE_ID CALL_RESOURCE_ID,
CALL_DIRECTION_CODE CALL_DIRECTION_CODE,
LOCAL_DATE_ID,
LOCAL_TIME_ID,
NODE_ID,
NUMBER_CALLS,
TALK_TIME,
HOLD_TIME,
0 RING_TIME,
WRAPUP_TIME,
QUEUE_TIME,
MAX_WAIT,
WAITING_TIME,
CALL_WORK_TIME,
NUMBER_OF_TIMES_ON_HOLD,
WITHIN_SERVICE_LEVEL,
(HOLD_TIME + TALK_TIME) IN_CALL_LEN,
(QUEUE_TIME + HOLD_TIME + TALK_TIME) OUT_CALL_LEN,
(HOLD_TIME + TALK_TIME + QUEUE_TIME) MSG_DURATION,
'' '' WT_WRAPUP,
'' '' WT_AVAIL
FROM S_AV_ACD_AGT_APPLIC_SPLIT_'||v_summary;
/* Added to compensate for different contributors w/different summary interval tables */
/* only adds UNION ALL if >1 contributor table of same summary interval actually found */
IF v_common = ' ' THEN
v_common := v_common || v_contributor;
ELSE
v_common := v_common ||' UNION ALL '|| v_contributor;
END IF;
/* End add */
END IF;
/***DELETE TEMP_TABLE ;
INSERT INTO TEMP_TABLE values ( v_common ) ;
COMMIT ;
***/
IF v_cnt <= v_tot_cont THEN
v_view_name := v_view_name || cont_rec || '_';
END IF;
/* Deleted to prevent concatenation of UNION ALL without actually confirming */
/* presence of >1 contributor tables */
-- IF v_cnt < v_tot_cont THEN
-- v_common := v_common || ' UNION ALL ';
-- END IF;
/* End Delete */
v_cnt := v_cnt + 1;
END LOOP;
CLOSE cont_cv;
/***DELETE TEMP_TABLE ;
INSERT INTO TEMP_TABLE values ( v_common ) ;
COMMIT ;***/
IF V_REQ_VIEW_TYPE = 'AGTAPP' THEN
v_view_name := 'V_'||v_view_name||'AGT_APP_'||v_summary ;
END IF;
/* Check whether view already exists or not */
v_view_name := (LTRIM(RTRIM(v_view_name))) ;
view_name := check_view(v_view_name) ;
IF view_name != 'NOTFOUND' THEN
e_error_code := 0;
po_view_name := v_view_name;
RAISE e_view_exists ;
END IF;
IF (view_name = 'NOTFOUND' AND v_common != ' ') THEN
create_view := 'CREATE OR REPLACE VIEW '||v_view_name||' AS '||v_common;
***execute immediate create_view;
e_error_code := 0 ;
ELSE
e_error_code := 32;
RAISE e_some_error ;
END IF;
IF e_error_code = 0 THEN
po_view_name := v_view_name ;
END IF;
po_error_code := e_error_code;
/***DELETE TEMP_TABLE ;
INSERT INTO TEMP_TABLE values ( v_common ) ;
COMMIT ;
***/
EXCEPTION
when e_view_exists then
select e_error_code into po_error_code from dual;
when e_cont_error then
select e_error_code into po_error_code from dual;
when e_some_error then
select e_error_code into po_error_code from dual;
when others then
po_error_code := 33;
END CREATE_AGT_APP_DYNAMIC_VIEW;
|
|
|
|
| Re: creating views in PL/SQL [message #8038 is a reply to message #8031] |
Tue, 22 July 2003 21:14  |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
|
Your exception handling is obscuring the error. Try commenting out the exception section, then running it, and see if you get a more meaningul error message.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 08:53:36 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
