if i have a function
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?
Been trying to concatenate the following two select statements (to then pass as a parameter in DML statement) without much luck.
Select lpad(ST_DAY,'2',0) || '/' || lpad(ST_MONTH,'2',0) from RET_FORMATS;
Select to_char(sysdate,'YYYY')"Year" FROM DUAL;
Is it possible to have $ substitutions in Select statements ?
For example
Select * from my_table where ID in ${ID_LIST} and DAY >= to_date(${SOME_DATE})
$ID_LIST = (100,200,300)
$SOME_DATE = 10-10-2011 12:12:00
For the ID_LIST is using Prepared statements with ?,?,? the way to go ? Or are there are any Define we can do in SQL plus for this substitions ?
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.
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.
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
customer_id,
customer_first_name,
customer_last_name,
drug_id,
drug_name,
prescription_unit
i think i need that information for the select but I cant seem to write a select statement that runs without errors.
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
SELECT NAME,
To_char(Decode(unit, 'bytes', VALUE / ( 1024 * 1024 ),
VALUE), '999,999,999.9') VALUE,
[Code]....
Apart from the little problem I have with number 2, not figuring out how to get the precentage seeing that all the data is store in the field VALUE.
I would also like to create a view that has both 1 and 2 in it, but whenever I try creating it, I got and error stating and INTO clause is expected.
how can i insert and update to a table in oracle database 10g through a select statement. not using merge.
View 2 Replies View Relatedive 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
Customer_Email Other_Email Customer_Whitemail Other_whitemail Customer_telephone Other_Telephone Cheque_total
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.
select PERIOD_NAME "Month/Year",segment1 "Company" ,Segment6 "GL Account", currency_code "Currency",Mmt "Movement",
BEGIN_BALANCE+PERIOD_DR-PERIOD_CR "Balance At Date"
FROM
(select gb.period_name "PERIOD_NAME",decode (gcc.segment1, '20', 'Internode', '10', 'Agile') SEGMENT1,
gcc.segment6 "SEGMENT6", gb.currency_code "CURRENCY_CODE", gb.begin_balance_dr "BBDR", gb.begin_balance_cr "BBCR",
[code].....
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
I want to merge the following two sql statements into single output.
select id,count(*) from derailed where changed_on between to_date('26-july-13 18:30:00','DD-Mon-YY hh24:MI:SS') and to_date('01-August-13 18:29:00','DD-Mon-YY HH24:MI:SS') group by id;
select id,code from dbo;
I am using oracle 10g i have a table on my computer that i made for a friend when i load it on their computer the select statements say no data found if i use select * from table name all the data will show
if a column name select * from table name where duty_date = '05-JAN-11'no data found
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.
Current database scn is changing faster even though no application,no transaction is running. Database is idle but scn is changing continue, why?
17:25:00 SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2477896
1 row selected.
[code]....
I have a BEGIN Block of the code where a loop is running .I also have a CONTINUE statement in the EXCEPTION Block of the code where I return the control to the BEGIN Block in case of any exception.Now as per ORACLE Company, CONTINUE has a old issue which is already registered by Oracle Company Bug 7306422.
The issue is where CONTINUE statement does not run properly when your code Optimization level is 2. However it will work properly if we have the Optimization Level as 0 and 1.are facing the same issue here and have a workaround.
Is there any code which I can permanently use instead of CONTINUE but have the same logic incorporated.i.e returning to the next iteration whenever I say to pass the control.
I am trying to execute the below package. While executing i face a problem where when NO DATA FOUND the excpetion is handled and coming out of the loop.but i want to to continue the loop after handling the exception.
Is there anyway i can modify the code
CREATE OR replace PACKAGE BODY pkg_purge_archive_check
AS
PROCEDURE Purge_archive_tables_check (purgerows IN NUMBER)
IS
v_num_1 NUMBER(10);
v_num_2 NUMBER(10);
v_multiplier NUMBER(10);
[code].....
i've a problem regarding my code at line: 76. URL....
If i put a RAISE_APPLICATION_ERROR just before:
SELECT ID_GIOCATORE INTO CONTR_GIOCATORE2
FROM COMP_CONTR_GIOCATORE_PARTITA GC JOIN CONTRATTO C ON GC.ID_CONTRATTO = C.ID_CONTRATTO
WHERE GC.ID_PARTITA = :NEW.ID_PARTITA AND (C.ID_CASELLA = 28 OR C.ID_CASELLA = 12) AND C.ID_CASELLA <> :NEW.ID_CASELLA AND
[code]....
But if i put the RAISE_APPLICATION_ERROR just after this statement and before IF(CONTR_GIOCATORE2 IS NOT NULL AND CONTR_GIOCATORE2 = CONTR_GIOCATORE) THEN, nothing happens after the insert(that goes well) and the trigger doesn't do his job(insert,update etc). if i do that select, i got the no data found, so i put the exception to set the variable CONTR_GIOCATORE2 to NULL.
I continue to get compile errors attempting a While Loop. Should I being using a For Loop?
DECLARE
v_item VARCHAR2(20) := &v_item;
v_cost vm_inventory.cost%TYPE;
v_expense CHAR(1) := '$';
loop_count Binary_Integer := 1000;
BEGIN
SELECT item, cost
[code]....
I was prompted by an error in the middle of Oracle 11g Release 2 installation and unable to continue further.
The error was file not found e:product11.2.0.dbhome_3oc4jj2eeoc4j_applicationsapplications em.ear.
I run procedure on toad 10.5, it gave me this error
Buffer overflow, limit of 20000 bytes
then it can not continue running the procedure.
Can we execute more than one insert statements at a time (eg 10) in database and givecommit at the end of insert statements or else give a commit one by one after each insert statements ?
View 8 Replies View RelatedWhen i am trying to install oracle database 10g, it is giving following error.Error in writing to directory :
c:Users:
ensure that this directory is writable and has atleast 55MB of disk space ,installation cannot continue.
I know, importing a *.CSV-table is easy using a few clicks with the GUI, but I want to know, how to import and create new tables, using the existing *. CSV-files, with SQL statements.
how to import a .CSV-file using SQL-statements?
I have a column in a database that contains both numerical and char data. I would like to be able to do two different things (two different queries)
1. divide the numerical data in the column by 10 and leave the char data alone (just return it)
2. detect the numerical data in the column and treat is as a different value so I can run averages & counts on it while disregarding the char data
I'm not at all sure how to do number 2. I thought a CASE statement would work for number 1, but then I realized CASE doesn't like different datatypes:
select
case when '1234' = 'checked' then 'checked'
when '1234' = 'gen.nograde' then 'gen.nograde'
when '1234' = null then null
else '1234'/10 end as "GRADE"
from dual
Error report:
SQL Error: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s"
I have a read access on all the 200+ tables in a schema, How can i generate 200+ .sql files with insert statements on all the tables of schema.
View 13 Replies View Related From database server, I need to monitor the details about the sql statements which are being currenlt running in client machines.
I tried with V_$SQLTEXT view where I can only see the SQL statements, hash value,address,SQL_id. but I'm not able to get the user name,name the client machine .
find out these details?.Which Data Dictionary i need to use ?.
I have a question regarding the SQL statements embedded behind the Self Service Pages in Oracle Applications.
Taking an example of Oracle E-business Suite, Is there a way to check what SQL statement is hard coded or embedded in a particular required Self Service Page?
I'm in a bit of a pickle with some SQL syntax, and while my Oracle-fu is weak, an associate with SQL skills is also stuck. I am developing a database for my department that is backed by Oracle Enterprise 11g and the front end is ChemAxon (basically, chemistry database software). The short of it is that I have one large table with every compound from different groups. Each row has a flag (column PI) on it that indicates which group it belongs to (let us say 'Smith' and 'Jones' for example). The software can apply row-level filtering which will basically only show the rows that a particular username are allowed to see.
I have a FLAG_TABLE table, which contains two columns: USERNAME and FLAG. An example set up is this:
USERNAME FLAG
------------- ----------
smith_minion Smith
jones_minion Jones
jones_minion Smith
The software automatically applies a SQL filter that begins with:
SELECT DISTINCT CHEMAXON.STRUCTURES."ID" FROM CHEMAXON.STRUCTURES WHERE
I can set up filtering to work dandy, such that when smith_minion logs in, he can only see rows with the Smith flag (or jones_minion can see both Jones and Smith) by using the filter:
STRUCTURES.PI IS NULL or STRUCTURES.PI IN(SELECT ALL FLAG from FLAG_TABLE where USERNAME = '__IJC_USERNAME__')
('__IJC_USERNAME__' is how the third party software passes the logged in username into SQL)
But we have a new problem: there also exists a master user (chemaxon) who needs to see every row no matter what the flag. The row filtering is applied no matter who logs on, so we need to set up the SQL filter to basically say "If chemaxon, then select all rows, otherwise, select rows based on the username". This is proving a problem as the select statement must be prefaced with SELECT DISTINCT CHEMAXON.STRUCTURES."ID" FROM CHEMAXON.STRUCTURES WHERE.
I've tried using DECODE in a few capacities, but I am always thwarted. My last attempt was:
STRUCTURES.PI IS NULL or STRUCTURES.PI IN (DECODE('__IJC_USERNAME__','chemaxon',(!='something'),(SELECT ALL FLAG from FLAG_TABLE where USERNAME = __IJC_USERNAME__));
But this throws a ORA-00936 missing expression error, with a * under the != portion (I test it by replacing '__IJC_USERNAME__' with 'CHEMAXON').