Home » SQL & PL/SQL » SQL & PL/SQL » Getting Expecting External Language Error When Compiling Package (PL/SQL)
Getting Expecting External Language Error When Compiling Package [message #596902] Fri, 27 September 2013 06:30 Go to next message
jmwdba
Messages: 1
Registered: September 2013
Location: London
Junior Member
Hi,

It's been some time since I've written any PL/SQL, can anyone help with the below, I'm getting Found 'CURSOR' Expecting: External Language for line 76 when I try to compile? :

1 create or replace PACKAGE pa_user_maint
2 AS
3
4
5 -- ------------------------------------------------------------------------------------
6 -- Exceptions
7 -- ------------------------------------------------------------------------------------
8
9 -- User Exists
10
11 ex_existingUser exception;
12 PRAGMA EXCEPTION_INIT (ex_existingUser, -01920);
13
14 --User Does Not Exist
15
16 ex_invalidUser exception;
17 PRAGMA EXCEPTION_INIT (ex_invalidUser, -01918);
18
19 -- User Owns Objects
20
21 ex_dropUserObj exception;
22 PRAGMA EXCEPTION_INIT (ex_dropUserObj, -01922);
23
24 -- Role Does Not Exist
25
26 ex_invalidRole exception;
27 PRAGMA EXCEPTION_INIT (ex_invalidRole, -01919);
28
29 -- Profile Does Not Exist
30
31 ex_invalidProfile exception;
32 -- PRAGMA EXCEPTION_INIT (ex_invalidProfile, -02380);
33
34 -- User Connected
35
36 ex_connectedUser exception;
37 PRAGMA EXCEPTION_INIT (ex_connectedUser, -01940);
38
39 -- Role Not Granted
40
41 ex_rolenotGranted exception;
42 PRAGMA EXCEPTION_INIT (ex_rolenotGranted, -01951);
43
44 -- Default Tablespace Does Not Exist
45
46 ex_invaliddefTblspce exception;
47 PRAGMA EXCEPTION_INIT (ex_invaliddeftblspce, -00959);
48
49 -- Temp Tablespace Does Not Exist
50
51 ex_invalidtmpTblspce exception;
52 PRAGMA EXCEPTION_INIT (ex_invalidtmptblspce, -00959);
53
54 subtype usernameType IS varchar2(30);
55 subtype databaseType IS varchar2(10);
56 subtype actionType IS varchar2(20);
57 subtype profileType IS varchar2(20);
58 subtype deftblspceType IS varchar2(30);
59 subtype tmptblspceType IS varchar2(30);
60 subtype passwordType IS varchar2(20);
61 subtype roleType IS varchar2(30);
62 subtype privType IS varchar2(30);
63 subtype cloneType IS varchar2(30);
64 subtype requesterType IS varchar2(30);
65 subtype sourceType IS varchar2(20);
66
67 -- -----------------------------------------------------------------------------------
68-- Functions
69-- -----------------------------------------------------------------------------------
70
71-- Function to Create Random 10 character Password
72 FUNCTION func_create_password
73 (password_string VARCHAR2(10))
74 RETURN VARCHAR2
75 IS
76 CURSOR cur_create_password IS
77 SELECT SUBSTR(o.strings,SUBSTR(stamp,1,1),1) ||
78 SUBSTR(stamp,2,1) ||
79 LOWER(SUBSTR(o.strings,SUBSTR(stamp,3,1),1)) ||
80 SUBSTR(o.strings,SUBSTR(stamp,4,1),1) ||
81 SUBSTR(stamp,5,1) ||
82 LOWER(SUBSTR(o.strings,SUBSTR(stamp,6,1),1)) ||
83 SUBSTR(stamp,7,1) ||
84 SUBSTR(o.strings,SUBSTR(stamp,8,1),1) ||
85 SUBSTR(stamp,9,1) ||
86 SUBSTR(o.strings,SUBSTR(stamp,10,1),1)
87 FROM (SELECT 'GIKMOQSUWX' strings,
88 TO_CHAR(SYSDATE,'MISSHH24DDMM') stamp
89 FROM dual o);
90 password_string VARCHAR2(10);
91 begin
92 open cur_create_password;
93 fetch cur_get_password into password_string;
94 close cur_get_password;
95
96 END func_create_password;
Re: Getting Expecting External Language Error When Compiling Package [message #596905 is a reply to message #596902] Fri, 27 September 2013 06:35 Go to previous message
ThomasG
Messages: 3087
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The function itself must be in the package body, not in the package header.

In the package header you only have

FUNCTION func_create_password
 (password_string VARCHAR2(10))
 RETURN VARCHAR2;


You can look at this example in the documentation

And also please have a look at the Forum Guide on how to format your post.
Previous Topic: RAW to number
Next Topic: hex to dec
Goto Forum:
  


Current Time: Fri Jul 25 04:08:10 CDT 2014

Total time taken to generate the page: 0.09320 seconds