GIT long filename fix

GIT long filename fix

git config --system core.longpaths true

Oracle CPQ: build TAB by ArraySet, Array Control, Attribute as Array

In Oracle CPQ, we can build from with multiple TAB

1. define form attributes as array
2. define one array control as integer: number of form
3. go to array set, create new one with array control above and put dependency relate to all array attributes
4. set display as TAB

how to: Procedure returns multiple rows to java with array and cursor

create  type test_obj AS object
(
                                P_Program  VARCHAR2(400)
                           
);

CREATE TYPE test_nt AS TABLE OF test_obj;


Procedure Proc1 (P_Cursor Out SYS_REFCURSOR)
AS

Cursor C1 Is
 Select * from .......;


Cursor C2  (P_Program_In In Varchar2) Is
    Select * from where program = P_Program_In ;

P_Program  VARCHAR2(400);


v_prog_nt test_nt;
i integer := 0;

Begin
    --SELECT test_obj(P_Program ) BULK COLLECT INTO v_prog_nt FROM DUAL;

   v_prog_nt := test_nt();
 
    For Ds In C1
    Loop
 
      For Rs In C2(Ds.Program)
      Loop
   
       v_prog_nt.extend;
       i:= i+1;
        P_Program := Rs.Program;
   
   
        v_prog_nt(i) := test_obj(P_Program  );

        --dbms_output.put_line(i);
        dbms_output.put_line(v_prog_nt(i).P_Program);

      End Loop;
    End Loop;

Open P_Cursor
  For
  Select P_Program  from table(v_prog_nt);

End;

Store Procedure PLSQL returns Cursor in Loop to Java

create  type test_obj AS object
(
                                P_Program  VARCHAR2(400)
                             
);

CREATE TYPE test_nt AS TABLE OF test_obj;


Procedure Proc1 (P_Cursor Out SYS_REFCURSOR)
AS

Cursor C1 Is
 Select * from .......;


Cursor C2  (P_Program_In In Varchar2) Is
    Select * from where program = P_Program_In ;

P_Program  VARCHAR2(400);


v_prog_nt test_nt;
i integer := 0;

Begin
    --SELECT test_obj(P_Program ) BULK COLLECT INTO v_prog_nt FROM DUAL;
 
   v_prog_nt := test_nt();
   
    For Ds In C1
    Loop
   
      For Rs In C2(Ds.Program)
      Loop
     
       v_prog_nt.extend;
       i:= i+1;
        P_Program := Rs.Program;
     
     
        v_prog_nt(i) := test_obj(P_Program  );

        --dbms_output.put_line(i);
        dbms_output.put_line(v_prog_nt(i).P_Program);
 
      End Loop;
    End Loop;

Open P_Cursor
  For
  Select P_Program  from table(v_prog_nt);

End;

ORA-29273: HTTP request failed , ORA-06512: at "SYS.UTL_HTTP", ORA-29273: HTTP request failed, ORA-24247: network access denied by access control list (ACL)

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1525
ORA-29261: bad argument

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)

ORA-06512: at "SYS.DBMS_ISCHED", line 196
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 8
29273. 00000 -  "HTTP request failed"

Do this
grant execute on utl_http to username;
grant execute on utl_smtp to username;
grant execute on  utl_tcp to username;

Check ACL

SELECT host, lower_port, upper_port, acl FROM   dba_network_acls;

SELECT acl,
       principal,
       privilege,
       is_grant,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges;

SELECT host, lower_port, upper_port, privilege, status
FROM   user_network_acl_privileges;


Create ACL

begin
   dbms_network_acl_admin.create_acl (
      acl          => 'utl_http.xml',
      description  => 'http acl',
      principal    => 'username',
      is_grant     => TRUE,
       privilege    => 'connect',
       start_date   => null,
      end_date     => null);
    commit;
  end;
  /

Add privs


 begin
     dbms_network_acl_admin.add_privilege (
       acl         => 'utl_http.xml',
      principal   => 'username',
       is_grant    => true,
      privilege   => 'connect',
      position    => null,
       start_date  => null,
       end_date    => null);

    commit;
   end;
   /
 
    begin
     dbms_network_acl_admin.add_privilege (
       acl         => 'utl_http.xml',
      principal   => 'username',
       is_grant    => true,
      privilege   => 'resolve',
      position    => null,
       start_date  => null,
       end_date    => null);

    commit;
   end;
   /
 
   IF want to delete

    begin
     dbms_network_acl_admin.delete_privilege (
      acl        => 'utl_http.xml',
      principal   => 'username',
      is_grant    => false,
      privilege   => 'connect');

    commit;
   end;
   /
 
Assign

BEGIN
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
  acl => 'utl_http.xml',
  host => '*.oraclecloud.com',
  lower_port => NULL,
  upper_port => NULL);
END;

BEGIN
 DBMS_NETWORK_ACL_ADMIN.unassign_ACL (
  acl => 'utl_http.xml',
  host => '*.oraclecloud.com',
  lower_port => 80,
  upper_port => 443);
END;


TEST
SELECT host, lower_port, upper_port, acl FROM   dba_network_acls;

SELECT acl,
       principal,
       privilege,
       is_grant,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges;

SELECT host, lower_port, upper_port, privilege, status
FROM   user_network_acl_privileges;


select utl_http.request('https://oraclecloud.com') from dual;


Reference
https://oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1
http://www.dba-oracle.com/t_11g_new_acls_plsql.htm
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9526497800346930725

How to check Oracle user privilege

SELECT * FROM USER_SYS_PRIVS;
SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

PLSQL oralce call shell script .sh issue

chown root $ORACLE_HOME/bin/extjob
chmod 4750 $ORACLE_HOME/bin/extjob

chown root $ORACLE_HOME/rdbms/admin/externaljob.ora
chmod 640 $ORACLE_HOME/rdbms/admin/externaljob.ora

chown root $ORACLE_HOME/bin/jssu
chmod 4750 $ORACLE_HOME/bin/jssu

vi $ORACLE_HOME/rdbms/admin/externaljob.ora


run_user = oracle

run_group = oinstall