I have created two non base table text items(from date, to date) and if values are not entered from front end i.e. null then hadrcoded in pre_query trigger to default values.But even i enter values dynamically from front end and when ever i press fetch button ,it is taking default values i.e. it is not accepting values what i have entered.
View 6 RepliesI am working on form builder 6i. I have a header block A(Multi record block), child block B. Both are non control blocks. My requirement is, if i put the cursor in any header record, i want to auto populate corresponding child record like in master child relation.
which trigger i need to write logic to auto populate the child record.
I have this error. To put it simply I have two blocks.
Block1 contains two drop down list with PL/SQL statements for queries.
Block2 contains tabular form created from block wizard (I tried already in manual)
that will catch the result in Block1 queries.
Now I have a button with a trigger when-button-pressed that contains
BEGIN
INSERT INTO dummy1
VALUES ('hello',1,2,3);
COMMIT;
END;
My goal is to add into dummy1 values from :block2.item_name1, :block2.item_name2, :block2.item_name3 but to put it simply I tried these values but I received the same error.
When I run it and first things first click the button, the values will be added into dummy1 table but when I execute the block1 - dropdown list queries and try to press the button. I received the error.
what executable statements in PL/SQL mean? My objectives are:
1)Writing Executable Statements
2)Create PL/SQL executable blocks using DML and transaction control
I have to explain what exactly each is and what should come under it. what kind of statements and what should be covered under executable statements?
We are having two data blocks as Earnings and Deductions. We need to export this to an excel in single sheet parallel [ imagine your payslip format ].
if we use normal text_io we are not able to get the result we want. so we have tried using a package called export2excel. we achieved what we want. The form is working perfectly in client server concept. When we move the same form to our Unix application server, it is not working.
I have 4 blocks in my form which is basically used for travel booking for the employees in a company within India
1) Header block :- contains info abt the person who is booking the tickets for number of employees. Here i have given booking no a primary key.
2) Employee Detail :- Here the basic info of an employee is entered. Here i have taken booking number as a foreign key and then given emp_cd & booking number as a composite primary key.
3) Travel Detail :- Here the travel detail of individual employee will be entered wherein a unique trv_no will b generated 4 every single travel. Again i have taken foreign key as bkng_no frm 1st blck and emp_cd frm 2nd blck and tkn a composite primary key which comprises of bk_no,emp_cd and trv_no. this is used to maintain the uniqueness for single travel.
4) Vehicle Hotel Details :- This block is placed on different canvas for same form.It is meant for Other details in which details regarding hotel,vehicle etc booking aftr reaching the destination is entered. In this block thrs no primary key, but i hv taken the composite primary key of 3rd block as a foreign key since thr will be multiple entries for this one entire travel.
At every level there will be multiple entries for each corresponding entered record. I am able to enter one single record properly i.e. for 1 emp i am able to enter multiple travel details and his other requirements but asi try to enter more then one employee info, his travel details, other requirements i face an error stating foreign key constraint violated.Parent key not found for 3 rd level block.
How can i get the desired o/p wherein all the multiple records for every single subsequent record are stored correctly taking all the constraints in to consideration.
I want to fetch the data through the cursor and cursor is getting the value of group_code through the variable 'a'. but when i am writing the code like this it is not coming.
My code is like this :
declare
a varchar2(400):='';
cursor c1 is select ref_no,ref_code,company_id from stock_detail where company_id=:global.company_id
[Code]....
FRM-40501: ORACLE error: unable to reserve record for update or delete.
ORA-24374: define not done before fetch or execute and fetch
My master-detail form has single canvas. For both blocks, master and detail, two tables joined together in each. One table to be updated, second table has some info for reference (query only).
I am getting these errors when in detail block the item from LOV is selected for existing record. This does not happen for new record inserted in detail block.
While increasing the tablespace i am getting below error. How to handle this
SQL> set lin 300
SQL> col TABLESPACE_NAME for a25
SQL> col FILE_NAME for a65
SQL> select TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE,sum(BYTES/1024/1024) MB
2 from dba_data_files where TABLESPACE_NAME='SYSAUX' group by TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE order by sum(BYTES/1024/1024) DESC,file_name;
TABLESPACE_NAME FILE_ID FILE_NAME AUT MB
------------------------- ---------- ----------------------------------------------------------------- --- ----------
SYSAUX 3 /ora2/oradata/dbname/sysaux_01.dbf NO 300
SQL> Alter database datafile 3 RESIZE 60000M;
Alter database datafile 3 RESIZE 60000M
*
ERROR at line 1: ORA-01144: File size (7680000 blocks) exceeds maximum of 4194303 block
CODE
Select
Nvl(Sum(DbAmt),0), Nvl(Sum(CrAmt),0)
From FnTrantt A
Where A.GrpCode=:1 And
A.CmpCode=:2 And';
A.DiviCode=:3 And';
A.SubDiVCd=:4 And';
A.FinYear>=''0001'' And';
A.VchDate Between :5 And :6' And
A.GlCode In :7;
[code]....
In The Above mentioned Code, am using bind variables, In variable no. 7 in passing character string ('05124','05125')I am not able to fetch the value,
I am using Oracle 11g
The procedure is compiled without any errors.
The select statement retrieves records well when executed at SQL promt.
But the same records are not displayed in this procedure by fetch from cursor.
The last message in the procedure 'completed' is also displayed.
create or replace procedure disp_rec(vid IN varchar2, vfeat IN number)
is
cursor c1 is select gid, listagg(vindex, ',') within group (order by tid) idx from ridrecords where idarpt=vid and feattype=vfeat group by gid;
type vg is table of number;
type vi is table of varchar2(3500);
[Code]....
i want to use cursor to get data from db to "control block "(db item =no ) this data had where clause depend on item on other block
this my code :
declare
cursor get_sol is
select SOL_STEP,PROB_ID
from MI_SOLUTION
where PROB_ID=:MI_FORM_PROB.PROB_ID;
begin
go_block('control');
[code]......
when am using when_validate_item trigger error raise :
FRM-40737:Illegal restricted procedure next_record in when_validate_item
that's the trigger ? or how to solve ? in case of execute query in what trigger i will write the same code to get data in case of execute query by user .
I have two block....both are the control block.. in first block i select the date and in second block the data of that date is populated.but the data is populated using cursor in when-button-pressed trigger of that first block button.in cursor the data is selected and placed in field of detail block using into clause.... each field and one item of detail block is srno which is create in post-query of detail block using :sysyem.trigger_record.
Now i want after populated the detail block the data is sorted desc one of the field of the detail block..Can this possible using set_block_property() of block although the block is control block if yes where i should do this?
I am interested about the fast way to access all data in physical block. what is the quick way to bring data blocks using the rowid, I found this script but soon as I can have faster access:
select * from table_name t
WHERE ROWID between 'AAAUaOAAEAAHkJiAAA' and 'AAAUaOAAEAAHkJiAA8';
where 'AAAUaOAAEAAHkJiAAA' is the last element in the block and 'AAAUaOAAEAAHkJiAA8' is the first one
my question is can retrieve all the data in one block more quick than this query.
We are suffering from very bad application response for last few days, when i try to check and drill down, where the actual contention is? I came to know that there may be contention on data blocks, which may be a prime reason for degraded performance. Herewith i'm pasting my actual stats of gathered from v$waitstat. I gone through some of asktom docs and find that there may be a problem with freelist or segment space management. My data tablespace is segment space management = Manual.
My main question is
1) Should i need to increase freelist value (Right now my value is 1)
2) Or i have to move on segment space management = auto
SQL> select * from v$waitstat;
CLASS COUNT TIME
------------------ ---------- ----------
data block 2022 4052
sort block 0 0
save undo block 0 0
segment header 1 1
save undo header 0 0
free list 0 0
extent map 0 0
1st level bmb 0 0
2nd level bmb 0 0
3rd level bmb 0 0
bitmap block 0 0
bitmap index block 0 0
file header block 0 0
unused 0 0
system undo header 0 0
system undo block 0 0
undo header 6 0
undo block 0 0
18 rows selected.
If the data blocks in the buffer continuously get updated such that they never reach the Least used list of LRU,then when will they be written to disk?
View 19 Replies View RelatedI have two blocks, both are multi record block. 1st block is control block and second is database block. Both the blocks have same fields(Example: Location,Location_name,Location_Type). In the first block(Control Block) I have check box. My goal is when I check the checkbox and click on add button all the records which are selected in first block should go to second block.
View 3 Replies View Relatedi am using form6i & oracle 8i.
i have master -detail form. both are database blocks.
i have inserted values for the master block bt not for the detail record my problem is.. "user should not be allowed" to move to next record of the master block before saving the current record
I am not able to create control file from scrip . it showing following error
ORA-01081: cannot start already-running ORACLE - shut it down first
CHARACTER SET WE8ISO8859P1
*
ERROR at line 19:
ORA-02236: invalid file name
I tried a lot to load data to table from excel(.csv) using sql*loader the oracle version of sql*loader doesn't support the control file created using notepad(.ctl).Though i given a filename with extension as .ctl it seems as a .txt file. Is there any alternate way to create it?
View 3 Replies View RelatedI HAVE 2 BLOCKS E.G. MASTER-DETAIL AND i have created it manually then how to insert values thrugh these blocks using a button called "save" using "commit_form;" suppose form fields are
master-block:
emp_id
emp name
age
detail block:
address
city
where emp_id is primary key as well foreign key in detail table
how these values will be inserted in both tables
I have two blocks, blockA and blockB, both from the same table which is tableX.
When I query from blockA and it has returned results, then I go to blockB and edit some data and save, there was no problem. But when I don't execute query from blockA or if the query returned zero results, then I go to blockB and edit some data then save, I got this error. THe cursor then go to blockA first item. So now I have to delete that empty row before saving.
i have multi data block filed. and checkbox field which based on control block...My task is when i check checkbox only one field should enabled and my mouse goes to that field
e.g
item11 item21 item31 chkbox1
item12 item22 item32 chkbox2
Scenario like this :
My item field based on data block and checkbox based on control block,while i checked chkbox1 , only item31 on that current record should be enabled and i changed value only on that field
when i checked chkbox1 , my cursor goes to item31...not item32
I have the following data with me
AddId Salaries
10600
10 50
10 400
10 300
10 200
10600
20 200
30200
30 600
30 300
Required output should be
The average of the three highest salaries exceeds 100, only those rows should be visible...wrt each addid.
Table structure:
table x
x1 x2 (Columns)
1 x
2 y
1 z
now i need to o/p like this
1 x,z
2 y
how to fetch data in above format.
create or replace PROCEDURE newprocedur(outname OUT VARCHAR2,outroll OUT NUMBER) AS
CURSOR c1 IS
select Name,Rollno,Section from emp;
BEGIN
Open c1;
fetch c1 into outname,outroll;
Here out of 3 columns in cursor is it possible to fetch only two columns using FETCH like i did above?
I want to write a SELECT query on the data which are collected in a PLSQL table which is having 4 columns.
Looping through all the records in the PLSQL table will not get my requirement. Because I need to group the data based on two columns and need to fetch the count of groups.
Is there any way to query that PLSQL table?
when i run this code i get a invalid number error
create or replace
PROCEDURE BULK_REJECT(System_name VARCHAR2)
as
MDM_run_id VARCHAR2(14);
V_sql clob;
cursor Job_metric is
select rowid_job,system_name, table_display_name, run_status
[code]....
This is my data
ID TIME_IDVCL_IDIDLE_END_TIME START_AT
351601834326910110/29/2009 1:18 10/29/2009 1:18
351601837056910110/29/2009 1:51 10/29/2009 1:51
351601838106910110/29/2009 2:39 10/29/2009 2:39
351601840626910110/29/2009 3:04 10/29/2009 3:04
351601841466910110/29/2009 3:09 10/29/2009 3:09
I want to minus 1st row of START_AT from 2nd row of END_TIME means (10/29/2009 1:51 - 10/29/2009 1:18)
I am trying to write a proper query to fetch data from database. Scenario:
I need to retrieve employees who are not working in multiple departments. scott@TESTCRM> select * from emp1;
EMPNO DEPTNO
---------- ----------
7654 30 7698 30 7788 20 7788 30 7876 20 7900 10 7900 30 7902 20 7934 10
scott@TESTCRM>
Ouput Expected is
EMPNO DEPTNO
---------- ----------
7654 30 7698 30 7876 20 7902 20 7934 10