You Asked

I am trying to use conditional compilation in a sql query of a cursor.
Below is the code

create or replace procedure p_env_check(i_env in  number,
                                        o_cur out sys_refcursor)
is
begin
open o_cur for select id, 
          $if $$i_env = 1 $then 
          name,
        $end
        id1
from t1;      
end p_enc_check;

I am using below code to execute the procedure p_env_check

DECLARE
  I_ENV NUMBER;
  O_CUR sys_refcursor;
BEGIN
  I_ENV := 1;

  P_ENC_CHECK(
    I_ENV => I_ENV,
    O_CUR => O_CUR
  );
  :O_CUR := O_CUR; --<-- Cursor 
END;

I am not getting any compile time error, but in the output I am only getting two columns id, id1. I expect three columns in the output because I have passed i_env = 1 in the block.
Please suggest me if it is a valid way to use conditional compilation. How should I change my query to get 3 columns in output based on the i_env value.

Thanks!!

and Chris said...

The clue's in the name:

Conditional compliation

To change the behaviour, you have to recompile the procedure setting i_env to 1 in the plsql_ccflags. Using a parameter doesn't cut it:

create or replace procedure p_env_check(i_env in  number,
                                        o_cur out sys_refcursor)
is
begin
open o_cur for 
  select 'c1' c1, 
          $if $$i_env = 1 $then 
            'c2' c2,
          $end
          'c3' c3
  from   dual;      
  
end p_env_check;
/
sho err

var cur refcursor;

exec p_env_check(1, :cur);

print :cur;

C1 C3
-- --
c1 c3

alter procedure p_env_check compile plsql_ccflags = 'i_env:1';

exec p_env_check(0, :cur);

print :cur;

C1 C2 C3
-- -- --
c1 c2 c3

If you want to do this based on user input, you need dynamic SQL. Or if-then logic to open the appropriate cursor:

create or replace procedure p_env_check(i_env in  number,
                                        o_cur out sys_refcursor)
is
begin
  if i_env = 1 then
    open o_cur for 
      select 'c1' c1, 
             'c2' c2,
             'c3' c3
      from   dual;     
  else
    open o_cur for 
      select 'c1' c1, 
             'c3' c3
      from   dual;     
  end if;
end p_env_check;
/

exec p_env_check(1, :cur);

print :cur;

C1 C2 C3
-- -- --
c1 c2 c3

exec p_env_check(0, :cur);

print :cur;

C1 C3
-- --
c1 c3

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Error while getting "print_post_processed_source"

Rajeshwaran, Jeyabal, January 07, 2020 - 11:20 am UTC

Team,

The below testcase was from 12.2 - could you help us to understand - why we got the error while invoking "print_post_processed_source" with parameter as "source" ?

demo@PDB1> set serveroutput on size unlimited format wrapped
demo@PDB1> alter session set plsql_ccflags='trace_level:1';

Session altered.

demo@PDB1>
demo@PDB1> create or replace procedure p
  2  is
  3     x number;
  4  begin
  5     x := 55;
  6
  7     $if $$trace_level = 1 $then
  8             dbms_output.put_line( 'Trace level = 1 ');
  9     $else
 10             dbms_output.put_line( 'Trace level != 1 ');
 11     $end
 12  end;
 13  /

Procedure created.

demo@PDB1> exec dbms_preprocessor.print_post_processed_source( source=>'P');
P
ORA-06550: line 1, column 1:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   := . ( @ % ;
The symbol ";" was substituted for "end-of-file" to continue.

PL/SQL procedure successfully completed.

demo@PDB1> begin
  2      dbms_preprocessor.print_post_processed_source(
  3      object_type=>'PROCEDURE',
  4      schema_name=>user,
  5      object_name=>'P') ;
  6  end;
  7  /
procedure p
is
        x number;
begin
        x := 55;


                dbms_output.put_line( 'Trace level = 1 ');



end;

PL/SQL procedure successfully completed.

demo@PDB1>

January 07, 2020 - 2:08 pm UTC

You pass the code itself when using the source parameter, not the name of the object!

alter session set plsql_ccflags='trace_level:1';

begin 
  dbms_preprocessor.print_post_processed_source( 
    source=> q'!declare
   x number;
begin
   x := 55;
   $if $$trace_level = 1 $then
           dbms_output.put_line( 'Trace level = 1 ');
   $else
           dbms_output.put_line( 'Trace level != 1 ');
   $end
end;!'
  );
end;
/

declare
   x number;
begin
   x := 55;
   
           dbms_output.put_line( 'Trace level = 1 ');
   


end;

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Error while getting "print_post_processed_source"

More to Explore

PL/SQL demos

PL/SQL docs

Conditional compilation in query

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Error while getting "print_post_processed_source"

More to Explore

PL/SQL demos

PL/SQL docs