here i m giving the code
DECLARE
TYPE SUBNO_TABLE_TYPE IS TABLE OF
TRANS_DEICMAIN1.SUBNO%TYPE
INDEX BY BINARY_INTEGER;
SUBNO_TABLE SUBNO_TABLE_TYPE;
K NUMBER := 1;
S NUMBER := 1;
[code]...
HERE CHECK1 IS A CHECKBOX.WHEN I AM USING THIS CODE ONLY ONE RECORD IS SAVED AT A TIME INSTEAD OF SAVING ALL RECORDS.
i have created a tree hierarchy....when the user clicks on any node,that particular form will open....But there is some problem...i click on form A,it opens.,...then i exit that form via a exit button on the toolbar....and then I click on form B on the tree....but first the form A only opens...and then again i exit form A, then form B will open..
i have written the following code in when_tree_node_selected trigger :
if condition then
call_form(path,no_hide,do_replace);
I created one Form manually. how to Drag and drop from items in Data Blocks to Canvas. when I created text in Canvas it was automatically creating the item in Data block. but when i create the items in Data blocks and then trying to copy to canvas.
View 1 Replies View RelatedI have a form in which menu tree is populated with this sql
SELECT 1, Level, MenuLabel, MenuName, MenuId
FROM MenuFile
CONNECT BY PRIOR MenuId = ParentId START WITH menuId = :moduleid||'0000'
The result is attached in the CSV file enclosed in sheet mrnufile1As we can see in row 3 the item LPC has menuid BDME13.
In my view This item should be in ROW 19.How can i change query do get the desired the result.I have also tried another sql query.
select * from menufile where applicationid = 'BD' order by parentid,menuid
Result of this query is in 2nd Half of CSV file
I have a block based on a table and display a few records on the form after retrieving records from the table behind. Now I should be able to edit a record or add a new record to the existing records on the screen. So when i click the save button I only want the rows which are new or have been edited to go back into the db. This is the piece of code I was using for the save button in the on-button-clicked action.
IF alert_button=alert_button1 THEN
GO_BLOCK('my_block');
IF NOT Form_Success THEN RAISE Form_Trigger_Failure;
END IF;
/* ** Commit if anything is changed */
IF :System.record_Status IN ('CHANGED','NEW') THEN Commit_Form;
END IF;
msg_info('Your changes have been saved.');
END IF;
When I do this all the records on the screen gets inserted into the table instead of only the one's I have edited or newly inserted. I only want to insert rows which are new or edited into the table after I click the save button.
how to save multiple records at once i did coding like this:
declare
cnt number;
l_rec number;
f_rec number;
begin
cnt := 0;
go_block('aan');
last_record;
l_rec := :system.cursor_record;
first_record;
[code]....
but this works as saving the last record only, not saving the all records.
I am using the following merge statement it's not working properly. If I tried to insert (or) update the existed record. I am getting unique key constraint violated error.
DECLARE
p_buid NUMBER;
p_ordernum NUMBER;
[Code].....
I'm trying to Create a procedure that generates:
Customer
Description of Order
Amount of Order
Total Of Order
As this Generates More than One Row I thought i would need to use cursor in order to store the results and then use them from the object So this is what i Coded:
CREATE OR REPLACE PROCEDURE UP_CustOrders
AS
/*I Am Selecting A Cursor Cause It Return More Than One Row*/
CURSOR ReportCursor IS
SELECT Company
[code]..
It appears It creates the procedure but im not sure why it has compilation errors Perhaps im missing some symbols? Here are the tables:
CREATE TABLE Customers
(CustNum NUMBER(10) NOT NULL,
Company VARCHAR2(20) NOT NULL,
CustRep NUMBER(10),
[code]...
There are two servers A and B ,and i am maintaining one table suppose table1 and another table suppose table2 , DML triggers are made for both of these tables.The action of DML trigger is that the movement we are inserting values , suppose a table1 on server A so automatically the same set of value in table2 on server Bso my problem is that the movement i inserted value on table1 on server A ,
the same set of values were inserted into table2 of server B and as the values were inserted intotable2 , the same set of values were inserted into the tabld1 because of the trigger. How to stop the non ending loop which occurs due to the simultaneous firing of triggers and insertion of same records into both the table endlessly...
version :---Personal Oracle Database 10g Release 10.2.0.3.0 TNS for 32-bit Windows: Version 10.2.0.3.0
It seems some issue with With Clause along with Row Number. Output is strange when it comes with SELECT MIN()... But Looks fine Once you add "order by" or "where" clasue inside WITH Clause
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
[code]....
But Output looks fine once you add ORDER By clause inside WITH clause
SQL> WITH EM AS
2 (
3 SELECT EMPNO, ROWNUM RN
4 FROM SCOTT.EMP T ORDER BY ROWNUM
[code]....
why it happens ?
#service setup:
$ srvctl add service -d dc1ext -s addval.dc1 -r "dc1ext1" -a "dc1ext2" -P BASIC
$ srvctl start service -d dc1ext -s addval.dc1
begin
DBMS_SERVICE.MODIFY_SERVICE(
service_name=>'addval.dc1',
aq_ha_notifications => true,
failover_method=>DBMS_SERVICE.FAILOVER_METHOD_BASIC,
[code]......
my attempt at verifying the TAF is working.
SQL> run
1 SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*)
2 FROM GV$SESSION
3* GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER
MACHINE FAILOVER_TYPE FAILOVER_M FAI COUNT(*)
---------------------------------------------------------------- ------------- ---------- --- ----------
anetapp02.dc2 NONE NONE NO 10
anetapp01.dc2 NONE NONE NO 10
app02.st2 NONE NONE NO 5
racdb02 NONE NONE NO 44
anetapp03 NONE NONE NO 10
anetapp04 NONE NONE NO 10
GNOME\APTSEA-6GKJHF1 SELECT BASIC NO 3
app01.st2 NONE NONE NO 5
anetapp05 NONE NONE NO 10
anetapp01 NONE NONE NO 10
anetapp03 NONE NONE NO 10
so it looks as thought the sessions are failing over, but my app guy says he get a few errors during the process.. i need it setup to move the current query over without returning an error.. do i not have this setup properly?
I have created a Table and a log of materialized view in an Schema SchemaAA of Server A
CREATE TABLE TABLA_TEST
(
TEST_PK NUMBER,
TEST_TEXTO VARCHAR2(50),
CONSTRAINT PK_TEST PRIMARY KEY (TEST_PK)
);
CREATE MATERIALIZED VIEW LOG ON TABLA_TEST;
I have created a DB Link from server B with user REPLIC
I have granted SELECT to REPLIC to the table and the log materialized View.
I have created a table in instanceBA of Server B
CREATE TABLA_TEST
(
HOSPCODE NUMBER(3),
TEST_PK NUMBER,
TEST_TEXTO VARCHAR2(50),
FECHA_INSERT DATE,
FECHA_UPDATE DATE,
FECHA_DELETE DATE,
CONSTRAINT TTEST_PK PRIMARY KEY (HOSPCODE, TEST_PK);
I have created a materialized view in schema SchemaBB of Server B with a trigger
CREATE MATERIALIZED VIEW WORKAREA.TABLA_TEST70
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS
SELECT TEST_PK,
TEST_TEXTO
[code].........
All schemas have the appropriate grants.
When inserting in TABLA_TEST (SchemaAA) and refreshing MView TABLA_TEST70 things go nicely.
But, when updating a record in the original TABLA_TEST and refreshing the MView the results in TABLA_TEST in SchemaBA are as if I have deleted the record (FECHA_DELETE is set to SYSDATE).
I have a query with FULL hint that is behaving in a strange manner. The query fetches around 700000 of data. Sometimes it fetches the data with the hint and sometimes it does not fetch any data with the hint and then I have to remove the hint and have to fetch the data. Below is the query,
select /*+ FULL(COMP_TM) FULL(TRANS_TM) FULL(INVC_TM) */
CUST_BE_ID ,
DISTR_BE_ID ,
FG_BE_ID ,
KIT_BE_ID ,
BG_ID_NO_BE_ID ,
[code]....
The statistics gathering activity of FACT_DLY_ALGND_SLS table takes around 5 hours to complete. It is a range partitioned table with subpartitions.
I applied the Jan 09 CPU to my database this past Saturday and now my daily schema export is not running properly:
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in WE8MSWIN1252 character set and UTF8 NCHAR character set server uses UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
[code]...
Every time I hit up, down, left, and right arrow keys to return to a previous sql command I got ^[[D, fix this issue? The backspace works fine with me.
View 2 Replies View RelatedThe multiple row insert is not working
CREATE TABLE example (
example_id INT NOT NULL,
name VARCHAR( 50 ) NOT NULL,
value VARCHAR( 50 ) NOT NULL,
OTHER_VALUE VARCHAR( 50 ) NOT NULL
);
For the below query its showing error .
INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');
the error message is.
Error starting at line 1 in command:
INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4')
Error at Command Line:3 Column:39
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
I am trying to write a trigger on a new table. (dest_test) This is the first trigger that I have ever attempted (fairly new DBA) and I am having some trouble with the trigger body.It is a before insert trigger that will need to select from another table (dest) for a particular value being inserted (destination).
create table dest_test (
destination varchar2(4) not null,
db_name varchar2(10) not null
)
desc dest
[code]...
I am getting the exact opposite results than I want, though. If the value appears in dest, it is inserting into dest_test... NOT whatI want it to do!If the value doesn't appear in dest, it is throwing ora-6512 and ora-4088 errors. Is there a way to suppress these errors, or to graceful exit from the block so that the trigger completes without throwing these errors?
I am using Apex 4.2, The Interactive report Search is not working properly, when i am entering complete date or time it is not showing any results...how to increase performance of the search...
View 5 Replies View RelatedIn oracle 9i, I have a table and i inserted the more then 3 records and while inserting each records i have created the save point. But, now i rollback to that particular save point, the whole transaction get roll backed.
Here the similar example what i have tried using SQL Developer Eg:
insert into dept (deptid,dept_name) values (3,'Purchase');
savepoint aa;
insert into dept (deptid,dept_name) values (4,'IT');
savepoint bb;
insert into dept (deptid,dept_name) values (5,'System');
savepoint cc;
rollback to bb;
How do I loop through a Input parameter (varchar_table) and pass the input value to a select query.
Procedure Test
(
param1 IN dbms_sql.varchar2_table
)
[Code]....
1. How do I define temp_tbl and is it a best practice to use temporary table - if not what is the best method to do the same.
I wrote the following block :
set serveroutput on
declare
rec employees%rowtype;
cur SYS_REFCURSOR;
begin
open cur for 'select * from employees where rownum<:a' using 4;
for i in cur
[code]....
It gave errors if we execute is as such, but worked when I commented out the for loop and instead de-commented the simple loop. Does that mean that FOR cannot be used to loop through the records of a ref cursor ?
I'm working with Oracle 10g.
I have a table like this;
ID Amount Date
123 5000 Oct-07-2011
123 null Oct-09-2011
124 7000 Oct-14-2011
124 null Oct-17-2011
124 null Oct-24-2011
What I'm trying to do here is loop thruogh the records and update the amount that's null with the previous amount with the same ID.
My oracle version is oracle 9i
I need to commit after every 2000 records.Currently am using the below statement without using the loop.how to do this?
do i need to use rownum?
BEGIN
UPDATE
(SELECT A.SKU,M.TO_SKU,A.TO_STORE FROM
RT_TEMP_IN_CARTON A,
CD_SKU_CONV M
WHERE
[Code].....
I enter a form, do nothing, and exit_form. It closes but reenter again. The second try, does not cause this effect and ends properly. Checked and commented code around the form, but no use.What could be happening ?
View 4 Replies View RelatedWhen I first access the form and query for records, it works as per the expectation and with scroll bar to scroll up and down. But when I do "View>Record>Last", it reaches to last record, but not able to use the scroll bar to scroll the records up(I am on last record, so can't do downward scrolling).
The scroll bar is displaying in Full, no room to scroll up down.It happens only when I do "View>Record>Last".
I have created a form which as two block as master and detail.I am facing two problem in my detail block.
First: When I populate records in detail block, it prompt a message 'Do you want to save record ?'.
Second : When I alter any value in detail block and move to next record, it prompt a message 'Do you want to save record ?'
Is there any way where, system don't prompt me a message for saving record and user can continue with changes and save the records when he desire.
the data not save the record the item CMPID not generate a number or id to press button NEW
secondly to click the button SAVE they not save the partcular transaction.
I am working on form 6i. I want to disable the 'SAVE' button which can be seen in the menu (front end).
View 2 Replies View Relateddeclare
blk_name varchar2(60);
form_name varchar2(60);
[Code].....
this is my coding on save button. this is working fine but only once. when i enter the data second time and try to save, it didn't do anything. what should i do?
I make button in detail section using this location button call another forms, I want first update the location entry then save our main forms.
View 3 Replies View Related