SELECT gjst.user_je_source_name source, ( SELECT gjct.user_je_category_name FROM apps.gl_je_categories_tl gjct WHERE gjh.je_category = gjct.je_category_name AND language = ‘US’ ) category, gjh.name batch_name, gjh.description header_description, gjl.je_line_num, gjl.description line_description, CASE WHEN gjl.entered_dr IS…
Read MoreWe might face some scenarios for convert string data into rows. Here you can separate a string into rows by giving the string separator as input. Users can give any…
Read MoreThis query is used to check the ORA error in past one hour. query: COLUMN ORIGINATING_TIMESTAMP FORMAT A40 COLUMN message_text FORMAT A100 set linesize 300 SELECT ORIGINATING_TIMESTAMP , message_text FROM…
Read MoreThe auditing mechanism for Oracle is extremely flexible. Oracle stores information that is relevant to auditing in its data dictionary. Every time when a user attempts anything in the database…
Read MoreCREATE OR REPLACE PROCEDURE INSERT_SINGLE_BANKS IS x_response_rec apps.iby_fndcpt_common_pub.result_rec_type; l_init_msg_list VARCHAR2 (2000); o_bank_id NUMBER; x_return_status VARCHAR2 (3000); l_msg_count NUMBER; l_msg_data VARCHAR2 (3000); l_msg_dummy VARCHAR2 (3000); l_output VARCHAR2 (3000); l_extbank_rec apps.iby_ext_bankacct_pub.extbank_rec_type; l_ext_bank_branch_rec…
Read MoreAIM: Component is used for Create the Requisition against the Blanket Purchase Agreement For different Locations. SCRIPT: CREATE OR REPLACE PACKAGE BODY APPS.XXTTK_BPO_PKG IS PROCEDURE XXTTK_REQ ( ERRORBUF OUT VARCHAR2…
Read MoreSET SERVEROUTPUT ON DECLARE v_used_bytes NUMBER(10); v_Allocated_Bytes NUMBER(10); BEGIN DBMS_SPACE.CREATE_INDEX_COST ( ‘ create index PROD.INDEX1 on PROD.EMP(EMPNO)’, v_used_Bytes, v_Allocated_Bytes ); DBMS_OUTPUT.PUT_LINE(‘Used Bytes MB: ‘ || round(v_used_Bytes/1024/1024)); DBMS_OUTPUT.PUT_LINE(‘Allocated Bytes MB: ‘…
Read MoreThe data in a GTT is written to the temporary tablespace, which is not directly protected by redo, so using a GTT improves performance by reducing redo generation. Unfortunately, prior…
Read MoreWhat is it? In previous releases the database stored compilation and execution statistics in a shared sql area which is non persistent. Starting in 12c the database can use a…
Read MoreDuring tuning a query, I found one table has stale statistics. While running gather stats for that table, got below error. Let me demonstrate with a demo table: SQL> execute…
Read More