create table src(id number,val number,data varchar2(100)) insert into src values (1,1,'SUN'); insert into src values (2,2,'WED'); insert into src values (3,3,'MON'); create table trg(id number,val number,data varchar2(100)) required rows to be inserted in the target table.
insert into trg values (1,1,'SUNDAY'); insert into trg values (2,0,NULL); insert into trg values (2,0,NULL); insert into trg values (3,0,NULL); insert into trg values (3,0,NULL);insert into trg values (3,0,NULL);
{code} based on the column value of the source table src's column val , i need to populate my target table trg . If the value of val is 1 then only one target row is created in the target .If the value of val in the source table src is 2 then the target is populated with 2 rows .The values of the target columns are mapped as follow:
1)id -as it is
2)val - if the val of src is 1 then map the val as it is .If the value of val is more than one then create as many rows as the value of val ,id will be as it is and the value of val and data will be null
3)data - if the val of src is 1 then expand the abbreviation else null .
I would like to create a trigger on a table which populates a log table. In addition to using the table where the trigger will exist, I would like to populate a couple more fields in the log table with with data from 2 other tables.
e.g.
NAME_TABLE -reg_id -name
ADDRESS_TABLE *trigger to be fired when a new record is created here. -reg_id -srv_id
PROCESS_TABLE -srv_id -start_time -end_time
This is what I would like the logging table to look like:
How to have a dynamic action populate text fields. When the create button is hit, it will insert the record in the DB like normal.
I had a search box doing a dynamic action when a record_number is entered and the selection changes It populates the remaining text boxes with the results of the record_number.
I used a "SET_VALUE" true action on each individual ITEM
For each individual item i have different SQL Statement populating that item.
Ex. To populate Last_Name/ P2_LAST_NAME item i do the following
Select LAST_NAME from patient_Demographics where record_id = :P2_RECORD_ID
Affected Elements: P2_LAST_NAME
So i have about 8 of these true statements, so i'm hitting the DB 8 times to get the individual items. Is there a way to hit the DB once and set the items using one PL/SQL statement? I tried using a PL/sql function body,
I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.
Explanation:
In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.
I need to query on this dynamic table and return the result as return result. But i am not able to do it.
Here T_web_loylty_report_table is a type.
CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number) return T_web_loylty_report_table is v_tbl_nm varchar2(50); i_cntry_cd varchar2(20); v_sql_str varchar2(32567); [code]......
This is the first time the table is getting loaded. I get the distinct emp name from table 'X' and insert into teh sales person table to list out all teh emp present in the company and give the corresponding details.
I am not sure how to write a proc to populate the whole table. emp id is sequence generated.
begin date any date say 10/01/2010 (mm/dd/yyyy) end date is 99/99/9999 indicating that the emp is not terminated is_current flag = 'Y' if end date is 99/99/9999.
I have done a distinct empname from table 'X' and inserted into the salesperson table. Do i proceed to update the table for individual columns? *is that the right approach*?
Having trouble creating a trigger to populate another table.
The SQL:
CREATE OR REPLACE TRIGGER "P_M_YES" AFTER INSERT OR UPDATE ON DOMAIN REFERENCING NEW AS NEW.P_M AND OLD AS OLD.P_M FOR EACH ROW WHEN (NEW.P_M = YES) BEGIN INSERT INTO PAGE_MAKER VALUES(:NEW.D_NAME, :NEW.USER_ID); END P_M_YES;
I had to create a new column in a particular table now i want to insert the values in that column though the other columns are already populated I entered the command (insert into Product(STANDARD_PRICE) values(895.99) when i hit return it says cannot enter null value into (SYSTEM .PRODUCT. PRODUCT_ID) product_id is the PK which is the first column STANDARD_PRICE is the last column in my table...how do i enter the values into that column without receiving this error or having to effect the other columns?
TABLE_FROM_BLOCK built-in Package is working in Forms 6i but not in Forms 10g. is there any other built -in available instead of this. how do we populate table from a block in Forms 10g?
and I was wondering if there is a quick method of populating it with calendar data so it would look like the following:
Product Year Month A 2008 Jan A 2008 Feb A 2008 Mar A 2008 Apr A 2008 May A 2008 Jun A 2008 Jul A 2008 Aug A 2008 Sep A 2008 Oct A 2008 Nov A 2008 Dec A 2009 Jan A 2009 Feb Etc.
I've a staging table STG_TABLEA which has a primary key discount_seq_no.
I am creating a pl/sql procedure to populate a primary key (discount_seq_no) with a database sequence. The intent is to keep this populated with next value incrementing by 1.
I am using Oracle 11.2.0.2 version.
I've put together the below code, not sure on next steps...
BEGIN UPDATE STG_TABLEA SET A.DISCOUNT_SEQ_NO = "INSERT A SEQUENCE HERE AND KEEP INCREMENTING the seq value by 1 COMMIT; EXCEPTION WHEN OTHERS THEN RAISE; END;
ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.
I am planning on adding Begin Date and End Date on an EFFDT (Effective dated table). easy method of populating the Begin and end Date columns based on the EFFDT already on the table. Below is an example:
Department Table
From:
DEPTID EFFDT ...DESCRIPTION 12345 1/1/1900 Accounting 12345 1/1/2000 Accounting Exp Unit 12345 2/1/2000 Acct. Expense Unit 12345 5/1/2000 Account Expense Unit 12345 10/1/2000 Account Exp Unit
TO:
DEPTID EFFDT ...DESCRIPTION BEGIN_DT END_DT 12345 1/1/1900 Accounting 1/1/1900 12/31/1999 12345 1/1/2000 Accounting Exp Unit 1/1/2000 1/31/2000 12345 2/1/2000 Acct. Expense Unit 2/1/2000 4/30/2000 12345 5/1/2000 Account Expense Unit 5/1/2000 9/30/2000 12345 10/1/2000 Account Exp Unit 10/1/2000 12/31/9999
how should i populate table column heading in list items of forms? I've create lov to select the column, then i have 10 separate list items. once i select the table from lov then list item should get populated with selected table column.
i have created a small table which contains only name of cities.On form level i want to create a dynamic list of the name of these cities just by clicking on the textbox without doing any hardcoding.
1) i have a hr.departments table that was loaded in hr schema on 1st oct 2012 with 4 columns(department_id, department_name, manager_id, location_id)
2) now I have a new schema by my name 'rahul' and I have loaded departments table but now an additional column has come into picture,ie created_date, this table got loaded on 1st-Nov-2012
3) Now going forward my columns could be dropped from the departments table (it can be a case), for example might be my departments table in my schema 'rahul' one day could comprise of only 3 columns(department_id,department_name,manager_id)
4) Now in the next step, I have managed to extract common column names(in a single line where columns are delimited using a comma) from both the tables(hr.departments and rahul.departments) which are (department_id, department_name, manager_id, location_id) using all_tab_cols table and I have written a function for it which i will be pasting below.
5) now going forward, using the above column names line with column names delimited using comma, I have used a ref cursor and assigned a query to it using the line of columns that I have extracted from the above point
6) Now I want to create a record variable which refers to my ref cursor, something like we do when we create a record variable by reffering to an explicit cursor defination that we give in the declaration block.
PS:
1) I have been out of touch with plsql for a long time so I have lost a lot of mmeory regarding plsql.
2) basically I need to compare data in hr.departments table with rahul.departments table for only columns that are common to both the tables, rest new or discarded columns information will go in one of the log tables that I have created(this is done already)
Code : =================================================================================================== create or replace procedure p_compare_data(fp_old_table_name in varchar2, fp_new_table_name in varchar2) is
I Have an apex page that display a modal window utilizing jquery. In the modal window I have a classic report with a link column that I want to capture its click event.
I was thinking I could create a dynamic action with selection type=jquery selector. Not for sure if I need to do anything on link column and do not know the syntax jquery selector.
when i press when button pressed trigger, i want first the form will delete all the previous data and then populate the data from the table, that's why i used clear_block first, but this clear_code is not working here. my coding is given below
go_block('show'); clear_block(NO_VALIDATE); declare cursor c1 is select * from qtr_demand order by 1; begin [code]..........
I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically
I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.
I do not know the table name but have a query that for sure returns the table name and I want to select a column value from the table into my PLSQL variable.
I trying to create a procedure that could update one or more different table into clob data - but only those tables that contain your search (indexed) word.
I have a 3 tables: item, common_lookup, member
> CREATE INDEX i_item_barcode ON item (item_barcode) INDEXTYPE IS ctxsys.context; CREATE INDEX i_item_title ON item (item_title) INDEXTYPE IS ctxsys.context; CREATE INDEX i_item_subtitle ON item (item_subtitle) INDEXTYPE IS ctxsys.context; CREATE INDEX i_common_lookup_meaning ON common_lookup (common_lookup_meaning) INDEXTYPE IS ctxsys.context; CREATE INDEX i_account_number ON member (account_number) INDEXTYPE IS ctxsys.context;
[Code]....
Now is the question.. If it possible to create the procedure search_execute which can add data depending on subbmited parameters ? If it possible, can You give me an example (dbms_output/update_clob), for search_execute to recive something like that:
> search_execute ('Wars', item, item_title) > >
select * from clob data : 1 1068 ASIN: B00003CX5P 1014 Star Wars - Episode I The Phantom Menace (CLOB) (BLOB) PG MPAA 22-mar-2005 3 16-lut-2012 17:31:55 3 16-lut-2012 17:31:55 2 1069 ASIN: B00006HBUJ 1014 Star Wars - Episode II Attack of the Clones (CLOB) (BLOB) PG MPAA 22-mar-2005 3 16-lut-
[Code]....
I try write something like this (it is not compile - and I know where is mistake, I tried to figure it out in several different ways - This is just my way of thinking expression) : > CREATE OR REPLACE PROCEDURE search_execute (v_text IN VARCHAR2, v_column_name IN VARCHAR2, v_table_name IN VARCHAR2) IS v_clob CLOB; v_tmp_clob CLOB; CURSOR c1 IS (SELECT * FROM v_table_name WHERE contains (v_column_name, v_text) > 0); CURSOR c2 IS (SELECT column_name FROM user_tab_cols WHERE table_name = v_table_name); BEGIN
i am practicing to get Dynamic columns as i have learnt from Orafaq SQL/PLSQL forum .i am using Default EMP table...first one is running smoothly as below:
I have 2 tables , which can be mapped with a common column and the second table's rest of the columns should become like columns(like pivot) and in the second table itself 1 more column called value , this sum of the value becomes a value to the pivot column value. For this I am using CASE structure and I am writing the individual case for each column , but I am sure after some extent case don't work at all and I would like to do it dynamically( when ever the new entry will come into the source table , my proc has to pick automatically the new value and put the new value as part of pivot structure) , so pls share your inputs and if possible provide some sample code.
Im facing some issues with my form, getting stuck... prob desc below : I have Table A which has columns; car_year, car_type, line_num, line_text. Table B has country, car_year, car_type, line_num, data_entry_amt.
Table A contains data which gets updated once every year only. Contains what year model is the car, what type of car it is.. Line_num has numbers starting from 1 which indicates the different part number and line_text has description for that line_num. eg : 2010 Toyota 1 Windshield 2010 Toyota 2 Door 2010 Toyota 3 Tire 2010 BMW 1 Windshield 2010 BMW 2 Door 2010 BMW 3 Tire 2010 BMW 4 Rear_mirror
Table B contains specific data related to table A, Contains country where car details n prices are, car_year, car_type, line_num, and amount($) for that part. for example : Australia 2010 Toyota 1 400.50 Australia 2010 Toyota 2 200.40 Australia 2010 Toyota 3 308.25
So in year 2010, in Australia, Toyota's Door was sold at $200.40 Now, Table A will have similar data for this year and users will enter data for table B throughout this year. I tried master-detail form for this but it doesnt work. Because every year line_num change in table A and therefore cant implement a fixed number or rows on the form for amount for table B.
How to use dynamic listing but im not familiar with it. So how i should go about doing this. My form has structure has below :
