set serveroutput on;
declare
username1 varchar2(40);
cnt number;
hello varchar2(20);
c sys_refcursor;
begin
select sys_context('userenv','session_user') into username1 from dual;
select lower(username1) into username1 from dual;
select count(lower(username)) into cnt from karuna.tableusers where lower(username)=username1;
if cnt=1 then
dbms_output.put_line('username found');
execute immediate 'connect karuna/password ';
open c for 'select item_name from sells12 where item_id=12';
fetch c into hello;
dbms_output.put_line(hello);
close c;
else
dbms_output.put_line('u dont have previllege to access database');
----raise_application_error(-20001,'error out');
end if;
end;
/
I want to switch session user but I am getting error at connect karuna/karuna line
CONNECTis a SQL*Plus command, not SQL, so not something you can call from a PL/SQL block. Why do you want to reconnect though; is there a reason the user you're connecting as to do this check can't have privileges to whatever it needs to, without exposing the entirekarunaschema? Do you just want to avoid prefixing objects with the schema (e.g.alter session set current_schema = 'KARUNA')?