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 would like to add another filed into the Employee Self Service page (OAF) Expense claim portion.Basically the field would require to grab an existing GL account code based on the element link setup from expense element.
The main point to add this additional field is to capture the expenses type and displaying its GL account code based on element link account code setup.
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 Relatedi have a html button. With the following attributes: onclick="return false" class="upd_description" A dynamic Action is listening on this button.
Event: Click
Selection-Type: jQuery Selector
JQuery Selector: .upd_description
static_URL: f?p=&APP_ID.:306:&SESSION.::&DEBUG.:306:P306_SER_SK:&P305_SER_SK.
as defined in my button attributes.
Button and dynamic action are on page 305. I can now open Page 306 as modal window but the page will close immediately.
We are using Application Express 4.1.0.00.32 on Wondows XP and Oracle version is 10g. I've created a report in APEX and the user is insisting on putting a header on each page in PRINT of the report which should contain the "from date" and "to date", which are present on the select criteria on top.
Can I somehow add from_date and to_date items in Title so that when they print the report, they'll see the from and to dates in the title?Or is there someother way to do this. The report is an interactive report with a control break and user prints the report with file->print.
In the login page, I want to add a text "Register" that when clicked will send the user to another page to register.
With a button, its simple because its inbuilt there,but I do not want a button,just this: Register
I want also to have the text in color not black.
I am new to APEX and I am developing an application to manage Oracle database user accounts. I have a page (8) that has text items for the username and the database name. I have a submit button (this works fine) that will issue the ALTER USER... statement to reset the user's password and unlock the account. I have another button that should:
get the value of the username - P8_USERNAMEassign it to an application item - FIND_USER_IN_DBredirect to page in application - to page 11
The issue is that FIND_USER_IN_DB does not get populated and page 11
which is a simple report with the following query:
SELECT *FROM valid_dbWHERE username = :FIND_USER_IN_DB;
How do I accomplish this?
All the reports are skipping one extra page after printing of last page irrespective of number of pages in the report. if same report file is generated and given to printer using dos or linex dosen't skips extra page at end. It happens only if i print the report through ORARRP. The report file extension is '.rrpt'.
View 2 Replies View RelatedI have different public page (page1) and different page for authorized user(page5).Upon successful login after process validation ,computation it should navigate to authorized user page 5 but the branching does work it keeps on going to page 1 .
View 8 Replies View RelatedNeed to create a report based on date ranges and for this created a interactive report and two page item datepicker fields P15_fromdate and p15_todate. Report works fine with this criteria.But user wants one more field quarter(P15_quarter), When they select the quarter the range values has to get reset and as to get applied to report.
Issue here is unable to find a way to set the page range item values based on the quarter field selection
how to reset the page item fields.
I have a report created in Reports6i. I have two fields at the bottom
1.Page Total
2.Voucher Total
I do not want to print the Page Total if the total number of pages = 1.
How can I achieve this?
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 RelatedBeen 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;
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'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').
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 ?
The link from where I can get syntax of all the commands available in oracle?
View 6 Replies View RelatedMy teacher taught the lesson of DML statemnts, he told us how does merge works , but he did not give us any query for that,provide query for Merge and if possible then explain it too , I am using Oracle 10g Sql Plus.
View 3 Replies View RelatedDDL statements automatically end with COMMIT the user transactions in which they appear. Foe example:
-------------------------------------------
create table mytable01 (i integer);
insert into mytable01
select 1 from dual;
create table mytable02 (i integer);
-------------------------------------------
After all three statements are executed, data are committed in mytable01.
In the Oracle DB server SQL guide we read:
"DDL commands, such as TRUNCATE, will fail if there is any DML command active on the table. A transaction will block the DDL command until the DML command is terminated with a COMMIT or a ROLLBACK."
But I executed the following without any problem:
-------------------------------------------
create table mytable (i integer);
insert into mytable
select 1 from dual;
commit;
update mytable
set i = 2;
alter table mytable add (j integer);
-------------------------------------------
So where's the truth? Are DDL statements blocked when they refer to an active object accessed from a DML or not?
Can we have 2 insert statements for 1 for loop? how can we have 2 insert statements into 1 for loop!
FORALL j IN stu.FIRST .. stu.LAST
insert into CHASSISM_test
(make, stu, invoiceno, gross_tot, discount)
values
(make(j), stu(j), INVOICEno(j), GrossTot(j), discount(j));
[Code]...
I am new to oracle programming.
I have a statement below
The statement below correctly filters the records that I require.. I now want to;
Count the number of records that meet the criteria and Delete the records that meet the criteria
select
sl.project_code,
sl.COST_code,
sl.category_code,
sl.supplier_code,
[Code]....
I don't have development env .create and insert statements work below for sample data:
View 8 Replies View RelatedI 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.
Recently we have upgraded from 11.1 to 11.2 . But after upgrade SQL statements that are running fine in 11.1 was running for hours in 11.2. Statistics are collected 100%...
View 2 Replies View Relatedconnect the following concepts/information I've been collecting. This is not my field but I'm interested in filling some of mine conceptual/technical gaps.
From a JDBC perspective, one of the benefits of Prepared (and so Callable) statements have over the regular ones is that the statement is "compiled"(*) once and then reused (performance gain).
(*) for SQL statements: building of parse tree and exec.plan
In which way can this notion be extrapolated to invocation of Oracle Stored Procedures through CallableStatements? (After clearing my doubts, I may end concluding that the only relevant feature of CallableStatements is their capacity to deal with stored procedure invocations)
According to procedure's precompiled execution plan SQL compilation implies execution plans generation PL/SQL compilation implies P-code generation and, SQL statements (from PLSQL code) are treated no differently by Oracle than SQL from Java or C/C++. These SQLs will be parsed and execution plans for those SQLs created.
...
When the PL code executes the SQL statement, only then does the SQL engine receive the SQL, parse it, and create an execution plan for it.
Therefore, even when the stored procedure can be parsed and cached in SGA (through the OracleConnection.preparedCall("proc") invocation), the SQL statements won't be effectively compiled until they are executed, right? And going deeper, will those SQL statements be cached to be reused in future invocations of the containing stored procedure? Is this a characteristic of the regular stored procedure execution in Oracle? or is it due to the CallableStatement "origin"?
It started out pretty simple where I had to update about 40 contacts in the database and I would have 40 separate update statements I would run. The task has jumped to about 300 contacts and I don't want to run 300 update statements. I would like to run this all at once. For example:
update contact
set name = 'Name1'
where row_id = 'row_id1'
update contact
set name = 'Name2'
where row_id = 'row_id2'
update contact
set name = 'Name3'
where row_id = 'row_id3'
My DB is oracle 10, and TOAD is version 9.
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.