1 2 Previous Next 20 Replies Latest reply on Apr 24, 2017 4:05 AM by Sruthi Tamiri

Authentication Scheme error for PL/SQL code

Sruthi Tamiri Apr 14, 2017 11:49 AM

Hi All,

In our application they had implemented Authentication Schema like as mentioned below

Name: OAM_HTTP_AUTHSCHEME

Schema Type: HTTP Header Variable

HTTP Header Variable Name: HTTP_OAM_REMOTE_USER

Action if Username is Empty: Redirect to Built-In URL

Verify username: Each Request

Now under Source we have option as PL/SQL where i want use below logic but throwing error as

Query logic used to implemented

declare
lv_from_email_id varchar2(4000);
begin
lv_from_email_id := null;
select distinct email_address into lv_from_email_id from email_table where lower(USER_INTRANET_ID)=lower(:APP_USER);
exception when no_data_found  
then  
    apex_error.add_error (  
        p_message          => '<span style="color:red"> No access exists</span>',  
        p_display_location => apex_error.c_inline_in_notification );  
end;

but below error was got unable to get what was cauing problem.

ORA-06550: line 3, column 1: PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current cursor delete exists prior The symbol "begin" was substituted for "DECLARE" to continue. ORA-06550: line 18, column 21: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma

Can some one help me here.

Regards,

Sruthitamiri

1. Re: Authentication Scheme error for PL/SQL code

Alli Pierre Yotti Apr 14, 2017 12:00 PM (in response to Sruthi Tamiri)

Hey,
you must Write in in function like in the picture

That is a example. You can customize it

FUNCTION cek_otentikasi (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN
AS
hasil NUMBER := 0;
l_password SA_REGISTRIEREN.SAREG_PASSWORT%type;
l_firstname SA_REGISTRIEREN.SAREG_EMAIL%type;
BEGIN
SELECT 1 into hasil from SA_REGISTRIEREN
WHERE UPPER(SAREG_EMAIL) = UPPER(p_username)
AND SAREG_PASSWORT = WWV_FLOW_ITEM.MD5(p_password)
AND SAREG_STATUS= 'AKTIF';
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END cek_otentikasi;
Like Show 0 Likes(0)

Actions

2. Re: Authentication Scheme error for PL/SQL code

Sruthi Tamiri Apr 14, 2017 12:23 PM (in response to Alli Pierre Yotti)

Hi Alli,

I am using APEX 4.2 version where i do not see any function option which you have mentioned in picture.

What i have done is that i had created function in SQL work space as shown below

create or replace function get_email
return varchar2
is
 lv_from_email_id varchar2(4000);
begin
 lv_from_email_id := null;
 begin   
   select email_address
      into lv_from_email_id
     from email_table
   where lower(USER_INTRANET_ID) = lower(v('APP_USER'))
       and rownum = 1;  
 exception when no_data_found then
       lv_from_email_id := 'The user id does not exist!';
 end;
 return lv_from_email_id;
end get_email;

and try to use that function in

but got error as

Any suggestions on this.

Regards,

Sruthitamiri

3. Re: Authentication Scheme error for PL/SQL code

Alli Pierre Yotti Apr 14, 2017 12:28 PM (in response to Sruthi Tamiri)

Hey,
Look this Custom Authentication in APEX | Power Of Oracle Apex

and Set your function like in the Blog
Like Show 0 Likes(0)

Actions
4. Re: Authentication Scheme error for PL/SQL code

Sruthi Tamiri Apr 14, 2017 12:41 PM (in response to Alli Pierre Yotti)

Thanks Alli, but this a custom Auth which i can't implement like this. Since my login is depend on LDAP OID process.
In additional to that i was trying to use that email_table to check user exists or not.

Our application is designed based on LDAP which is global one, so when ever user logs if user exists in OID he can access application but at same time i want to check in email_table if user not exists then i want to restrict there itself by using my logic which i had mentioned in thread#1.

Is they a way to check both ways.

Regards,
Sruthitamiri
Like Show 0 Likes(0)

Actions
5. Re: Authentication Scheme error for PL/SQL code

Alli Pierre Yotti Apr 14, 2017 12:47 PM (in response to Sruthi Tamiri)

Hey,
YES. You must use a Custom Authentication to handle the LDAP and the Users in the Table

1- First i check if the User Status in the Database is 'AKTIF'. If yes, than the Authentication Works only for the Database

FUNCTION customAu (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN
AS
hasil NUMBER := 0;
l_password SA_REGISTRIEREN.SAREG_PASSWORT%type;
l_firstname SA_REGISTRIEREN.SAREG_EMAIL%type;

BEGIN
SELECT 1 into hasil from SA_REGISTRIEREN
WHERE UPPER(SAREG_EMAIL) = UPPER(p_username)
AND SAREG_PASSWORT = WWV_FLOW_ITEM.MD5(p_password)
AND SAREG_STATUS= 'AKTIF';
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
WHEN others THEN
return apex_ldap_auth(p_username,p_password);(2)
END customAu;

2- If the User has not the Status 'AKTIF', i use LDAP to check if the User is in the Windows Active Directory

create or replace FUNCTION apex_ldap_auth( p_username IN VARCHAR2,
p_password IN VARCHAR2
)
RETURN BOOLEAN IS
p_count number;
begin
IF APEX_LDAP.AUTHENTICATE(
    p_username =>p_username,
    p_password =>p_password,
    p_search_base => 'cn=user,l=amer,dc=my_company,dc=com',
    p_host => 'our_ldap_sever.my_company.com',
    p_port => 389) THEN
return true;
ELSE
    return false;
END IF;
end apex_ldap_auth;

You can adapt it for you

Regards
Pierre
Like Show 0 Likes(0)

Actions
6. Re: Authentication Scheme error for PL/SQL code

Sruthi Tamiri Apr 14, 2017 1:25 PM (in response to Alli Pierre Yotti)

Hi Pierre,

So we need to create two functions and we need to call both functions one after other what "AKTIF" means indicates.

Regards,
Sruthitamiri
Like Show 0 Likes(0)

Actions
7. Re: Authentication Scheme error for PL/SQL code

Alli Pierre Yotti Apr 14, 2017 1:27 PM (in response to Sruthi Tamiri)

Hey,
"So we need to create two functions and we need to call both functions one after other" YES
"AKTIF" Is in my Case. you can ignrore it
Like Show 0 Likes(0)

Actions
8. Re: Authentication Scheme error for PL/SQL code

Sruthi Tamiri Apr 19, 2017 11:47 AM (in response to Alli Pierre Yotti)

Hi Pierre,

I have followed Power of Oracle APEX link which you had shared.
When i had created custom authentication it will redirect to default page #101, but we are not using this page at all.
Our login page is .jsp page format. any help on this if i am missing anything.

Regards,
Sruthitamiri
Like Show 0 Likes(0)

Actions
9. Re: Authentication Scheme error for PL/SQL code

Sruthi Tamiri Apr 20, 2017 3:40 AM (in response to Sruthi Tamiri)

Hi Pierre,

In apex.oracle.com i had created like this, can you please take a look and advice me.

URL : https://apex.oracle.com/pls/apex/f?p=12182:1
Work Space:cts_demo
user name:seshutamiri@gmail.com
password:seshutamiri

1. I have created new OAM_HTTP_AUTHSCHEME which is currently used in existing application.
2. I have created two functions as get_email & apex_ldap_auth
3. By default we see page#101, but that page i will not be using instated of that in our existing application they have used SSO login page , and we could able redirect using OAM_HTTP_AUTHSCHEMA.
4. Could you please help me how to use two functions in OAM_HTTP_AUTHSCHEMA level.
5. Because of this you may not able to logon in demo application but you can navigate same.

Regards,
Sruthitamiri
Like Show 0 Likes(0)

Actions
10. Re: Authentication Scheme error for PL/SQL code

Sruthi Tamiri Apr 21, 2017 5:21 AM (in response to Sruthi Tamiri)

Any update on this request please.

Regards,
Sruthitamiri
Like Show 0 Likes(0)

Actions
11. Re: Authentication Scheme error for PL/SQL code

Alli Pierre Yotti Apr 21, 2017 5:28 AM (in response to Sruthi Tamiri)

Hey,
what do you means with "4. Could you please help me how to use two functions in OAM_HTTP_AUTHSCHEMA level."?

does your authentication not works?
is there Some Errors?

regards
pierre
Like Show 0 Likes(0)

Actions
12. Re: Authentication Scheme error for PL/SQL code

Sruthi Tamiri Apr 21, 2017 6:02 AM (in response to Alli Pierre Yotti)

Hi Pierre,

My current authentication used in my application is OAM_HTTP_AUTHSCHEMA, which I have shown in apex.oracle.com.
Now I had created two functions in SQL work shop, how do we call those functions [get_email & apex_ldap_auth] in authentication OAM_HTTP_AUTHSCHEMA, also my function will not contain any username & password, so just I want to check with email address.

Regards,
Sruthitamiri
Like Show 0 Likes(0)

Actions
13. Re: Authentication Scheme error for PL/SQL code

Alli Pierre Yotti Apr 21, 2017 6:13 AM (in response to Sruthi Tamiri)

Hey,
i have call get_email. you can check and make for the another function

Regards

Pierre
Like Show 0 Likes(0)

Actions
14. Re: Authentication Scheme error for PL/SQL code

Sruthi Tamiri Apr 21, 2017 6:35 AM (in response to Alli Pierre Yotti)

Hi Pierre,

I did called function similar way, but in table we do have any data then it should display below error message after login into application.
I had kept some debug message but it is not displayed any idea please, why function not fringing.
If you see get_email function logic in exception block i have return message as, but particular message not displayed, & could able to login.
lv_from_email_id := 'The user id does not exist!';

Regards,
Sruthitamiri
Like Show 0 Likes(0)

Actions

1 2 Previous Next

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.