Hot answers tagged

16

No. All (standard) packages are written in PL/SQL. The DBMS engine itself is written in C Edit: Oracle does include a JVM which runs on the same machine as the database itself, but that is not used to run any "DBMS related" code. It's only there to run stored procedures/functions written in Java.


11

In addition to Craig's advice I would like to advise you to examine the storage parameters of the affected tables. I am currently in a similar situation to yours. The largest table in my system contains ~200 million records and the performance was really bad. Tune the storage parameters of your tables and indexes Besides adding several indexes to the ...


8

After you create a connection to your database, execute the following two commands: USE <DATABASE NAME>; SELECT * FROM <TABLE NAME>; Then MySQL Workbench will show another pane with the results. This will be the entire contents of the table.


7

to_timestamp() expects the parameter to be given in seconds. Your value is in miliseconds. Just divide it by 1000: SELECT to_timestamp(1462975819.250); gives 2016-05-11 16:10:19.25+02


6

Deferred indexing would be nice, but isn't currently supported. Adding indexes has a cost - write performance. They're a trade-off. COPY won't help much if index maintenance is the main issue. The simplest solution is to drop the indexes, and re-create them when you're done importing. Since you can live with losing all your data if the DB crashes, you ...


6

There's a nice technology available in Oracle called External Tables. In your scenario, you could access your external plain-text data using External Tables from within the database and update your existing data in database with SQL statements you love and are used to – for example, INSERT, MERGE etc. In most cases, using Oracle supplied utilities is the ...


5

Incorrect syntax near '-'. This tells me that you've named a database, table or column with a dash in it, not that POJO is having an issue mapping your columns. As an example, if you've named your entity bar-none and POJO issues the following CREATE TABLE statement: CREATE TABLE dbo.foo(bar-none BIT); Or this one: CREATE TABLE dbo.foo-bar(none BIT); ...


5

Just saw the update, 60-col table with mostly VARCHAR(2k) fields -- that is (potentially) a monster table. First things first... You have to understand your bottleneck FIRST. On the app side, go all the way back to your single-threaded batch-insert solution (1/2/3k at a time) and begin running it and login to the DB machine and run a 'top' -- see how much ...


5

You can get most of those messages, but unfortunately not all. See my question on Stackoverflow regarding that. In general those messages (e.g. messages from a PRINT statement) are returned as warnings on the Statement object by the JDBC driver. To retrieve them use Statement.getWarnings() in a loop: Statement stmt = ...; stmt.execute("some_procedure"); ...


5

If someone can point me to a better, performant way of searching for a non-primary key data [...] Add an index in your database on the column(s) you are filtering on - in this case COL_5. The resulting execution plan should be a single index seek and the performanze difference negligible (or even zero). Also rather than getting the entire result set, ...


5

Seems like you are storing milliseconds since 1970-01-01 00:00:00 so Adam's answer is right. Another way would be to use interval addition to the 'epoch' timestamp (which is 1970-01-01 00:00:00): select timestamptz 'epoch' + 1462975819250 * interval '1 millisecond' as my_timestamp ; Tested: test=# select timestamptz 'epoch' + 1462975819250 * ...


4

The simple version would be to generate a hash from the CLOB and use this as a key. This key will fit into the allowed key width for your engine (900 SQL Server, 767 InnoDB, 1000 MyISAM etc). The hash can be generated by the engine as a computed column, trigger, or by some ETL process, or by the application There is a faint chance of collision (birthday ...


4

You are attempting to pass in a blank password. Odds are the sa account has a password. You should create a separate account for the application to use instead of using the sa account. Using the sa account is a major security issue.


4

Invoking direct path insert with the append hint causes an exclusive lock to be taken against the entire table, so having multiple threads performing the insert will not help. You would need to explicitly address a different partition with each insert ... insert /*+ append */ into my_table partition (partition_name_1) ... ... to get partition level ...


4

Instead of EXEC, you should put it between begin ... end, or use the { call } syntax. Here is an example, in the documentation: http://docs.oracle.com/cd/E11882_01/java.112/e16548/getsta.htm#i1014127


4

By creating a unique constraint on your table, you are telling the database "each time I try to insert a row in this table, please check that there is no existing row with this combination of columns the same. Oh, and make sure you do it in an atomic fashion so that if someone else tries to insert one at the same time as me, only one of us will succeed". ...


4

If you don't always want to pass all parameters, create a function with parameter defaults. The basic, simple form would be an SQL function without dynamic SQL: CREATE OR REPLACE FUNCTION func( _a int = NULL , _b text = NULL , _c text = NULL) RETURNS TABLE(...) AS $func$ SELECT ... FROM ... WHERE (speed = $1 OR $1 IS NULL) AND ...


4

The common solution is to express this as: interval '1 day' * ? with ? as a placeholder for a numeric value (possibly with a fractional part). The syntax tried in the question is rejected because for the SQL grammar, the entire expression interval '1 day' is a constant. It cannot be changed by injecting a placeholder into it, just like we couldn't write ...


3

Your trigger doesn't need the CALL keyword. create or replace trigger TRYTABLE_BEF_UPD_ROW before update or insert on TryTable for each row begin TryJavaHelperRun(:new.arg1, :new.arg2); end; / Generally, you should never use CALL in a PL/SQL block-- just execute the procedure. I assume that CALL is some ancient leftover syntactic remnant from some ...


3

Already answered at a parallel thread on serverfault: http://serverfault.com/questions/345253/oracle-11-updating-blob-field-db-file-sequential-read-inappropriately-slow/345588#345588 In Oracle, LOB (including BLOB) is stored as: in-the-table LOB - if the LOB is smaller than 3900 bytes it can be stored inside the table row; by default this is ...


3

You ask two quesions, firstly: is there a significant difference in terms of scan speed when searching for a data in a table that containts 350 columns than in table containing 25 columns? If you have an index defined, then no, there should be no difference. If you don't have an index, then there may be a difference. Assuming you don't have ...


3

Consider the example below: CREATE TABLE test.child ( id integer, p_id1 integer, p_id2 integer ); CREATE OR REPLACE FUNCTION named_cols() RETURNS SETOF test.child LANGUAGE sql AS $$ SELECT 0, 12, ...


3

Reading the log seems to make the problem fairly apparent: 2013-11-27T15:08:08.791992Z 9 Query drop table devicedata That's not mysql dropping the table. That's your application dropping the table. There's another line in the log that is very suspicious: ,s.Plan_Hash_Value FROM V$sql s WHERE s.Sql_Text LIKE 'SELECT /*+ FIRST_ROWS XX */ V$sql is ...


3

SELECT DISTINCT a.id, a.etc FROM years y JOIN activitymapper am ON (y.targetyearid = am.targetyearid) JOIN activity a ON (a.id = am.activityid) WHERE y.targetyearname IN ('Year1', 'Year2') Should do it. We're going from years to activity through activitymapper, selecting each activity only once (DISTINCT).


3

Pass the values as XML (in a VARCHAR). The following database all support shredding XML from the query language: Postgres MySQL SQL Server Oracle DB2 UDM Obviously, not every obscure database on the planet supports it. But the ones above are realistically the only ones you need to care about to hit 99.9% of the database market.


3

I try to make an hypothesis about the second question: Given the commit happens before the select (commit is at 14:16:06 and select is at 14:16:07) how is it that the select does not return the row inserted by the transaction? Transactions are managed by Spring. So it would be possible that before running the select spring has raised a start ...


2

Take a look at Comparison of database tools, there you find some tools supporting both HSQL and MySQL. Then it should be possible to export the MySQL DB and directly import to HSQL DB from the same GUI.


2

I am not sure whether you need compulsory callable statement or not. If you are ready to use other ways, best way is convert your Excel sheet into CSV file and you can directly load data from CSV file into database by using following syntax: "Load data infile "c:/filename.csv" into table tablename Fields terminated by ',' Lines terminated by '\r\n'" This ...


2

Ok, what I believe you are after is creating Java routines (ie, stored procedures implemented in Java and stored within DB2). You will want to follow the documentation in this section on Java routines. Pay special attention to the Creating, Building, and Deploying sections. And even though this is an older article, you may wish to check out this article "...



Only top voted, non community-wiki answers of a minimum length are eligible