PL/SQL :: Use Two Temporary Tables To Assign Color To Data Results?

Jul 16, 2012

Example Tables and Data:

I have 5 Courses, each course has 2 assignments.

with Courses as (
select 100 courseID from dual union all
select 200 from dual union all
select 300 from dual union all
select 400 from dual union all
select 500 from dual
), Assigns as (

[code]...

I need each assignment to get a color assigned to it according to it's courseID. So my theory is that I'd have a temporary table with a list of colors; each courseID would get assigned a color but my question is how do I assign each courseID a color without anything to join the temporary table on? Once each courseID has a color, I'd join in Assigns so that each assignment has a color associated with it according to it's courseID.

--Color table with 6 colors
with Colors as (
select 'red' color from dual union all
select 'blue' from dual union all
select 'yellow' from dual union all
select 'green' from dual union all
select 'purple' from dual union all
select 'teal' from dual
)

View 8 Replies


ADVERTISEMENT

Global Temporary Tables

Jan 17, 2011

I have two database DB1 for EBS database and DB2 for Portal database. DB2 is always up.

DB1 uses some Global Temporary tables to write and store session level information.

I have Secondary database also for DB1. Whenever DB1 is down and its secondary database base is up, my requirement is to enable write operation to these Global Temporary Tables. Since secondary database we open Read-Only mode , I can't write to these GTTs.

DB2 is always up so I want to create the copies of these GTTs in DB2 portal database. Is there any harm on doing this.

Is there any harm storing session level information of DB1 database In DB2 database through DB-Link.

View 1 Replies View Related

Querying Records From Temporary Tables

Feb 2, 2011

I have a temporary table (with on commit preserve rows property) which is populated thru insert into command from a procedure. After which, i need to query the records from the populated temp table.

However, my query returns nothing. My procedure works fine cause i tried executing it to populate a regular table and it is ok. However, it shows no output in the temp table cause probably it is creating another session. How do i select the rows from the temp table after populating it from a procedure.

View 3 Replies View Related

Server Utilities :: How To Export Global Temporary Tables

Apr 18, 2013

There's an Application Express application which is based on a schema named TRAFOGLED. In order to let users test new features, there's a test application (Apex has export/import capabilities; no problem about that) which is based on another schema whose name is TRAFOTEST.

I'd like to export TRAFOGLED and import it into TRAFOTEST.I'm using 10gR2 EXPDP utility with a parameter file. Everything seems to be OK, except the fact that I'm unable to export global temporary tables (GTT). How can I tell? I didn't see them after import!

These are my GTTs:
SQL> show user
USER is "TRAFOGLED"
SQL>
SQL> select table_name from user_tables where temporary = 'Y';

[code]...

C:TEMP>
No tables were exported. Certainly, I don't expect any data to be exported, but I'd be happy with CREATE TABLE statements so that I don't have to create these tables separately.

View 4 Replies View Related

SQL & PL/SQL :: Search B If Not In A - But Only Use Results From A If In Both Tables?

Nov 11, 2010

I'm trying to select from table "A" where value1 and value2 match. If the values are not in table "A", try table "B". If the values exist in BOTH tables - only look at the results from table "A".

Table "A" may or may not have "new" data coming into the system.
Table "B" may or may not have "existing" data.

The code I am writing needs to find the "newest" data row for value1 and value2. Eventually the data in table "A" gets "Merged" into table "B" further on in the process.

Supposedly this can be done in a single query using a left outer join and the NVL function, but the person I inherited this from isn't available.

I've been able to get the row back if it exists in "A" or "B", but end up with two rows or no rows if the row exists in both... Arrrg...

View 9 Replies View Related

Omit Results - Two Tables That Each Have A Status Field?

Aug 6, 2010

I'm trying to leave out results of a query that match certain conditions, basically I'm looking at two tables that each have a status field. If the status of both is equal to zero then I want the result to be left out of the search results. I'm struggling with the logic though. In the where clause I've tried:

(ACCO.STATUS != '0' AND CCO.STATUS != '0')

this, however, omits everything where the status combination isn't 1 and 1. I've also tried:
(
(ACCO.STATUS = '1' AND CCO.STATUS = '1') OR
(ACCO.STATUS = '1' AND CCO.STATUS = '0') OR
(ACCO.STATUS = '0' AND CCO.STATUS = '1')
)

but this does not work either.

View 6 Replies View Related

Forms :: Change Color In Fields In Form Filled Via Data Block?

Jan 4, 2012

I'm trying to change the attributes on a display where the info is gathered via a routine that fills the data block with info. I've searched and tried using 'set item instance property' in several places within the data block using different triggers but still doesn't work.

No errors are encountered or shown. Attributes aren't changed based on a small test condition.

View 2 Replies View Related

PL/SQL :: Assign Variables To User Defined Data Type Within Package

Oct 16, 2013

I have this requirement, I have a following Record type within a package, instead of selecting datas into this i need to assign variables to this record type  

TYPE xx_delivery_detail_rectype IS RECORD (      p_delivery_id                    NUMBER,      p_ultimate_dropoff_location_id   NUMBER,      p_creation_date                  DATE,      p_last_update_date               DATE,      p_container_name                 VARCHAR2 (30),      p_inventory_item_id              NUMBER,      p_shipped_quantity               NUMBER,      p_shipment_line_id               NUMBER   );     TYPE xx_delivery_table_rectype IS TABLE OF xx_delivery_detail_rectype      INDEX BY BINARY_INTEGER; 

Say i have variables to assign to all the values within the record type, How do i do it within the package

View 4 Replies View Related

Reformat Data In A Temporary Table

Apr 26, 2011

Is there a neat way other than having to reformat the data in a temporary table to do the following,I've got the following content in a table:

CODECustid           Type               Nb                Amount
1                  Deposit           2             10000
1                  Withdrawal        1             4000

I'd like to show the data in this manner:

CustID Deposit  DepositAmount  Withdrawal WithdrawalAmount
1       2         10000            1          4000

View 3 Replies View Related

SQL & PL/SQL :: Can Lock Data In Global Temporary Table

Nov 15, 2011

Can we lock data in global temporary table?

View 4 Replies View Related

SQL & PL/SQL :: Job Schedule - Insert Data Into Table Temporary

Nov 7, 2012

how made a success of insert data into table temporary in job_schedule ? because when i tried to change insert into table permanent always successfully.

CREATE GLOBAL TEMPORARY TABLE YG_PAYMENT_TMP
( SITE_CODE VARCHAR2(100 BYTE),
KBON VARCHAR2(100 BYTE),
FUTURE_DATE DATE

[code]...

why insert into table temporary not successfully in job_submit.

View 3 Replies View Related

SQL & PL/SQL :: Partial Data Inserted When Reading From Global Temporary Table?

Jun 25, 2012

I have a complex sql query that fetches 88k records. This query uses a global temporary table which is the replica of one of our permanent tables. When I do Create table..select... using this query it inserts only fewer records. But when I make the query point to the permanent table it inserts all 88k records.

1. I tried running the select query separately using temp and perm table. Both retrieves 88k records.

2. From debugging I found that this problem occurred when we were trying to perform a left outer join on an inline view.

However this problem got resolved when I used the /*+ FIRST_ROWS */ hint.

From my limited oracle knowledge I assume that it is the problem with the query and how it is processed in the memory.

View 1 Replies View Related

How To Confirm Order Of Data In Results Of Two Sql Queries

Feb 9, 2009

I want to confirm the order of the data in results of two sql queries.

For example, in my table 5 columns are there.c1 to c5.

c1 c2 c3 c4 c5
--------------------------------------------------
test100 1 A 1-Jan-2006 2-Jan-2006
test100 2 B 3-jan-2006 4-Jan-2006
test101 1 A 01-Jan-2006 2-Jan-2006
test101 2 B 03-Jan-2006 4-Jan-2006
test101 3 c 05-Jan-2006 06-Jan-2006

In this table if you order by c2,

select c3
from tab1
where c1='test101'
order by c2;

c3
---------
A
B
C

If you order by c4,

select c1,c2,c3
from tab1
where c1='test101'
order by c4;

c3
------
A
B
C

Sometimes order of these data may change like. the result of the 2nd query may change (bcos of c4 date column changes)

C3
--
A
C
B

So,@ run time i want to check the order of these 2 results should match....if not,i need to display the corresponding c1 column.

View 3 Replies View Related

Function Not Returning Data (Desired Results)

Jul 14, 2010

I'm trying to debug this function to get the desired results. See attachment for the function code and the test data insert script.

----Create Test Table
CREATE TABLE VC_WORKINGDAYS
(
WK_ID number NUMBER(10,0),
WK_DATE DATE,
);

-- Insert test Data
INSERT INTO VC_WORKINGDAYS_1 VALUES (308, '25-MAR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (316, '06-APR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (324, '18-APR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (332, '03-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (340, '13-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (348, '25-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (356, '06-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (364, '16-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (372, '28-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (380, '08-JUL-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (388, '20-JUL-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (396, '01-AUG-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (404, '11-AUG-11');

SQL
----- Result Should be WHY
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),1)) FROM DUAL 14/JUL/10 15/JUL/10 is 1 working day from today
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),2)) FROM DUAL 14/JUL/10 16/JUL/10 is 2 working days from today
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),3)) FROM DUAL 14/JUL/10 19/JUL/10 is 3 working days from today

Attached File(s)

 create_Function.zip ( 6.39K )
Number of downloads: 1

 InsertData.txt ( 84.34K )
Number of downloads: 0

View 1 Replies View Related

PL/SQL :: Transposing Data And Joining The Results To A Query

Apr 10, 2013

I am new to writing queries for an oracle database and I was giving a bit of challenge. Here's the situation. I have 3 tables I am using. 2 of the tables are being used to transpose people's names from rows to columns by account number (there are multiple people associated with each account). The last table is a pretty straight forward query. I can run each query by itself and I get the results I want. But when I try to compile the two together, I start getting a variety of errors. Below is the two queries:

Query 1 (returns about 1,500 rows):
SELECT DISTINCT CAST (EIS_DW.ACCTCOMMONLOAN.ACCOUNT as VARCHAR(20)) as ACCOUNT_NUM,
EIS_DW.ACCTCOMMONLOAN.ACCOUNT_STATUS,
EIS_DW.ACCTCOMMONLOAN.MINOR_DESCRIPTION,
EIS_DW.ACCTCOMMONLOAN.OWNER_NAME,
[code]......

Query 2 (returns about 570 rows):
SELECT ACCTNBR,
max(DECODE (rn , 1, FULLNAME)) GUARANTOR_1,
max(DECODE (rn , 2, FULLNAME)) GUARANTOR_2,
max(DECODE (rn , 3, FULLNAME)) GUARANTOR_3,
max(DECODE (rn , 4, FULLNAME)) GUARANTOR_4,
[code]....

Ideally, I want to join these two queries on ACCOUNT and ACCTNBR. I have tried working my first query into my second query, but the best I get with that, is the 570 or so accounts, not all the accounts.

View 4 Replies View Related

Forms :: User Privileges - Assign Permissions To One User To Add / Delete / Edit Data?

May 28, 2011

how can i assign permissions to one user to add,delete,edit data and other user should be able to perform all functions or selected functions

View 12 Replies View Related

Maintain Large Tables / Cleanup Data From Our Tables

May 18, 2011

I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?

To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.

I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.

View 5 Replies View Related

Implementation Where Data From DB2 Tables Are Moved To Oracle Tables

Sep 3, 2012

I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?

View 1 Replies View Related

Oracle Data Pump - Export Data From Schemas Or Tables

Oct 11, 2012

I need to export only the data from schemas or tables, how to do that with Oracle Data Pump? when we use schemas parameter this export all schema, not only the data right?

View 7 Replies View Related

Data Warehouse - How To Insert / Access Data In The Tables

May 30, 2011

I created a data warehouse in oracle 10g n with three Dimension and one cube after that it crates 4 tables . How to use an insert sql statement to insert data in those tables n how to access them.

View 7 Replies View Related

Performance Tuning :: Same Data But Different Performance Results

Sep 3, 2010

what the principal things to look at when we have for the same query different performance results are?I have 2 different bases: the plan and data are the same but performance results are very differents.

View 10 Replies View Related

SQL & PL/SQL :: Assign The End Date

Dec 19, 2011

I had as source a set of actions where I have to assign the end date, the general rule is that each action has the same end date to start date of next action, if there is no next action it will be assigned the default('35001230').

I can do the assignment with the date of order:

NVL (LAG (DAT_INI) OVER (PARTITION BY ID_CLIENT DAT_INI ORDER BY DESC, DESCCOD_SEVERITY), TO_DATE ('35001230 ',' YYYYMMDD ')),

However, I have an exception to the rule, if the action have COD_SEVERITY = 3 or 4 and the next action have the COD_SEVERITY = 3 or 4, the end date will be for the previous day. If in the same day I have actions with COD_SEVERITY 3 and 4, the end date of actions with COD_SEVERITY = 3 will be DAT_INI -1 and the action with COD_SEVERITY = 4 will have the same date of the next action or DAT_INI -1 if the next action had COD_SEVERITY = 3 or 4

Input
ID_CLIENTDAT_INICOD_SEVERITYVALUE
1201112011100
1201112013100
1201112044101
1201112073100
[code].......

Output except
ID_CLIENTDAT_INICOD_SEVERITYVALUEDAT_END
120111201110020111201
120111201310020111203
120111204410120111206
120111207310020111213
[code]........

The source
SELECT 1 as ID_CLIENT, 20111201 as DAT_INI, 1 as COD_SEVERITY, 100 as VALUE FROM DUAL
UNION ALL
SELECT 1 as ID_CLIENT, 20111201 as DAT_INI, 3 as COD_SEVERITY, 100 as VALUE FROM DUAL
UNION ALL
SELECT 1 as ID_CLIENT, 20111204 as DAT_INI, 4 as COD_SEVERITY, 101 as VALUE FROM DUAL
UNION ALL
[code].......

View 1 Replies View Related

PL/SQL :: How To Assign Value To Parameter

Mar 20, 2013

I have written this code, its throwing error.

begin
declare tmdate DATE;
select TO_DATE(TO_CHAR(MAX(DATERANGE),'DD-MON-YYYY')) into tmdate from table_check_date_range;
dbms_output.put_line(tmdate);
end;

View 10 Replies View Related

Forms :: Individual Tab Page Bar Color?

Aug 12, 2011

Can i set individual tab page bar color?

Like the attached screenshot..

View 3 Replies View Related

Forms :: How To Change Color Of Text

Mar 13, 2012

i want to change the text background color as red ex: if desc is the field in the forms and when i type the word china in that field then that china backgroud color should change when i move to next item to red for that record alone..in the key_next_item trigger i wrote this

set_item_property('desc' backgroud_colour,'RED');

IS THIS RIGHT only that particular record should change

View 14 Replies View Related

Forms :: Change Color Of Canvas

Aug 30, 2012

I have a problem while changing in a form canvas color

i make some modification in a form. The layout color of the canvas is blue. i want to change the layout color of it to default (<unspecified>) (which is the default color).

when i change in the property pallet the background color to <unspecified>. it give me that error FRM-12305 color by this name doesn't exist and i want it the default color.

how can i do it without recreating the form from the beginning.

View 2 Replies View Related

Forms :: Text Box Background Color?

Nov 24, 2011

How can i change my text box into a transparent background?Any color code for it? I'm using Form 6i

View 5 Replies View Related

Forms :: Drop Down List Color

Mar 3, 2010

I want to display may drop down list on different colors. Let's say I have 3 choices on list,

I want choice 1 to have a blue background, red for choice 2 and green for choice 3.

View 3 Replies View Related

Forms :: Highlight Field Value With Blue Color

Jun 25, 2011

My requirement is, when ever cursor entered into a field, the value of the field will highlight with Blue color and text color will be white, if field is not empty. It the feature of oracle application.

View 5 Replies View Related

Forms :: Change Color Of Canvas To Default

Apr 12, 2010

The Background colour of the canvas is "gray" i want to change it to default.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved