Create Table Not Replicating SQL Select Results


all,

This is a very odd one...never encountered this before within SQL Developer

I have a SQL statement that returns a set of columns...but...when I create table as I get the same columns but with 2 of the columns containing each others data, e.g:

SQL Select:

COL1 COL2 COL3 COL4
___________________________________
AND 10200000017805 CG-4 CG-3

Create Table as :

COL1 COL2 COL3 COL4
___________________________________
AND 10200000017805 CG-3 CG-4

The SQL Select is correct and the Create Table As is wrong.

Here is my SQL:

Code: Create table ALTERNATENUMBERS as SELECT ctry, id, MAX(DECODE(tp,'EN', RN)) EN, MAX(DECODE(tp,'RN', RN)) RN, MAX(DECODE(tp,'AN', RN)) AN FROM (SELECT * FROM (SELECT ctry, id, tp, trn, listagg(routenum, '/') within GROUP ( ORDER BY routenum) over (partition BY id, tp) RN FROM (SELECT ctry, id, routenum, rteprior, trn, CASE WHEN rtetyp= 5 THEN 'EN' WHEN (rtetyp 5 AND trn =1) THEN 'RN' ELSE 'AN' END AS tp FROM (SELECT mn_rn.*, row_number() over (partition BY id order by lengthb(routenum) ASC, rteprior) trn FROM MN_RN ) ) ) GROUP BY ctry, id, tp, rn, trn ORDER BY ctry, id, tp, rn, trn ) GROUP BY ctry, id ORDER BY ctry, id

Unfortunately I cannot give you any data (too much of it) and small scale testing works, it's only when I run it on the 11million records do I get some (not all), just some of the data being mixed up between columns.

Now, I've tried:

1. Using SQLPLus - no joy
2. Creating the Table and then inserting the data into a blank table - also no joy
3. Using a VIEW - no joy, listagg doesn't work in VIEW tables

It's very odd and I do understand that without data it's hard to replicate the issue but does anyone have any ideas as to why this statement works as a SELECT but when written to a table has data anomolies

Posted On: Monday 31st of December 2012 01:51:49 AM Total Views:  252
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




statspack advised needed

The first thing is that your snapshot period of 120 minutes is way too high - go for 15 minute snaps. The major problem that sticks out a mile is your Soft parse ratio of 13.18% is really bad. You're doing 163 hard parses per second. This probably points to you app not using bind variables, which will mean that your application wont scale. Do a search for "hard parse" or "bind variables" on AskTom: http://asktom.oracle.com/pls/ask/fp=4950:1: Theres loads of great info there. Hard parsing is very CPU intensive, hence the high CPU figures. Concentrate on your parsing before looking at db file sequential read HTH
VIEWS ON THIS POST

120

Posted on:

Wednesday 7th November 2012
View Replies!

SQL-Backtrack by BMC

Has anyone implemented or looked at SQL-Backtrack from BMC Software If so, does it offer better functionality over OEM/RMAN in Oracle 8.1.7
VIEWS ON THIS POST

94

Posted on:

Wednesday 7th November 2012
View Replies!

RMAN recovery window and KEEP FOREVER

We've got the following backup policy for a 9R2 database using RMAN and Netbackup v6.0 - Level 0 - Sunday - Level 2 - Mon, Tues, Thu, Fri, Sat - Level 1 - Wed We've got a retention policy of 90 days, enforced through Netbackup policies. We've also got a requirement to be able to restore to the 1st of any month indefinitely, which would require restoring the last level 0 backup and rolling forward. I really just want to keep the necessary records in the RMAN repository to be able to restore to the 1st of any particular month and to any point in time in the last 90 days. I cant use KEEP FOREVER as using that on my oldest backup will mean no archivelog backups will ever become OBSOLETE so I'm unsure if I can enforce the business requirement without keeping every RMAN record for all backups forever.
VIEWS ON THIS POST

102

Posted on:

Wednesday 7th November 2012
View Replies!

RMAN compatiblity with lower version

hi, Can i use Oracle8i as catalog d/b for a Oracle9i Target database
VIEWS ON THIS POST

69

Posted on:

Wednesday 7th November 2012
View Replies!

unable to start up RAC DB

I have RAC DB and I wasn't able to start it up, below is the error: SQL> startup ORA-01078: failure in processing system parameters ORA-00828: specified value of shared_pool_reserved_size inconsistent with internal settings SQL> Obviously, there is a wrong value for shared_pool_reserved_size and I need to somehow get the value so I can change it to the right value. I wasn't able to oen, neither mount the DB so I can't get the information and correct it. How can I fix this issue thx.
VIEWS ON THIS POST

298

Posted on:

Wednesday 7th November 2012
View Replies!

Renaming a user in Oracle9i

Is it possible to rename a user instead of dropping and recreating it I am on Oracle9i2. I'll appreciate a sql statement or script to do that if it is possible.
VIEWS ON THIS POST

167

Posted on:

Wednesday 7th November 2012
View Replies!

tnsnames error messages

Hi , I am using Oracle 8i Personal Edition on Win98. When I attempted to start my listener service I get these error messages. TNSLSNR for 32-bit Windows: Version 8.1.5.0.0 - Production System parameter file is c:\oracle\ora81\network\admin\listener.ora Log messages written to c:\oracle\ora81\network\log\listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0))(PROTOCOL_STACK =(PRESENTATION=TTC)(SESSION=NS))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bassline01)(PORT=1521))( PROTOCOL_STACK=(PRESENTATION=TTC)(SESSION=NS))) Attempted to listen on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bassline01)(PO RT=1521))(PROTOCOL_STACK=(PRESENTATION=TTC)(SESSION=NS))) TNS-12538: TNS:no such protocol adapter TNS-12560: TNSrotocol adapter error TNS-00508: No such protocol adapter Could anyone explain what's going on here please!
VIEWS ON THIS POST

226

Posted on:

Wednesday 7th November 2012
View Replies!

problem with partitioned tables

The problem is that i want to patitioned my table on the week days and in a book from me, there is the information that i can partitioned the table on a lot of time units. Now my statment is: CREATE TABLE part ( stime DATE, etc. ) partition by range (stime) (partition sun values less than (to_date('MONDAY','DAY')), partition mon values less than (to_date('TUESDAY','DAY')), partition tue values less than (to_date('WEDNESDAY','DAY')), partition wen values less than (to_date('THURSDAY','DAY')), partition thr values less than (to_date('FRIDAY','DAY')), partition fri values less than (to_date('SATURDAY','DAY')), partition sat values less than (to_date('SUNDAY','DAY'))) So i get the following mistake: ORA-01846: not a valid date of the week I need a table partitioned of the week !!!!
VIEWS ON THIS POST

149

Posted on:

Wednesday 7th November 2012
View Replies!

TNS-12541: TNS:no listener PROBLEM in Win2k

I am getting the above error while i say Tnsping Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = rajat)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = r4king.research4.c om))) TNS-12541: TNS:no listener I have following tnsname.ora R4KING = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rajat)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = r4king.research4.com) ) ) And listener.ora ISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rajat)(PORT = 1521)) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = r4king.research4.com) (ORACLE_HOME = F:\oracle\ora92) (SID_NAME = r4king) ) (SID_DESC = (GLOBAL_DBNAME = r4king) (PROGRAM = extproc) (SID_NAME = r4king) (ORACLE_HOME = F:\oracle\ora92) ) ) and sqlnet.ora SQLNET.AUTHENTICATION_SERVICES= (NONE) NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME) can any body help me.
VIEWS ON THIS POST

85

Posted on:

Wednesday 7th November 2012
View Replies!

Replication. How do this?

I have three databases. Database A in one plans in the city of Belo Horizonte Database B in one plans in So Paulo's city Database C in one plans in the city of Rio de Janeiro As master database has the A, and in him, I have the white schema that has 4 tables that are called of PIPOCA, TAPIOCA, JUREMA, JUPIRA all they have the column city in common. I have to answer through snapshot the tables for the database B, C with interval of 30 seconds. And for the database that this in So Paulo's city should only go the data of the tables that owned to that city. The same happening for the City Rio de Janeiro. 1) Do I have to create a schema in you plan them B, identical C to the of the it plans A\t 2) do I create the snap in the banks B,C or in the A\t 3) it would not be better to create a dblink of the database B,C and later to create Views for the database passing IT @dbklink with the clause where city = ' So Paulo' and tb where city = ' Rio of Janeiro'\t 4) it is only enough to create the snapshot in it plans it ABC with the dblink configured correctly that the load is made surely\t 5) does anybody have some example script so that I can looks at\t\t Tanks
VIEWS ON THIS POST

49

Posted on:

Wednesday 7th November 2012
View Replies!

Renaming Package/Procedure

hi, Is it possible to Rename a PACKAGE/PROCEDURE rather than dropping and recreating
VIEWS ON THIS POST

41

Posted on:

Wednesday 7th November 2012
View Replies!

PLS-00801: internal error & PL/SQL: ORA-06544: PL/SQL: internal error, arguments:

i am getting these errors when i was trying to comile this function. Error1 - PLS-00801: internal error [20605] Error2 - PL/SQL: ORA-06544: PL/SQL: internal error, arguments: [20605], [], [], [], [], [], [], []. i am using pipeline function for getting records from a function, that function is calling to another function. please help me out. CREATE OR REPLACE FUNCTION funCBForEventdate(v_insured_id IN varchar2) RETURN myTableType PIPELINED IS bvForScreen varchar2(30) := NULL; v_given_name varchar2(150) ; v_ReportedClaim varchar2(30) ; v_NonReportedClaim varchar2(30) ; v_Company varchar2(60) ; v_policy_number varchar2(20) ; v_cession_id varchar2(30) ; v_BENEFIT_NAME varchar2(256) ; v_Par_Nonpar varchar2(30) ; v_aar NUMBER ; v_Status varchar2(30) ; v_StatusDate date ; CURSOR c1 IS SELECT distinct cb.cession_benefit_id, claimevent.event_date FROM ces_benefit_version bv, ces_risk_assessment ra, ins_name ins, ces_benefit cb, ces_cession c, trt_plan_benefit tpb, trt_plan tp, (SELECT ce.insured_id, event_date FROM clm_event ce, ins_insured ins, ins_name inm WHERE ins.insured_id = inm.insured_id AND ce.insured_id = ins.insured_id AND inm.insured_id = v_insured_id --'tstdat-525300' ) claimevent, (SELECT VALUE FROM co_company co, co_operating_parameter cop WHERE co.company_id = cop.owner_id AND company_oper_param_type_id = 'resoft-346596' /*CBS*/ AND company_code = 'RGAUSA' /*Logged in Managed Company*/) graceperiod WHERE cb.assuming_benefit_id = tpb.treaty_benefit_id AND tpb.treaty_plan_id = tp.treaty_plan_id AND cb.cession_id = c.cession_id AND cb.is_wip '1' AND ins.insured_id = claimevent.insured_id AND ( bv.change_effective_date = (claimevent.event_date - graceperiod.VALUE ) ) ) AND bv.cession_benefit_id = cb.cession_benefit_id AND bv.is_history = '0' AND ra.parent_benefit_version_id = bv.benefit_version_id AND ins.insured_name_id = ra.insured_name_id; -- AND ins.insured_id = :insured_id; BEGIN for c1_rec in c1 loop dbms_output.put_line('Main Event_Date- ' || c1_rec.event_date ); dbms_output.put_line('Main Cession Benefit ID- ' || c1_rec.cession_benefit_id); select funFilterBV(c1_rec.cession_benefit_id,c1_rec.event_date) into bvForScreen from dual; select ins.given_name, '' ReportedClaim, '' NonReportedClaim, (select company_name from co_company where company_id = c.CEDING_COMPANY_ID) Company, c.policy_number, c.cession_id, tpb.BENEFIT_NAME, nvl(getmlt(getabbrid(tp.PARTICIPATION_TYPE_ID),'resoft-101'),'Undefined') "Par/Nonpar", getAAR(bv.benefit_version_id, claimevent.event_date) aar, getmlt(getabbrid(bv.BENEFIT_VERSION_STATUS_ID),'resoft-101') Status, bv.CHANGE_EFFECTIVE_DATE StatusDate INTO v_given_name,v_ReportedClaim,v_NonReportedClaim,v_Company,v_policy_number,v_cession_id,v_BENEFIT_NAM E,v_Par/Nonpar,v_aar,v_Status,v_StatusDate From ces_benefit_version bv, ces_benefit cb, ces_cession c, trt_plan_benefit tpb, trt_plan tp, ins_name ins, (select ce.insured_id, event_date from clm_event ce, ins_insured ins, ins_name inm where ins.insured_id = inm.insured_id and ce.insured_id = ins.insured_id -- and inm.INSURED_NAME_ID = 'tstdat-525300' ) claimevent where cb.ASSUMING_BENEFIT_ID = tpb.TREATY_BENEFIT_ID AND tpb.TREATY_PLAN_ID = tp.TREATY_PLAN_ID AND cb.CESSION_BENEFIT_ID = bv.CESSION_BENEFIT_ID AND cb.cession_id = c.cession_id AND bv.BENEFIT_VERSION_ID = bvForScreen AND ins.insured_id = claimevent.insured_id ; PIPE ROW (pipeObjectFormat(bvForScreen,v_given_name,v_ReportedClaim,v_NonReportedClaim,v_Company,v_policy_num ber,v_cession_id,v_BENEFIT_NAME,v_Par_Nonpar,v_aar,v_Status,v_StatusDate)); exit when c1%NOTFOUND; END LOOP; dbms_output.put_line('Last Main' || bvForScreen ) ; RETURN; END funCBForEventdate; /
VIEWS ON THIS POST

238

Posted on:

Wednesday 7th November 2012
View Replies!

temp tablespace problem

Hi , OS : NT 4 DB : Oracle 8.1.5 We have temp tablespace which is occupied more space in our db.Can anyone help us to clear the temp tablespace.if we remove this, will it be a problem for our db. pls help.
VIEWS ON THIS POST

193

Posted on:

Wednesday 7th November 2012
View Replies!

table/view for grants

Hi This question should be an easy one. What table/view can I query to find out what privileges I have granted to users - much like dba_role_privs which can be queried to find out roles granted to users. Let say, I do: grant execute on any procedure to ; grant create any procedure to ; Which table/view can I check to find the things that have granted
VIEWS ON THIS POST

102

Posted on:

Wednesday 7th November 2012
View Replies!

RAC test

Hi Friends, We have 2 servers DELL POWER-EDGE 840 with 140GB SATA DISKS each. Can I used these to test install Oracle RAC for Windows or Linux Is there any other special requirement for hard disk to become a RAC database
VIEWS ON THIS POST

74

Posted on:

Wednesday 7th November 2012
View Replies!

Partition option in Enterprise Edition.

Just a Quick Question,If we are using Oracle Enterprise Edition,Can we use partition option which comes with the bunddle or else do we need to buy license seperately for using this feature from ORACLE Corp. Kind
VIEWS ON THIS POST

47

Posted on:

Wednesday 7th November 2012
View Replies!

precision & scale

Hi Does anyone know what is the precision of scale if we create a table with column number without specifying number(x,y) Just use for exmaple empno number, cheers
VIEWS ON THIS POST

39

Posted on:

Wednesday 7th November 2012
View Replies!

SQL*Loader-350

, I am getting the following problem from an SQL Loader file. This file ran correctly last year when the database was version 7.3.3, however is has since been upgraded to version 8i (running on NT) SQL*Loader-350: Syntax error at line 14. Expecting positive integer or column name, found keyword month. month integer external NULLIF month=BLANKS, The cmd file looks like:- set ORACLE_SID=DRPL f:\orant8i\bin\sqlldr userid=DRPL/DRPL control=f:\drpl\drplload\ctl\week_2003.ctl log=f:\drpl\drplload\log\week_2003.log The ctl file is:- OPTIONS ( DIRECT=TRUE ) LOAD DATA INFILE "f:\drpl\drpldata\week2003.dat" DISCARDFILE "f:\drpl\drplload\discard\week2003.dis" DISCARDMAX 999 APPEND INTO TABLE WEEK_CONVERSION FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ( year char, start_date date(8) "YYYYMMDD", day_number integer external, week_number integer external, year_week char, month integer external NULLIF month=BLANKS, quarter integer external NULLIF quarter=BLANKS, last_friday_of_month char, seq integer external) Many
VIEWS ON THIS POST

67

Posted on:

Wednesday 7th November 2012
View Replies!

Partitioning Issues

Hi! , I have a table whoose structure is *COMPANYID NOT NULL NUMBER(20) *FINANCIALYEAR NOT NULL NUMBER(4) *TLSDATAPOINTCODE NOT NULL VARCHAR2(20) QUARTER NUMBER(2) NUMBERVALUE NUMBER(20,5) STRINGVALUE VARCHAR2(4000) DATEVALUE DATE BOOLEANVALUE NUMBER(1) CREATEDBY NUMBER CREATEDDATE DATE MODIFIEDBY NUMBER MODIFIEDDATE DATE MANUALMODIFICATIONFLAG NUMBER(1) *- PK The table has 7005892 rows in it currently and it is going to triple in comming 3 months. I want to partition the table on Financialyear. 1) Can I partition the table with data 2) What are the pros and cons of partitioning on financialyear which not the left most column of the PK
VIEWS ON THIS POST

40

Posted on:

Wednesday 7th November 2012
View Replies!

Overhead of running dbms resource manager

I was looking at DBMS resource manager. What are the overheads of running this in Oracle This seems to be a good package, kind of controlling operating system indirectly. I will be interested to know downside of this. I have not found any negative comments on Oracle site on this(of course).
VIEWS ON THIS POST

97

Posted on:

Wednesday 7th November 2012
View Replies!

Oracle User Missing

Hi! I'm facing a strange situation with a user. I'm using Oracle 10g. The first time I was creating a user y putted it in SYSTEM tablespace. The user got SYSDBA privileges. My problema is that I can log with this user in SQLPlus, but when I log with with SYS as SYSDBA the user doesn't appear... I want to delete this user but I don't know how.
VIEWS ON THIS POST

31

Posted on:

Wednesday 7th November 2012
View Replies!

shell script for multiple sid

I would like to write a shell script that executes when i log in and prompts me for the SID to connect to with a menu like 1)sid = Xiamin 2)sid = World enter your choice : when i say one it should connect to Xiamin. any pointers on how to do this \t regards Hrishy
VIEWS ON THIS POST

210

Posted on:

Wednesday 7th November 2012
View Replies!

Result from v$sysstat

Please consider the following statement: PROMPT PROMPT Redo Log Buffer should be as close to 0 as possible PROMPT COLUMN value FORMAT 9999.9999 COLUMN name FORMAT a50 select substr(name,1,30) as name, value from v$sysstat where name ='redo log space requests'; / The result of the statement when run on my production db (oracle 8.1.7): "Redo Log Buffer should be as close to 0 as possible" NAME | VALUE ------------------------| ---------- redo log space requests | 4121.0000 This DB is up for months now, and I don't know if this is a bad number. The statement came from the oraYAPS (Yet Another Performance Script), from http://www.integer.org. It's a project about building a versatile performce script. the results of the statements inside that script looked fine, but I don't trust the result of this particular section. Can someone tell me how 'bad' this number is and what I should do
VIEWS ON THIS POST

101

Posted on:

Wednesday 7th November 2012
View Replies!

Practical Knowledge of ORACLE ADMINISTRATION

Hi to all, I want some brief administration knowledge of oracle administration which gives me full information about administration and tuning and troubleshooting which comes on a routinly basis. In short different scenerio and solution for that. THANKS IN ADVANCE MISTRY PRADIP 8I CERTIFIED DBA mistry_pradip@rediffmail.com
VIEWS ON THIS POST

58

Posted on:

Wednesday 7th November 2012
View Replies!

Parallel Query Option

The database server has 4 cpus, but the application is not using all these cpus due to tables not created with degree is... and the parallel init parameters not implemented. Can I alter the table (degree is 4) online etc.
VIEWS ON THIS POST

55

Posted on:

Wednesday 7th November 2012
View Replies!

Overriding an RMAN backup set/piece

Hi DBA's, I do take cold backups of my database (96G) using RMAN (to disk). My configuration parameter for disk storage is as follows: CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT $ORACLE_BASE/backup/db_name/%U'; This configuration creates a backup set with 3 pieces. Now, i need to set the configuration such that every time a backup is taken, it should override the present set/pieces & replace them with a new set/pieces, such that the old set/pieces would be automatically deleted. Is there the possibility of doing this Please help...
VIEWS ON THIS POST

199

Posted on:

Wednesday 7th November 2012
View Replies!

problem in opening the attachment file using c# .net

don't know what happen before its working but now got some error in opening the attachment file in my web page using c# .net for ex. filename: from doc1.xls change to doc1.xls%00... but it my oracle 10g database the filename is correct doesn't have the %00 any help\t here's my code in opening the attachment file (code behind). inserting the data in database private Boolean InsertUpdateData(OleDbCommand cmd) \t { \t\t\t string myConnection = "Provider=OraOLEDB.Oracle;Data Source=test;User Id=test;Password=test;"; \t\t\t OleDbConnection myConn = new OleDbConnection(myConnection); \t\t\t cmd.Connection = myConn; \t\t\t try \t\t\t { \t\t\t\t\t myConn.Open(); \t\t\t\t\t cmd.ExecuteNonQuery(); \t\t\t\t\t return true; \t\t\t } \t\t\t catch (Exception ex) \t\t\t { \t\t\t\t\t Response.Write(ex.Message); \t\t\t\t\t return false; \t\t\t } \t\t\t finally \t\t\t { \t\t\t\t\t myConn.Close(); \t\t\t\t\t myConn.Dispose(); \t\t\t } \t } \t protected void btnUpload_Click(object sender, EventArgs e) \t { \t\t\t string filePath = FileUpload1.PostedFile.FileName; \t\t\t string filename = Path.GetFileName(filePath); \t\t\t string ext = Path.GetExtension(filename); \t\t\t string contenttype = String.Empty; \t\t\t switch (ext) \t\t\t { \t\t\t\t\t case ".doc": \t\t\t\t\t\t\t contenttype = "application/vnd.ms-word"; \t\t\t\t\t\t\t break; \t\t\t\t\t case ".docx": \t\t\t\t\t\t\t contenttype = "application/vnd.ms-word"; \t\t\t\t\t\t\t break; \t\t\t\t\t case ".xls": \t\t\t\t\t\t\t contenttype = "application/vnd.ms-excel"; \t\t\t\t\t\t\t break; \t\t\t\t\t case ".xlsx": \t\t\t\t\t\t\t contenttype = "application/vnd.ms-excel"; \t\t\t\t\t\t\t break; \t\t\t\t\t case ".jpg": \t\t\t\t\t\t\t contenttype = "image/jpg"; \t\t\t\t\t\t\t break; \t\t\t\t\t case ".png": \t\t\t\t\t\t\t contenttype = "image/png"; \t\t\t\t\t\t\t break; \t\t\t\t\t case ".gif": \t\t\t\t\t\t\t contenttype = "image/gif"; \t\t\t\t\t\t\t break; \t\t\t\t\t case ".pdf": \t\t\t\t\t\t\t contenttype = "application/pdf"; \t\t\t\t\t\t\t break; \t\t\t } \t\t\t if (contenttype != String.Empty) \t\t\t { \t\t\t\t\t Stream fs = FileUpload1.PostedFile.InputStream; \t\t\t\t\t BinaryReader br = new BinaryReader(fs); \t\t\t\t\t Byte[] bytes = br.ReadBytes((Int32)fs.Length); string sq = @"'"; \t\t\t\t\t string strCmd; \t\t\t\t\t strCmd = "Insert into CATHLAB(NAME, CONTENTTYPE, DATA) Values ("; \t\t\t\t\t strCmd += ":NAME"+","; \t\t\t\t\t strCmd += ":CONTENTTYPE"+","; \t\t\t\t\t strCmd += ":DATA"+")"; OleDbCommand cmd = new OleDbCommand(strCmd); \t\t\t\t\t cmd.Parameters.Add(":NAME", OleDbType.VarChar).Value = filename; \t\t\t\t\t cmd.Parameters.Add(":CONTENTTYPE", OleDbType.VarChar).Value = contenttype; \t\t\t\t\t cmd.Parameters.Add(":DATA", OleDbType.Binary).Value = bytes; \t\t\t\t\t InsertUpdateData(cmd); \t\t\t\t\t Response.Redirect("recordsubmit.aspx"); \t\t\t\t\t lblMessage.ForeColor = System.Drawing.Color.Green; \t\t\t\t\t lblMessage.Text = "File Uploaded Successfully"; \t\t\t } \t\t\t else \t\t\t { \t\t\t\t\t lblMessage.ForeColor = System.Drawing.Color.Red; \t\t\t\t\t lblMessage.Text = "File format not recognised. Upload Image/Word/PDF/Excel formats"; \t\t\t } \t } to Open the file. protected void Page_Load(object sender, EventArgs e) \t { \t\t\t if (Session["dcode"] == null) \t\t\t { \t\t\t\t\t Response.Redirect("Default.aspx"); \t\t\t\t\t return; \t\t\t } \t\t\t if (Request.QueryString["ImageID"] != null) \t\t\t { \t\t\t\t\t string strQuery = "select Name, ContentType, Data from CATHLAB where id=:id"; \t\t\t\t\t OleDbCommand cmd = new OleDbCommand(strQuery); \t\t\t\t\t cmd.Parameters.Add(":id", OleDbType.Integer).Value = Convert.ToInt32(Request.QueryString["ImageID"]); \t\t\t\t\t DataTable dt = GetData(cmd); \t\t\t\t\t if (dt != null) \t\t\t\t\t { \t\t\t\t\t\t\t Byte[] bytes = (Byte[])dt.Rows[0]["Data"]; \t\t\t\t\t\t\t Response.Buffer = true; \t\t\t\t\t\t\t Response.Charset = ""; \t\t\t\t\t\t\t Response.Cache.SetCacheability(HttpCacheability.NoCache); \t\t\t\t\t\t\t Response.ContentType = dt.Rows[0]["ContentType"].ToString(); \t\t\t\t\t\t\t Response.AddHeader("content-disposition", "attachment;filename=" + dt.Rows[0]["Name"].ToString()); \t\t\t\t\t\t\t Response.BinaryWrite(bytes); \t\t\t\t\t\t\t Response.Flush(); \t\t\t\t\t\t\t Response.End(); \t\t\t\t\t } \t\t\t } \t } \t private DataTable GetData(OleDbCommand cmd) \t { \t\t\t DataTable dt = new DataTable(); \t\t\t string strConnString = "Provider=OraOLEDB.Oracle;Data Source=test;User Id=test;Password=test;"; \t\t\t OleDbConnection con = new OleDbConnection(strConnString); \t\t\t OleDbDataAdapter sda = new OleDbDataAdapter(); \t\t\t cmd.CommandType = CommandType.Text; \t\t\t cmd.Connection = con; \t\t\t try \t\t\t { \t\t\t\t\t con.Open(); \t\t\t\t\t sda.SelectCommand = cmd; \t\t\t\t\t sda.Fill(dt); \t\t\t\t\t return dt; \t\t\t } \t\t\t catch \t\t\t { \t\t\t\t\t return null; \t\t\t } \t\t\t finally \t\t\t { \t\t\t\t\t con.Close(); \t\t\t\t\t sda.Dispose(); \t\t\t\t\t con.Dispose(); \t\t\t } \t } please help!!!
VIEWS ON THIS POST

150

Posted on:

Wednesday 7th November 2012
View Replies!

Unable to Update Database with updated dataset.

I want to update my existing table with the new data i get from a excel file. I tried it like. i fetch data from excel using datareader and i get my database table into a dataset. Then i try to update my dataset with values in datareader by doing sm checks and comparisions. Now i want to update my updated dataset to datasource. This is not working. the dataset is getting updated but when i call da.update(ds). the datasource is not being filled with new alues. here is the source code. Please suggest me as why this is not wrking. /**************************************************/ string connectionString1 = "test connection string"; SqlConnection conn = new SqlConnection(connectionString1);SqlCommand cmd = new SqlCommand("", conn); conn.Open(); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); da.Fill(ds); /******************************************/ bool flag = false; int r = 0; int i = 0,j=0; try { string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\data.xls;Extended Properties=""Excel 8.0;HDR=YES;"""; DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");using (DbConnection connection = factory.CreateConnection()) { connection.ConnectionString = connectionString; using (DbCommand command = connection.CreateCommand()) { command.CommandText = "SELECT * FROM [sheet$]"; connection.Open(); using (DbDataReader dr = command.ExecuteReader()) { while (dr.Read()) { r = 0; string dlr = dr["Dealer Number"].ToString().Trim(); flag = false; foreach (DataRow dtr in ds.Tables[0].Rows) { r++; if (dtr[2].ToString().Trim() == dlr ) { //if (dr["Dealer Number"].ToString() == "05002") // da.UpdateCommand = new SqlCommand("UPDATE tbl_dealer SET dealernumber = 'test05002' where dealernumber=" + dr["Dealer Number"].ToString(), conn); flag = true;break; } } if (flag) { ds.Tables[0].Rows[r - 1][2] = dlr + "test"; ds.AcceptChanges(); Response.Write(dlr + " : " + ds.Tables[0].Rows[r-1][2]);Response.Write(""); i++; } } } } } ds.AcceptChanges(); bool get = ds.HasChanges(); int jk = da.Update(ds.Tables[0]); conn.Close();
VIEWS ON THIS POST

111

Posted on:

Wednesday 7th November 2012
View Replies!

Mysql Database problems

I am trying to insert some values into a table that i have created in mysql. I am trying to put values in from a form, so I am supplying parameters. There is a problem, when I check the database null values are being added into the database. Here is the c# code that I have written: void update_database() { //This inserts user data into database string strConString = ConfigurationSettings.AppSettings[ "conString" ]; OdbcConnection myConnection = new OdbcConnection(strConString); \t\t\t myConnection.Open(); // open connection \t\t\t string strInsert = "Insert tbl_expression_of_interest (name,email_address,address_1,address_2,town,county,postcode,where_heard) VALUES (@name,@email,@add1,@add2,@town,@county,@postcode,@whereheard)"; OdbcCommand myCommand = new OdbcCommand(strInsert, myConnection); // declare command object, parameters are sql string and connection//add parameters to the command object myCommand.Parameters.Add( "@name",name.Text ); myCommand.Parameters.Add( "@email",txtemailaddress.Text ); myCommand.Parameters.Add( "@add1",txtaddress1.Text ); myCommand.Parameters.Add( "@add2",txtaddress2.Text ); myCommand.Parameters.Add( "@town",txttown.Text ); myCommand.Parameters.Add( "@county",txtcounty.Text ); myCommand.Parameters.Add( "@postcode",txtpostcode.Text ); myCommand.Parameters.Add( "@whereheard",dropwhereheard.SelectedItem.ToString() ); myCommand.ExecuteNonQuery(); myConnection.Close(); } Is there anything that I am doing wrong
VIEWS ON THIS POST

144

Posted on:

Wednesday 7th November 2012
View Replies!

how to connect to oracle 10g XE to asp.net using C#

Hi I am very new on this and i just want to to ask how to connect to an oracle 10g XE to an .net using C#, and read each row of the database. I am having some difficulties in connecting the database with my web application.
VIEWS ON THIS POST

213

Posted on:

Wednesday 7th November 2012
View Replies!

INSERT into MYSQL results to NULL values.. I NEED HELP!!!

why does this code generates null values on the mysql table... i can't figure out why it does not insert the right inputs... please help me!! \t\t\t string strConnectionString = ConfigurationManager.ConnectionStrings["OdbcConnectionString"].ConnectionString; \t\t\t OdbcConnection myConnection = new OdbcConnection(strConnectionString); \t\t\t myConnection.Open(); \t\t\t string strQuery = "INSERT INTO trydatabase(Names,Addresses) VALUES (@Namex, @Addressex)"; \t\t\t OdbcCommand myCommand = new OdbcCommand(strQuery, myConnection); \t\t \t\t\t myCommand.Parameters.AddWithValue("@Namex", txtNames.Text); \t\t\t myCommand.Parameters.AddWithValue("@Addressex", txtAddresses.Text); \t\t\t myCommand.ExecuteNonQuery(); \t\t\t myConnection.Close(); i have already tried replacing @ with a but it does not help... it generate this error:: ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.41-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Namex, 'ssss'Addressex)' at line 1 please help me with this,, i really need everybody's help..
VIEWS ON THIS POST

180

Posted on:

Wednesday 7th November 2012
View Replies!

OracleBulkCopy - Excel to Oracle problem???

hi, I am trying to copy excel sheet data to Oracle. I am using ODP.NET the latest one. But getting this error: ORA-00604: error occurred at recursive SQL level 1 ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 8 ORA-00942: table or view does not exist How Can I fix this best
VIEWS ON THIS POST

315

Posted on:

Wednesday 7th November 2012
View Replies!

web.config connection

I am trying to access Mysql database from aspx.pages using a web config file. My web.config connection string is: \t\t\t \t I can connect to the Mydb using lengthy code on each page i.e. Dim myConnection As MySqlConnection Dim myDataAdapter As MySqlDataAdapter Dim myDataSet\t\t As DataSet Dim strSQL\t\t\t As String myConnection = New MySqlConnection("Database=Mydb;Data Source=10.xxx.x.xx;UId=xxxxx;Password=1234;") strSQL = "SELECT * FROM Diary ORDER BY diarydate ASC If the web.config code is correct, is there a way to access the Mydb from any aspx. page simpler than my present code.
VIEWS ON THIS POST

252

Posted on:

Wednesday 7th November 2012
View Replies!

Help me to understand the Stored Procedures in Oracle.

I am develepiong web application using csharp. I have been learning how to use stored procedures, so Have no experience in these.I got the stored procedures from internet below. But I am having problems to understand how this procedure call actual oracle sql becuase it is just showing "USP_ExtractSelectedRecord" where the sql suppose to be. How can I connect this a oracle sql where there are names of table on the database The code is below. publicDataTableFetchSelectedRecordSP(intAge) { OracleConnectionconnection=newOracleConnection(@connectionPath); connection.Open(); OracleCommandSelectCmd=newOracleCommand(); OracleDataAdapterAdpt=newOracleDataAdapter(); DataTableDtRecord=newDataTable(); try { SelectCmd.Connection=connection; SelectCmd.CommandType=CommandType.StoredProcedure; SelectCmd.CommandText="USP_ExtractSelectedRecord"; SelectCmd.Parameters.Add("recordSet",OracleType.Cursor); SelectCmd.Parameters[0].Direction=ParameterDirection.Output; SelectCmd.Parameters.Add("age_Param",OracleType.Int32); SelectCmd.Parameters[1].Value=Age; Adpt.SelectCommand=SelectCmd; Adpt.Fill(DtRecord); } catch(Exceptionex) { stringerror=ex.Message; } Adpt=null; SelectCmd=null; returnDtRecord; }
VIEWS ON THIS POST

58

Posted on:

Wednesday 7th November 2012
View Replies!

converting varchar date to date format

I am using mysql and stored date in varchar format, and compare that with current date,i have stored like(dd-mm-yyyy) in database i need to compare it with date in select query. How to take date from database in dd-mm-yyyy format. please help me.
VIEWS ON THIS POST

153

Posted on:

Wednesday 7th November 2012
View Replies!

how do i store the user name in ms visual studio 2005

Hi ! I'm new to MySQL, and i'm using ms visual studio 2005 C#. I would like to know how i can store the user name and password in MySQL from the CreateUserWizard from MV Visual Studio 2005 and how i can get the data thateach user keyed in MySQL Really
VIEWS ON THIS POST

93

Posted on:

Wednesday 7th November 2012
View Replies!

Connect to MySql database hosted under x10hosting

I need to connect to the MySql server used by x10hosting.com. No one on their site knows ASP.NET, so I am out of luck there. I have been trying for days to connect to the server, but keep getting error messages of different sorts. I cannot figure it out. I am willing to give out a templorary FTP username and password for the site to anyone who is willing to help. Please, this is important to get the site up. If someone could upload a file and get it to display the database data on the main screen, that would be very very helpful.
VIEWS ON THIS POST

275

Posted on:

Wednesday 7th November 2012
View Replies!

how to update row in excel using OleDB?

I am importing data from excel file. According to the values on each row, i want to write some statement in the last column. I want to do this without having to install Office or the Redistributable Primary Interop Assemblies Package. I saw a similar example http://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx saying it can be done using only OleDB, something like : command.CommandText="Update[Cities$]SetCity= \"Venice\"WHEREID=1"; command.ExecuteNonQuery(); But this wont work the way i am reading the spreadsheet. My code is like this :: string ExelConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" + "Mode=ReadWrite;ReadOnly=false;" + "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";", Server.MapPath("./Uploads/" + excelName)); OleDbDataAdapter ExcelDataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$A8:AZ]", ExelConnectionString); DataSet objDataSet = new DataSet(); ExcelDataAdapter.Fill(objDataSet, "ExcelTable"); for (int r = 0; r < dataTable.AsEnumerable().Count(); r++) { Slab slb = new Slab(); slb.ReceiveDate = dataTable.Rows[r].Field(0); slb.VesselName = dataTable.Rows[r].Field(1); slb.Lot = Convert.ToInt16(dataTable.Rows[r].Field(2)); slb.SlabSource = dataTable.Rows[r].Field(3); slb.CertificateNumber = dataTable.Rows[r].Field(4); if (DateTime.Compare(DateTime.Now, slb.ReceiveDate)>0) dataTable.Rows[r].Field(5) = "time travel not possible yet"; // the above line is of course wrong, but i need something like this. } The end result would be to read each row in the excel file and output something in the last column.
VIEWS ON THIS POST

213

Posted on:

Wednesday 7th November 2012
View Replies!

Datediff tell me if a weekend or holiday is in these dates

Good morning, I have been tasked with finding a way to remove time from a field if the record's two date fields have a holiday or weekend between those two dates. So far, I have not had any luck with this and this website http://www.artfulsoftware.com/infotree/queries.php#94 has not helped me either. EDIT: I am using MySQL for my database I would like to know if there might be a way to do something like case when date1 thru date 2 has weekend then 1 when date1 thru date2 has holiday then 2 else 0 end as field
VIEWS ON THIS POST

105

Posted on:

Wednesday 7th November 2012
View Replies!

Connection string in Web.Config

What would I need to do to my connection string inside my Web.Config file to use a MySQL database with my ASP.NET site Also, does anyone happen to know how I could export a table setup and data input from my Access database and upload it to my MySQL
VIEWS ON THIS POST

79

Posted on:

Wednesday 7th November 2012
View Replies!

Inserting data into Clob column uning ODP.Net

Hi , \t Is any one used ODP.Net providerto insert data into CLOB data type column in Oracle.
VIEWS ON THIS POST

119

Posted on:

Wednesday 7th November 2012
View Replies!

SqlDataSource and MySql

How I configure SqlDataSource to work with MySql .Net Connector 1.0.6 in VWD Please!! Thks! Ubirat
VIEWS ON THIS POST

99

Posted on:

Wednesday 7th November 2012
View Replies!

inserting field name from dropdown into query

I'm making a search page for a phone directory in ASP.NET 2.0 with the VWD. It can search (in theory) by last name, first name, and location. Each of these corresponds to a table column in the directory table. I also have a textbox to enter a search term with. My query looks like: SELECT first, last, phone FROM directory WHERE ( xxx LIKE 'yyy'); The yyy part is where the search term goes, like sm, smi, smith. This works and I have no problem with this part. I did use a parameter (SelectParameters/ControlParameter) for this. The part I'm having problems with is inserting the field name into the xxx part of the query. I tried to use a parameter for this, but the field name gets inserted with single quotes, like 'xxx'. This will not work because of the quote marks being added to the field name. I've tried using MyDropdown.SelectedValue in the xxx part, and this doesn't work either. Can anyone suggest a solution to this problem. I can't imagine that inserting a field name into a query isn't common. There has got to be a way of doing this.
VIEWS ON THIS POST

165

Posted on:

Wednesday 7th November 2012
View Replies!

How do I use ASP.NET to access Oracle on an HP Unix box?

HI, I have ASP classic code running on an NT Web Server (IIS 4) that currently accesses an Oracle 9 database on an HP Unix box. I have created a test win2k server with the .NET framework and would like to convert the code to ASP.NET. Obviously I will be using the Oracle Data Provider for .NET, but I am at a loss as where to start - the present ASP code uses a System DSN and Oracle SQL*NET software on the Web server, but I'm not sure what I need to connect to the Unix box using the new setup. I've installed the the ODP for .NET on the test Web server. I've also ended up installing the entire Oracle 9.2.0.1.0 package, although I think I only needed the client, if that. I'm unsure as to the significance of the TNSNames file, etc, being rather unfamiliar with Oracle altogether, so I'm really groping in the dark. Can anyone get me started
VIEWS ON THIS POST

120

Posted on:

Wednesday 7th November 2012
View Replies!

Sqlnet encryption with SecureCRT port forwarding

, I am trying to achieve sqlnet encryption without using Oracle ASO. Client - Windows Database Server - 9ir2 on Solaris (ssh enabled). I tried configuring SecureCRT with port forwarding (on client). Am running into issues. End up with ORA-12570: Tnsacket reader failure. The sqlnet tracefiles aren't being very useful. Additional question: How do you deal with dynamic ips while configuring this Has someone else set this up successfully Am guessing it's some trivial port forwarding setting that I've messed up.
VIEWS ON THIS POST

421

Posted on:

Thursday 15th November 2012
View Replies!

Resizing redo logs in 10g

, I need some help in knowing how to increase the size for redo logs on a 10g. Can someone post some pointers for that or steps to do so..
VIEWS ON THIS POST

101

Posted on:

Friday 16th November 2012
View Replies!

oracle streams

I am trying to setup oracle streams by following the instructions in the link below: DB1 is on a solaris 2.8 machine running oracle 10.2.0.1.0 DB2 is on an AIX 5.2 machine running oracle 10.2.0.1.0 When I get to step number 10 in the documenation below I get the following the error: SQL> declare 2 source_scn number; 3 begin 4 source_scn := dbms_flashback.get_system_change_number(); 5 dbms_apply_adm.set_table_instantiation_scn@DB2 6 ( source_object_name => 'pbh.taj', 7 source_database_name => 'DB1', 8 instantiation_scn => source_scn); 9 end; 10 / declare * ERROR at line 1: ORA-04052: error occurred when looking up remote object STRMADMIN.DBMS_APPLY_ADM@DB2.WORLD.COM ORA-00604: error occurred at recursive SQL level 1 ORA-02085: database link DB2.WORLD.COM connects to AIXSNAP.WORLD.COM Can somebody point me in the right direction and tell me what this error means and how can I fix it. Note: where ever it says the user "scott" I substitued the user "pbh", which is a valid user in both DB's and has DBA privs http://dbataj.blogspot.com/2008/01/o...tween-two.html Set up below parameters on both databases (db1, db2) 1. Enable ARCHIVELOG MODE on both database Reference: http://dbataj.blogspot.com/2007/09/h...elog-mode.html 2. Create Stream administrator User Source Database: DB1 SQL> conn sys@db1 as sysdba Enter password: Connected. SQL> create user strmadmin identified by strmadmin; User created. SQL> grant connect, resource, dba to strmadmin; Grant succeeded. SQL> begin dbms_streams_auth.grant_admin_privilege 2 (grantee => 'strmadmin', 3 grant_privileges => true); 4 end; 5 / PL/SQL procedure successfully completed. SQL> grant select_catalog_role, select any dictionary to strmadmin; Grant succeeded. Target Database: DB2 SQL> conn sys@db2 as sysdba Enter password: Connected. SQL> create user strmadmin identified by strmadmin; User created. SQL> grant connect, resource, dba to strmadmin; Grant succeeded. SQL> begin dbms_streams_auth.grant_admin_privilege 2 (grantee => 'strmadmin', 3 grant_privileges => true); 4 end; 5 / PL/SQL procedure successfully completed. SQL> grant select_catalog_role, select any dictionary to strmadmin; Grant succeeded. 3. Setup INIT parameters Source Database: DB1 SQL> conn sys@db1 as sysdba Enter password: Connected. SQL> alter system set global_names=true; System altered. SQL> alter system set streams_pool_size = 100 m; System altered. Target Database: DB2 SQL> conn sys@db2 as sysdba Enter password: Connected. SQL> alter system set global_names=true; System altered. SQL> alter system set streams_pool_size = 100 m; System altered. 4. Create Database Link Target Database: DB1 SQL> conn strmadmin/strmadmin@db1 Connected. SQL> create database link db2 2 connect to strmadmin 3 identified by strmadmin 4 using 'DB2'; Database link created. Source Database: DB2 SQL> conn strmadmin/strmadmin@db2 Connected. SQL> create database link db1 2 connect to strmadmin 3 identified by strmadmin 4 using 'DB1'; Database link created. 5. Setup Source and Destination queues Source Database: DB1 SQL> conn strmadmin/strmadmin@db1 Connected. SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(); PL/SQL procedure successfully completed. Target Database: DB2 SQL> conn strmadmin/strmadmin@db2 Connected. SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(); PL/SQL procedure successfully completed. 6. Setup Schema for streams Schema: SCOTT Table: Taj NOTE: Unlock scott schema because in 10g scott schema is locked by default Source Database: DB1 SQL> conn sys@db1 as sysdba Enter password: Connected. SQL> alter user scott account unlock identified by tiger; User altered. SQL> conn scott/tiger@db1 Connected. SQL> create table TAJ ( no number primary key,name varchar2(20),ddate date); Table created. Target Database: DB2 SQL> conn sys@db2 as sysdba Enter password: Connected. SQL> alter user scott account unlock identified by tiger; User altered. SQL> conn scott/tiger@db2 Connected. SQL> create table TAJ ( no number primary key,name varchar2(20),ddate date); Table created. 7. Setup Supplemental logging at the source database Source Database: DB1 SQL> conn scott/tiger@db1 Connected. SQL> alter table taj 2 add supplemental log data (primary key,unique) columns; Table altered. 8. Configure capture process at the source database Source Database: DB1 SQL> conn strmadmin/strmadmin@db1 Connected. SQL> begin dbms_streams_adm.add_table_rules 2 ( table_name => 'scott.taj', 3 streams_type => 'capture', 4 streams_name => 'capture_stream', 5 queue_name=> 'strmadmin.streams_queue', 6 include_dml => true, 7 include_ddl => true, 8 inclusion_rule => true); 9 end; 10 / PL/SQL procedure successfully completed. 9. Configure the propagation process Source Database: DB1 SQL> conn strmadmin/strmadmin@db1 Connected. SQL> begin dbms_streams_adm.add_table_propagation_rules 2 ( table_name => 'scott.taj', 3 streams_name => 'DB1_TO_DB2', 4 source_queue_name => 'strmadmin.streams_queue', 5 destination_queue_name => 'strmadmin.streams_queue@DB2', 6 include_dml => true, 7 include_ddl => true, 8 source_database => 'DB1', 9 inclusion_rule => true); 10 end; 11 / PL/SQL procedure successfully completed. 10. Set the instantiation system change number (SCN) Source Database: DB1 SQL> CONN STRMADMIN/STRMADMIN@DB1 Connected. SQL> declare 2 source_scn number; 3 begin 4 source_scn := dbms_flashback.get_system_change_number(); 5 dbms_apply_adm.set_table_instantiation_scn@DB2 6 ( source_object_name => 'scott.taj', 7 source_database_name => 'DB1', 8 instantiation_scn => source_scn); 9 end; 10 / PL/SQL procedure successfully completed. 11. Configure the apply process at the destination database Target Database: DB2 SQL> conn strmadmin/strmadmin@db2 Connected. SQL> begin dbms_streams_adm.add_table_rules 2 ( table_name => 'scott.taj', 3 streams_type => 'apply', 4 streams_name => 'apply_stream', 5 queue_name => 'strmadmin.streams_queue', 6 include_dml => true, 7 include_ddl => true, 8 source_database => 'DB1', 9 inclusion_rule => true); 10 end; 11 / PL/SQL procedure successfully completed. 12. Start the capture and apply processes Source Database: DB1 SQL> conn strmadmin/strmadmin@db1 Connected. SQL> begin dbms_capture_adm.start_capture 2 ( capture_name => 'capture_stream'); 3 end; 4 / PL/SQL procedure successfully completed. Target Database: DB2 SQL> conn strmadmin/strmadmin@db2 Connected. SQL> begin dbms_apply_adm.set_parameter 2 ( apply_name => 'apply_stream', 3 parameter => 'disable_on_error', 4 value => 'n'); 5 end; 6 / PL/SQL procedure successfully completed. SQL> begin 2 dbms_apply_adm.start_apply 3 ( apply_name => 'apply_stream'); 4 end; 5 / PL/SQL procedure successfully completed. NOTE: Stream replication environment is ready, just needed to test it. SQL> conn scott/tiger@db1 Connected. SQL> --DDL operation SQL> alter table taj add (flag char(1)); Table altered. SQL> --DML operation SQL> begin 2 insert into taj values (1,'first_entry',sysdate,1); 3 commit; 4 end; 5 / PL/SQL procedure successfully completed. SQL> conn scott/tiger@db2 Connected. SQL> --TEST DDL operation SQL> desc taj Name Null Type ----------------------------------------- -------- ---------------------------- NO NOT NULL NUMBER NAME VARCHAR2(20) DDATE DATE FLAG CHAR(1) SQL> --TEST DML operation SQL> select * from taj; NO NAME DDATE F ---------- -------------------- --------- - 1 first_entry 24-JAN-08 1
VIEWS ON THIS POST

145

Posted on:

Friday 16th November 2012
View Replies!

Data Access Layer using Stored Procedures - ODP.NET - C#

Hi! I have a webapp, in C#, the database is Oracle 10, Installed the ODP With ODAC 11 on my computer to use with visual studio 2008. Here's my question. How could I use the stored procedures from the database in my dataacess layer within a DataSet.xsd! It's something like this. The application will be build in 3 layers, I wanted to create a Data Access Layer setting the Procedures in the .xsd file, then configure it on the Business Logic Layer, but as I get the stored procedure from the server explorer window and drop it inside the DataSet, it creates a TableAdapter that is not working properly when I test it. the stored procedure should write some data within a table, I'm trying to make the 'preview data' passing the values but it's not writing the values on the DB. Any suggestion is really apreciated!
VIEWS ON THIS POST

166

Posted on:

Tuesday 20th November 2012
View Replies!

ORA-01427: single-row subquery returns more than one row

I am returning the ORA-01427 after running the query below. Can someone help me understand why I am returning the error and how to address it. Thank you. select b.value , b.name, p.value ...... (case when p.value 'G2' then null else (select c.oldvalue from ad_changelog c where c.record_id = b.c_bpartner_id and c.ad_table_id = 291 and c.ad_column_id = 4216 ) end) as oldtradeName from c_bpartner b, zz_receipt r, zz_recp_alloc a, m_product p, ad_user us where a.c_bpartner_id = b.c_bpartner_id and a.zz_receipt_id = r.zz_receipt_id and us.ad_user_id = r.createdby and p.m_product_id = a.m_product_id
VIEWS ON THIS POST

117

Posted on:

Friday 28th December 2012
View Replies!

Update Error

, I am trying to update a column based on another column in the same table (student table) and a column from another table (school table) Code is: update student_table set student_code = (select l.student_code from school_table l, student_table n where l.school = n.schoolname) I get the following error ORA - 01427 Single-row subquery returns more than one row
VIEWS ON THIS POST

159

Posted on:

Saturday 29th December 2012
View Replies!