Help::: Convertng Sql(T-Sql) Stored Proc To Oracle(Pl-Sql)

[mtkr]

[quote name='BlueBerry' timestamp='1355206011' post='1302930017']
Np mama.. repu morning konchem free vuntaa... emina doubts vunte...taadu lepu... ledha PM chei... work out chedhaam
[/quote]

ok mama.....
thnx

[mtkr]

e block ki tool tho convert chesthe

[CODE]
CREATE PROCEDURE sp_newprocedure
@Channel Varchar(40),
@StartDate datetime
As
BEGIN
select *
from (
select
'Application Not Received from Siebel' as Problem,
aa.Channel,
aa.HIC,
se.medicare_no,
aa.ReceiveDate,
aa.LastName,
se.last_name as SMM_LastName,
aa.FirstName,
se.first_name as SMM_FirstName,
aa.Address,
aa.City,
aa.State,
aa.Zip,
aa.Phone,
aa.SSN,
aa.DOB,
se.date_of_birth as SMM_DOB,
aa.EffectiveDate,
se.application_id,
se.subscriber_id,
se.hcc_id,
se.cip_id,
SUBSTRING(aa.filename,69,20) as filename,
aa.confirmation_number,
'' AS DCN,
aa.Application_Source,
'MA' as product_type
from
appaudit aa
left join vw_stage_enrollment_cms se on aa.confirmation_number = se.confirmation_number
where se.confirmation_number is null
and aa.channel = 'CMS'
and aa.channel IN (SELECT * FROM dbo.fn_SplitList2(@Channel,','))
and aa.Invalid = 'N'
and( (@StartDate is null) or (aa.ReceiveDateTime >= @StartDate))
and aa.HIC not in (select HIC from AppAudit aa1 inner join vw_stage_enrollment_cms se1 on aa1.HIC = se1.medicare_no where se1.medicare_no is null and aa1.channel='CMS')
)a
end
[/CODE]



idi vochindiiii

n idi oracle ki create chesthunte error ostundhiii

[CODE]
CREATE OR REPLACE PROCEDURE sp_newprocedure
(
v_channel IN VARCHAR2 ;
v_startdate IN DATE ;
cv_1 OUT SYS_REFCURSOR
)
AS

BEGIN
OPEN cv_1 FOR
SELECT *
FROM ( SELECT aa.Channel ,
aa.HIC ,
se.medicare_no ,
aa.ReceiveDate ,
aa.LastName ,
se.last_name SMM_LastName ,
aa.FirstName ,
se.first_name SMM_FirstName ,
aa.ADDRESS ,
aa.City ,
aa.STATE ,
aa.Zip ,
aa.Phone ,
aa.SSN ,
aa.DOB ,
se.date_of_birth SMM_DOB ,
aa.EffectiveDate ,
se.application_id ,
se.subscriber_id ,
se.hcc_id ,
se.cip_id ,
SUBSTR(aa.FILENAME, 69, 20) FILENAME ,
aa.confirmation_number ,
' ' DCN ,
aa.Application_Source ,
'MA' product_type
FROM appaudit aa
LEFT JOIN vw_stage_enrollment_cms se
ON aa.confirmation_number = se.confirmation_number
WHERE se.confirmation_number IS NULL
AND aa.channel = 'CMS'
AND aa.channel IN ( SELECT *
FROM TABLE(fn_SplitList2(v_Channel, ',')) )

AND aa.Invalid = 'N'
AND ( ( v_StartDate IS NULL )
OR ( aa.ReceiveDateTime >= v_StartDate ) )
AND aa.HIC NOT IN ( SELECT HIC
FROM AppAudit aa1
JOIN vw_stage_enrollment_cms se1
ON aa1.HIC = se1.medicare_no
WHERE se1.medicare_no IS NULL
AND aa1.channel = 'CMS' )
) a ;
END;
[/CODE]
ERROR:::

[1]: ORA-24344: success with compilation error
[1]: 3/24 PLS-00103: Encountered the symbol ";" when expecting one of the following:
[1]: := . ) , @ % default character



ny help!!!!!

[iTeachSAP]

mama idhi try cheyi ... it should work ...


/* Formatted on 12/11/2012 10:30:09 (QP5 v5.149.1003.31008) */
CREATE OR REPLACE PROCEDURE sp_newprocedure (
v_channel IN VARCHAR2,
v_startdate IN DATE,
cv_1 OUT SYS_REFCURSOR)
AS
BEGIN
OPEN cv_1 FOR
SELECT *
FROM (SELECT aa.Channel,
aa.HIC,
se.medicare_no,
aa.ReceiveDate,
aa.LastName,
se.last_name SMM_LastName,
aa.FirstName,
se.first_name SMM_FirstName,
aa.ADDRESS,
aa.City,
aa.STATE,
aa.Zip,
aa.Phone,
aa.SSN,
aa.DOB,
se.date_of_birth SMM_DOB,
aa.EffectiveDate,
se.application_id,
se.subscriber_id,
se.hcc_id,
se.cip_id,
SUBSTR (aa.FILENAME, 69, 20) FILENAME,
aa.confirmation_number,
' ' DCN,
aa.Application_Source,
'MA' product_type
FROM appaudit aa
LEFT JOIN
vw_stage_enrollment_cms se
ON aa.confirmation_number = se.confirmation_number
WHERE se.confirmation_number IS NULL AND aa.channel = 'CMS'
AND aa.channel IN
(SELECT *
FROM TABLE (fn_SplitList2 (v_Channel, ',')))
AND aa.Invalid = 'N'
AND ( (v_StartDate IS NULL)
OR (aa.ReceiveDateTime >= v_StartDate))
AND aa.HIC NOT IN
(SELECT HIC
FROM AppAudit aa1
JOIN
vw_stage_enrollment_cms se1
ON aa1.HIC = se1.medicare_no
WHERE se1.medicare_no IS NULL
AND aa1.channel = 'CMS')) a;
END;

[mtkr]

SAP mama...

rendu procedures ki any diff???

is it , instead of ;


n oka mistake aithe chesa... procedure lo table n view two times join avutunnai....
nen oracle lo tbl name vere crte chesa n proc lo only oke chota marchaa...ippudu chusukunna adi..


n nuv ichina proc crtd successfully... ippudu tst data esi chk cheyyalii..

thnx mama...

[iTeachSAP]

[quote name='mtkr' timestamp='1355241038' post='1302930880']
SAP mama...

rendu procedures ki any diff???

is it , instead of ;


n oka mistake aithe chesa... procedure lo table n view two times join avutunnai....
nen oracle lo tbl name vere crte chesa n proc lo only oke chota marchaa...ippudu chusukunna adi..


n nuv ichina proc crtd successfully... ippudu tst data esi chk cheyyalii..

thnx mama...
[/quote]


nuvvu itchina issue.. syntax error ani .. so initial gaa nenu syntax error clear chesa.. yes oracle does not support semi colons between the parameters that are declared at the beginning.

ika table names sangathi , I cannot predict the error as i dont have table structures in my database .. so nenu only formatting matrame chesa...

Let me know if you need any more inputs...

[mtkr]

help on executin ....


[font="Courier New"][size="2"][color="#ff0000"][font="Courier New"][size="2"][color="#ff0000"][font="Courier New"][size="2"][color="#ff0000"]EXECUTE[/color][/size][/font][/color][/size][/font][/color][/size][/font][font="Courier New"][size="2"][font="Courier New"][size="2"][color="#000000"] sp_newprocedure[/color][/size][/font][/size][/font][font="Courier New"][size="2"][color="#ff0000"][font="Courier New"][size="2"][color="#ff0000"][font="Courier New"][size="2"][color="#ff0000"]([/color][/size][/font][/color][/size][/font][/color][/size][/font][font="Courier New"][size="2"][color="#008080"][font="Courier New"][size="2"][color="#008080"][font="Courier New"][size="2"][color="#008080"]'acs,cms,'[/color][/size][/font][/color][/size][/font][/color][/size][/font][font="Courier New"][size="2"][color="#ff0000"][font="Courier New"][size="2"][color="#ff0000"][font="Courier New"][size="2"][color="#ff0000"],[/color][/size][/font][/color][/size][/font][/color][/size][/font][font="Courier New"][size="2"][color="#008080"][font="Courier New"][size="2"][color="#008080"][font="Courier New"][size="2"][color="#008080"]'12-10-2010'[/color][/size][/font][/color][/size][/font][/color][/size][/font][font="Courier New"][size="2"][color="#ff0000"][font="Courier New"][size="2"][color="#ff0000"][font="Courier New"][size="2"][color="#ff0000"]);[/color][/size][/font][/color][/size][/font][/color][/size][/font]


ila chesthe error vastundhiiii...

[font="Courier New"][size="2"][color="#ff0000"][1]: ORA-06550: line 1, column 7:
[1]: PLS-00306: wrong number or types of arguments in call to 'SP_NEWPROCEDURE'
[2]: ORA-06550: line 1, column 7:
[2]: PL/SQL: Statement ignored[/color][/size][/font]

[chelsea]

Help

[iTeachSAP]

[quote name='mtkr' timestamp='1355256272' post='1302933386']
help on executin ....


[font=Courier New][size=2][color=#ff0000][font=Courier New][size=2][color=#ff0000][font=Courier New][size=2][color=#ff0000]EXECUTE[/color][/size][/font][/color][/size][/font][/color][/size][/font][font=Courier New][size=2][font=Courier New][size=2][color=#000000] sp_newprocedure[/color][/size][/font][/size][/font][font=Courier New][size=2][color=#ff0000][font=Courier New][size=2][color=#ff0000][font=Courier New][size=2][color=#ff0000]([/color][/size][/font][/color][/size][/font][/color][/size][/font][font=Courier New][size=2][color=#008080][font=Courier New][size=2][color=#008080][font=Courier New][size=2][color=#008080]'acs,cms,'[/color][/size][/font][/color][/size][/font][/color][/size][/font][font=Courier New][size=2][color=#ff0000][font=Courier New][size=2][color=#ff0000][font=Courier New][size=2][color=#ff0000],[/color][/size][/font][/color][/size][/font][/color][/size][/font][font=Courier New][size=2][color=#008080][font=Courier New][size=2][color=#008080][font=Courier New][size=2][color=#008080]'12-10-2010'[/color][/size][/font][/color][/size][/font][/color][/size][/font][font=Courier New][size=2][color=#ff0000][font=Courier New][size=2][color=#ff0000][font=Courier New][size=2][color=#ff0000]);[/color][/size][/font][/color][/size][/font][/color][/size][/font]


ila chesthe error vastundhiiii...

[font=Courier New][size=2][color=#ff0000][1]: ORA-06550: line 1, column 7:
[1]: PLS-00306: wrong number or types of arguments in call to 'SP_NEWPROCEDURE'
[2]: ORA-06550: line 1, column 7:
[2]: PL/SQL: Statement ignored[/color][/size][/font]
[/quote]


chustha mama.. give me some time..

[iTeachSAP]

[font=Courier New][size=2][color=#ff0000][font=Courier New][size=2][color=#ff0000][font=Courier New][size=2][color=#ff0000]Idhi try cheyi ...

[size=6][font=comic sans ms,cursive]EXECUTE[/font][/size][/color][/size][/font][/color][/size][/font][/color][/size][/font][size=6][font=comic sans ms,cursive][color=#000000] sp_newprocedure[/color][color=#FF0000][color=#FF0000][color=#FF0000]([/color][/color][/color][color=#008080][color=#008080][color=#008080]'acs','cms,'[/color][/color][/color][color=#FF0000][color=#FF0000][color=#FF0000],[/color][/color][/color][color=#008080][color=#008080][color=#008080]'12-10-2010'[/color][/color][/color][color=#FF0000][color=#FF0000][color=#FF0000]);[/color][/color][/color][/font][/size]

[mtkr]

[CODE]
[1]: ORA-06550: line 1, column 7:
[1]: PLS-00306: wrong number or types of arguments in call to 'SP_NEWPROCEDURE'
[2]: ORA-06550: line 1, column 36:
[2]: PLS-00363: expression '12-10-2010' cannot be used as an assignment target
[3]: ORA-06550: line 1, column 7:
[3]: PL/SQL: Statement ignored
[/CODE]


error vastundhii...
acs, cms separate ga ivvakudadhu....
adi okate string b/c sp middle lo oka funcion ni cal chestundhii... adi ee sting ni individual rows ga chesthadiii.....



ggle lo search chesthe either IN or OUT parameter we need to supply while execution ani undhii...
so OUt ni ela ivvali ani chustunna!!!

[KOTC]

[KOTC]

[iTeachSAP]

[quote name='mtkr' timestamp='1355259363' post='1302933828']
[CODE]
[1]: ORA-06550: line 1, column 7:
[1]: PLS-00306: wrong number or types of arguments in call to 'SP_NEWPROCEDURE'
[2]: ORA-06550: line 1, column 36:
[2]: PLS-00363: expression '12-10-2010' cannot be used as an assignment target
[3]: ORA-06550: line 1, column 7:
[3]: PL/SQL: Statement ignored
[/CODE]


error vastundhii...
acs, cms separate ga ivvakudadhu....
adi okate string b/c sp middle lo oka funcion ni cal chestundhii... adi ee sting ni individual rows ga chesthadiii.....



ggle lo search chesthe either IN or OUT parameter we need to supply while execution ani undhii...
so OUt ni ela ivvali ani chustunna!!!
[/quote]


Mama.. ni stored proc lo three parameters petti .. only 2 pampisthe ela tesukuntundhi bayya ? third parameter necessity enti ?

[mtkr]

[quote name='iTeachSAP' timestamp='1355261715' post='1302934357']



Mama.. ni stored proc lo three parameters petti .. only 2 pampisthe ela tesukuntundhi bayya ? third parameter necessity enti ?
[/quote]

3 ante.... 2 r input n 3rd di cursor kosam declare chesam ga....
so aaaa cursor kosam declare chesindhi ela pampistham n em ani pampinchaali..???

[mtkr]

EDITED:::



no view