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.
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 View RelatedI 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';
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
Below Query I understand is improper wrt syntax. Need to modify query to fetch correct result for me.
select * from tableA order by
case when a is not null then
a DESC
else
b DESC, c DESC END
Note: DESC or ASC is dynamic coming from java-code.
I need to generate a select query in runtime and store the results of it into a file.Each time the column name and table name in the query will differ.Now im able to generate the select query through for cursor but problem is to store the results to the file.I tried using plsql table,im able to get the values to that table and store the results to a file,but the results of the query is more then 10000 lines (it might increase also)where only 4000 characters where able to store in the plsql table.so rest of them are not stored in the file.
View 3 Replies View RelatedI need to generate in report using PL/SQL code for counting the number of rows of all the tables from source and target database..The report should consist of following columns..
table name|source table row count|target table row count|mismatch|..provide me the PL/SQL code?
I wanna to load data in a data block using sql which using multiple table, views based on condition
View 7 Replies View RelatedHow to generate a PAN number by concatenation of data from different tables
View 3 Replies View RelatedI'm new to Oracle Forms 10g.
When I create a data block based on a table using Data Block Wizard, the block's Query Data Source Columns property is automatically populated with column definition entries corresponding to the columns of the base table.
I tried making changes to these entries, for example by changing the data types to wrong data types or even deleting them, and I found that those changes had no effect on the block at all. The form was still working as I wanted.
What is exactly the role of the block's Query Data Source Columns property.
The F1 key help says "The Query Data Source Columns property is valid only when the Query Data Source Type property is set to Table, Sub-query, or Procedure". So, explain in each context of Query Data Source Type.
We have standby dataguard set up namely CRMSTDY of 3 node RAC clusterware. It has been observed that log sequence number 1 is generated since last 4 days on standby database. There is nothing more information in alert_standby.log file and the same file is visible when we checked with ASMCMD utility. Check the attached print sceen and suggest. Below mentioned system information.
DB : Release 10.2.0.4.0
OS : Linux Enterprise release 5.2 (Carthage)
I have two Tables, the one table is called (calves_per_breed) and contains all my query results. I then have another table (calves_per_breed_crosstable) which is used to place the generated count values in calves_per_breed to create a crosstable from the count data into calves_per_breed_crosstable.
I'm using the following procedure to generate the crosstable from the data inside the calves_per_breed table:
PROCEDURE pcalves_per_breed_genCrossTable
IS
BEGIN
---------------------------------------------------------------------------
--SEX CODES
update calves_per_breed_crosstable
set (m, f) = (select count(decode(geslag, 'M', 1, null)),
count(decode(geslag, 'F', 1, null))
from calves_per_breed)
[codee]....
Now this procedure works 100%, the only problem is it generates the ENTIRE table. if you know how a crosstable works, thetop right section of the table is exactly the same as the bottom left section of the table. I wish to optimize my code so that it only generates the values for the needed columns, and not ALL the columns, as values are generated twise now, which increases the query time! Here is a tipical output of the kalwers_per_ras_crosstable:
Note: You will notice that i used a Column called TID with string values to indicate the vertical columns for the crosstable. The Vertical Columns are the same as the top Columns(which are actual columns, and not row values as the vertical columns)
select *from kalwers_per_ras_crosstable:
TID M F NFR A B C SP RED BLACK SC1 SC2 SC3
----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
M 138 0 0 0 0 0 138 122 16 74 64 0
F 0 173 0 0 0 0 173 161 12 92 81 0
NFR 0 0 0 0 0 0 0 0 0 0 0 0
A 0 0 0 0 0 0 0 0 0 0 0 0
[code]....
i want to generate bar code in forms 6i but i don't know the code.
View 3 Replies View RelatedHow can i sort data in the execute Query mode during runtime,i have a columns which has number, char and date field.
This below data is fetched in execute query from a table;
x1 x2 x3
1343 adfa 11/14/2012
1353 adfa 11/11/2012
1333 adfa 11/10/2012
1333 adfa 11/12/2012
1353 adfa 11/09/2012
now i like to sort like this below;
x1 x2 x3
1343 adfa 11/09/2012
1353 adfa 11/10/2012
1333 adfa 11/11/2012
1333 adfa 11/12/2012
1353 adfa 11/13/2012
how to do it?
Is theree any way to generate the insert script (with the data) for an existing table.
In Toad, we can generate the DDL for a particular table. Just curious if it can be done using any Tool.
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 Relatedhow do you generate serial numbers? i have an invoice where i have to add items, so i want the numbering to start from 1 everytime i start a new form and increment by 1 as i i enter a new record. For this i have put my code in when-new-record-instance but problem is if i delete a record and enter a new one, the serial number starts from the nex number.
For ex. if i have 4 items with serial numbers 1, 2, 3, 4 and i delete the item at no. 3 and add a new item , the serial number comes as 5.
proper code to generate the serial numbers?
I want to gerate the excel(Office 2010)file using oracle forms 6i. How we can achieve this with forms 6i?
View 4 Replies View RelatedI am using the following code to generate my report
pat:= :global.path||'us01127p4';
run_report('heboracle1.pace.internal:7778',
'r1',pat,'c:','c:','pdf','pacerep',pl_id,'yes',
'&co_code='||:global.co_code||'&type_sl='||mtype_sl||'&prog_sl='||:global.prog_sl||'&mode='||'default'||'&desname ='||:global.user_fname||'&destype='||'cache'||
'&to_no='||:to_doc_no||'&year='||myear||'&from_no='||:from_doc_no,'no','no','','wide180');
When i set my param form variable as 'Yes' the report parameters window pops up and when i press submit query the report gets generated. But if i set param form to 'No' then i get a msg saying
TERMINATED_WITH_ERROR-Report output aborted ORA-0000
if i put an exception i am getting a same msg.
The same code is working fine for other forms.
I want to generate a PDF report from Form Developer 6i.
View 5 Replies View RelatedI 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
what is this error.it will come on after save record on table through form.
FRM-40654:Record has been updated by another user.Re-query to see change
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 Relatedi have one project about school system.there is one column of total marks.and other one is position column.i want a trigger that system genrate the position against total marks. problem is this when total marks become equal for example 101 and 101 than position also same.but dont now what a trigger i write for this.here some example
if total marks 100 than position is 1stand if total marks 99 than position is 2ndbut third person is also same number in total marks column i mean 99 than in position column must write the 2nd
I need is to generate this chart automatically from the form builder without opening any program like visio (for instant). that's mean when I select the wanted department the chart will be generated automatically .
View 9 Replies View RelatedI have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).
the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?
select customer_id from
(select customer_id from capitalPLAN
where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165')
order by price desc
)
where rownum <= 25
I have a forms that generate a file from database into database server and copy it to client machine by the follow process:
IF webutil_file_transfer.Is_AS_readable(V_server_way || V_file_name) THEN
l_success := webutil_file_transfer.AS_to_Client
(clientFile => :V_screen_way
[code]...
But is showing the erro ora-105100 in one client machine
I have made one application form where users need to enter some data. This data is getting inserted in four tables. As in for now the data is properly getting saved and retrieved only for one user at a time. But problem arrives when more than one users are simultaneously making an entry and saving the data at one time.Same number is getting generated for the users who are saving the data at one time which should not happen.
View 4 Replies View RelatedI have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?
e.g.:
Table emp:
e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800
XML File
<e_id>
143
876
So, I want the output to be:
e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100
how to achieve F11(Query mode) and Execute Query in Oracle Forms?
View 1 Replies View Related