Forms :: Generate Query Result In Oracle 10g

Apr 3, 2013

Is there any way to Create Form in which i give a query and in result it return the query result in detail block in form of fields just like we return in toad or pl/sql developer.

View 3 Replies


ADVERTISEMENT

Forms :: How To Generate XML Data Of SQL Query Through 10g

Jul 19, 2013

I have got requirement to generate a .XML format of a select query in a particular folder in server. The XML coding of a select query i got through DBMS_XMLGEN.getXML package.

SELECT DBMS_XMLGEN.getXML('SELECT * FROM EMPL_NEW') FROM dual

but through forms i am unable to create it!! and the result output should be saved as .xml in a particular folder in server.

View 3 Replies View Related

SQL & PL/SQL :: Query Result More Row Return In Oracle

Apr 3, 2013

I have a view base on this query returned more rows returned.

select *
From (
Select c_code ,
from_date,
c_range ,
[code].........

View 5 Replies View Related

PL/SQL :: Oracle 11g / R2 Query Result Cache - Incremental Update

Oct 15, 2012

In Oracle 11g/R2, I created replica of HR.Employees table & executed the following statement (+Although using SUM() function is non-logical in this case, but just testifying the result+)

STEP - 1

SELECT      /+ RESULT_CACHE */ employee_id, first_name, last_name, SUM(salary)*
FROM           HR.Employees_copy
WHERE      department_id = 20
GROUP BY      employee_id, first_name, last_name;

EMPLOYEE_ID      FIRST_NAME LAST_NAME     SUM(SALARY)
-------------------------------------------------------------------------------------------------------
202           Pat           Fay          6000
201           Michael           Hartstein     13000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3837552314
--------------------------------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT      | | 2 | 130 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE      | 3acbj133x8qkq8f8m7zm0br3mu | | | | |
| 2 | HASH GROUP BY      | | 2 | 130 | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL     | EMPLOYEES_COPY | 2 | 130 | 3 (0)| 00:00:01 |
  
  Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
*690* bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

STEP - 2

INSERT INTO HR.employees_copy
VALUES(200, 'Dummy', 'User','Dummy.User@email.com',NULL, sysdate, 'MANAGER',5000, NULL,NULL,20);

STEP - 3

SELECT      /*+ RESULT_CACHE */ employee_id, first_name, last_name, SUM(salary)
FROM           HR.Employees_copy
WHERE      department_id = 20
GROUP BY      employee_id, first_name, last_name;

EMPLOYEE_ID      FIRST_NAME LAST_NAME SUM(SALARY)
--------------------------------------------------------------------------------------------------
202      Pat      Fay      6000
201      Michael      Hartstein      13000
200      Dummy User      5000

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3837552314

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |          | 3 | 195 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE | 3acbj133x8qkq8f8m7zm0br3mu | | | | |
| 2 | HASH GROUP BY | | 3 | 195 | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMPLOYEES_COPY | 3 | 195 | 3 (0)| 00:00:01 |
    
 Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
*714* bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

In the execution plan of STEP-3, against ID-1 the operation RESULT CACHE is shown which shows the result has been retrieved directly from Result cache. Does this mean that Oracle Server has Incrementally Retrieved the resultset?

Because, before the execution of STEP-2, the cache contained only 2 records. Then 1 record was inserted but after STEP-3, a total of 3 records was returned from cache. Does this mean that newly inserted row is retrieved from database and merged to the cached result of STEP-1?

If Oracle server has incrementally retrieved and merged newly inserted record, what mechanism is being used by the Oracle to do so?

View 2 Replies View Related

Forms :: How To Generate Excel In Oracle 11g

May 4, 2012

I had a form in oracle 6i with which I can generate an excel sheet when clicked a button(by using ole2 built in)...this form I have converted to oracle forms 11g and replaced the ole2 with client_ole2 and added webutil library...but the excel is not generated as in 6i.

View 1 Replies View Related

Backup & Recovery :: Generate Xml From Oracle Forms 6i

Mar 23, 2012

I am using oracle 9i database in my project. I am trying to generate an xml from oracle 9i.

I am creating a procedure but i got an error like this.

Debugger connected to database.
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 121
ORA-06512: at "SYS.UTL_FILE", line 293
ORA-06512: at "SYS.TEST", line 30
ORA-06512: at line 2

View 1 Replies View Related

Forms :: How To Generate Numbers Automatically In Multirecord Table Using Oracle 6i

Sep 28, 2013

I have two control block i.e. class_register and student_info. In which student_info is multi-record block which contains stud_id, stud_name, stud_roll_no. Based on the class_register block, stud_id and stud_name is generated in the student_info table. Now I want to generate stud_roll_no automatically until the last record is present.

View 4 Replies View Related

Forms :: Migrating From Oracle 8i To 10g - How To Get Result In Ascending Order

Aug 31, 2010

I have migrated from Oracle 8i (8.1.7) to Oracle 10g, but when I execute a query in 8i without any order by clause, I get a result in ascending order. The same query when executed in 10g gives a result which is not ordered. How to get an order result in 10g. There are many forms and reports which use lov which are not ordered. Can I set the ordering at the database, so that I do not have to alter all the forms and reports.

I have also migrated my forms from 5 to 6, but the combo box in some forms in 6i do not appear at run time. How I can solve this problem. I have attached an forms5 .fmb file/.

View 7 Replies View Related

Storing Select Query Result Into Array And Using It In Another Query?

Aug 7, 2009

I am looking to simplify the below query,

DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)

Since both the inner queries are same,I want to extract out to a local variable and then use it.

Say,

Array var = SELECT ID FROM B WHERE BID=0;

And then ,

DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)

How to do this using SQLPLUS?

View 8 Replies View Related

PL/SQL :: Query To Get Result Set

Feb 12, 2013

I want to run below query to get the result set that I am after. But It takes long time even with the indexes...Here in IM_Mapping table is having 1.7 mio records and T_Extract table about 35000 records. All the other tables having below 1000 records

SELECT DISTINCT T_Extract.SerGrp, T_Extract.SerCat, T_Extract.Component, T_Extract.Mod_Item ID,
     " ##" AS IM, IM_Mapping.TrfCode, IM_Mapping.CatCode, IM_Mapping.CatDescr, IM_Mapping.SubCatCode, IM_Mapping.SubCatDescr,
    LKP_Item.Group, LKP_Item.Modality Source
    FROM ((LKP_Product
[code]....

T_Extract.Component
T_Extract.Mod_Item ID
T_Extract.SerCat
T_Extract.SerGrp

Just want to know if there is any possibility of running the query with all the Groups in one go

Even if I run group wise it took 30 min query still running without any results...Is there anyway of restructuring the query for better performance.

View 1 Replies View Related

SQL & PL/SQL :: Query To Generate SET NEWNAME Command

Apr 7, 2013

I want to run a query to generate command to rename ASM files:

This is how the ASM data looks:

+DATA/db1/datafile/report.180.248540158

This is how I want the command to look like:

SET NEWNAME FOR DATAFILE 1 TO '/u02/data/dev/db1/report.180.dbf';

How can I write the query, using SUBSTR and INSTR to generate the command:
e.g. - SELECT 'SET NEWNAME FOR DATAFILE '||FILE#||' TO '''||'/u02/......

To look like:

SET NEWNAME FOR DATAFILE 1 TO '/u02/data/dev/db1/report.180.dbf';

View 7 Replies View Related

Concatenating Result Of Query

Aug 30, 2007

My scenario is something like this:

MyTable

Rownum Colval
1 23
2 34
3 45

I need to write a query which will get me output: 233445, i.e. all the three rows concatenated. How can it be done? I want to do it through sql only and not to use PL/SQL. Is this possible?

View 2 Replies View Related

SQL & PL/SQL :: Query Result Into Excel Or CSV

Jun 15, 2011

I have a sql query which fetch the data from 4 different tables. I want to write the output of that query into a excel or a CSV file without using TOad and all. Let me know is it possible via creating function or procedure.

View 4 Replies View Related

SQL & PL/SQL :: Query Result In One Line

Aug 29, 2011

I have a select that return the query like this:

EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br
434Ronaldo 11 21454117compras2@gralha.br
434Thiago 13 25418745thiago.alcarin@br.com.br

so,I need the query result in just one line...

EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br 434Ronaldo 11 21454117compras2@gralha.br

I saw some exemples of "pivot query" but I still looking for a solution.

View 10 Replies View Related

SQL & PL/SQL :: Different Result On Every Execution Of Query?

Jan 13, 2011

when I am executing the below query getting diffrent count every time and not able to guess what is happening.

SELECT
count(1)
FROM table1 where table1.LAST_UPDATE_DATE >= current_date - interval '9' day

View 6 Replies View Related

SQL & PL/SQL :: Query Yields No Result

Mar 20, 2012

in my table there is a row as below.

CITY_ICITY_NST_PROV_CPSTL_CUPDT_USER_IUPDT_TSUPDT_PGM_I
342DC79a03654822-FEB-12 02.02.13.410000000 PMOMR

when i use below below query, there are no values returned.

select city_i from city_e where (trim(city_n))= 'DC' and st_prov_c=79 but while i use below query, im bale to get the result

select city_i from city_e where city_n='DC' and st_prov_c=79

View 5 Replies View Related

Result Of Expensive Query

Jul 11, 2012

I need a a query which will give the result of most expensive query i.e. which is taking most of I/O /memory.

View 3 Replies View Related

SQL & PL/SQL :: Wrong Result For Query With Like And %?

Jul 21, 2012

I have a strange problem with query with like and %.

When I run this script:

ALTER SESSION SET NLS_SORT = 'BINARY_CI';
ALTER SESSION SET NLS_COMP = 'LINGUISTIC';
-- drop table test1;
CREATE TABLE TEST1(K1 NVARCHAR2(80));
INSERT INTO TEST1 VALUES ('gsdk');

[code]....

I get this:

K1
ŁFa
ła
Śab <- WRONG
Śrrrb <- WRONG
4 rows selected

When i change datatype in column to varchar2 this code work correct.

The execution plan:

PLAN_TABLE_OUTPUT
SQL_ID d3d64aupz4bb5, child number 2
select * from TEST1 where k1 like N'Ł%'
Plan hash value: 4122059633
Id Operation Name Rows Bytes Cost (%CPU) Time
0 SELECT STATEMENT 2 (100)
* 1 TABLE ACCESS FULL TEST1 1 82 2 (0) 00:00:01

[code]....

View 7 Replies View Related

SQL & PL/SQL :: Query To Generate Report For Shopping Store

Mar 25, 2013

I've below test case

CREATE TABLE MYMERCHANT
(
SRNONUMBER(5),
PARENTSRNONUMBER(5),
LEVELNONUMBER(5),
LEVELNAMEVARCHAR2(25)
) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 1, NULL, 101, 'HQ1' ) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 2, 1, 102, 'DIV1' ) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 3, 1, 103, 'DIV2' ) ;
[code]........

Structure is like above. There is a Head Quarter (HQ1) under which we have different division like (DIV1 and DIV2). Under division we have different location where our shopping stores are operating and transactions are done (LOC1, LOC2 for DIV1) and (LOC3 for DIV2) respectively.

DESIRED OUTPUT
User can generate reports at two levels i.e. Head Quarter level and Division Level.

When user wants report at Head Quarter Level :
Page 1 (Should Display as below)
HQ1 DIV1 LOC1 450
HQ1 DIV1 LOC2 400
HQ1 DIV2 LOC3 600

Page 2 (Should Display as below)
HQ1 DIV1 LOC1 450
HQ1 DIV1 LOC2 400

Page 3 (Should Display as below)
HQ1 DIV2 LOC3 600

When user wants report at Division Level : eg DIV1
Page 1 (Should Display as below)
HQ1 DIV1 LOC1 450
HQ1 DIV1 LOC2 400

View 2 Replies View Related

SQL & PL/SQL :: How To Execute One Query Result In Another At Runtime

Dec 14, 2011

I have a query, when i run this this will give another sql statement. I want run this dynamically...

How to proceed?

View 10 Replies View Related

SQL & PL/SQL :: Why Sub Query Factoring Produces Different Result

Aug 19, 2010

select 1 from dual
where 1 >= any (select 1 as col from dual
union all
select 1 from dual
union all
select 1 from dual);

Output:
------
1
-
1
[code]....

Why the sub query factoring produces different result.Is sub query factoring can be a problem and leads to different result?

View 27 Replies View Related

SQL & PL/SQL :: Inserting Result Set Of Query Into Another Table

Jul 24, 2012

CREATE TABLE tbl_emp
(
name VARCHAR2(20),
price NUMBER,

[Code]...

NAME PRICE DATAENTRD ROWRANK

aaa 9999 24.07.2012 05:56:00 1

aaa 10000 24.07.2012 05:55:58 2

I want to insert this result into another table, how can I do it??

Quote:TABLE

CREATE TABLE tbl_emp_result_set
(
name VARCHAR2(20),
price NUMBER,
dataEntrd date,
rowrank number
)

View 8 Replies View Related

PL/SQL :: Send Query's Result Via Utl_stmp?

May 25, 2013

11.2.0.3 got the send mail code from [URL]

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
p_from      IN VARCHAR2,
p_subject   IN VARCHAR2,

[Code].....

/I want to call send_mail to pass a query output to "p_message": as example: select sid, program,module,machine from v$session where program='XXX';

And have been struggling due to lack of experience in PLSQL.

View 18 Replies View Related

Globalization :: Wrong Result For Query With Like And %?

Jun 28, 2012

I have a strange problem with query with like and %.

When I run this script:

ALTER SESSION SET NLS_SORT = 'BINARY_CI';
ALTER SESSION SET NLS_COMP = 'LINGUISTIC';
-- SELECT * FROM NLS_SESSION_PARAMETERS;
-- drop table test1;
CREATE TABLE TEST1(K1 NVARCHAR2(80));

[code]....

When i change datatype to varchar2 this code work correct.

The execution plan:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID d3d64aupz4bb5, child number 2
-------------------------------------
select * from TEST1 where k1 like N'Ł%'

[code]....

Note - dynamic sampling used for this statement (level=2)

View 2 Replies View Related

Forms :: F11(Query Mode) And Execute Query In Oracle?

Jul 17, 2011

how to achieve F11(Query mode) and Execute Query in Oracle Forms?

View 1 Replies View Related

Which Background Process Is Responsible For Getting Result Of SQL Query

Jul 21, 2011

Which background process is responsible for getting result of sql query issued to an oracle engine?

View 1 Replies View Related

SQL & PL/SQL :: Query Result Into XLS File With Column Names?

Oct 6, 2012

I want to load query result into .xls file with column names by using plsql.

Like this i need to generate 4 files and zip it then send mail with attachement of that zip file.

Can we automate in PLSQL? If it is possible pls share the script.

Ex: select ename,eno,dept,deptno,sal,hiredate from employee;

ENAME ENO DEPT DEPTNO SAL HIREDATE like this we need to store the query result into .xls file.

View 1 Replies View Related

SQL & PL/SQL :: Query Where Condition Has 4 Rows In Table But Result Is Zero Row

Oct 29, 2012

There are 4 rows in table with stat_flag 'Updated Record' and stat_date with todays date.

stat date has date & time both, for that reason just trying to format with yyyy.mm.dd

I am getting zero rows as result.

where STAT_FLAG = 'Updated Record' and to_date(stat_date,'yyyy.mm.dd') = to_date(sysdate(),'yyyy.mm.dd')

View 4 Replies View Related

SQL & PL/SQL :: Check Datatype Of Result From Select Query?

Jul 7, 2011

I have this query

SELECT wmsys.wm_concat (gp.prog_acronym)
FROM inf_program gp, ea_audit_program ap
WHERE ap.sys_prog_id = gp.sys_prog_id
AND ap.sys_audit_id =484

is there any way I can check the datatype of the result of the above query ? ,my dba added some patch to oracle , after the patch this query is returning a clob in java , it should return string and it used to return string before patch and in other databases it returns string, I can check the return type only from java side , is there any way oracle can say me the datatype ?

View 13 Replies View Related

SQL & PL/SQL :: Show Result Of Query Just Like Pivot Table?

Jul 20, 2011

me in building a query. I want to show the result of the query just like pivot table.
Test case
CREATE TABLE CPF_YEAR_PAYCODE
(
CPF_NO NUMBER(5),
INC_DATE DATE,
PAYCODE_TYPE CHAR(1 BYTE),

[code]...

I want that my query should look like the format as attached in the xls sheet.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved