Internet Explorer either crashes or forms simply don’t launch from Form Builder

Steps to resolve:

Rename jvm.dll in C:\Program Files (x86)\Oracle\JInitiator 1.3.1.22\bin\hotspot directory.

Copy jvm.dll from

C:\DevSuiteHome_1\jdk\jre\bin\server

to

C:\Program Files (x86)\Oracle\JInitiator 1.3.1.22\bin\hotspot

Administrator permission might be required.

Advertisements

Adding Sysadmin Responsibility to Application User from Backend

set SERVEROUTPUT on;

Begin
fnd_user_pkg.addresp (‘<Username>’,’SYSADMIN’,’SYSTEM_ADMINISTRATOR’,’STANDARD’,’add responsibility to user using pl/sql’,sysdate-1,sysdate + 100);
commit;
dbms_output.put_line(‘responsibility added successfully to the user ‘);
exception
when others then
dbms_output.put_line(‘ responsibility adding failed due to’ || sqlcode || substr(sqlerrm, 1, 100));
rollback;
end;

Note:replace <Username> with front end application user

Connecting Report/Form Builder to XE Database

Assumption: We have installed our XE database and the Developer Suite 10G. Database service is up and running.

When we try to connect Report/Form Builder we encounter an error message like

ORA-12154: TNS:could not resolve the connect identifier specified

Or

ORA-12560: TNS:protocol adapter error in SQLPLUS.

To resolve this one needs to copy the sqlnet.ora, tnsnames.ora and listener.ora from the Admin folder of the installed database to the Admin folder of the installed Dev Suite Home.

The Admin folder of DB server will be most likely (On Windows OS)

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN

And the Admin folder on Oracle Home will be:

C:\DevSuiteHome_1\NETWORK\ADMIN

Installing Oracle XE 11GR2 Database

Link to download the exe file:

http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

Select appropriate version as per your machine and Install.

Download Oracle Database Express Edition 11g Release 2 for Windows x64
– Unzip the download and run the DISK1/setup.exe
Download Oracle Database Express Edition 11g Release 2 for Windows x32
– Unzip the download and run the DISK1/setup.exe

While installing it will prompt for a password which is common for SYS and SYSTEM Accounts.

You are all set.

Install SQL Developer and connect using user SYS, Role :SYSDBA and Password :Use the password that you set initially.

Modify HR user credentials:

ALTER USER HR IDENTIFIED BY <your password>

Below 2 command need to be entered to access database through HR schema else you will get a message that the account is locked.

alter user HR account unlock;

grant connect, resource to HR;

 

APP-FND-00874 Routine FDF found no rows in table FND_DESCRIPTIVE_FLEXS error during concurrent program submission.

The cause of this error is unknown.

It can be resolved by following the below steps.:

  1. Remove the affected program from the all the request groups to which it it attached.
  2. Query the concurrent program and uncheck Use In SRS and then save.
  3. Requery the program and check ‘Use in SRS’ and save again.
  4. Attach the program to request group and re-test.

Bug Reference: https://support.oracle.com/epmos/faces/BugDisplay?_afrLoop=185783879448816&id=222553&_afrWindowMode=0&_adf.ctrl-state=1a9g740e9n_4

Snippet:

To do so, you may need to toggle the Standard Submission field in the Define
Concurrent Program form (from Yes to No and back to Yes).  Then you should be
able to re-enter the parameters.

PL/SQL Compiler Limits

Came across this piece in PL/SQL Language Reference:

https://docs.oracle.com/database/121/LNPLS/limits.htm#LNPLS018

Table C-1 PL/SQL Compiler Limits

Item Limit
bind variables passed to a program unit 32768
exception handlers in a program unit 65536
fields in a record 65536
levels of block nesting 255
levels of record nesting 32
levels of subquery nesting 254
levels of label nesting 98
levels of nested collections no predefined limit
magnitude of a PLS_INTEGER orBINARY_INTEGER value -2147483648..2147483647
number of formal parameters in an explicit cursor, function, or procedure 65536
objects referenced by a program unit 65536
precision of a FLOAT value (binary digits) 126
precision of a NUMBER value (decimal digits) 38
precision of a REAL value (binary digits) 63
size of an identifier (characters) 30
size of a string literal (bytes) 32767
size of a CHAR value (bytes) 32767
size of a LONG value (bytes) 32760
size of a LONG RAW value (bytes) 32760
size of a RAW value (bytes) 32767
size of a VARCHAR2 value (bytes) 32767
size of an NCHAR value (bytes) 32767
size of an NVARCHAR2 value (bytes) 32767
size of a BFILE value (bytes) 4G * value of DB_BLOCK_SIZE parameter
size of a BLOB value (bytes) 4G * value of DB_BLOCK_SIZE parameter
size of a CLOB value (bytes) 4G * value of DB_BLOCK_SIZE parameter
size of an NCLOB value (bytes) 4G * value of DB_BLOCK_SIZE parameter
size of a trigger 32 K

SAVE EXCEPTIONS and SQL%BULK_EXCEPTION

CREATE TABLE exception_test (
  id  NUMBER(10) NOT NULL
);

set serveroutput on;

DECLARE
TYPE exc_tab
IS
TABLE OF exception_test%ROWTYPE;
l_exc exc_tab := exc_tab();
l_error_count NUMBER;
E_EXC_ERRORS EXCEPTION;
PRAGMA EXCEPTION_INIT(e_exc_errors, -24381);
BEGIN
— Fill the collection.
FOR I IN 1 .. 100
LOOP
L_EXC.extend;
l_exc(l_exc.last).id := i;
END LOOP;
–Error Statments stated below
L_EXC(50).id := NULL;
L_EXC(51).id := NULL;
–End of Error Statments
EXECUTE IMMEDIATE ‘TRUNCATE TABLE exception_test’;
— Perform a bulk operation.
BEGIN
FORALL i IN l_exc.first .. l_exc.last SAVE EXCEPTIONS
INSERT INTO EXCEPTION_TEST VALUES l_exc
(i
);
EXCEPTION
WHEN e_exc_errors THEN
l_error_count := SQL%BULK_EXCEPTIONS.count;
DBMS_OUTPUT.put_line(‘Number of failures: ‘ || l_error_count);
FOR i IN 1 .. l_error_count
LOOP
DBMS_OUTPUT.put_line
(
‘Error: ‘ || i || ‘ Array Index: ‘ || SQL%BULK_EXCEPTIONS(i).error_index || ‘ Message: ‘ || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)
)
;
END LOOP;
END;
END;
/

Associative Array Example

DECLARE
–– Define a varray of twelve strings.
TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
–– Define an associative array of strings.
TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR) INDEX BY BINARY_INTEGER;
–– Declare and construct a varray.
month MONTHS_VARRAY :=
months_varray(‘January’,’February’,’March’,’April’,’May’,’June’
,’July’,’August’,’September’,’October’,’November’,’December’);
–– Declare an associative array variable.
calendar CALENDAR_TABLE;
BEGIN
–– Check if calendar has no elements, then add months.
IF calendar.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE(‘Assignment loop:’);
DBMS_OUTPUT.PUT_LINE(‘–——––––––––’);
FOR i IN month.FIRST..month.LAST LOOP
calendar(i) := ”;
DBMS_OUTPUT.PUT_LINE(‘Index [‘||i||’] is [‘||calendar(i)||’]’);
calendar(i) := month(i);

END LOOP;
–– Print assigned element values.
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE(‘Post-assignment loop:’);
DBMS_OUTPUT.PUT_LINE(‘––––––––––———–‘);
FOR i IN calendar.FIRST..calendar.LAST LOOP
DBMS_OUTPUT.PUT_LINE(‘Index [‘||i||’] is [‘||calendar(i)||’]’);
END LOOP;
END IF;
END;

/