Home » SQL & PL/SQL » SQL & PL/SQL » Rows not populating (Oracle DB 12c)
Rows not populating [message #657687] Fri, 18 November 2016 12:12 Go to next message
krishna.orcl97
Messages: 4
Registered: November 2016
Junior Member
created a table RAW_Table, which has two primary keys SEARCHID and SERVICE_INVOKED.

When the program runs, my database updates SEARCHID. This is unique and for every SEARCHID generated, there can be multiple SERVICE_INVOKED.

The table should have an individual row for every SERVICE_INVOKED. At the moment, only one row is ever populated, even if there are multiple services invoked. What should I do? Here is the SQL query for creating the RAW_TABLE.....

CREATE TABLE "SAT_RAW_TABLE"
(
"SEARCHID" VARCHAR2(20 BYTE) ,
"USER_NAME" VARCHAR2(100 BYTE),
"SAT_NAME" VARCHAR2(100 BYTE),
"ADDRESS" VARCHAR2(100 BYTE),
"CITY" VARCHAR2(50 BYTE),
"STATE" VARCHAR2(10 BYTE),
"ZIPCODE" VARCHAR2(20 BYTE),
"TERM" VARCHAR2(20 BYTE),
"BANDWIDTH" VARCHAR2(20 BYTE),
"SERVICE_INVOKED" VARCHAR2(50 BYTE) ,
"SERVICE_REQUEST" CLOB,
"SERVICE_RESPONSE" CLOB,
"INVOKED_DATETIME" date,
PRIMARY KEY (SEARCHID,SERVICE_INVOKED)
);

To fix this, we ran this script.
ALTER TABLE SAT_RAW_TABLE DROP CONSTRAINT SYS_C001274759;

ALTER TABLE SAT_RAW_TABLE ADD CONSTRAINT SYS_C001274759
PRIMARY KEY ( SEARCHID , SERVICE_INVOKED )

ENABLE;




The result should look something like this,

SearchID Username SAT-NAME SERVICE INVOKED
100 ABCD SAT1 Service1
100 ABCD SAT1 Service2
100 ABCD SAT1 Service3
200 PQRS SAT2 SERVICE1
200 PQRS SAT2 SERVICE2



But I can see only the last Service in the table, but all the services from service_invoked should have multiple rows for each SearchID


Thanks
Re: Rows not populating [message #657688 is a reply to message #657687] Fri, 18 November 2016 12:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

>created a table RAW_Table,
What is difference between table above & table below?
>CREATE TABLE "SAT_RAW_TABLE"
Re: Rows not populating [message #657689 is a reply to message #657687] Fri, 18 November 2016 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
Align the columns in result.

Quote:
created a table RAW_Table, which has two primary keys SEARCHID and SERVICE_INVOKED
No you have one primary key with two columns. A table can't have more than one primary key.

The result you showed is from which query?
If you have only the last service maybe this is because there is only the last service in the table.

Quote:
At the moment, only one row is ever populated, even if there are multiple services invoked.
So instead of UPDATE use INSERT.

Are you sure that
"USER_NAME" VARCHAR2(100 BYTE),
"SAT_NAME" VARCHAR2(100 BYTE),
"ADDRESS" VARCHAR2(100 BYTE),
"CITY" VARCHAR2(50 BYTE),
"STATE" VARCHAR2(10 BYTE),
"ZIPCODE" VARCHAR2(20 BYTE),
"TERM" VARCHAR2(20 BYTE),
"BANDWIDTH" VARCHAR2(20 BYTE),
depends on SERVICE_INVOKED?

I think the table should be split in 2.
Read Normalization.

Re: Rows not populating [message #657690 is a reply to message #657688] Fri, 18 November 2016 12:32 Go to previous messageGo to next message
krishna.orcl97
Messages: 4
Registered: November 2016
Junior Member
Sorry,

Name of the table is SAT_RAW_TABLE
Re: Rows not populating [message #657691 is a reply to message #657689] Fri, 18 November 2016 12:40 Go to previous messageGo to next message
krishna.orcl97
Messages: 4
Registered: November 2016
Junior Member
Yes,

You are right. I did not run any query. Just looked at the data in SQL developer.

The application we run, updates the database for every service invoked. But that is not happening ever since we ran a script with constraint.

So the requirement is,

To display serviceid and services invoked. ServiceID remains the same and services invoked ( which are more than one) should reflect in database.

As of now, Only the last service invoked is getting populated in database.
Re: Rows not populating [message #657692 is a reply to message #657691] Fri, 18 November 2016 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I already answered this question.

Re: Rows not populating [message #657698 is a reply to message #657691] Fri, 18 November 2016 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And to add to my previous answer: the data model is flawed, the application code is wrong, how can we help you in a forum?
I think you should hire someone to review the whole stuff from the beginning.

Re: Rows not populating [message #657701 is a reply to message #657698] Fri, 18 November 2016 14:11 Go to previous messageGo to next message
krishna.orcl97
Messages: 4
Registered: November 2016
Junior Member
Haha I agree.

I am not a SQL OR DB expert.. so I am just looking out for views and suggestions.
Re: Rows not populating [message #657702 is a reply to message #657701] Fri, 18 November 2016 14:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Suggestion is : "hire someone to review the whole stuff from the beginning."

Re: Rows not populating [message #657703 is a reply to message #657702] Fri, 18 November 2016 14:18 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
title reports rows not populating.
That is likely true.
Since you refuse to show us what you actually do & how Oracle responds, we can't know what you did wrong.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Previous Topic: Oracle 11g Ntile function on top of Dense_rank
Next Topic: Show all months between 2 months in a group by query
Goto Forum:
  


Current Time: Thu Mar 28 19:21:12 CDT 2024