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 Go to next message
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 #344027 is a reply to message #344025] Thu, 28 August 2008 11:26 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

> This one throws a weird error
Error? What error? i don't see any error!
Re: Stored Procedure help [message #344041 is a reply to message #344025] Thu, 28 August 2008 12:18 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
It amazes me how many time people say "I am getting an error in this code..." and then fail to actually post the error message.

Also, please use the code formatter on OraFAQ to format code before you post it.

Kevin
Re: Stored Procedure help [message #344144 is a reply to message #344025] Thu, 28 August 2008 20:47 Go to previous messageGo to next message
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 #344145 is a reply to message #344025] Thu, 28 August 2008 20:51 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
You choose to not follow posting guidelines;
therefore I choose to not provide any answers until after you post according to guidelines.

You're On Your Own (YOYO)!
Re: Stored Procedure help [message #344152 is a reply to message #344025] Thu, 28 August 2008 22:33 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #344314 is a reply to message #344152] Fri, 29 August 2008 06:37 Go to previous messageGo to next message
waitnsee
Messages: 3
Registered: August 2008
Junior Member
hello Flyboy,

I have added the code in my original posting. Please refer to that and let me know what I am missing.

Thank you,
R
Re: Stored Procedure help [message #344318 is a reply to message #344314] Fri, 29 August 2008 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: Stored Procedure help [message #344322 is a reply to message #344314] Fri, 29 August 2008 06:58 Go to previous message
flyboy
Messages: 1832
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
Previous Topic: date display
Next Topic: Defining and initializing a VARRAY in PL/SQL for insert into a table
Goto Forum:
  


Current Time: Wed Dec 07 05:05:03 CST 2016

Total time taken to generate the page: 0.07710 seconds