SQL & PL/SQL :: How To Move Next Record In Function
Oct 8, 2012
create or replace function kardex_ob (f_item_code varchar2,f_date date) return number is
f_ob_quantity kardex.cb_quantity%type;
cursor cur_kardex is
select cb_quantity
[code]...
If the above function is called it shows the first Record in the Table "kardex" for example:
if we run this query which is taken in the above function;
select cb_quantity
from kardex
where item_code = f_item_code and
document_date < f_date
[code]...
so here if i call the function(Kardex_ob) it shows the first Record "8500",well if i wish to show the next record 5000, thenhow to get it through the above Coded function?
I'm trying to execute a dynamic sql that calls a function. But that function has inserts and deletes inside and this way it can't be called through a select statement. And to be worst, it has an other problem, my function uses a record type as parameter.
My code (sample): ----------------- DECLARE type r_parameters is record (cd_query cons_query_param.cd_query%type, cd_usuario cons_query_user.cd_usuario%type, nr_param cons_query_param.nr_param%type, vl_param varchar2(2000),
I have Two cursor record block..which is attached in form..
My TASK IS
In my first Block, When DBCR Column = 'D' Then in backend this column value should be save as a '1' WHEN DBCR Column = 'C' Then Then in backend this column value should be save as a '2'
My Both Field is on Data Block...
In Property palette of this field can we write any decode condition..so it reflects directly on database.
I want to use a function in join clause. so i go for pipelined function(using for loop to get record & 1 more loop to fetch in table type variable). i achieved what i required. but problem is it takes much time to fetch data. is there any other approach which returns table records without pipelined function.
1 - i want to ask few things as i m new to apex, i am using apex 4.1, and created 3 select list and a button in selecting of parameter,
1 select list : select area 2 select list: select product 3- select list - size of the product
i want to generate Ids for the following. for that i created query for INSERTING RECORD FROM ONE TABLE TO ANOTHER , generation the ids when button pressed "Generate" after selecting parameters,
Now where i call that QUERY on button ? because when i create button its gives me option to submit, defined dynamic action, etc, where i call the function name id_generation when button pressed?
2- second thing i created tabular " select user_id, product_name, product_type from product".
By default check box list are create delete submit button are created, first when i insert record it saves that was fine, e.g i entered 50 records and afterward i want to update only one record, e.g there is a record product name = box, if i change it to box small and click submit then it saves all the page means all 50 records,
I want to submit only that record that i changed, for that i use the logic that only those records should be updated which are checked but the user. how will i do this ?
I am loading data file using SQL Loader in TOAD 10.5.1.3 in Oracle 10g using the control file below and loading data into 2 tables post1.thead and post1.tdetl. THEAD contains item level transaction and TDETL is detail level when a transaction has a discount or promo attached to for that item. When the
LOAD DATA APPEND INTO TABLE post1.thead WHEN (1:5) = 'THEAD' TRAILING NULLCOLS (file_type POSITION(1:5)CHAR,
[code]....
A particular THEAD value may have 0, 1 or many TDETL corresponding values.below is a sample data file. When the position 21 in the TTAIL has a value of 1 or 2, then we know that there is a promo or discount applicable to the ITEM (THEAD).
What I want to acheive is to accurately reflect a TDETL to its corresponding THEAD, as both THEAD and TDETL are loaded into separate tables. How can we have the 2 records correlated?
I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name
Null Type ------ ---- ------------ ID_P NUMBER NAME_P VARCHAR2(12) DATE_P TIMESTAMP(6) Records---------------------1 loosi 22-AUG-13 01.27.48.000000000 PM1 nammi 26-AUG-13 01.28.10.000000000 PM2 kk 22-AUG-13 01.28.26.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM
now i have to select below 2 rows how can write select qurie for this?
I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.
No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.
Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.
I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:
Aim was to shrink datafile and got "ORA-03297: file contains used data beyond requested RESIZE value". Datafile size 7GB but used 128MB.
So created new Tablespace and moved all tables and index partitions to new TS. Now I see some entries in USER_INDEXES, which I need to move to new TS. But they are IOT's.
I want to move partitions and subpartitions to different tablespace but I am getting error when I am trying to move PARTITION tablespace after on move of subpartition tablespace.
CREATE TABLE IN_TEST ( LINE_SKU_SEQ NUMBER NOT NULL, REGION_CODE VARCHAR2(10 CHAR) NOT NULL ) TABLESPACE USER02 PARTITION BY RANGE (REGION_CODE) SUBPARTITION BY HASH (LINE_SKU_SEQ) ( PARTITION REGION_AMERICAS VALUES LESS THAN ('AMER_') STORAGE ( BUFFER_POOL DEFAULT [code]....
ORA-14257: cannot move partition other than a Range, List, System, or Hash partition
I have to move data from old tables into new tables. I use a sqlplus script and execute multiple 'insert from select'. I thought the SQL statements are executed in sequence, one after the other. I run into this problem: Let's take this script for an example
insert into A (a,b,c,.... select a,b,c...... from X; insert into B (a,b,.... select a,b,.... from A joined with Y; commit;
After executing the script in our development DB
select count (*) from A returns 200
When the script is executed in production, A table record count is 5
select count (*) from A joined with Y; (used in the second insert)
returns 200.
It seems that the two statements are executed at the same time and table A is not fully populated when the select in statement two is executed.I suspect this is due to parallel execution in production.
Will executing
ALTER SESSION DISABLE PARALLEL DML; ALTER SESSION DISABLE PARALLEL QUERY ;
I know how to move a PDB from one CDB to another CDB on the same server but have yet to see in the documentation of how to move from CDB to CDB on a different server. I know unplugging the database produces the XML file which you need in order to carry the metadata from one CDB to another but how to get the datafiles to a new CDB if on a different server. Is it just a case of using transportable tablespaces.
We plan to move the database to a new host with much more RAM, CPU. The new database server will have exactly the same OS version, Kernel version and the same version of database / grid standalone (ASM). I will have to install OS, Database software, Grid Standalone on the new server.
The current version of the database is 11.2.0.3.0 Enterprise Edition and the OS version is Oracle Enterprise Linux 6.3. However, we plan to use the same disks (ASM disks). I believe I don't need to use RMAN to do the migration, Is it correct?
I have already created large no of indexes on my Database without specifying specific tablespace, now I would like to move all the created indexes into particular tablespace.
I want drop some old partitions from big table but this will not increase free space on disk. So I want to move table with indexes to anothers tablespaces. What is the fastest way to do that? ALTER TABLE ... MOVE TABLESPACE ...? CTAS ? Or something else?
I have a table CISCOWORKS that contains dumps from Ciscoworks. Now I want to make a history table CISCOWORKS_HISTORY that contains all information I no longer need actively (entries that have the same CISCOWORKS_MAC as newer entries).
The two tables are like this, exactly the same.
DROP TABLE CISCOWORKS IF EXISTS; CREATE TABLE CISCOWORKS ( CISCOWORKS_IDNUMBER(9,0), CISCOWORKS_MACVARCHAR2(20 BYTE), CISCOWORKS_SWITCHVARCHAR2(10 BYTE), CISCOWORKS_PORTVARCHAR2(10 BYTE),
[code].....
I guess I will have to check the INSERTED table and check every entry to see if there is a entry in the CISCOWORKS table with the same CISCOWORKS_MAC. If so, insert an entry in CISCOWORKS_HISTORY with the attributes of the CISCOWORKS entry, and then delete the CISCOWORKS entry. how would I check every entry separately?
