UTL_FILE another Query

declare
             fileptr    utl_file.file_type;
             buffer    varchar2(2048);
             line_num number(10):=0;
             loc_no  integer;
             string_var varchar2(4000);
     begin
             fileptr:=utl_file.fopen('G_USER_DIR','sp_details.txt','R');
             loop
                     line_num:=line_num+1;
                     utl_file.get_line(fileptr,buffer);
                     --dbms_output.put_line(line_num||' '||buffer);
                     string_var:=buffer;
                     dbms_output.put_line(string_var);
                    
             end loop;
     exception
             when no_data_found then
                     utl_file.fclose(fileptr);
                     dbms_output.put_line('Number of lines parsed ='||line_num);
             when value_error then
                     dbms_output.put_line('value error');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_path  then
                     dbms_output.put_line('invalid path');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_mode  then
                     dbms_output.put_line('invalid_mode');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_filehandle  then
                     dbms_output.put_line('invalid_filehandle');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_operation  then
                     dbms_output.put_line('invalid_operation');
                     raise_application_error(-20100,'file error');
             when utl_file.read_error  then
                     dbms_output.put_line('read_error');
                     raise_application_error(-20100,'file error');
             when utl_file.write_error  then
                     dbms_output.put_line('write_error');
                     raise_application_error(-20100,'file error');
             when utl_file.internal_error  then
                     dbms_output.put_line('internal_error');
                     raise_application_error(-20100,'file error');
             when others then
                     dbms_output.put_line('un-handled');
                     raise_application_error(-20100,'file error');
     end;


-------------------------------------------------------------------

DECLARE
file1   UTL_FILE.file_type;
p_line VARCHAR2(4000);
BEGIN
file1 := UTL_FILE.fopen ('G_USER_DIR', 'sp_details.txt', 'w');
p_line := '19320418'||','||'18649335'||','||'18016235'||','||'14032419'||','||'10383346'||','||'13194228'||','||'18882026'||','||'11073020'||','||'19539731'||','||'16712789'||','||'10808330'||','||'14903288'||','||'17118027'||','||'15928693'||','||'11532136'||','||'12627057'||','||'19961984'||','||'19138862'||','||'11398161'||','||'16115467'||','||'14890139'||','||'11560049'||','||'14105816'||','||'17121334'||','||'19542285'||','||'11384858'||','||'17418279'||','||'13293065'||','||'14692220'||','||'11362967'||','||'10436234'||','||'19341379'||','||'16134493'||','||'15493206'||','||'15745525'||','||'15135785'||','||'16573746'||','||'13439175'||','||'16272382'||','||'11229845'||','||'12836326'||','||'11402844'||','||'13419723'||','||'19343730'||','||'11310078'||','||'18766875'||','||'10249762'||','||'10849323'||','||'16495481'||','||'15412637';
UTL_FILE.put_line (file1, p_line);
UTL_FILE.fclose (file1);
END;

UTL File

declare
             fileptr    utl_file.file_type;
             v_buffer    varchar2(2048);
             line_num number(10):=0;
             loc_no  integer;
             string_var varchar2(4000);
              CURSOR process_unix_ssn_c(unix_ssn VARCHAR2)
              IS
              SELECT sp ,
              sp s_id3
              FROM sa.sp
              WHERE sp_id        =unix_ssn
              AND sp_change_ind IS NULL;
              v_process_unix_ssn process_unix_ssn_c%rowtype ;
             
            
            
     begin
             fileptr:=utl_file.fopen('G_USER_DIR','sp_details.txt','R');
             loop
                     line_num:=line_num+1;
                     utl_file.get_line(fileptr,v_buffer);
                     --dbms_output.put_line(line_num||' '||buffer);
                     string_var:=v_buffer;
                     dbms_output.put_line(string_var);
                    
                 
                  OPEN process_unix_ssn_c(v_buffer);
                  LOOP
                  FETCH process_unix_ssn_c INTO v_process_unix_ssn;
                  EXIT
                  WHEN process_unix_ssn_c%notfound;
                 
                  dbms_output.put_line('s_pidm3: '||v_process_unix_ssn.s_pidm3||' s_id3 : '||v_process_unix_ssn.s_id3);
                  END LOOP;
                  CLOSE process_unix_ssn_c;
                    
             end loop;
     exception
             when no_data_found then
                     utl_file.fclose(fileptr);
                     dbms_output.put_line('Number of lines parsed ='||line_num);
             when value_error then
                     dbms_output.put_line('value error');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_path  then
                     dbms_output.put_line('invalid path');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_mode  then
                     dbms_output.put_line('invalid_mode');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_filehandle  then
                     dbms_output.put_line('invalid_filehandle');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_operation  then
                     dbms_output.put_line('invalid_operation');
                     raise_application_error(-20100,'file error');
             when utl_file.read_error  then
                     dbms_output.put_line('read_error');
                     raise_application_error(-20100,'file error');
             when utl_file.write_error  then
                     dbms_output.put_line('write_error');
                     raise_application_error(-20100,'file error');
             when utl_file.internal_error  then
                     dbms_output.put_line('internal_error');
                     raise_application_error(-20100,'file error');
             when others then
                     dbms_output.put_line('un-handled');
                     raise_application_error(-20100,'file error');
     end;
/

-------------------------------------------------------------------

DECLARE
file1   UTL_FILE.file_type;
p_line VARCHAR2(4000);
BEGIN
file1 := UTL_FILE.fopen ('GWU_USER_DIR', 'sp_details.txt', 'w');
--p_line := '19320418'||','||'18649335'||','||'18016235'||','||'14032419'||','||'10383346'||','||'13194228'||','||'18882026'||','||'11073020'||','||'19539731'||','||'16712789'||','||'10808330'||','||'14903288'||','||'17118027'||','||'15928693'||','||'11532136'||','||'12627057'||','||'19961984'||','||'19138862'||','||'11398161'||','||'16115467'||','||'14890139'||','||'11560049'||','||'14105816'||','||'17121334'||','||'19542285'||','||'11384858'||','||'17418279'||','||'13293065'||','||'14692220'||','||'11362967'||','||'10436234'||','||'19341379'||','||'16134493'||','||'15493206'||','||'15745525'||','||'15135785'||','||'16573746'||','||'13439175'||','||'16272382'||','||'11229845'||','||'12836326'||','||'11402844'||','||'13419723'||','||'19343730'||','||'11310078'||','||'18766875'||','||'10249762'||','||'10849323'||','||'16495481'||','||'15412637';
p_line :=   '
19320418
18649335
18016235
14032419
10383346
13194228
18882026
11073020
19539731
16712789
10808330
14903288
17118027
15928693
11532136
12627057
19961984
19138862
11398161
16115467
14890139
11560049
14105816
17121334
19542285
11384858
17418279
13293065
14692220
11362967
10436234
19341379
16134493
15493206
15745525
15135785
16573746
13439175
16272382
11229845
12836326
11402844
13419723
19343730
11310078
18766875
10249762
10849323
16495481
15412637';

UTL_FILE.put_line (file1, p_line);
UTL_FILE.fclose (file1);
END;

Cursors

DECLARE
  v_ename VARCHAR2(300):=NULL;
  v_job   VARCHAR2(300):=NULL;
BEGIN
  BEGIN
    SELECT ename
    INTO v_ename
    FROM emp
    WHERE empno='7839';
    /* Implicit Cursor with one row*/
    dbms_output.put_line('Implicit Cursor one row : '||v_ename);
  END;
  BEGIN
    FOR i IN
    (SELECT ename,JOB FROM emp WHERE empno IN ('7839','7698')
    )
    /* Explicit Cursor  */
    LOOP
      dbms_output.put_line(' Explicit Cursor retving more then on row ---->'||i.ename||'          '||i.job);
      FOR j IN
      (SELECT ename,JOB FROM emp WHERE ename=i.ename AND JOB=i.JOB
      )
      LOOP
        dbms_output.put_line(' Passing i values in j cursor retving more then on row ---->'||j.ename||'          '||j.JOB);
      END LOOP;
    END LOOP;
  END;
  BEGIN
    SELECT ename INTO v_ename FROM emp;
    /* Implicit Cursor */
    dbms_output.put_line('Implicit Cursor : '||v_ename);
  END;
EXCEPTION
WHEN too_many_rows THEN
  dbms_output.put_line('v_ename Implicit Cursor return Too many Rows');
END;

RefCursor

DECLARE
  refvar    VARCHAR2(100)   :='and EMP_MI=''J''';
  query_str VARCHAR2 (3000) := NULL;
TYPE cur_typ
IS
  REF
  CURSOR;
    c cur_typ;
    V_SPRIDEN_ID VARCHAR2(1000);
  BEGIN
    query_str:='SELECT SPRIDEN_ID FROM EMP WHERE EMP_ID in(''G19320418'',''G18649335'')'||refvar||'';
    OPEN c FOR query_str;
    LOOP
      FETCH c INTO V_SPRIDEN_ID;
    EXIT
  WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(V_SPRIDEN_ID);
  END LOOP;
  CLOSE c;
END;

Comma Separated Values Code

/* Comma Separated Values By Dynamic Sql */

DECLARE
  L_COUNT BINARY_INTEGER;
  L_ARRAY DBMS_UTILITY.LNAME_ARRAY;
  VAR VARCHAR2(100);
  temp_count9999 NUMBER;
BEGIN
  DBMS_UTILITY.COMMA_TO_TABLE (
  LIST => REGEXP_REPLACE ('10,11,121,123', '(^|,)', '\1X'), TABLEN => L_COUNT, TAB => L_ARRAY ) ;
  DBMS_OUTPUT.PUT_LINE ('L_COUNT :   ' || L_COUNT);
  FOR I IN 1 .. L_COUNT
  LOOP
    --DBMS_OUTPUT.PUT_LINE (SUBSTR (L_ARRAY (I), 2));
    VAR:=SUBSTR (L_ARRAY (I), 2);
    --DBMS_OUTPUT.PUT_LINE (VAR);
    select
    to_char(nvl(count(*), 0))  INTO temp_count9999
    from  (SELECT COLUMN_VALUE col_value FROM TABLE (gzkplib.f_string_2_tab ('10,43,51,85'))) a
    where a.col_value = VAR;
    DBMS_OUTPUT.PUT_LINE('temp_count9999 :'||temp_count9999);
  END LOOP;
END;


Dynamic Sql


If par_value is not null then

if ((PARA_VAL_TABLE IS NOT NULL) AND (PARA_VAL_COLUMN IS NOT NULL) AND (PARA_VAL_CONDITION IS NOT NULL)) THEN

--Separating With Commas **

  DBMS_UTILITY.COMMA_TO_TABLE (
  LIST => REGEXP_REPLACE (par_value, '(^|,)', '\1X'), TABLEN => L_COUNT, TAB => L_ARRAY ) ;
  DBMS_OUTPUT.PUT_LINE ('L_COUNT :   ' || L_COUNT);
  --var_value:=SUBSTR (L_ARRAY (I), 2);

FOR I IN 1 .. L_COUNT
  LOOP
  var_value:=SUBSTR (L_ARRAY (I), 2);
  temp_str:=PARA_VAL_TABLE||' where '||PARA_VAL_COLUMN || ' ' ||PARA_VAL_CONDITION  || ' ''' || var_value || '''';
    DBMS_OUTPUT.PUT_LINE (SUBSTR (L_ARRAY (I), 2));
    DBMS_OUTPUT.PUT_LINE (var_value);
    
    ValidatePara_Qry := 'select    to_char(nvl(count(*), 0)) temp_count9999  from  '||temp_str||'';
    DBMS_OUTPUT.PUT_LINE(ValidatePara_Qry);  
    EXECUTE IMMEDIATE ValidatePara_Qry into v_temp_count9999;
    DBMS_OUTPUT.PUT_LINE('temp_count9999 :'||v_temp_count9999);
   
   
     /* if #_debug
      dbms_output.put_line('Validation count = '||v_temp_count9999||' for# '||para_number||'.'||para_index||' select= '||temp_str); -- show 'Validation count = ' &temp_count9999 ' for# ' #para_number'.' #para_index  ' select= ' $temp_str
      end-if */
      if (v_temp_count9999= '0') then
      temp_str:= 'Invalid Value. Value not in ';
      temp_str:= temp_str || PARA_VAL_TABLE;
     if PARA_VAL_ERROR is null then
      SetParaError(para_number, para_index, temp_str,v_job_name ,v_job_number ) ;
      else
      SetParaError(para_number, para_index, PARA_VAL_ERROR ,v_job_name ,v_job_number ) ;
      end if;
      end if;

Wrod Wrapin in PL Sql

----Wrod Wrap-- package--
CREATE OR REPLACE PACKAGE WROD_WRAP
AS
FUNCTION bi_word_wrap (p_str varchar2, p_linesize pls_integer, p_sep varchar2 := ' ') RETURN VARCHAR2;
END WROD_WRAP;

---BOdy---
CREATE OR REPLACE PACKAGE BODY WROD_WRAP
AS
FUNCTION bi_word_wrap (p_str varchar2, p_linesize pls_integer, p_sep varchar2 := ' ') RETURN VARCHAR2
as
l_str long := p_str || p_sep;
l_line long;
l_pos pls_integer;
R_VAL VARCHAR2(3000):=NULL;
begin
while l_str is not null loop
l_line := substr(l_str, 1, p_linesize);
l_pos := instr(l_line, p_sep, -1);
l_line := substr(l_line, 1, l_pos);
dbms_output.put_line(l_line);
R_VAL:=R_VAL||CHR(10)||l_line;
l_str := substr(l_str, l_pos + 1);
end loop;
RETURN R_VAL;
end bi_word_wrap;
END WROD_WRAP;

 ----execution 
  DECLARE
  RET VARCHAR2(1000);
  begin
  RET:=WROD_WRAP.bi_word_wrap(:S,20);
  END;

select WROD_WRAP.bi_word_wrap(:S,20) a from dual;

Public Synonym

CREATE PUBLIC SYNONYM PACKAGE_NAME FOR BANINST1.PACKAGE_NAME

replace for stirng

variable:=REPLACE(PARM_ECLS_EXC,',',''',''');

 system flush::
 
alter system flush shared_pool;

DECODE::

decode(T_CONTRACT_NAME,null,decode(T_STREET_LINE1,null,'No Contract Name',T_STREET_LINE1),T_CONTRACT_NAME ) decode,