Insert into .. select… na oraclu gubi dane.

Problem dotyczy serwra w wersji Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bi i pobrania danych z serwera zdalnego w wersji Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production.

Na serwerze zdalnym mamy tabelę z danymi do zaczytania.

Dla ustalenia uwagi niech nazywa się tab_1 i będzie utworzona poleceniem:

CREATE TABLE tab_1
(ID NUMBER,
param_id NUMBER,
param_value VARCHAR2(4000));

Na systemie do którego chcę pobrać z niej dane tworzę perspektywę:

create or replace view v_tab1 as select * from tab_1 @db_link;

Po wykonaniu polecenia select * from v_tab1; widzimy wszystkie dane.

Tworzymy teraz w systemie docelowym tabelę tymczasową tmp_data:

CREATE GLOBAL temporary TABLE tmp_data
(ID NUMBER,
param_id NUMBER,
param_value VARCHAR2(4000))  ON COMMIT PRESERVE ROWS;

i próbujemy wykonać polecenie

insert into tmp_data select  id,param_id,  param_value from v_tab1 where id BETWEEN :1 and :2;

i od razu sprawdzamy wynik

select * from tmp_data ;

I niestety w ostatniej kolumnie zamiast spodziewanych danych są same nulle.

Cofam transakcję:

Rollback;

Zachowanie dziwne, ale jakoś trzeba sobie poradzić i te dane pobrać.

Robię prawie to samo, ale pobierając dane wykonuję konkatenację ostatniej kolumny z pustym stringiem (czyli nullem).

insert into tmp_data select id,param_id, '' || param_value as param_value  from v_tab1 where id BETWEEN :1 and :2;
select * from tmp_data ;
rollback;

Tym razem dane w tabelce tymczasowej są prawidłowe.

Podobny efekt zaobserwowałem z inną tablą, gdzie ostatnia kolumna była typu number

W rzeczywistym systemie na którym zaobserwowałem problem.

You May Also Like

HISE

HISE stands for Human Interactions Service Engine.I have recently posted a proposal, which was accepted by Apache ODE PMC, which means the development will start soon.If you are interested in this project, you are welcome to join us.HISE stands for Human Interactions Service Engine.I have recently posted a proposal, which was accepted by Apache ODE PMC, which means the development will start soon.If you are interested in this project, you are welcome to join us.

Super Confitura Man

How Super Confitura Man came to be :)

Recently at TouK we had a one-day hackathon. There was no main theme for it, you just could post a project idea, gather people around it and hack on that idea for a whole day - drinks and pizza included.

My main idea was to create something that could be fun to build and be useful somehow to others. I’d figured out that since Confitura was just around a corner I could make a game, that would be playable at TouK’s booth at the conference venue. This idea seemed good enough to attract Rafał Nowak @RNowak3 and Marcin Jasion @marcinjasion - two TouK employees, that with me formed a team for the hackathon.

Confitura 01

The initial plan was to develop a simple mario-style game, with preceduraly generated levels, random collectible items and enemies. One of the ideas was to introduce Confitura Man as the main character, but due to time constraints, this fall through. We’ve decided to just choose a random available sprite for a character - hence the onion man :)

Confitura 02

How the game is played?

Since we wanted to have a scoreboard and have unique users, we’ve printed out QR codes. A person that would like to play the game could pick up a QR code, show it against a camera attached to the play booth. The start page scanned the QR code and launched the game with username read from paper code.

The rest of the game was playable with gamepad or keyboard.

Confitura game screen

Technicalities

Writing a game takes a lot of time and effort. We wanted to deliver, so we’ve decided to spend some time in the days before the hackathon just to bootstrap the technology stack of our enterprise.

We’ve decided that the game would be written in some Javascript based engine, with Google Chrome as a web platform. There are a lot of HTML5 game engines - list of html5 game engines and you could easily create a game with each and every of them. We’ve decided to use Phaser IO which handles a lot of difficult, game-related stuff on its own. So, we didn’t have to worry about physics, loading and storing assets, animations, object collisions, controls input/output. Go see for yourself, it is really nice and easy to use.

Scoreboard would be a rip-off from JIRA Survivor with stats being served from some web server app. To make things harder, the backend server was written in Clojure. With no experience in that language in the team, it was a bit risky, but the tasks of the server were trivial, so if all that clojure effort failed, it could be rewritten in something we know.

Statistics

During the whole Confitura day there were 69 unique players (69 QR codes were used), and 1237 games were played. The final score looked like this:

  1. Barister Lingerie 158 - 1450 points
  2. Boilerdang Custardbath 386 - 1060 points
  3. Benadryl Clarytin 306 - 870 points

And the obligatory scoreboard screenshot:

Confitura 03

Obstacles

The game, being created in just one day, had to have problems :) It wasn’t play tested enough, there were some rough edges. During the day we had to make a few fixes:

  • the server did not respect the highest score by specific user, it was just overwritting a user’s score with it’s latest one,
  • there was one feature not supported on keyboard, that was available on gamepad - turbo button
  • server was opening a database connection each time it got a request, so after around 5 minutes it would exhaust open file limit for MongoDB (backend database), this was easily fixed - thou the fix is a bit hackish :)

These were easily identified and fixed. Unfortunately there were issues that we were unable to fix while the event was on:

  • google chrome kept asking for the permission to use webcam - this was very annoying, and all the info found on the web did not work - StackOverflow thread
  • it was hard to start the game with QR code - either the codes were too small, or the lighting around that area was inappropriate - I think this issue could be fixed by printing larger codes,

Technology evaluation

All in all we were pretty happy with the chosen stack. Phaser was easy to use and left us with just the fun parts of the game creation process. Finding the right graphics with appropriate licensing was rather hard. We didn’t have enough time to polish all the visual aspects of the game before Confitura.

Writing a server in clojure was the most challenging part, with all the new syntax and new libraries. There were tasks, trivial in java/scala, but hard in Clojure - at least for a whimpy beginners :) Nevertheless Clojure seems like a really handy tool and I’d like to dive deeper into its ecosystem.

Source code

All of the sources for the game can be found here TouK/confitura-man.

The repository is split into two parts:

  • game - HTML5 game
  • server - clojure based backend server

To run the server you need to have a local MongoDB installation. Than in server’s directory run: $ lein ring server-headless This will start a server on http://localhost:3000

To run the game you need to install dependencies with bower and than run $ grunt from game’s directory.

To launch the QR reading part of the game, you enter http://localhost:9000/start.html. After scanning the code you’ll be redirected to http://localhost:9000/index.html - and the game starts.

Conclusion

Summing up, it was a great experience creating the game. It was fun to watch people playing the game. And even with all those glitches and stupid graphics, there were people vigorously playing it, which was awesome.

Thanks to Rafał and Michał for great coding experience, and thanks to all the players of our stupid little game. If you’d like to ask me about anything - feel free to contact me by mail or twitter @zygm0nt

Recently at TouK we had a one-day hackathon. There was no main theme for it, you just could post a project idea, gather people around it and hack on that idea for a whole day - drinks and pizza included.

My main idea was to create something that could be fun to build and be useful somehow to others. I’d figured out that since Confitura was just around a corner I could make a game, that would be playable at TouK’s booth at the conference venue. This idea seemed good enough to attract >Conclusion