Server Utilities :: Possible To Configure Auditing To Get Number Of Rows Returned By Select Statements
Aug 22, 2011
I am importing some data from Oracle into another database on a regular basis. It works fine for most of the queries but couple of queries don't work sometimes (random). I don't get any errors or any data.
We switched on the Oracle auditing to find out the queries being sent to oracle db. We can see all the queries in the Audit log. Is it possible to configure Auditing to get the "Number of Rows" returned by Select statements so that we can be sure that some data was returned.
I'm attempting to audit unsuccessful Select statements in order to trap a problem we're experiencing with our application. I have set the AUDIT_TRAIL initialization parameter to DB_EXTENDED, and bounced our database.
I've issued the AUDIT SELECT ANY TABLE WHENEVER NOT SUCCESSFUL command, and when I issue a SELECT statement as an application user, nothing appears in SYS.AUD$ even though the application has issued a select statement which returned no rows.
I'm working with sqlldr and i try to insert data from a csv file to a CTL file. One field of my table contains 5 characters but one row has 6 characters in this field, so it's rejected by oracle. (Logical, you can't insert 6 chars in a 5 chars field)
an error is visibly returned, so i wondered how you could catch the value of this error?is it a code? a message?
I'd like to add to my script a condition so that the end of the script would continue even if this error code is returned for that CTL execution.
I have an export dumpfile and when I am importing it in the new database I want to see all the SQL statements including "CREATE", "ALTER", and "INSERT". I am able to see CREATE and ALTER by using the option SHOW=Y in my import statement (IMP) but not able to see the INSERT commands.
Is there any way I can see INSERT statements as well.
I have a column COL1 in table TAB1 which is varchar2. I want select only rows which has number and not alphanumeric value? I don't want to use regexp for this since
FOOTBALL PLAYERS < ID, NAME, ATTRIBUTE> 100-JIM-TALL 101-BOB-STRONG 102-MARK-SMART ...etc
I want to form a query that regardless of the total returned records, I will be able to specify how many of each "kind" of players I want returned. There are several good reasons that it has to be one query and not many.
I am facing a problem in bulk insert using SELECT statement.My sql statement is like below.
strQuery :='INSERT INTO TAB3 (SELECT t1.c1,t2.c2 FROM TAB1 t1, TAB2 t2 WHERE t1.c1 = t2.c1 AND t1.c3 between 10 and 15 AND)' ....... some other conditions.
EXECUTE IMMEDIATE strQuery...These SQL statements are inside a procedure. And this procedure is called from C#.The number of rows returned by the "SELECT" query is 70.
On the very first time call of this procedure, the number rows inserted using strQuery is 70. But in the next time call (in the same transaction) of the procedure, the number rows inserted is only 50.And further if we are repeating calling this procedure, it will insert sometimes 70 or 50 etc. It is showing some inconsistency.On my initial analysis it is found that, the default optimizer is "ALL_ROWS". When i changed the optimizer mode to "rule", this issue is not coming.I am using Oracle 10g R2 version.
I need to create a function where in data from 5 rows is clubbed into one row. Like this I have around 425 rows which should be clubbed to 85 rows. Requirement is similar to pivot but not exactly like a pivot as different columns need to be taken from those 5 rows. This is for reporting purpose in order to get data in the desired report format.
SQL mentioned below works fine. It does return data.When below code is used as a normal procedure with OUT parameter as Index by table of Record type code works fine. It returns data. Functionality is met. But when used as a pipeline function, it returns no data.
Below code gets compiled but returns nothing. I didn't find anything on Google or any website for same.
I have employees under a supervisor defines as below:
select LOGIN_USER_ID from APPWMS.VIEW_EMP_LATEST_INFO where SPVSR_LOGIN_USER_ID='erbrand' and EMP_STAT_CODE='ACTIVE'
Now I need to determine if all above employees are clocked in , clocked out or not clocked between yesterday and today using following:
select to_char(CLOCK_IN_DATE,'dd-mon-yyyy hh24:mi:ss' ) ClockIn,to_char(CLOCK_OUT_DATE,'dd-mon-yyyy hh24:mi:ss' ) ClockOut ,LOGIN_USER_ID,--CLOCK_IN_DATE,CLOCK_OUT_DATE, CLOCK_OUT_DATE-CLOCK_IN_DATE,trunc(sysdate) , trunc(sysdate-1), case when CLOCK_OUT_DATE is null then 'Not clocked out' else
[code]....
The first SQL gives me 66 rows while second gives me 40 rows. For 26 people , no rows are returned which means these people donot have a clock in record between two timestamps.
How can we modify query to show those 26 people as 'Not clocked In'
1 select s.reg_no,s.course_code, 2 s.section src_sec,a.section a_sec,a.att_date,a.att_flag 3 from attendance a ,src s 4 where a.semester_code=1 5 and a.semester_year=2013 6 and s.semester_code=1 [code]....
I have a requirement like getting list of values from one table and inserting them into another table.I have tried with sub querying but didn't worked out because the select query is returning multiple values.
how to proceed further and the ways how can I write this requirement.
is it possible to use the records returned by a query as column names in a select query.
select (select column_name from dba_tab_cols where table_name='V_$DATABASE' and column_name like '%CONTROL%') from v$database; * ERROR at line 1: ORA-01427: single-row subquery returns more than one row
I Want to make a query to select finished goods product in sales having product code greater than 280 but i have face a problem that order by is not working because products column have character code as well as number. how to sort that column.
when i was loading a large file sqlldr is committing after every record, when i used
OPTIONS(bindsize=20000000, readsize=20000000 , rows=200) LOAD DATA LENGTH SEMANTICS CHARACTER APPEND INTO TABLE TABLE_NAME TRAILING NULLCOLS ( ) still the same.. no change at all..
I have been out of work for 2+ years. Am about to start a job next week doing Oracle back end, Forms, and Reports development among other things. I was asked if I could take a look at 3 report requirements and give an estimate on how long it would take to correct errors in these reports. All I have is a user requirement document stating what the report is currently doing and what it should be doing, a partial screen print of an Oracle Form showing correct data, and a sample report page showing incorrect data.
I am finding it rather difficult to give an estimate without seeing tables, relations, code, etc. Is it me or does this seem nearly impossible?I do not have access to their system yet so cannot view the database or run select statements, run the report, etc. All I have are the documents I listed above.
We are doing select statements. I have 3 tables that I need to get information out of and I believe I need to use a join but everything I put into oracle gives me an error.I'm doing the selects for a pharmacy and have a customer table, a drug table, and a prescriptions table.
I need to write a select statement that shows what customers are taking what drugs and how many mgs they take
1. I Wnat to analyze the buffer cache hit ratio. This is what i did.
DECLARE bufcac NUMBER(10, 2); BEGIN
[Code]....
2. I would like to analyze the PGA and determine what percentage out of the maximum allocated PGA is being used. I tried the code below but can't find the percentage. Sad
function test_1 begin if....then ...end SELECT... BULK COLLECT INTO.. FROM .... [code]...
obviously this is not the whole code, but lets say the first if...then..end is true , does this mean it will exit the block? like it won't continue to the other stuff like select, if ...then statements?
ive got two select statements which fetches data from different tables. I need to join the two result set . is it possible to do it from sql. Heres the query.
1)
SELECT COUNT(CASE WHEN (INTERACTION_TYPE= 'EmailED' AND CONTACT_PARTY=1) THEN 1 END)CUSTOMER_EMAIL, COUNT(CASE WHEN INTERACTION_TYPE= 'EmailED' AND CONTACT_PARTY=2 THEN 1 END)OTHER_EMAIL,
[Code]....
2)
SELECT SUM (CHEQUE_TOTAL) CHEQUE_TOTAL FROM RI_CHEQUE_VOUCHER_REFUND refund INNER JOIN CH_CASE case ON (case.id = refund.id) INNER JOIN EVA_ENTITY_DEFINITION ed ON (ed.name= 'ChequeRefundCaseED') WHERE case.creation_time<= SYSDATE AND case.creation_time>= SYSDATE-7
Again I need to combine the resultset.So the result would look like
I used bulk collect and for all statements to select and insert the data in temp table.The select SQl is returning one row. But its not inserting this row into temp table.Its not throwing any exceptions. Used ref cursor because the select statement is going for every cursor.
here modified the code and provided only one cursor.
Create Or Replace Procedure Sales_Hist_Update_Bkp Is Type Type_Name Is Record( Sku_Item_Key Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Sku_Item_Key%Type, Locationno Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Locationno%Type, Bsns_Unit_Key Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Bsns_Unit_Key%Type, Act_Item_Cost_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Item_Cost_Amt%Type, Act_Rglr_Unit_Price_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type, [code]...
I'm trying to retrieve data with multiple select statements. The query works fine for 1 account (segment6) but fails for more than account. Below is the query.
I want to count the batch records using BATCH_ID with CASE statement ,for that i am using below query but its not working ,
SELECT COUNT(*) FROM <TABLENAME> WHERE VNBATCH_ID=CASE WHEN #SDC <10 AND #PERIOD >=10 THEN 0||#SDC||#PERIOD||#BATCH_ID WHEN #SDC <10 AND #PERIOD <10 THEN 0||#SDC||0||#PERIOD||#BATCH_ID WHEN #SDC >=10 AND #PERIOD <10 THEN #SDC||0||#PERIOD||#BATCH_ID ELSE #SDC||#PERIOD||#BATCH_ID END
