SELECT text
from ALL_source
WHERE lower(name)='dbms_db_version'
order by line
begin
dbms_output.put_line(dbms_db_version.version);
dbms_output.put_line(dbms_db_version.release);
if dbms_db_version.ver_le_12 or dbms_db_version.ver_le_12_1
or dbms_db_version.ver_le_12_2 then
dbms_output.put_line('it is 12c :)');
end if;
end;
-----------------------
create or replace function get_sal
(p_emp_id number)
return number $if dbms_db_version.version>=11 $then result_cache $end
is
v_sal number;
begin
select salary into v_sal
from employees
where employee_id=p_emp_id;
return v_sal;
end;
------------------------
SELECT name, value
FROM v$parameter
WHERE name ='plsql_optimize_level' ;
begin
dbms_output.put_line($$plsql_Code_type);
dbms_output.put_line($$plsql_optimize_level);
dbms_output.put_line($$plsql_warnings);
end;
----------------------
create or replace procedure g_test
is
begin
dbms_output.put_line('test');
end;
create or replace procedure g_test
is
begin
$if $$plsql_optimize_level <>3 $then
$error 'it should be compiled with plsql_optimize_level=3 ' $end
$end
dbms_output.put_line('test');
end;
alter session set plsql_optimize_level=3;
create or replace procedure g_test
is
begin
$if $$plsql_optimize_level <>3 $then
$error 'it should be compiled with plsql_optimize_level=3 ' $end
$end
dbms_output.put_line('test');
end;
------------------------------------------------------------------------------------------------------------------
SELECT name, value
FROM v$parameter
WHERE name ='plsql_ccflags' ;
alter session set plsql_ccflags='language:1';
SELECT name, value
FROM v$parameter
WHERE name ='plsql_ccflags' ;
--to print this directive
begin
dbms_output.put_line($$language);
dbms_output.put_line($$plsql_ccflags);
end;
--to use it in PLSQL code
create or replace function get_curr_lang
return varchar2
is
v_lang varchar2(100);
begin
v_lang:='the curr language in system is '||
$if $$language=1 $then 'English'
$else 'Arabic'
$end
;
return v_lang;
end;
select get_curr_lang from dual;
select * from user_plsql_object_settings
where lower(name)='get_curr_lang';
alter session set plsql_ccflags='language:2';
select * from user_plsql_object_settings
where lower(name)='get_curr_lang';
create or replace function get_curr_lang
return varchar2
is
v_lang varchar2(100);
begin
v_lang:='the curr language in system is '||
$if $$language=1 $then 'English'
$else 'Arabic'
$end
;
return v_lang;
end;
select * from user_plsql_object_settings
where lower(name)='get_curr_lang';
select get_curr_lang from dual;
-----------------------------
--from left to right
alter session set plsql_ccflags='plsql_ccflags:true,debug:true,debug:0';
SELECT name, value
FROM v$parameter
WHERE name ='plsql_ccflags' ;
begin
dbms_output.put_line ($$plsql_ccflags);
end;
begin
if $$plsql_ccflags then
dbms_output.put_line('plsql_ccflags value is true');
end if;
if $$debug=0 then
dbms_output.put_line('debug value is 0');
end if;
dbms_output.put_line($$ddfdfgf);
end;
--now
alter session set plsql_warnings='enable:all'
--now it should give warning, but it will not
--the warning only in subprograms
begin
if $$plsql_ccflags then
dbms_output.put_line('plsql_ccflags value is true');
end if;
if $$debug=0 then
dbms_output.put_line('debug value is 0');
end if;
dbms_output.put_line($$ddfdfgf);
end;
create or replace procedure cc
is
begin
if $$plsql_ccflags then
dbms_output.put_line('plsql_ccflags value is true');
end if;
if $$debug=0 then
dbms_output.put_line('debug value is 0');
end if;
dbms_output.put_line($$ddfdfgf);
end;
--------------------------------------------------------------------------------------------------
SELECT name, value
FROM v$parameter
WHERE name ='plsql_ccflags' ;
SELECT name, value
FROM v$parameter
WHERE name ='plsql_code_type' ;
--the values in plsql_ccflags could be pls_integer,boolean, null
alter session set plsql_ccflags='language:1,language:null,plsql_code_type:10';
SELECT name, value
FROM v$parameter
WHERE name ='plsql_code_type' ;
begin
dbms_output.put_line($$language);
dbms_output.put_line($$plsql_code_type);
dbms_output.put_line($$plsql_warnings);
end;
-----------------------------------------------------------------------------------
--some other examples on plsql_ccflags
SELECT name, value
FROM v$parameter
WHERE name ='plsql_ccflags' ;
alter session set plsql_ccflags='trace_time:true,maxsizev:2000';
SELECT name, value
FROM v$parameter
WHERE name ='plsql_ccflags' ;
create or replace procedure test_only
is
v varchar2($$maxsizev);
begin
if $$trace_time then
dbms_output.put_line(to_char(sysdate,'hh:mi:ss') );
end if;
for i in 1..1000
loop
dbms_output.put_line($$maxsizev);
end loop;
if $$trace_time then
dbms_output.put_line(to_char(sysdate,'hh:mi:ss') );
end if;
end;
exec test_only;
alter session set plsql_ccflags='trace_time:false,maxsizev:2000';
exec test_only;
--you should compile again
create or replace procedure test_only
is
v varchar2($$maxsizev);
begin
if $$trace_time then
dbms_output.put_line(to_char(sysdate,'hh:mi:ss') );
end if;
for i in 1..1000
loop
dbms_output.put_line($$maxsizev);
end loop;
if $$trace_time then
dbms_output.put_line(to_char(sysdate,'hh:mi:ss') );
end if;
end;
select * from user_plsql_object_settings
where lower(name)='test_only';
exec test_only;
call dbms_preprocessor.print_post_processed_source
('procedure', 'hr','test_only');
----------------------------------------------------------------------------------------------------------------
--without wrap
create or replace function get_sum_sal_dept
( dept_id number )
return number
is
v_sal number;
begin
select sum(salary)
into v_sal
from
employees
where department_id =dept_id;
return v_sal;
exception
when others then return null;
end;
select get_sum_sal_dept(90) from dual;
select line,text from user_source
where lower(name)='get_sum_sal_dept';
--with warap
begin
dbms_ddl.create_wrapped
(
'create or replace function get_sum_sal_dept
( dept_id number )
return number
is
v_sal number;
begin
select sum(salary)
into v_sal
from
employees
where department_id =dept_id;
return v_sal;
exception
when others then return null;
end;'
);
end;
select get_sum_sal_dept(90) from dual;
select line,text from user_source
where lower(name)='get_sum_sal_dept';
--with wrap dynamic
declare
v1 varchar2(1000):='create or replace function get_sum_sal_dept
( dept_id number )
return number
is
v_sal number;
begin
select sum(salary)
into v_sal
from
employees
where department_id =dept_id;
return v_sal;
exception
when others then return null;
end;';
begin
dbms_ddl.create_wrapped(v1);
end;
-----------------------------------------------------------------------------------------------------
--1 take the code between /* */ and save in script test.sql in f: for example
drop table table_1;
drop procedure insert_table_1;
/*
create table table_1
( id number,
name varchar2(100)
);
insert into table_1 values (1,'nader ali');
insert into table_1 values (2,'khaled rami');
insert into table_1 values (3,'naser hassan');
insert into table_1 values (4,'ameen hadi');
create or replace procedure insert_table_1
(p_id number, p_name varchar2)
is
begin
insert into table_1 values (p_id,p_name);
commit;
end;
*/
--2
--now open cmd command
--go to f:
--and do this wrap iname=test.sql
--3 now you can do this
@f:\test.plb
select * from table_1;
exec insert_table_1 (5,'ahmed alali');
select line,text from user_source
where name=upper('insert_table_1'
No comments:
Post a Comment