Help: Unable to create procedure [message #272686] |
Sat, 06 October 2007 03:53  |
Debugger
Messages: 4 Registered: October 2007 Location: Karachi
|
Junior Member |
|
|
Dear Gurus!
I am new to oracle. I had crated a procedure in SQL Server and then Converted that procedure from SQL SERVER to ORACLE.
Please see the below converted code. I'll appriciate early response on this.
Thanks in advance.
Waseem.
SQL> edit
Wrote file afiedt.buf
1 /* @(#)C:\AdventNet\SwisSQL\SQLServerToOracle3.2/Create Proc FetchSMS_migrate_PL.sql - Generat
2 CREATE OR REPLACE PROCEDURE FetchAlerts
3 (
4 RCT1 IN OUT GLOBALPKG.RCT1
5 )
6 AS
7 MSGID NUMBER;
8 MSGTYPE VARCHAR2(50);
9 MOBILENO VARCHAR2(20);
10 INSDATE DATE;
11 REFNO VARCHAR2(19);
12 AMOUNT NUMBER;
13 PARAM1 VARCHAR2(500);
14 PARAM2 VARCHAR2(500);
15 PARAM3 VARCHAR2(500);
16 PARAM4 VARCHAR2(500);
17 SMS_MSG VARCHAR2(500);
18 REFNO2 VARCHAR2(19);
19 AMOUNT2 VARCHAR2(100);
20 ASSIGNMENTVARIABLE0 INTEGER := 0;
21 CURSOR UnSent
22 IS
23 SELECT
24 MSGID,
25 MSGTYPE,
26 MOBILENO,
27 INSDATE,
28 REFNO,
29 AMOUNT,
30 PARAM1,
31 PARAM2,
32 PARAM3
33 FROM SMS_QUE
34 WHERE DELVFLAG = 'N'
35 ;
36 BEGIN
37 OPEN UnSent;
38 FETCH UnSent INTO MSGID, MSGTYPE, MOBILENO, INSDATE, REFNO, AMOUNT, PARAM1, PARAM2, PARAM3;
39 REFNO2 := REFNO;
40 AMOUNT2 := AMOUNT;
41 << LABEL3 >>
42 WHILE UnSent%FOUND
43 LOOP
44 BEGIN
45 -- 1. BPS1
46 BEGIN
47 SELECT DISTINCT OnFlag INTO ASSIGNMENTVARIABLE0
48 FROM SMSOnOff
49 WHERE AlertId = 1;
50 EXCEPTION
51 WHEN NO_DATA_FOUND THEN
52 ASSIGNMENTVARIABLE0 := NULL;
53 WHEN TOO_MANY_ROWS THEN
54 NULL;
55 END;
56 IF MSGTYPE = 'BPS1' AND(( ASSIGNMENTVARIABLE0) = 'Y') THEN
57 BEGIN
58 SMS_MSG := 'Rs. ' || LTRIM(RTRIM(SUBSTR(LPAD(TO_CHAR(ROUND(AMOUNT)), 10), 1, 10))) || ' pr
59 ' || LTRIM(REFNO2) || ' ';
60 UPDATE SMS_QUE
61 SET PARAM4 = FetchSMS.SMS_MSG
62 WHERE MSGID = FetchSMS.MSGID;
63 END;
64 END IF;
65 FETCH UnSent INTO MSGID, MSGTYPE, MOBILENO, INSDATE, REFNO, AMOUNT, PARAM1, PARAM2, PARAM3;
66 END;
67 END LOOP;
68 CLOSE UnSent;
69 OPEN RCT1 FOR
70 SELECT
71 PARAM4 AS MSG,
72 MOBILENO AS MOBILE_NO
73 FROM sms_que
74 WHERE DELVFLAG = 'N'
75 AND PARAM4 <> 'NULL';
76 UPDATE SMS_QUE
77 SET DELVFLAG = 'Y',
78 PARAM4 = 'NULL'
79 WHERE DELVFLAG = 'N'
80 AND PARAM4 <> 'NULL';
81 RETURN;
82* END;
SQL>
SQL> /
Warning: Procedure created with compilation errors.
SQL> execute fetchalerts;
BEGIN fetchalerts; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SCOTT.FETCHALERTS is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
[Updated on: Mon, 08 October 2007 08:04] by Moderator Report message to a moderator
|
|
|
|
Re: Plz Help: Unable to create procedure [message #272693 is a reply to message #272686] |
Sat, 06 October 2007 04:22   |
Debugger
Messages: 4 Registered: October 2007 Location: Karachi
|
Junior Member |
|
|
thnx michel.
Do i need to create something? (All the required tables exist).
SQL> show errors;
Errors for PROCEDURE FETCHALERTS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
3/39 PLS-00201: identifier 'GLOBALPKG.RCT1' must be declared
|
|
|
Re: Plz Help: Unable to create procedure [message #272695 is a reply to message #272693] |
Sat, 06 October 2007 04:26   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Yes, you'll need to declare the 'GLOBALPKG.RCT1' as its type is unknown to the procedure (sounds as if it was a type declared in a package).
[EDIT]
Or, you might try with Oracle native datatype, for example
2 CREATE OR REPLACE PROCEDURE FetchAlerts (RCT1 IN OUT NUMBER)) to avoid creating a package (if possible, just to see whether the procedure compiles correctly or not).
[Updated on: Sat, 06 October 2007 04:28] Report message to a moderator
|
|
|
Re: Plz Help: Unable to create procedure [message #272700 is a reply to message #272686] |
Sat, 06 October 2007 05:04   |
Debugger
Messages: 4 Registered: October 2007 Location: Karachi
|
Junior Member |
|
|
Yes, you are right. I had to create a package.
CREATE OR REPLACE PACKAGE GLOBALPKG
AS
TYPE RCT1 IS REF CURSOR;
TRANCOUNT INTEGER := 0;
IDENTITY INTEGER;
END;
/
Now if i try to run FetchAlerts procedure, i am getting error as under:
SQL> execute FetchAlerts(globalpkg);
BEGIN FetchAlerts(globalpkg); END;
*
ERROR at line 1:
ORA-06550: line 1, column 16:
PLS-00226: package 'GLOBALPKG' used as variable reference
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Seems like i am invoking it incorrect way, whats right way to do it?
Thanks in advance,
Deb!
|
|
|
Re: Plz Help: Unable to create procedure [message #272706 is a reply to message #272700] |
Sat, 06 October 2007 07:27  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
You have to pass something of good type to your procedure:
exec FetchAlerts(<a variable of type GLOBALPKG.RCT1>)
Note that this can't be directly done in SQL*Plus as you can't declare of variable of this type in SQL*Plus.
Regards
Michel
|
|
|