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
Java, SpringBoot, React, Angular, Oracle, PL/SQL, Ellucian Banner, Puppet, Docker, Terraform technical notes
Thursday, February 1, 2018
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;
Wednesday, December 27, 2017
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
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;
Tuesday, December 26, 2017
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
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
PLSQL call Java ora_sqlerrm: ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission
begin
dbms_java.grant_permission( 'username', 'SYS:java.io.FilePermission', '/home/abcd', 'read' );
end;
Subscribe to:
Posts (Atom)
AWS how to delete VPC when it has error with Network interface , Gateway decencies
how to delete VPC when it has error with Network interface , Gateway decencies in AWS 1. Check if it is running on EC2 instance then Sto...
-
ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1525 ORA-29261: bad argument ORA-29273: HTTP request failed ...
-
Banner 9 Admin: Invalid Object Name fix do steps: A record in GUBOBJS with a UI Version of D. • Records in GURAOBJ to define the defau...
-
chown root $ORACLE_HOME/bin/extjob chmod 4750 $ORACLE_HOME/bin/extjob chown root $ORACLE_HOME/rdbms/admin/externaljob.ora chmod 640 $ORA...