Database

Re: Hibernate error ORA-02289: sequence does not exist [SOLVED]

18-Feb-19 2:37

Well, as you can see in my hibernate.cfg.xml, I'm using SYSTEM, so that isn't the problem unfortunately.

Edit:
After some more research I found the problem. All the sites with tutorials google suggested were using either
<generator class="native"> or <param name="sequence" />SEQUENCE_NAME
The problem is that hibernate devs changed <param name="sequence" /> to <param name="sequence_name" />. It is weird that you get sequence does not exist error, instead of something like invalid value for param or something to suggest that is the problem.

modified 18-Feb-19 8:54am.

Valentinor18-Feb-19 2:59

Oracle SQL Developer filter "default tables" [SOLVED]

18-Feb-19 2:59

To solve this problem you have to change:

<generator class="sequence">
   <param name="sequence">SEQUENCE_NAME</param>
 </generator>

into

<generator class="sequence">
   <param name="sequence_name">SEQUENCE_NAME</param>
 </generator>

The value for param was changed from just "sequence" to "sequence_name".

Valentinor16-Feb-19 3:16

Re: Oracle SQL Developer filter "default tables"

16-Feb-19 3:16

After you install Oracle 11g XE, it will automatically create some tables (from what I've read, some are used for "Application Express", and you shouldn't drop them), but having them listed in Tables view is really annoying.
Is there a way to filter them out? And why do you use $ in table name for?

modified 16-Feb-19 14:04pm.

Jörgen Andersson16-Feb-19 7:25

Jörgen Andersson

16-Feb-19 7:25

Can't help you with the filtering since I don't work with Oracle anymore, but the V$ is just the naming standard Oracle uses for "Volatile" files, containing statistics and such stuff

Wrong is evil and must be defeated. - Jeff Ello

Re: Oracle SQL Developer filter "default tables"

Valentinor16-Feb-19 7:54

Re: Oracle SQL Developer filter "default tables"

16-Feb-19 7:54

Oh OK, I though they have some special meaning, something that I will have to use in the future too, guess not. Thanks!

Valentinor16-Feb-19 8:04

Can't Get Required Output while executing Store Procedure. Any help would be appreciated ?

16-Feb-19 8:04

For some reason, last time I searched on google it didn't showed me anything useful (on any of the first 4 pages), but this time, I got the answer I needed.

Solutions:
1. Use a different user then SYSTEM. I didn't though at this, after all I didn't wanted to complicate things on a learning project.
2. Filter each group using %. When I tried it before it didn't worked, but now after I started the DB again, the filter worked as it should have. Maybe there was an error connecting to the DB, don't know, it's good that now it is working.

K K Shah13-Feb-19 23:18

K K Shah

13-Feb-19 23:18

When i execute the procedure what i get the output is like -

ALTER PROC [dbo].[PROC_EMPLOYEE_PAY_SLIP]
@EmplopyeeCode varchar(100),
@MonthName varchar(50),
@Year Float
As
Begin
SELECT ISNULL(HR_EmployeeMaster.em_EmplopyeeCode,'') As EmplopyeeCode,
ISNULL(CASE WHEN fa_ComponentMaster.Cm_ADTag = 'A' THEN fa_ComponentMaster.Cm_Name ELSE '' END,'') As Additions,
ISNULL(CASE WHEN fa_ComponentMaster.Cm_ADTag = 'D' THEN fa_ComponentMaster.Cm_Name ELSE '' END,'') As Deductions,
ISNULL(CASE WHEN fa_ComponentMaster.Cm_ADTag = 'A' THEN Fa_MonthSalary.Ms_Amount ELSE 0 END,0) As AdditionsAmount,
ISNULL(CASE WHEN fa_ComponentMaster.Cm_ADTag = 'D' THEN Fa_MonthSalary.Ms_Amount ELSE 0 END,0) As DeductionsAmount
FROM HR_EmployeeMaster
LEFT JOIN Fa_MonthSalary ON Fa_MonthSalary.ms_EmpCode = @EmplopyeeCode
LEFT JOIN Fa_MonthDetails ON Fa_MonthDetails.mo_id = Fa_MonthSalary.Ms_MonCode
LEFT JOIN fa_ComponentMaster ON fa_ComponentMaster.cm_code = Fa_MonthSalary.Ms_CompCode
WHERE HR_EmployeeMaster.em_EmplopyeeCode = @EmplopyeeCode AND
(SELECT CONVERT(CHAR(3), Fa_MonthDetails.mo_name)) = @MonthName AND
Fa_MonthDetails.mon_year = @Year
End

Current Output -

EmployeeCode  Additions      Deductions   AdditionsAmount  DeductionsAmount
M1010         ALLOWANCE                     960.00               0
M1010         BASIC SALARY                  1390.00              0
M1010         OT1                           171.35               0
M1010                        OTHERS-DED     0.00                 1800.00

Required Output -

EmployeeCode  Additions      Deductions   AdditionsAmount  DeductionsAmount
M1010         ALLOWANCE      OTHERS-DED     960.00               1800.00
M1010         BASIC SALARY                  1390.00              0
M1010         OT1                           171.35               0

Any help would be appreciated Frown | :(

Re: Can't Get Required Output while executing Store Procedure. Any help would be appreciated ?

Richard MacCutchan14-Feb-19 1:35

Richard MacCutchan

14-Feb-19 1:35

You already posted this question in QA at Can't get required output while executing procedure[^]. Please do not crosspost.

MongoDB Transactions

Kevin Marois13-Feb-19 14:25

Kevin Marois

13-Feb-19 14:25

I am trying to figure out how to use transactions. I've read a lot of info, including this, but it's limited.

Does anyone know of a decent MongoDB Transactions tutorial?

If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.

Re: MongoDB Transactions

k505414-Feb-19 6:09

14-Feb-19 6:09

Maybe try asking in the mongodb-users forum : mongodb-users

C# MongoDB Question

Kevin Marois10-Feb-19 7:58

Kevin Marois

10-Feb-19 7:58

I'm very new with MongoDb, so excuse my ignorance.

I know that MongoDb doesn't store data in "tables" like SQL Server, but is there any way to look at the "structure" of a container? Basically I'd like to see the shape of the data.

Thanks.

If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.

Re: C# MongoDB Question

k505412-Feb-19 6:07

SQLite how to delete rows in a table (C++)

12-Feb-19 6:07

You do have the mongo cli, which allows you to query the data, etc. But there is no "shape" to the data, like there is with SQL Data. Mongodb has "collections" which are roughly equivalent to tables, but unlike SQL tables, they have no structure imposed on them. e.g.

MongoDB shell version: 3.2.12
connecting to: test
> db.mycoll.find()
> db.mycoll.insertOne( { x: 1} )
{
        "acknowledged" : true,
        "insertedId" : ObjectId("5c62fb2a93ce6e6b14a14369")
}
> db.mycoll.insertOne( { able: 1, baker: "hello world", charlie: {xray: 0, zulu: 6.9} } )
{
        "acknowledged" : true,
        "insertedId" : ObjectId("5c62fb5293ce6e6b14a1436a")
}
> db.mycoll.find()
{ "_id" : ObjectId("5c62fb2a93ce6e6b14a14369"), "x" : 1 }
{ "_id" : ObjectId("5c62fb5293ce6e6b14a1436a"), "able" : 1, "baker" : "hello world", "charlie" : { "xray" : 0, "zulu" : 6.9 } }

modified 12-Feb-19 12:29pm.

Re: C# MongoDB Question

jschell23-Feb-19 11:11

jschell

23-Feb-19 11:11

That other reply is precise but not very useful.

If you have a Mongodb where collections do not have some specific form then you are using it wrong.

Besides the CLI I have also used two different UI clients for Mongo and both show the data that are in the collections in a reasonable way.

Now if you are starting with a empty collection you won't know what will go in there and with a SQL table you would but that that is somewhat a matter of degree and not usability. In that case you should go look to your design, which you should have regardless, or the code.

steve_94966138-Feb-19 5:08

8-Feb-19 5:08

Hello everybody.

I have no database experience so I apologize in advance if my question is too trivial.

I'm writing an application in C++ (Embarcadero C++ Builder Tokyo) and I'm using sqlite.

I have a database with a table "EVENTI" with two columns ("DataOra" and "Evento") and about 6000 rows and I want to delete the first ~5000 rows to reduce the database size to about 1000 rows.

I have to use a command like:
"DELETE FROM EVENTI WHERE (condition)"

but I don't know exactly how to write the condition.
Is there a line identifier so I can write a condition like:
"DELETE FROM EVENTI WHERE (LineId < 5000)"

Or I have to read line 5000 (how?) and build a condition on the content of that line?

Someone can help me?

Thank you

k50548-Feb-19 7:21

8-Feb-19 7:21

You'll need to use a command like
"DELETE FROM EVENTI WHERE (condition)",
but we can't tell you what that condition is, since we don't know the table structure.

You can't think of SQL tables like flat files. Successive SELECTS from a table won't necessarily return the rows in the same order.
All tables should have a PRIMARY KEY, which uniquely identifies a given row. You should be able to use this to select the rows you wish to delete. Alternatively you may have a timestamp or other identifying piece of information in the row that will help you choose which rows to delete.

steve_949661310-Feb-19 21:30

10-Feb-19 21:30

Thank you k5054.

I have not set a PRIMARY KEY in my table but the "DataOra" column contains a timestamp in the form "YYYY/MM/DD hh:mm:ss" so I think I could use this information to select the rows to delete.

But to do this I need to read the value of row 5000 without any information about this row, I mean that I have not a PRIMARY KEY and I just know the row number, is this information enought to read row 5000?

And if I read row 5000, then I can use a command like:
"DELETE FROM EVENTI WHERE DataOra < 'YYYY/MM/DD hh:mm:ss'"

or I have to convert in some way my timestamp?

Thank you

k505413-Feb-19 7:14

13-Feb-19 7:14

You should probably use a DATETIME field type for DataOra, but if you know that all your timestamps are 'YYYY/MM/DD hh:mm:ss', then then comparing as strings should work out OK.

How you truncate your data is somewhat dependent on exactly what your need is. If you just want to delete approximately 5000 records, you could do
SELECT count(*) FROM EVENT where DataOra < 'YYYY/MM/DD 00:00:00'
and adjust the date and or time up/down until you get about 5000 and then use that date as your comparison. Or maybe you would be happy with just deleting records before Feb 01/2019 in which case you could just use '2019/02/01 00:00:00' as your comparison

Alternatively, if you want to delete exactly 5000 records, ordering by timestamps, then this might work for you:

SQL

CREATE TEMP TABLE TMP(id INTEGER PRIMARY KEY AUTOINCREMENT, DataOra text);
INSERT INTO TMP(DataOra) SELECT DataOra from EVENT  order by DataOra;
DELETE FROM EVENT where DataOra <= (SELECT DataOra from TMP where id = 5000);

hope this helps. Don't forget to back up your data before you begin - just in case!

steve_949661320-Feb-19 20:55

20-Feb-19 20:55

Sorry for the delay.
Thank you for your detailed answer.
Where is created the TMP table? In the same database where there is the EVENTI table?
Thanks.

k505421-Feb-19 7:15

21-Feb-19 7:15

In my example the TMP table is created with the attribute TEMP, which means that the table will be TEMPORARY, and are dropped when the connection that created them closes. TEMP tables are created in the "temp" database (as per sqlite docs). Other than only existing for the current session, TEMP tables are just like regular tables - they can be indexed, altered, updated, etc.

steve_949661325-Feb-19 4:25

25-Feb-19 4:25

Thank you very much k5054 for your explanation! Thumbs Up | :thumbsup:

Mycroft Holmes8-Feb-19 12:50

Mycroft Holmes

8-Feb-19 12:50

If "DataOra" or "Evento" are not your line numbering system then you have a problem.

Assuming there is some sort of sequence in your data, date or number you could select the top 50000, get the sequence value and then delete anything less than the selected sequence. I would test this on a copy of the database Big Grin | :-D

select - How to get Top 5 records in SqLite? - Stack Overflow[^] add an orderby your sequence field and you get the values for your where condition.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

steve_949661310-Feb-19 21:56

10-Feb-19 21:56

Thank you Mycroft Holmes.

My DataOra column contains a timestamp in the form "YYYY/MM/DD hh:mm:ss", as I answered k5054, so this seems a good reference.

The data in my table is ordered, it is a sequence of events, so if I say

"SELECT * FROM EVENTI ORDER BY DataOra ASC LIMIT 5000"

I really select the first 5000 rows?

How I put the result of SELECT in my WHERE condition?

Thank you.

Mycroft Holmes11-Feb-19 10:53

Mycroft Holmes

11-Feb-19 10:53

Now get the latest date from the result set and delete all record where the date is less than the latest date.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP