[Oracle Client] Provider not found windows 7 x64 - Oracle Client

Description

    * Update 18 July 2017 ---------

     this is caused by the installation was not correct. for example case your application using ODAC (Oracle Data Access Component) like OLEDB or ODBC, but you install Oracle Client 10G 32bit. the oracle client install successful but the provider not list in unified data link (UDL).
    Or another case like this , when in win XP your application using MSDAORA (it's data component for oracle from microsoft) but in x64 Architecture MSDAORA not support so you must using ODAC for x64. microsoft not support anymore MSDAORA for x64.

Solution :

1. Disable UAC

    - Open User Account Control Settings by clicking the Start button , and then clicking Control Panel. In the search box, type uac, and then click Change User Account Control settings.

    - move the slider to the Never notify position, and then click OK.  If you're prompted for an administrator password or confirmation, type the password or provide confirmation. 
   
    - Don't forget to RESTART THE COMPUTER (it doesn't effect if you not restart).


2. you must install the correct architecture oracle client, if the target machine use x86 architecture use oracle client x86. but if the target machine use x64 architecture use x64 instead. Advised to renew the oracle client form 10G to 11G..

here's the link, before download register account first and accept the agreement on the top of page
x86 or 32bit
Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (32-bit)

x64 or 64bit
Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64).

*path installation example "
d:\oracle\product\11.2.0\client_1\


3. Download Oracle Data Access 64-bit (ODAC x64) DOWNLOAD HERE and extract it (in my case i extract to d:\oracle\odac *you can put anywhere).


4. Open CMD in Administrator Mode and navigate to d:\oracle\odac.

5. Execute command line from CMD :
    install.bat all D:\oracle\product\11.2.0\client_1\ odac
    * wait until finish install

6. After finish navigate D:\oracle\product\11.2.0\client_1\ODP.NET\bin\4\
   execute command :
   OraProvCfg.exe /action:gac /providerpath:D:\oracle\product\11.2.0\client_1\ODP.NET\bin\4\Oracle.DataAccess.dll

7. Next, navigate : D:\oracle\product\11.2.0\client_1\ASP.NET\bin\4\
    execute command :
      OraProvCfg.exe /action:gac /providerpath:D:\oracle\product\11.2.0\client_1\ASP.NET\bin\4\Oracle.web.dll

8. Now setting environment variable path, on system variable :
    Add New :
    ORACLE_HOME : D:\oracle\product\11.2.0\client_1\
    TNS_ADMIN : D:\oracle\product\11.2.0\client_1\network\admin

    Add PATH:  
    xxxxx;%ORACLE_HOME%;%TNS_ADMIN%

9. Change connection provider from "msdora.1" to "oraoledb.oracle"

10. andddddd it's MAGICCC !!!
   

Read more

[Oracle Client] ORA-01019 unable to allocate memory in the user side

Deskripsi
    - This is caused by some of the resources that need by oracle client is protected by operating system (privilage level ?) , usually appear on windows 7 or 8 even the account is Administrator. the solution is simple , just TURN OFF the UAC (User Account Control).

How to 

1. Open User Account Control Settings by clicking the Start button , and then clicking Control Panel. In the search box, type uac, and then click Change User Account Control settings.
2. Do one of the following:
• To turn off UAC, move the slider to the Never notify position, and then click OK.  If you're prompted for an administrator password or confirmation, type the password or provide confirmation. 
• To turn on UAC, move the slider to choose when you want to be notified, and then click OK.  If you're prompted for an administrator password or confirmation, type the password or provide confirmation.

    3. Don't forget to RESTART THE COMPUTER (it doesn't effect if you not restart).

    4. If still occured , reinstall oracle client with UAC off. 

* i tried to figure it out for 3 days LOL :)

Read more

[Database][Oracle] Operator Query

Deskripsi
      Untuk mengetahui selak beluk query pada database oracle. Content Summarize from Oracle.com


* Level Priority (semakin kecil semakin didahulukan)

level 1	Deskripsi Unary + - arithmetic operators, PRIOR operator
2	* / arithmetic operators
3	Binary + - arithmetic operators, || character operators
4	All comparison operators
5	NOT logical operator
6	AND logical operator
7	OR logical operator

** Arithmetic Operators

Operator	Description	Example
+ (unary)	Makes operand positive	SELECT +3 FROM DUAL;
- (unary)	Negates operand	SELECT -4 FROM DUAL;
/	Division (numbers and dates)	SELECT SAL / 10 FROM EMP;
*	Multiplication	SELECT SAL * 5 FROM EMP;
+	Addition (numbers and dates)	SELECT SAL + 200 FROM EMP;
-	Subtraction (numbers and dates)	SELECT SAL - 100 FROM EMP;

** Character Operator

Operator	Description	Example
||	Concatenates character strings	SELECT 'Your name: ' || ENAME FROM EMP;

** Comparison Operator

Comparison operators are used in conditions that compare one expression with another. The result of a comparison can be TRUE, FALSE, or UNKNOWN.

Operator	Description	Example
=	Equality test.	SELECT ENAME "Employee" FROM EMP WHERE SAL = 1500;
!=, ^=, <>	Inequality test.	SELECT ENAME FROM EMP WHERE SAL ^= 5000;
>	Greater than test.	SELECT ENAME "Employee", JOB "Title" FROM EMP WHERE SAL > 3000;
<	Less than test.	SELECT * FROM PRICE WHERE MINPRICE < 30;
>=	Greater than or equal to test.	SELECT * FROM PRICE WHERE MINPRICE >= 20;
<=	Less than or equal to test.	SELECT ENAME FROM EMP WHERE SAL <= 1500;
IN	"Equivalent to any member of" test. Equivalent to "= ANY".	SELECT * FROM EMP WHERE ENAME IN ('SMITH', 'WARD');
ANY/ SOME	Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, or >=. Evaluates to FALSE if the query returns no rows.	SELECT * FROM DEPT WHERE LOC = SOME ('NEW YORK','DALLAS');
NOT IN	Equivalent to "!= ANY". Evaluates to FALSE if any member of the set is NULL.	SELECT * FROM DEPT WHERE LOC NOT IN ('NEW YORK', 'DALLAS');
ALL	Compares a value with every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, or >=. Evaluates to TRUE if the query returns no rows.	SELECT * FROM emp WHERE sal >= ALL (1400, 3000);
[NOT] BETWEEN x and y	[Not] greater than or equal to x and less than or equal to y.	SELECT ENAME, JOB FROM EMP WHERE SAL BETWEEN 3000 AND 5000;
EXISTS	TRUE if a sub-query returns at least one row.	SELECT * FROM EMP WHERE EXISTS (SELECT ENAME FROM EMP WHERE MGR IS NULL);
x [NOT] LIKE y[ESCAPE z]	TRUE if x does [not] match the pattern y. Within y, the character "%" matches any string of zero or more characters except null. The character "_" matches any single character. Any character following ESCAPE is interpretted litteraly, useful when y contains a percent (%) or underscore (_).	SELECT * FROM EMP WHERE ENAME LIKE '%E%';
IS [NOT] NULL	Tests for nulls. This is the only operator that should be used to test for nulls.	SELECT * FROM EMP WHERE COMM IS NOT NULL AND SAL > 1500;

   


** Logical Operator

Operator	Description	Example
NOT	Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.	SELECT * FROM EMP WHERE NOT (job IS NULL)SELECT * FROM EMP WHERE NOT (sal BETWEEN 1000 AND 2000)
AND	Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE; otherwise returns UNKNOWN.	SELECT * FROM EMP WHERE job='CLERK' AND deptno=10
OR	Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise, returns UNKNOWN.	SELECT * FROM emp WHERE job='CLERK' OR deptno=10

** Set Operator

Operator	Description	Example
UNION	Returns all distinct rows selected by either query.	SELECT * FROM(SELECT ENAME FROM EMP WHERE JOB = 'CLERK' UNION SELECT ENAME FROM EMP WHERE JOB = 'ANALYST');
UNION ALL	Returns all rows selected by either query, including all duplicates.	SELECT * FROM(SELECT SAL FROM EMP WHERE JOB = 'CLERK' UNION SELECT SAL FROM EMP WHERE JOB = 'ANALYST');
INTERSECT and INTERSECT ALL	Returns all distinct rows selected by both queries.	SELECT * FROM orders_list1INTERSECT SELECT * FROM orders_list2
MINUS	Returns all distinct rows selected by the first query but not the second.	SELECT * FROM (SELECT SAL FROM EMP WHERE JOB = 'PRESIDENT'MINUS SELECT SAL FROM EMP WHERE JOB = 'MANAGER');

** Other Operator

Operator	Description	Example
(+)	Indicates that the preceding column is the outer join column in a join.	SELECT ENAME, DNAME FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO (+);
PRIOR	Evaluates the following expression for the parent row of the current row in a hierarchical, or tree-structured query. In such a query, you must use this operator in the CONNECT BY clause to define the relationship between the parent and child rows.	SELECT EMPNO, ENAME, MGR FROM EMP CONNECT BY PRIOR EMPNO = MGR;

Read more

[Database][Oracle] Union with Order By

Deskripsi
      sometimes we get confused at the time of the union query with sort order and always ended with "invalid identifier" , where's the problem ?? the problem oracle doesn't know about the field_name. what the solution ?? to identify the field union when using order by just use column index. 

Howto
case :
1. i have the table with different column format, but there's 2 same field and i want to union
2. i want order by y and z column

Column table A : x,y,z
Column table B : s,y,z

the solution
select *
from
(
select x,y,z
from A
union all -- or union
selecy null,y,z -- null means fake column
from B
) data --aliases
order by 2,3 ;
-- 2 and 3 is index column from left : x(1) , y(2) , z(3) .


if you want still use field_name , add it with alias (recommended)
select *
from
(
select x as x_one,
         y as y_two,
         z as z_three
from A
union all -- or union
selecy null,y,z -- null means fake column
from B
) data --aliases
order by data.y_two,data.z_three ;

Read more

[Database][Oracle] Toad not shown hidden record !

Deskripsi
      Kejadian ini terjadi ketika ada hidden record tapi tidak mampu ditampilkan padahal sudah menjalankan syntax seperti di halaman INI dan ketika dicoba di komputer lain yang sudah diinstal toad tapi BISA, aneh kan ? itu kenapa ? apa perlu diinstal ulang ?

Howto
penyebabnya ada di bagian registry
1. Buka registry dan cari Binary Data dengan nama NLS_LANG, klo mau cepat gunakan fitur find (CTRL + F )
2. Jika sudah ketemu pastikan valuenya "AMERICAN_AMERICA.WE8ISO8859P1" (tanpa tanda kutip).
3. Lakukan pencarian kembali dengan pencet F3 dan samakan semua valuenya sampe semua selesai.
4. jika masih tidak bisa berarti penyebabnya bukan di bagian registry.
5. terus gmn dengan value yang sudah keubah ?? instal ulank bro oracle clientnya *kaburrr

Read more

[Database][Oracle] Show View (Hidden)

Deskripsi
      Oracle mempunyai cara untuk membuat record pada view menjadi HIDDEN (tidak bisa dishow), pada artikel ini akan dijelaskan bagaimana membuat agar record tersebut menjadi terlihat, tetapi tidak semua data yang hidden bisa terlihat karena juga ada faktor-faktor lain.

How To

jalankan query di bawah ini,

Recommended Method No.1 (already tested on production server)

1. Using Fnd_client_info
FND_CLIENT_INFO is mainly used to set the organization of the application. ITs something similar to setting the environment in unix box for pareticular Oracle instance. The code below when run, sets the organization to 0 and the datas for the operating unit = 0 will be retrieved from the oracle application views like po_headers,ap_vendors etc.....

Begin

    Fnd_client_info.Set_org_context(0);

End;

Atau / or

2. Using dbms_application_info (Not Recommended, we need view only)

client_info Supplies any additional information about the client application. This information is stored in the V$SESSION view. Information exceeding 64 bytes is truncated.

Note:

CLIENT_INFO is readable and writable by any user. For storing secured application attributes, you can use the application context feature.

Begin

    dbms_application_info.set_client_info('0');
End;

Legend :

* 0 merupakan org_id, organization ID may be vary. 

* this show only temporary , after disconnect you will not able to see the record and you must run that query again

Read more

[E-Bussiness Suite] Mendapatkan File Report dan Memasukan Kembali ke Server

Deskripsi

     Mendapatkan File Report dan Memasukan kembali dari dan ke Oracle E-Bussiness Suite

How To

1. Dapatkan Nama File Report :

- Jalankan Run > Report di oracle , pilih Name Report kemudian catat nama reportnya.

- Buka Concurrent Program (Administrator), FIND kemudian masukan nama report tadi.

- Setelah muncul lihat pada bagian kotak informasi executable, catat nama filenya.

- kemudian buka  Concurrent Program Executable (Administrator) , FIND, masukan nama file tadi.

- Baca keterangan informasi "APPLICATION" untuk menentukan FOLDER di SERVER.

- jika ORACLE PURCHASING => folder codenya PO

    jika ORACLE RECEIVABLE => folder codenya AR
   jika ORACLE PAYABLE => folder codenya AP

2. Buka FTP lewat toad (untuk mendapatkan file report) 

  - Masukan IP xx.xx.xx.xx (Database dan Report) , kemudian masukan username:xxx dan juga 
     passwordnya. setelah itu ganti path ke 
    "/U02/orafin/prodappl/*folder_code*/11.5.0/reports/xxx/US"
    *path may be different

- Dapatkan file reportnya tadi sesuai nama filename reportnya, klik tanda < (download).

3. Edit File Report menggunakan Report Builder 6i / 10i. Setelah selesai di edit jangan lupa 
   di compile di PROGRAM > COMPILE > ALL, kemudian save.

4. Buka FTP toad, buka kembali path directory sama seperti sebelumnya, BACKUP FILE
    ORIGINAL di sisi server production yang ingin di replace dengan cara direname dengan format :
    NAMEFILEORIGINAL_YYYYMMDD (penamaan dari saya :D ) , kemudian klik upload atau >.
    
JIKA MUNCUL TULISAN OVERWRITE PILIH NO, BERARTI FILE PRODUCTION BELOM DI BACKUP !!

5. Report sudah bisa digunakan server PRODUCTION.

6. jangan coba" menjalankan report PO karena ada FLAG DUPLIKASI !!!! dibagian paling bawah report.

Read more

[E-Bussiness Suite] Compile Oracle Forms di Linux

Deskripsi

Mungkin ada yang baru belajar cara membuat atau modifikasi oracle form builder di EBS.  Saya mencoba sharing cara compile oracle forms builder di Linux. Langkah-langkahnya adalah :

How To 

1. Login ke user aplikasi

2. Aktifkan enveroment.

    (Exp :  . /u01/orafin/testappl/APPSORA.env)

3. Copy/ftp file form (xxx.fmb) ke folder aplikasi, biasanya terletak di $AU_TOP. ( /u01/orafin/testappl/au/11.5.0/forms/US/ )

4. Compile form tersebut.

    (format : f60gen module=<formname>.fmb userid=apps/<apps_pwd> output_file=/forms/US/<formname>.fmx )

    (Exp :  f60gen module=//u01/orafin/testappl/au/11.5.0/forms/US/PATESTPR.fmb userid=apps/apps output_file=//u01/orafin/testppl/pa/11.5.0/forms/US/PATESTPR.fmx

* Beberapa path mungkin berbeda, tidak semua sama harap dipahami.

Selamat mencoba.

Read more