Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure help (Oracle 10G, Windows XP)
| Stored Procedure help [message #344025] |
Thu, 28 August 2008 11:19  |
waitnsee
Messages: 3
Registered: August 2008
|
Junior Member |
|
|
hi,
I am trying to execute the following procedure from TOAD 9.6.1 version. This one throws a weird error which I'm not understanding. Can you please help me execute this procedure successfully and verify the results.
PROCEDURE get_unv( p_security_id IN VARCHAR2,
p_ind IN VARCHAR2,
p_h_data OUT SYS_REFCURSOR,
p_session_id OUT NUMBER
)
IS
l_key NUMBER;
l_days NUMBER;
l_assets number;
l_rsch_st_dt DATE;
l_rsch_ed_dt DATE;
l_dob VARCHAR2(32767);
l_selt_gc VARCHAR2(32767);
l_gc_st_dt DATE;
l_gc_ed_dt DATE;
l_ch_data VARCHAR2(32767);
l_nh_data VARCHAR2(32767);
l_pos NUMBER;
l_1 char(2);
l_2 char(2);
l_3 char(2);
l_4 char(2);
l_5 char(2);
l_6 char(2);
l_7 char(2);
l_8 char(2);
l_9 char(2);
l_unv_res VARCHAR2(32767);
l_dob_l VARCHAR2(32767);
l_dob_h VARCHAR2(32767);
l_selt_dbl VARCHAR2(1000);
l_selt_dbh VARCHAR2(1000);
l_where VARCHAR2(3000);
l_session_id NUMBER;
l_ch_data1 VARCHAR2(32767);
BEGIN
EXECUTE IMMEDIATE 'alter session set sort_area_size =512000000';
--This is to reterieve only the codes which are live in T1IR and IRC currently (C-H-A-M-P)
l_key := 8;
l_days :=0;
l_assets := 0;
l_pos := 0;
l_1 := 'BK';
l_2 := 'FD';
l_3 := 'HF';
l_4 := 'IA';
l_5 := 'IC';
l_6 := 'IH';
l_7 := 'PF';
l_8 := 'MF';
l_9 := 'EF';
--Research Data
HOLDERS_NEW.Get_Working_Days(sysdate,90,l_days);
l_rsch_ed_dt := trunc(sysdate);
l_rsch_st_dt := trunc(sysdate-l_days);
--Filling Data
l_days :=0;
HOLDERS_NEW.Get_Working_Days(sysdate,180,l_days);
l_gc_ed_dt := trunc(sysdate);
l_gc_st_dt := trunc(sysdate-l_days);
SELECT OA_Session_No.nextVAL
INTO l_session_id
FROM DUAL;
get_ch(l_dob,l_selt_gc);
l_ch_data := ' SELECT '''||l_session_id||''',b.id,''CH'' hld FROM ( '||l_dob||' UNION '||l_selt_gc||' ) b ';
DELETE FROM oa_user_ticker_unv WHERE session_id = l_session_id;
EXECUTE IMMEDIATE 'insert into oa_user_ticker_unv'||l_ch_data
USING p_security_id,l_rsch_st_dt,l_rsch_ed_dt,l_pos,l_key,
p_security_id,l_rsch_st_dt,l_rsch_ed_dt,l_pos,l_key,
p_security_id,l_key,l_pos,p_security_id,l_key,l_pos,
p_security_id,l_key,l_pos,p_security_id,l_key,l_pos,
l_gc_st_dt,l_gc_ed_dt;
l_selt_dbl := ' SELECT a.owner_id, a.rpt_dt, ROUND(NVL(a.share_pos,0)) share_pos FROM ';
l_selt_dbh := ' SELECT a.owner_id, a.rpt_dt, ROUND(a.share_pos) share_pos FROM ';
l_where := ' WHERE a.security_id = :sec and exists ( select 1 from oa_user_ticker_unv b where b.session_id = :session_id '||
' and b.owner_id = a.owner_id ) ';
--RESEARCH DATA
l_dob_l := l_selt_dbl||' dob_latest a '||l_where||
' UNION '||
l_selt_dbl||' dob_latest_mf a '||l_where;
l_dob_h := l_selt_dbh||' ( '||l_selt_dbh||' dob_historical a '||l_where||' UNION '||l_selt_dbh||' dob_historical_mf a '||l_where||') a ,'||
' (SELECT owner_id,max(rpt_dt) max_dt'||
' FROM ('|| l_selt_dbh||' dob_historical a '||l_where||' UNION '||l_selt_dbh||' dob_historical_mf a '||l_where||') a'||
' GROUP BY owner_id ) b WHERE a.owner_id = b.owner_id and a.rpt_dt = b.max_dt ';
l_unv_res := ' SELECT a.owner_id,a.share_pos curr_pos,( case when b.share_pos is not null then (a.share_pos - b.share_pos) '||
' else null end) chng FROM ('||l_dob_l||') a,('||l_dob_h||') b WHERE a.owner_id = b.owner_id(+) ';
l_ch_data := ' SELECT c.owner_id id,c.holder_type hld,(b.curr_pos) currpos,(b.chng) chng,'||
' a.owner_turnover turn,a.type_cd ty,e.pwlist_id pwlid'||
' FROM instetc a,('||l_unv_res||' ) b,oa_user_ticker_unv c,firm_sub_type st, firm_type mt,mf_flow_master d,oa_indices_map e'||
' WHERE c.session_id = :session_id and c.owner_id = a.owner_id and c.owner_id = b.owner_id(+) '||
' and a.owner_assets_am > :assets AND '||
' a.institution_type_cd = st.firm_sub_type_cd AND st.firm_type_id = mt.firm_type_id AND a.institution_type_cd IN '||
' (:l_1,:l_2,:l_3,:l_4,:l_5,:l_6,:l_7,:l_8,:l_9) AND c.owner_id = d.carson_id(+) AND d.index_1 = e.benchmark_id(+) ' ;
IF p_ind = 'B'
THEN
l_nh_data := ' SELECT '''||l_session_id||''',b.id,''NH'' hld FROM ( SELECT a.owner_id id from cms_key a '||
' where key_id = :key and not exists ( select 1 from oa_user_ticker_unv b where b.session_id = :session_id and '||
' b.owner_id = a.owner_id ) ) b ';
EXECUTE IMMEDIATE ' insert into oa_user_ticker_unv '||l_nh_data USING l_key,l_session_id;
END IF;
p_session_id := l_session_id;
l_ch_data1 := ' Delete from oa_user_ticker_unv where session_id = :session_id and owner_id not in ( SELECT c.owner_id id '||
' FROM instetc a,('||l_unv_res||' ) b,oa_user_ticker_unv c,firm_sub_type st, firm_type mt,mf_flow_master d,oa_indices_map e'||
' WHERE c.session_id = :session_id and c.owner_id = a.owner_id and c.owner_id = b.owner_id(+) '||
' and a.owner_assets_am > :assets AND '||
' a.institution_type_cd = st.firm_sub_type_cd AND st.firm_type_id = mt.firm_type_id AND a.institution_type_cd IN '||
' (:l_1,:l_2,:l_3,:l_4,:l_5,:l_6,:l_7,:l_8,:l_9) AND c.owner_id = d.carson_id(+) AND d.index_1 = e.benchmark_id(+) ) ' ;
EXECUTE IMMEDIATE l_ch_data1
USING l_session_id,p_security_id,l_session_id,p_security_id,l_session_id,p_security_id,l_session_id,p_security_id,l_session_id,p_security_i d,
l_session_id,p_security_id,l_session_id,l_session_id,l_assets,l_1,l_2,l_3,l_4,l_5,l_6,l_7,l_8,l_9;
commit;
OPEN p_h_data FOR l_ch_data
USING p_security_id,l_session_id,p_security_id,l_session_id,p_security_id,l_session_id,p_security_id,l_session_id,p_security_id,l_session_i d,
p_security_id,l_session_id,l_session_id,l_assets,l_1,l_2,l_3,l_4,l_5,l_6,l_7,l_8,l_9;
END get_unv;
Thanks,
R
|
|
|
|
|
|
|
|
| Re: Stored Procedure help [message #344144 is a reply to message #344025] |
Thu, 28 August 2008 20:47  |
waitnsee
Messages: 3
Registered: August 2008
|
Junior Member |
|
|
here is the error I am getting while executing the above stored procedure. Please help me with the next steps.
ORA-06550: line 5, column 16:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 5, column 16:
PL/SQL: Item ignored
Thanks,
R
|
|
|
|
|
|
| Re: Stored Procedure help [message #344152 is a reply to message #344025] |
Thu, 28 August 2008 22:33 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
> I am trying to execute the following procedure from TOAD 9.6.1 version.
Do you not think that it would be good to show what code are you exactly trying to execute?
My guess is, you are passing wrong parameter type; either not a SYS_REFCURSOR or not a variable in an OUT parameter(s).
|
|
|
|
|
|
|
|
| Re: Stored Procedure help [message #344322 is a reply to message #344314] |
Fri, 29 August 2008 06:58 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
> I have added the code in my original posting. Please refer to that and let me know what I am missing.
Your original posting contains the code of the procedure.
I do not see there any code which calls the procedure.
Wait. You mention TOAD. Is it some action from its menu? I do not know TOAD, but you may consult its help (F1 button) first.
Anyway, you have to pass the procedure parameters somehow. I have no idea how you entered them and what you expect as a result. As it has two OUT parameters, I suppose you would at least want to check its content. But, here I totally guess,so it is time to stop.
You may find very useful info in the documentation, found e.g. online on http://tahiti.oracle.com/.
PL/SQL User's Guide and Reference
Chapter 8 Using PL/SQL Subprograms
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 18:05:28 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
