Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 16 hours 7 min ago

Query never finish without clear reason

Wed, 2020-10-21 01:06
Some times an aleatory select statement stop working without a clear reason. Some times the statement is inside procedure, some times it is executed directly from ODAC FOR .NET 4 Then only thing in common it was always executed by ODAC client, so I never got this problem from one of my jobs ! When I check gv$sesion the session is active When I check plan using DBMS_XPLAN.DISPLAY_CURSOR I it is the best one When I simulate via pl/sql the query works fine with the very same plan When I kill the session and execute again from the ODAC , the same problem happen When I kill the session, SYS.DBMS_SHARED_POOL.PURGE(ADDRESS || ', ' || HASH_VALUE, 'C'), and execute again from the ODAC then BINGO the problem is SOLVED. ...however we know that it will happen again ... What kind of evidence am I missing? did you ever see this kind of behavior?
Categories: DBA Blogs

Complete transient session for testing

Wed, 2020-10-21 01:06
We have looked at dbms_flashback and dbms_wm but both don't deliver what we actually want: Is there a possibility to make an entire session transient? We would like to use this for our testing scenario's: 1. Start the session in "transient mode" 2. execute a lot of pl/sql with commits, rollbacks and savepoints 3. drop/stop the session ....and everything is magically back to before point 1. dbms_flashback.ENABLE_AT_SYSTEM_CHANGE_NUMBER is almost there by showing the data at a specified scn, but then you have to reverse every table that is hit by the -third party- code by hand. Not feasible in our environment I'm not very optimistic, but perhaps I missed a new capability of the DB. Is there a way?
Categories: DBA Blogs

Select for update statement too slow

Wed, 2020-10-21 01:06
Hi Connor, Chris, I have a FOR UPDATE SQL used to lock certain rows in ORDERS table but it seems to be bit slow (takes around 1 min). I tried getting plan from dbms_xplan.display_awr. Could you please give me some lead from your past experience and I can look for the any SQL tuning stuff. <code> SELECT PT.ORDER_ID FROM STAGING_001 PN JOIN GTT_TAB IDS ON IDS.MSG_ID = PN.MSG_ID, XMLTABLE ( 'hsbcEnvelope/hsbcMessageBody/pymtTran' PASSING PN.XML_MSG COLUMNS REF_001 VARCHAR2 (50 CHAR) PATH 'REF_001', REF_002 VARCHAR2 (50) PATH 'REF_001', REF_003 VARCHAR2 (10 CHAR) PATH 'REF_001') PMT, ORDERS PT WHERE 1 = 1 AND ( ( PMT.REF_002 IS NOT NULL AND PMT.REF_001 IS NOT NULL AND PMT.REF_002 = PT.REF_002 AND PT.REF_001 = PMT.REF_001 AND NVL (PMT.REF_003, :B1) = PT.REF_003) OR ( PMT.REF_002 IS NOT NULL AND PMT.REF_002 = PT.REF_002 AND NVL (PMT.REF_003, :B1) = PT.REF_003) OR ( PMT.REF_001 IS NOT NULL AND PT.REF_001 = PMT.REF_001 AND NVL (PMT.REF_003, :B1) = PT.REF_003) ) FOR UPDATE OF PT.ORDER_ID NOWAIT; </code> <code> ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 27043 (100)| | | | | 1 | FOR UPDATE | | | | | | | | | 2 | BUFFER SORT | | | | | | | | | 3 | CONCATENATION | | | | | | | | | 4 | NESTED LOOPS | | 1003 | 1935K| 11972 (1)| 00:00:01 | | | | 5 | NESTED LOOPS | | 2940 | 3930K| 210 (1)| 00:00:01 | | | | 6 | NESTED LOOPS | | 10 | 13630 | 13 (0)| 00:00:01 | | | | 7 | INDEX FAST FULL SCAN | SYS_C006227 | 10 | 130 | 2 (0)| 00:00:01 | | | | 8 | TABLE ACCESS BY INDEX ROWID | STAGING_001 | 1 | 1350 | 2 (0)| 00:00:01 | | | | 9 | INDEX UNIQUE SCAN | PK_STG_INT | 1 | | 1 (0)| 00:00:01 | | | | 10 | XPATH EVALUATION | | | | | | | | | 11 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS | 1 | 607 | 4 (0)| 00:00:01 | ROWID | ROWID | | 12 | INDEX RANGE SCAN | IDX_PT_REF_001 | 1 | | 3 (0)| 00:00:01 | | | | 13 | NESTED LOOPS | | 1011 | 1950K| 14172 (1)| 00:00:01 | | | | 14 | NESTED LOOPS ...
Categories: DBA Blogs

How can I do a variable "in list"

Wed, 2020-10-21 01:06
I have a simple stored procedure, that I would like to have a passed in string(varchar2) for used in select from where col1 in (var1) in a stored procedure. I've tried everything but doesn't work. Followed is my proc. Thanks CREATE OR REPLACE PROCEDURE WSREVSECT_5 pSectNos varchar2, pRetCode OUT varchar2 ) AS nCount number; BEGIN SELECT count(fksrev) into nCount FROM SREVSECT WHERE sectno IN (pSectNos ) /* as in 'abc', 'xyz', '012' */ ; pRetCode:=to_char(ncount); End;
Categories: DBA Blogs

How to return header and data using sys_refcursor in oracle pl sql

Tue, 2020-10-20 07:06
I want to return headers of column along with data while returning result using refcursor. <code> create table fetch_header_rows ( company_name varchar2(500), Company_id number, ammount number(20,8), data_commnets varchar2(500) ); insert into fetch_header_rows values('company1' , 1, 128.80,'test data1'); insert into fetch_header_rows values('company2' , 2, 129.80,'test data1'); insert into fetch_header_rows values('company3' , 3, 128,'test data1'); insert into fetch_header_rows values('company4' , 4, 100.80,'test data1'); create or replace procedure SP_fetch_header_rows(data_fetch out sys_refcursor ) as begin open data_fetch for select * from fetch_header_rows; end; </code> Here we are fetching cursor result in file. hence we required header as a first row in data. ( in current scenario we have more than 150 columns)
Categories: DBA Blogs

Generate string based on pattern

Tue, 2020-10-20 07:06
Hi Chirs, Connor, Could you please help or suggest a way to generate string based on pattern Pattern - <b>^[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[89ab][a-f0-9]{3}-[a-f0-9]{12}$</b> I need to generate millions of string like <b>f9f8c8e2-0b20-4160-8f74-e836f4661fc5</b> matches with the pattern. e.g. <code>SELECT * FROM dual WHERE REGEXP_LIKE('f9f8c8e2-0b20-4160-8f74-e836f4661fc5', '^[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[89ab][a-f0-9]{3}-[a-f0-9]{12}$');</code>
Categories: DBA Blogs

Update rows when cursor returns no data

Tue, 2020-10-20 07:06
Purchase_Mas contains data of purchase master and payment_amt contains data of payment received from party. Cursor c2 does not return any value when not found in payment but still i want to some calculation happen even data not found in payment table. run following script and when you fire a query " select * from purchase_mas_tmp ; " Look at ( ID =5 and party code=12 ) and ( ID = 6 and party code= 14 ) when payment amount not found in cursor c2 but i want to os_amt display as 10000 for 5 and 20000 for 6 id of payment. so how its possible <code>create table PURCHASE_MAS ( id NUMBER, party_code NUMBER, total_pcs NUMBER, total_amt NUMBER, purchase_date DATE, reg_flg CHAR(1), discount_amt NUMBER ); create table PAYMENT ( id NUMBER, party_code NUMBER, payment_date DATE, payment_amt NUMBER ); create global temporary table PURCHASE_MAS_TMP ( id NUMBER, party_code NUMBER, total_pcs NUMBER, total_amt NUMBER, purchase_date DATE, reg_flg CHAR(1), payment_date DATE, payment_amt NUMBER, os_amt NUMBER, discount_amt NUMBER ) on commit preserve rows; insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (5, 12, 456, 10000, to_date('01-08-2018', 'dd-mm-yyyy'), 'Y', 100); insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (6, 14, 878, 20000, to_date('21-08-2018', 'dd-mm-yyyy'), 'N', 200); insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (1, 11, 457, 30000, to_date('11-08-2018', 'dd-mm-yyyy'), 'Y', 300); insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (2, 12, 658, 40000, to_date('10-08-2018', 'dd-mm-yyyy'), 'Y', 400); insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (3, 11, 1454, 50000, to_date('07-08-2018', 'dd-mm-yyyy'), 'Y', 500); insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (4, 13, 1254, 60000, to_date('18-08-2018', 'dd-mm-yyyy'), 'N', 600); insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT) values (1, 11, to_date('01-09-2018', 'dd-mm-yyyy'), 2500); insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT) values (2, 12, to_date('12-09-2018', 'dd-mm-yyyy'), 3000); insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT) values (3, 11, to_date('11-09-2018', 'dd-mm-yyyy'), 30000); insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT) values (4, 13, to_date('21-09-2018', 'dd-mm-yyyy'), 400); declare cursor c1 is select id, party_code, total_pcs, total_amt, purchase_date, reg_flg , discount_amt from purchase_mas; cursor c2 is select id, party_code, sum(payment_amt) payment_amt from payment group by id, party_code ; begin for i in c1 loop insert into purchase_mas_tmp (id, party_code, total_pcs, total_amt, purchase_date, reg_flg,discount_amt) values (i.id, i.party_code, i.total_pcs, i.total_amt, i.purchase_date, i.reg_flg, i.discount_amt); end loop; for i in c2 loop update purchase_mas_tmp tbl set payment_amt = nvl(i.payment_amt,0), os_amt = tbl.total_amt - nvl(tbl.discount_amt,0) - nvl(i.payment_amt,0) where id = i.id and party_code = i.party_code ; end loop; end; -- select * from purchase_mas_tmp ; /* drop table PURCHASE_MAS purge ; drop table PAYMENT purge ; drop table purchase_mas_tmp purge ; */</code>
Categories: DBA Blogs

Oracle Processes consuming 100% CPU

Mon, 2020-10-19 12:46
The server machine on which the DB resides has 32 CPUs. (2 sockets * 8 cores per socket * 2 threads per core) I notice many oracle processes (both non-background and background) consuming high (sometimes 100%) of the CPU. Now, this CPU % is only for 1 CPU out of the total 32 in the server. And once a process hits 100% of CPU consumption, the process cannot take resources from other CPUs. (please correct me if I am wrong) Right now, THREADED_EXECUTION parameter is set to FALSE. I was thinking, if we can enable multi threading in the Database, then, may be the process that is hitting 100% and is looking for more CPU can take it from the other CPUs. Is this a good idea? If yes, then how should we enable multi threading in the DB and what is the possible impact on the DB? Please Note: This is a single instance DB (non-RAC) OS: SUSE Linux Enterprise Server 12 Thanks in Advance, Abhishek, Kolkata, India
Categories: DBA Blogs

Oracle TNS poison attack vulnerability

Mon, 2020-10-19 12:46
Hi Team We are running Non-RAC Oracle 11.2.0.3.0 and the TNS poison attack vulnerability (Oracle Security Alert for CVE-2012-1675 - https://www.oracle.com/security-alerts/alert-cve-2012-1675.html) looks affected this version of Oracle. What we have done: * Have applied the latest version of Oracle critical patches update (July/2015) on 11.2.0.3.0 What we are expecting: * We hope applying the latest CPU (July/2015) for 11.2.0.3.0 can fix it and no further actions required. My question is: * Do we still need to follow the steps in <Using Class of Secure Transport (COST) to Restrict Instance Registration (Doc ID 1453883.1)> mentioned in Oracle Security Alert for CVE-2012-1675 to fix this issue? Best Regards
Categories: DBA Blogs

DBMS_ASSERT returning the ORA-44002: invalid object name exception for existing database links and directories

Mon, 2020-10-19 12:46
Hi, In my procedure I'm trying to check whether a database link and a directory exist. If they don't I wanted to display a nice message about them needing to be created etc. I thought of using dbms_assert.sql_object_name, this seems to do the trick for tables, views, functions but not for database links or directories. Here is my test case (not my actual procedure, but I have the same issue) The table, view, function return the name / don't error when running the dbms_assert part. The database link, directory returns "ORA-44002: invalid object name" -- table <code>create table test_tbl (x number); select table_name from user_tables where table_name = 'TEST_TBL'; select sys.dbms_assert.sql_object_name('TEST_TBL') from dual;</code> -- view <code>create view test_vw as select * from test_tbl; select view_name from user_views where view_name = 'TEST_VW'; select sys.dbms_assert.sql_object_name('TEST_VW') from dual;</code> -- function <code>create or replace function test_f return date is dt date; begin dt := sysdate; return dt; end; select object_name from user_objects where object_name = 'TEST_F'; select sys.dbms_assert.sql_object_name('TEST_F') from dual</code>; -- database link <code>create database link test_link connect to user123 identified by user123 using 'dwh'; select db_link from user_db_links where db_link = 'TEST_LINK'; select sysdate from dual@test_link; select sys.dbms_assert.sql_object_name('test_link') from dual; select sys.dbms_assert.sql_object_name('TEST_LINK') from dual;</code> -- directory <code>create directory test_dir as '/apps1/oradata/big_dump'; select directory_name from all_directories where directory_name = 'TEST_DIR'; select sys.dbms_assert.sql_object_name('test_dir') from dual; select sys.dbms_assert.sql_object_name('TEST_DIR') from dual;</code> Thanks Nick
Categories: DBA Blogs

load date into apex 20.1 with defaults for some columns

Fri, 2020-10-16 10:06
I want to insert data from excel or csv file (Id, Name, Address, Amount) into database table by using data load but during the insert of this data i want to decide this data is related to HSBC Bank (Because i have many bank and each bank have thousand of customer). so how i can insert the name of HSBC bank to the table during the load of data (Note that the excel or csv file did not contain the name of HSBC bank).
Categories: DBA Blogs

Oracle .DBF file format structure

Thu, 2020-10-15 15:46
Hi, i want to iterate all rows in table and do some operations on them, but i don't want to get data with some select statement. In order to do that i tried to export data as .dbf and read it with Java and do some stuff. But it seems Oracle .dbf format differs than xBase (https://www.clicketyclick.dk/databases/xbase/format/) .dbf format. Where can i get Oracle .dbf data file structure details. Or can you give me more suitable techniques and advice in this purpose. Thank you.
Categories: DBA Blogs

Does Oracle 10g database UTL_HTTP support TLS 1.2 ?

Thu, 2020-10-15 15:46
Hi everyone Thanks in advance. I am trying to connect HTTPS API from PLSQL procedure in Oracle 10g database. I would like to connect with the server which accepts TLS 1.2 1) Does Oracle 10g support TLS 1.2 connectivity ? 2) Is there a patch which enables TLS 1.2 in Oracle 10g database. ? 3) Connecting through JVM in Oracle database 10g is not an option as our java version is java4. Any other solution as a work around kindly post . Note: I have gone through following websites link: 1) Oracle product support forum (which needs login for seeing the resource) <code> https://www.oracle.com/webfolder/community/oracle_database/3926485.html</code> 2) Burleson Consulting wesbite which states Oracle 10g supports TLS ssl protocol <code> http://www.dba-oracle.com/10g_transport_layer_security.htm</code> Thanks Naveen Mani
Categories: DBA Blogs

Homepage URL without "/apex/workspace/r/app"

Thu, 2020-10-15 15:46
Hi Tom and team, let's suppose my public website, built on Oracle Apex of course and running on a shared host, is at www.example.com Unfortunately, any new customers visiting "www.example.com" will be immediately redirected to a URL of something like "www.example.com/apex/workspace/r/exampleapp/app_homepage" (assuming we use friendly URLs). Is there any way to display the URL as just "www.example.com", at least for the home page? Even something like "www.example.com/app_homepage" would be great. All guidance much appreciated! Many thanks, Greg https://www.comparefootballodds.co.uk/ PS I've seen some discussion of masking, but that has various downsides (e.g. poor for SEO, and unable to bookmark pages).
Categories: DBA Blogs

In-database row archival and compression

Thu, 2020-10-15 15:46
In Oracle 12c 12.1 version when a table is compressed, we cannot do a row archival upon that In Oracle 19c, if a table is compressed, we can do row archival , Why it is so And what's the method to do indatabase archiving for compressed tables in 12c And what would be its rolling back scripts. if don't need archiving for a compressed table
Categories: DBA Blogs

In database row archival

Thu, 2020-10-15 15:46
I do have a question about Oracle Indatabase archiving in Oracle 19c, given below table has a XMLtype column alter table HTS_SUMMARY_DATA_CELL row archival Error report - SQL Error: ORA-38353: This table type is not supported by the ROW ARCHIVAL clause. This error comes. The same Indatabase archiving in Oracle 12c,12.1.0.2 alter table HTS_SUMMARY_DATA_CELL row archival The table gets archived. What is the differnce there in 12C and 19C versions,
Categories: DBA Blogs

Transforming multiple rows into a single row with multiple columns, without aggregation

Wed, 2020-10-14 21:26
Hello, Is it possible to write a single SQL query that would concatenate multiple rows into a single row with multiple columns? For example, I have a table with the following data: <code> TYPE DATE AMOUNT -------------------------------- B 30.6.2019 15 B 30.11.2019 20 C 22.12.2019 17 </code> And I want a query that would return a single row: <code> TYPE1 DATE1 AMOUNT1 TYPE2 DATE2 AMOUNT2 TYPE3 DATE3 AMOUNT3 ------------------------------------------------------------------------- B 30.6.2019 15 B 30.11.2019 20 C 22.12.2019 17 </code> The column names don't really matter, I just need to combine the original three rows into a single row with multiple columns, so I can't use concatenation. I know that there will always be 3 rows in the original table, so there's not going to be a million columns in the final result. For performance reasons, I would like to avoid using PL/SQL if at all possible, and just do it in a single query, but I'm not sure if it's possible. Thank you!
Categories: DBA Blogs

Replace hardcoded values in case statement with reference table lookup

Wed, 2020-10-14 21:26
I've a requirement to convert hardocdings in below case statement used in sql to a lookup reference table . The case statement refers to 4 columns i.e enterprise,pbm_vendor,bhc_relationship_type,payer_entity I would like to create a lookup table using these 4 columns as distinct values and an additional column say regional_payer to return the value. I'm facing issues due to different conditions is the each when clause and the use of like operator. Kindly assist. <code> CASE WHEN CLM.ENTERPRISE = 'Harvard Pilgrim' THEN 'Harvard Pilgrim' WHEN CLM.ENTERPRISE = 'Blue Cross Blue Shield of Massachusetts' THEN 'Blue Cross Blue Shield of Massachusetts' WHEN CLM.ENTERPRISE = 'Blue Cross & Blue Shield of Rhode Island' THEN 'Blue Cross & Blue Shield of Rhode Island' WHEN CLM.ENTERPRISE = 'Tufts Health Plan' THEN 'Tufts Health Plan' WHEN CLM.ENTERPRISE = 'Blue Cross Blue Shield of Michigan' THEN 'Blue Cross Blue Shield of Michigan' WHEN CLM.ENTERPRISE = 'Health Delegates' THEN 'Health Delegates' WHEN CLM.ENTERPRISE = 'Highmark Inc.' THEN 'Highmark Inc.' WHEN CLM.ENTERPRISE = 'Spectrum / Priority Health' THEN 'Spectrum / Priority Health' WHEN CLM.ENTERPRISE = 'UPMC Health Plan' THEN 'UPMC Health Plan' WHEN CLM.ENTERPRISE = 'Independence Blue Cross' THEN 'Independence Blue Cross' WHEN CLM.ENTERPRISE = 'Geisinger Health Plan' THEN 'Geisinger Health Plan' WHEN CLM.ENTERPRISE = 'Capital BlueCross' THEN 'Capital BlueCross' WHEN CLM.ENTERPRISE = 'Horizon BlueCross BlueShield' THEN 'Horizon BlueCross BlueShield' WHEN CLM.ENTERPRISE = 'Excellus BlueCross BlueShield' THEN 'Excellus BlueCross BlueShield' WHEN CLM.ENTERPRISE = 'EmblemHealth' THEN 'EmblemHealth' WHEN CLM.ENTERPRISE = 'Independent Health' THEN 'Independent Health' WHEN CLM.ENTERPRISE = 'HealthNow New York' THEN 'HealthNow New York' WHEN CLM.ENTERPRISE = 'MVP Healthcare' THEN 'MVP Healthcare' WHEN CLM.ENTERPRISE = 'Federal Employee Program' THEN 'Federal Employee Program' WHEN CLM.ENTERPRISE = 'Blue Cross and Blue Shield of North Carolina' THEN 'Blue Cross and Blue Shield of North Carolina' WHEN CLM.ENTERPRISE = 'BlueCross BlueShield of South Carolina' THEN 'BlueCross BlueShield of South Carolina' WHEN CLM.ENTERPRISE = 'Carefirst BlueCross BlueShield' THEN 'Carefirst BlueCross BlueShield' WHEN CLM.ENTERPRISE = 'Anthem' AND UPPER(CLM.PAYER_ENTITY) LIKE '%NORTH CAROLINA%' THEN 'Anthem(NC, SC, MD, VA, WV)' WHEN CLM.ENTERPRISE = 'Anthem' AND UPPER(CLM.PAYER_ENTITY) LIKE '%SOUTH CAROLINA%' THEN 'Anthem(NC, SC, MD, VA, WV)' WHEN CLM.ENTERPRISE = 'Anthem' AND UPPER(CLM.PAYER_ENTITY) LIKE '%MARYLAND%' THEN 'Anthem(NC, SC, MD, VA, WV)' WHEN CLM.ENTERPRISE = 'Anthem' AND UPPER(CLM.PAYER_ENTITY) LIKE '%VIRGINIA%' THEN 'Anthem(NC, SC, MD, VA, WV)' WHEN CLM.ENTERPRISE = 'Anthem' AND UPPER(CLM.PAYER_ENTITY) LIKE '%WEST VIRGINIA%' THEN 'Anthem(NC, SC, MD, VA, WV)' WHEN CLM.PAYER_ENTITY = 'Florida Blue' THEN 'Florida Blue' WHEN CLM.ENTERPRISE = 'Anthem' AND CLM.PAYER_ENTITY LIKE '%Georgia%' THEN 'Anthem Blue Cross and Blue Shield of Georgia' WHEN CLM.PBM_VENDOR = 'ProCare Rx' THEN 'ProCare' WHEN CLM.ENTERPRISE = 'BlueCross BlueShield of Tennessee' THEN 'BlueCross BlueShield of Tennessee' WHEN CLM.ENTERPRISE = 'Blue Cross Blue Shield of Arkansas' THEN 'Blue Cross Blue Shield of Arkansas' WHEN CLM.ENTERPRISE = 'Blue Cross and Blue Shield of Alabama' THEN 'Blue Cross and Blue Shield of Alabama' WHEN CLM.ENTERPRISE = 'Blue Cross & Blue Shield of Mississippi' THEN 'Blue Cross & Blue Shield of Mississippi' WHEN CLM.PAYER_ENTITY LIKE '%Public Education Em...
Categories: DBA Blogs

insert only one row on IG report depends on value of page item

Wed, 2020-10-14 03:06
Hi everyone, I have a challenge case as: I have a select list page item and one IG report. I want to when I choose a page item valued by 'W', it allows IG report only one row. While I choose another value, it still allows many rows for IG report. I try after submit with code : <code>Declare v_count number; Begin select count(*) into v_count from E_EDUFIT_MARKS_DETAIL md, e_edufit_marks m where md.marks_id = m.marks_id and m.type_marks = 'W' AND md.marks_id = :P2645_MARKS_ID; if v_count > 1 then return 'FALSE'; end if; end;</code> I think it may be successfully when one row is created before. I wonder that when create new records, user click add row (over 2 rows) it wil be not run. I have not found a way to solve that problem. I hope you help me have a good idea. Thank in advance.
Categories: DBA Blogs

Oracle 19c SE2 Installation

Wed, 2020-10-14 03:06
OS: Windows Installation: Silent Installer(Using a response file) Oracle 19c SE2 Installation creates three files(TNSnames.ora, listener.ora and SQLNet.ora) in its default location (Unzip Location\network\admin). If Unzip Location is C:\dbhome then these files location would be C:\dbhome\network\admin. But if an environment variable(TNS_ADMIN) is set then it creates these three files over there. Is there a way, it should always create in its default location rather TNS_ADMIN one.
Categories: DBA Blogs

Pages