How to insert some data into JDBC data source in REPORT9i ? - Reports

Hi :
How to insert some data into JDBC data source througth JAVA PDS in REPORT9i ?
Thanks lot !!!

xie
From the way you have asked the question, you seem to be have used the JDBC query functionality in Reports 9i, but you have a question "How to insert data into JDBC data source" and not about " How to query data from" JDBC data source.
Reports and hence the JDBC PDS has been for making DDL statements to get the data from the data source and not for "Inserting" data into database via DML statements. Hence you can't do that
If you had really wanted to know how to get data from JDBC datasource to reports, then the answer is you can do it with the very simple JDBC Query wizard in the reports builder. Please refer to the Online help of the reports Builder for JDBC query. You can also get more details from "Builing reports" manual at
http://otn.oracle.com/docs/products/reports/content.html
Thanks
The Oracle Reports Team

Thank you for your reply.
But i really want to insert data into DB througth JDBC PDS .
Does Oracle Report 9i support this function ???? 

xie
Oracle Report is a Reporting Tool and JDBC-PDS allows user to create query and fetch data from JDBC Data Sources .
JDBC-PDS can't be used for Inserting data into DB.
Oracle Reports Team

Related

Can one build a data warehouse using SQL rather than Warehouse Builder?

I would like to build a data warehouse purely using SQL statements. Where can I find the data warehouse extension of SQL statements? 
Why would you want to do that? Any specific reason?
Eric 
There aren't any data warehouse extensions. CREATE DATABASE... is the same regardless of the type of database, it's how else you design and use the database that makes it OLTP vs ODS vs DW. There are differences in some init.ora parameters, but there is no reason whatsoever you couldn't build a warehouse using only SQL. 
I am exploring the internal workings of Warehouse Builder.
I have written a SQL script to generate sample data to be inserted into tables, then write SQL script to do Extraction, Transformation and Loading using MERGE,, GROUP BY CUBE, DECODE, etc.
If anyone has any experience of just using SQL to perform ETL, would you share your expeience here? Thanks. 
Hi
I have posted a couple of blog entries which might be interesting for you on reverse engineering an OWB mapping from SQL, this may give you some useful insights;
http://blogs.oracle.com/warehousebuilder/newsItems/viewFullItem$198
http://blogs.oracle.com/warehousebuilder/newsItems/viewFullItem$132
Cheers
David

Sqldevloper migration -- help needed

Hi All,
Does sqldeveloper migration kit support custom transformation?
Like source blob column -> base64encode -> target blob colum
I would appreciate your help.
thanks,
kt 
Hi KT,
Can you provide a little more information on what you are doing.
What database are you migrating? Whats transformation do you want in the data ?
SQL Developer allows you to migration Sybase, SQL Server, MySQL, DB2, MS Access, Teradata to Oracle.
BLOBs and CLOBs are supported.
We offer online (through JDBC) and offline (using scripts, SQL*Loader, BCP,...) data move.
Regards,
Dermot
SQL Developer Team. 
Hi Dermot,
We are using OTRS application which has only clob column to hold the attachments in base64 encoding.since i couldn't map the blob to clob in the captured model, we have created blob column in the attachment table and after migration we created clob column,converted the blob to clob base64 encoded and dropped the blob and renamed the clob column.
we used separate java prog for the base64 encoding . is it possible to customize the transformation within sqldeveloper to do online migration?
Thanks,
kt 
Hi KT,
I don't quite understand.
SQL Developer only support migration from non Oracle database like Sybase,SQL Server,MySQL to Oracle.
Are moving data from an Oracle table to another Oracle table? or moving data from a non Oracle database to Oracle?
From reading between the lines it sounds like
1) You have OTRS running against an Oracle database and it references a CLOB column with Base64 values.
2) You have a non Oracle database and you are moving the data to Oracle.
3) The non Oracle database has BLOBs (Images?) which get converted to Oracle BLOBs
4) But you would prefer if the BLOBs where converted directly to CLOBS (with base64 values)
Is that correct?
Unfortunately you cannot extend SQL Developer to perform actions/conversions during the data move.
I think your process of moving directly to a BLOB first and then using your own tool to convert the BLOB to a CLOB(base 64) is the best practice.
Some other solutions which you may want to consider are
1) Use SQL Developer offline data move. Modify by hand the scripts to pump the data directly into a CLOB (base64) , maybe using Oracles UTL_ENCODE package
2) Use SQL Developers "Bridge Command".
http://dermotoneill.blogspot.com/2010/11/cross-database-bridge-statement.html
You would need to convert the the BLOB to a the CLOB(base64) on the source side.
BRIDGE newOracleTable AS sourceDatabaseConnection(SELECT BLOBTOCLOB_BASE64(col1) FROM sourceTable);
Above is only pseudo code, it with depend on what your source database was and if it even supported a BLOBTOCLOB_BASE64 type function.
All in all your current solution sounds best.
Regards,
Dermot
but data conversion during the data move is not supported yet.
Regards,
Dermot
SQL Developer Team. 
Hi Dermot,
Thanks for your time.
I just forgot to mention the source. the source was mysql.
What you pointed out is absolutely correct.
the mysql attachment table was 1GB and column datatype is longblob.
even i tried utl_encode.it took more than a day hrs to convert because we were to do base64 encoding( 76 bytes per line - as per OTRS expectation).so we went for java and used apache api for base64 encoding.which finished the job in 7 min.
so i was curious that it could be done by sqldeveloper.
thanks,
kt 
Hi kt,
Thanks for clarifying.
Data conversion is an interesting area, but so far we have concentrated on getting the value into oracle as is.
BLOB -> BLOB.
Will definitely look into this in the future, but any implementation will be some way off.
For the moment any conversion has to happen in the source before the data move, or oracle after the data move.
Or use a few tweaks to the offline data move scripts.
Thanks,
Dermot.
SQL Developer Team

SQL developer online data move

Hi,
I am using SQL developer 2.1 to migrate tables from Sybase 12 database to oracle 11g.
I have used online data move option for moving sybase data into oracle tables, but even after data move is completed not all rows have been moved from sybase tables to corresponding oracle tables. Some rows are missing but still there is no error messege being displyed, how to find out what's going wrong.
Is there any log file that is being generated in case of oracle sql developer?
Please help me with this. 
Sorry, can't give insider info, but do try the latest 3.0 release or 3.1 EA, for sure improvements there...
Hope that helps,
K. 
So there is no other way to sortout this issue? 
Hi <Please supply name>,
If online data move fails, then use the offline data move.
Offline data move generates 2 sets of scripts for you, which you can run.
1) Dump out the data from Sybase using BCP
2) Load in the data using Oracle SQL*Loader.
BCP and SQL*Loader are much more performant at moving data then online.
They have far better error handling/logging
And as the scripts are in plain text files, you can customize then.
Hope this helps,
Dermot
SQL Developer Team. 
Actually i can't use offline data move method as i do not have direct access to sybase server, thats why i was using online data move method 
Hi Mandark,
This is a weakness of SQL Developers online datamove.
It doesn't have logging and you cannot tweak the data move itself.
It very basic SELECT * FROM -> INSERT INTO ... under the covers.
If some rows are failing to be inserted,but some rows are being inserted, in the same table.
Then my guess is that the data in some rows doesn't "fit" into the target row/column.
And the datatype mapping used to convert the tables/columns was not good enough.
Ex: Sybase column of VARCHAR(10000) mapped to Oracle VARCHAR2(4000).
Some data which is greater than 4000 chars.
Which causes the insert to fail for those rows with >4000 chars. But ok for other rows.
The solution (in this example) is to map Sybase VARCHAR(100000) to Oracle CLOB.
You can specify the data type mapping in the Convert page of the migration wizard.
Another workaround is to use Copy To Oracle.
http://dermotoneill.blogspot.com/2010/11/copy-to-oracle.html
This is a very simple data move feature again. But it works slightly differently than Online Data Move.
It has no logging or tweaking either, but may do the job "online" for you.
You should temporarily turn off any check constraints, triggers, keys on each table you want to move data with using this method.
If all those fail, then the only sure fire way would be to use Offline Data Move.
Regards,
Dermot
SQL Developer Team. 
Thnks, that was great help

How to upload data from an Microsoft Excel sheet

I have set of lines of data in an excel sheet. The columns are in the same order of a database table columns. I would like to insert the excel data into that table directly with a single SQL statement. How to use the loader features of Oracle 8i? Please advice a method of uploading. If you know any good website describes data uploading from different sources, please do advice me the URLs of those sites.
Thanking you,
Sicnerely,
ForumAns
What version are you using? If in 9i, you can use external tables. Otherwise, you will need either SQL loader, or to use ODBC. If you send me an example of your data, I can create a sample of each for you.

Retrive data fron the Oracle DBMS without SQL

Hi!
I am trying to retrieve data from the Oracle DBMS using stored procedures (REF CURSOR). The data will be in the form of Master Detail fromat. Update to the data must be possible.
If I use the normal SQL the data traffic will be very high.
How this is possible? Can someone send me a demo source code to do this.
Thanks

Categories

Resources