This might give you an idea:
create table Customer (
c_firstname varchar2(50),
c_lastname varchar2(50),
c_userid varchar2(50)
);
insert into Customer values ('Micky' , 'Mouse', 'mm');
insert into Customer values ('Donald', 'Duck' , 'dd');
insert into Customer values ('Peter' , 'Pan' , 'pp');
create or replace function GetCustomer(
FirstN varchar2 := null,
LastN varchar2 := null,
CID varchar2 := null
) return sys_refcursor
as
stmt varchar2(4000);
ret sys_refcursor;
begin
stmt := 'select * from Customer where 1=1';
if FirstN is not null then
stmt := stmt || ' and c_firstname like ''%' || FirstN || '%''';
end if;
if LastN is not null then
stmt := stmt || ' and c_lastname like ''%' || LastN || '%''';
end if;
if CID is not null then
stmt := stmt || ' and c_userid like ''%' || CID || '%''';
end if;
dbms_output.put_line(stmt);
open ret for stmt;
return ret;
end;
/
Later, in SQL*Plus:
set serveroutput on size 100000 format wrapped
declare
c sys_refcursor;
fn Customer.c_firstname%type;
ln Customer.c_lastname %type;
id Customer.c_userid %type;
begin
c := GetCustomer(LastN => 'u');
fetch c into fn, ln, id;
while c%found loop
dbms_output.put_line('First Name: ' || fn);
dbms_output.put_line('Last Name: ' || ln);
dbms_output.put_line('user id: ' || id);
fetch c into fn, ln, id;
end loop;
close c;
end;
/
Edit: The comment is right, and the procedure is subject to SQL injection. So, in order to prevent that, you could go with bind variables such as in this modified procedure:
create or replace function GetCustomer(
FirstN varchar2 := null,
LastN varchar2 := null,
CID varchar2 := null
) return sys_refcursor
as
stmt varchar2(4000);
ret sys_refcursor;
type parameter_t is table of varchar2(50);
parameters parameter_t := parameter_t();
begin
stmt := 'select * from Customer where 1=1';
if FirstN is not null then
parameters.extend;
parameters(parameters.count) := '%' || FirstN || '%';
stmt := stmt || ' and c_firstname like :' || parameters.count;
end if;
if LastN is not null then
parameters.extend;
parameters(parameters.count) := '%' || LastN || '%';
stmt := stmt || ' and c_lastname like :' || parameters.count;
end if;
if CID is not null then
parameters.extend;
parameters(parameters.count) := '%' || CID || '%';
stmt := stmt || ' and c_userid like :' || parameters.count;
end if;
if parameters.count = 0 then
open ret for stmt;
elsif parameters.count = 1 then
open ret for stmt using parameters(1);
elsif parameters.count = 2 then
open ret for stmt using parameters(1), parameters(2);
elsif parameters.count = 3 then
open ret for stmt using parameters(1), parameters(2), parameters(3);
else raise_application_error(-20800, 'Too many parameters');
end if;
return ret;
end;
/
Note, that now, whatever the input, the select statement becomes something like select ... from ... where 1=1 and col1 like :1 and col2 :2 ...
which is obviously much safer.
It depends on the GUI but they all should have a way of selecting the SYSDBA
privilege. Toad has it appear as a drop-down box called 'Connect as:' as does Oracle Enterprise Manager. For SQL Developer you use the 'Role' box when creating a new connection.
As far as / as sysdba
is concerned it just means that operating system authentication is being used so no password is needed. You can only use it if you are logged in as the oracle user or a member of the dba group (or for Windows, I believe as an administrator).
When you grant a user the SYSDBA
privilege it only means they have the option to log in with the SYSDBA
privilege and not that they will have dba privileges when they log in normally. The same is true for the SYS
user: you can log in as SYS
without the SYSDBA
privilege and so lack the ability to shutdown the database, for example.
Best Answer
The database management views are the newest addition to the SQL Server family as of 2005 and newer and replace a lot of tables that were previously used in older versions of SQL Server. A lot of old
sys
schema tables have comments which point this out as is stated on the page forsys.syslogins
.Instead you would use the following database management views:
sys.server_principals
orsys.sql_logins
(instead of sys.syslogins)sys.database_principals
(instead of sys.sysusers)There is a mapping page which I have linked in the comment, which helps you find the newer views for the relevant data.
All links point to Microsoft Pages in the official SQL Docs
General Catalog Views
There is a list of System Catalog Views (Transact-SQL) that are focused on various areas of SQL Server.
The
sys.database_principals
,sys.server_principals
andsys.sql_logins
for example are all part of the Security Catalog Views which can be found on the overview page.Recommendation
If you are just starting your SQL Server journey, then I would recommend learning the new views, as the old ones are deprecated and could become unsupported.