Home » SQL & PL/SQL » SQL & PL/SQL » Help: Unable to create procedure
icon7.gif  Help: Unable to create procedure [message #272686] Sat, 06 October 2007 03:53 Go to next message
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 #272689 is a reply to message #272686] Sat, 06 October 2007 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

"show errors" after creation gives you what errors are in the procedure.

Regards
Michel
Re: Plz Help: Unable to create procedure [message #272693 is a reply to message #272686] Sat, 06 October 2007 04:22 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20894
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Not a single-group function
Next Topic: Updating two tables in single Update Statement
Goto Forum:
  


Current Time: Mon Dec 05 10:49:48 CST 2016

Total time taken to generate the page: 0.10684 seconds