SQL Query question (count dates for each month)


,

Hope you can help me with the following, i have the following view availble:
DD/MM/YYYY


ENTITY | StartDate | EndDate | CodeA | CodeB | Revenue | Currency
AZERT | 01/01/2011 | 02/01/2011 | SU | BOLD | 100 | EUR
AZERT | 28/01/2011 | 02/02/2011 | SU | BOLD | 500 | EUR

Can someone help with a query to pull the data so that I get the following summed

ENTITY | YYYY.MM | CodeA | CodeB | DAYS | TIMES | Revenue | Currency
AZERT | 2011.01 | SU | BOD | 5 | 2 | 500 | EUR
AZERT | 2011.02 | SU | BOD | 1 | 0 | 100 | EUR


Where YYYY.MM is created depending on the difference between Sdate and EDate.
And DAYS is the variance between the start and end day in the right month
And TIMES is the number of times that the StartDate occurs in that month
Revenue splitted depening how many days there are.
Posted On: Monday 31st of December 2012 01:53:24 AM Total Views:  2645
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




windows authentication/local system logon - Oracle XA

hi, I am fairly new, and a little confused. If we want to allow applications such as oracle-xa to log on via dtcc etc, and the user login is currently set to use the local system account, does that user have to have an entry in ora_dba group to be allowed to connect to the database Can you not use windows authentication without the user being in that group. I thought that group allowed any specified user to basically connect as SYS and with DBA priviledges. We don't really want the user to have dba priviledges, but do want them to be able to log on to select things from the database. We are getting errors because the XA transaction manager is attempting to perform recovery with XA resource manager unsuccessfully. It cannot log on to get the in-doubt transactions I believe. But everything else that needs to be in place is in place, just that the windows user is not in the ora_dba group, but he is set to use the local system account to log in.
VIEWS ON THIS POST

250

Posted on:

Monday 12th November 2012
View Replies!

what is the max. number of records in a table

I would like to know what is the max number of rows we can put in partitioned table or per partition. Hear i have to insert 1.2 billion per partition. i would like to know your openion. Thank
VIEWS ON THIS POST

251

Posted on:

Monday 12th November 2012
View Replies!

web application from DB

This is sethu murugan from chennai. Iam working for a Finance Company in chennai. Iam in a situation to develope an web based applciation [ should be running on a browser ] for my MD. The situation is, we have oracle 8i database. We have developed an applciation and it is smoothly running. Now My md wants to see some critcal reports from his laptap which is not connected to the office network. He does not want to me to load any apllication in his sytem. So i have to use the browser for this. I have seen in some places that ppl r using broser for data view etc. Now I dont know any idea how to acheive it and where to start and wht to use. Can any one of u help me in this regard. 1. Wht to use 2. where to start 3. wht all i need 4. With orale 8i database itself can i acheive this. 5. I was searching thru the ineternet 6. I found some thing called webdb. 7. We have that media [ the pack was n't opend for past 3 yrs ] 8. Can i use that to acheive wht i want If any one is around chennai and willing me to help on this, pls buzz me on my mobile 31109842 if u cant reach me pls leave voice mail and i will keep i touch with u.. or u can reach me on my email sethumurugan@yahoo.com Thank u all.. Sethu
VIEWS ON THIS POST

242

Posted on:

Monday 12th November 2012
View Replies!

Virtual Private Database Problem

I am testing Virtual Private database i want to restrict access test table in hr schema EMPLOYEE_ID FIRST_NAME SALARY ----------- -------------------- ---------- 201 ABC 13000 202 XYZ 6000 203 USER3 6500 204 USER4 10000 205 USER5 12000 For Example when user abc query the test table he can't see other users record 1-i have created a functtion in hr schema by using the following script create or replace function policy_funct(owner varchar2,objname varchar2) return varchar2 is where_clause varchar2(200); begin where_clause:='fisrt_name=sys_context(' 'USERENV' ',' 'SESSION_USER' ')'; return where_clause; end; 2-Then i had added the policy function begin dbms_rls.add_policy(object_schema=>'HR',object_name=>'TEST', policy_name=>'test_policy',function_schema=>'SYS', policy_function=>'policy_func',sec_relevant_cols=>'SALARY'); end; 3-Then i connect as ABC user and try to execute the following query select * from hr.test * ERROR at line 1: ORA-28110: policy function or package HR.POLICY_FUNC has error Policy Function is valid when i checked its status Can anybody help me in this regard thanx in advance
VIEWS ON THIS POST

189

Posted on:

Monday 12th November 2012
View Replies!

Value of a variable

Hi , FOR c2 in (select name from emp) LOOP FOR i in 1..40 LOOP vvci := Replace(upper(vvci), ' :INTERMEDIATETABLE.VCOLUMN'||to_char(i), c2.vcolumn||i); END LOOP; END LOOP; In the above code I want to get the value of c2.vcolumn||i. Is there any function which returns the same in PL/SQL.
VIEWS ON THIS POST

241

Posted on:

Monday 12th November 2012
View Replies!

Utl

Hi all, I have a bug to be fixed in Utl_file .Utl file writes to the most recent open file . My proc will open the file, write the data from the cursor into it and will set the flag to 'y'such that the file remains open .And at the end of the loop the file gets closed. > Sample data: > > ID PROG_ID FILENAME > > > > 1245 Cmmm M (MORE THAN 1) > > 2444 Cdr D > > 7878 SOp A > > 7899 SOp A > > 1245 PROS M (MORE THAN 1) > > 2344 PINT B > > > According to my procedure , first data goes to M file, followed by D, A, A,next record (5th record) should go to 'M' file but utl_file writes the data to the most recent open file where the file is 'A'. How can i avoid this and these files names are generated during the process.
VIEWS ON THIS POST

233

Posted on:

Thursday 15th November 2012
View Replies!

unable to open DB

Only one instance is using this DB....coz while creation of DB i had given MAXINSTANCES 1...... am getting below error while opening DB....can any 1 let me know if i am going wrong ne where or do i have to set any thing else....or the very basic close and open wont work\t\t\t\t Code: SQL> alter database close; Database altered. SQL> alter database open; alter database open * ERROR at line 1: ORA-01531: a database already open by the instance SQL> shutdown ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> Am confused Abhay.
VIEWS ON THIS POST

253

Posted on:

Thursday 15th November 2012
View Replies!

ultra search in oracle 9i

hi gurus, Yesterday i attented the oracle 9i seminar and they said that in oracle 9i there is new tool called ultra search which can search through your multiple databases. but when today i tried to install the search i am not able to do so. please help me how can i install the search please help ......
VIEWS ON THIS POST

351

Posted on:

Thursday 15th November 2012
View Replies!

Tunning

Hi Guys, We have performance problem in Database. SQL queries taking too much time. I found that some users are created under SYSTEM tablespace. Table Data and Index are in the same drive. I don't know where I have to tune the database either in DB Buffer or Shared memory etc., Can anyone help me please Cheers Ram
VIEWS ON THIS POST

225

Posted on:

Thursday 15th November 2012
View Replies!

tuning private SQL area?

Why does my PARSE times always equal to EXECUTE times when I query data using the same sql statement with SQLPLUS The initiation parameter OPEN_CURSORS equals to 100, is it too small How can I tune it Any suggestions will be appreciated.
VIEWS ON THIS POST

233

Posted on:

Thursday 15th November 2012
View Replies!

Tuning - Low RAM - Load/Performance issue

Environment: Windows 2000, TNS for 32-bit Windows: Version 8.1.7.0.0 I have this windows 2000 server hosting 15 GB database (the only one on server) which is now working at 98 % memory utilization for a month now. Its gets really slow once a while. Now I have to 10 more new tablespaces (4 new schemas) to create on this db. The server is low on RAM - DB Buffer Caching issue EVENT TOTAL_WAITS ---------------------------- ------------------- buffer busy waits 1646206 (1144088 yesterday) NAME VALUE -------------------------- ---------- free buffer inspected 1453 (1168 yesterday) Currently the parameters are set at db_block_size = 4096 db_block_buffers = 94372 DB_BLOCK_MAX_DIRTY_TARGET = 94372 open_cursors = 300 max_enabled_roles = 30 db_file_multiblock_read_count = 8 db_block_buffers = 94372 shared_pool_size = 236M large_pool_size = 614400 java_pool_size = 20971520 Please advice - how can I improve/handle the current scenario. Do we definetly need more RAM. What else can we do - Increase db_block_buffers = and Decrease DB_BLOCK_MAX_DIRTY_TARGET = more
VIEWS ON THIS POST

274

Posted on:

Thursday 15th November 2012
View Replies!

Tuening issues

Hi Folks,,,, please help me,,,,,, OS - Win NT DB - Oracle 8.1.5 Prod. I get the following error often whenever i run the batch process,,, and other users also getting the same error in the client side. " User requested cancel of current operation" I think this is the tuening issue of DB. Memory - 512. Can anyone give some solution for this,, bcoz every user is struggling to access with the db. Thanx in advance.
VIEWS ON THIS POST

260

Posted on:

Thursday 15th November 2012
View Replies!

trigger question

I have a user request that when a new row being inserted into certain table, s/he wants to be notified(via e-mail, pager etc) about when, what has been changed and who changed it. Can this being done by trigger If yes, how If no, what can help to implement this feature
VIEWS ON THIS POST

337

Posted on:

Thursday 15th November 2012
View Replies!

To which Tablespace the user System be attached to ????

Should the user System be attached to the System table space or is it advisable to attach it to some other. Please suggest. Also whats the difference between sys and system. I know sys is the super user but what is system.
VIEWS ON THIS POST

222

Posted on:

Thursday 15th November 2012
View Replies!

Statistics (URGENT)

how does computing statistics improve performance in Oracle
VIEWS ON THIS POST

232

Posted on:

Thursday 15th 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

280

Posted on:

Thursday 15th November 2012
View Replies!

sqlcode and sqlerrm

Hi Friends I have and Oracle Error code, If I want to know the error message of that how do I find out. Shailendra
VIEWS ON THIS POST

241

Posted on:

Thursday 15th November 2012
View Replies!

SQL Tuning

I did an explain plan on the following query. The plan is appended below. I am wondering if there is anyway to tune this query. I tried essentially moving things around (order of the tables and the WHERE and AND clauses) but nothing changed. I am a novice to performance tuning so please bear with me. Many thanks in advance! Sankar. Query: ===== EXPLAIN PLAN SET STATEMENT_ID = 'SMTEST02' FOR SELECT a.deal_id, a.customer_company_nm, a.deal_nm, a.deal_revenue, a.deal_status_val, a.create_dt deal_action_date, b.sun_id sales_rep_id, c.first_nm sales_rep_first_nm, c.last_nm sales_rep_last_nm FROM nsda_user c, deal_account_team b, deal a WHERE b.title_nm = 'PRIMARY SALES REP' AND a.deal_id = b.deal_id AND b.sun_id = c.sun_id (+); =========================================== Table Counts : FYI =========================================== SQL> select count(*) from deal_account_team; COUNT(*) ---------- 10000 SQL> select count(*) from nsda_user; COUNT(*) ---------- 34 SQL> select count(*) from deal; COUNT(*) ---------- 30000 ======================================== Plan Output: =========== SELECT lpad(' ',level-1)||operation||' '||options||' '||object_name "Query Plan", cardinality "Rows", cost "Cost" FROM PLAN_TABLE CONNECT BY prior id = parent_id AND prior statement_id = 'SMTEST02' START WITH id = 0 ORDER BY id; Query Plan Rows Cost ---------- ---- ---- SELECT STATEMENT 10000 47 HASH JOIN 10000 47 HASH JOIN OUTER 10000 11 INDEX FAST FULL SCAN SYS_C00156567 10000 5 TABLE ACCESS FULL NSDA_USER 34 2 TABLE ACCESS FULL DEAL 30000 30
VIEWS ON THIS POST

261

Posted on:

Thursday 15th November 2012
View Replies!

sql functions to trim absolute path to display just filename

You can do it this way in perl script. #!/usr/bin/perl ################################################################################ # Author: Malay Biswal # Date : 2009-08-25 # ################### use strict; use constant LOOKUP => "D:/perl/perl_data/file.txt"; my $line;my@x;my $x;my $len;my $len;my $name; MAIN: { open(IN, "
VIEWS ON THIS POST

246

Posted on:

Thursday 15th November 2012
View Replies!

SQL commands from SHELL prompt

Hi Friends, Is it possible to execute SQL or PL/SQL commands from Unix shell prompt. For example, I want to execute a CREATE COMMAND from "$" prompt. eg. $ sqlplus SCOTT/TIGER CREATE TABLE t1(I INT)
VIEWS ON THIS POST

278

Posted on:

Thursday 15th November 2012
View Replies!

single table ONLY recovery

I have the following setup Database is in archive log mode and one particular table got corrupted or dropped. How can I recover that one table ONLY when database is online and people are working I have been reading up about point in time recovery I dont think that is what I want to do. My understanding is that will bring the entire database to a state as of a particular time. Most of the test cases I see online mention restoring all the datafiles (not control files and logs). I don't think that is what I want to do either since that will affect the people working. Can somebody outline the steps I need to follow to test this scenario or point me to some good online documenation that explains how to do this. My gut feeling tells me this sounds a bit complex.
VIEWS ON THIS POST

252

Posted on:

Thursday 15th November 2012
View Replies!

SELECT ANY TABLE and VIEWS in different schema

I'm confident that this a stupid question but I'm rather confused as to what's is going on here. If anyone can shed any light on this it would be much appreciated: Code: system@DEV> system@DEV> SET LINES 132 system@DEV> SET PAGES 9999 system@DEV> system@DEV> DROP USER proxy CASCADE; User dropped. system@DEV> DROP USER tableuser CASCADE; User dropped. system@DEV> DROP USER viewuser CASCADE; User dropped. system@DEV> system@DEV> define the_pw=&1 Enter value for 1: qwerty+123 system@DEV> system@DEV> CREATE USER proxy IDENTIFIED BY "&the_pw" 2 DEFAULT TABLESPACE USERS 3 TEMPORARY TABLESPACE TEMP; old 1: CREATE USER proxy IDENTIFIED BY "&the_pw" new 1: CREATE USER proxy IDENTIFIED BY "qwerty+123" User created. system@DEV> system@DEV> CREATE USER tableuser IDENTIFIED BY "&the_pw" 2 DEFAULT TABLESPACE USERS 3 TEMPORARY TABLESPACE TEMP 4 QUOTA UNLIMITED ON USERS; old 1: CREATE USER tableuser IDENTIFIED BY "&the_pw" new 1: CREATE USER tableuser IDENTIFIED BY "qwerty+123" User created. system@DEV> system@DEV> CREATE USER viewuser IDENTIFIED BY "&the_pw" 2 DEFAULT TABLESPACE USERS 3 TEMPORARY TABLESPACE TEMP; old 1: CREATE USER viewuser IDENTIFIED BY "&the_pw" new 1: CREATE USER viewuser IDENTIFIED BY "qwerty+123" User created. system@DEV> system@DEV> GRANT CREATE SESSION TO proxy; Grant succeeded. system@DEV> GRANT CREATE SESSION TO viewuser; Grant succeeded. system@DEV> system@DEV> CREATE TABLE tableuser.t ( 2 id NUMBER 3 ); Table created. system@DEV> system@DEV> GRANT SELECT ON tableuser.t TO viewuser; Grant succeeded. system@DEV> system@DEV> CREATE VIEW viewuser.tv AS 2 SELECT * FROM tableuser.t; View created. system@DEV> system@DEV> GRANT SELECT ON viewuser.tv TO proxy; Grant succeeded. system@DEV> system@DEV> CONNECT viewuser/&the_pw Connected. system@DEV> system@DEV> SELECT * FROM tv; no rows selected system@DEV> system@DEV> CONNECT proxy/&the_pw Connected. system@DEV> system@DEV> SELECT * FROM viewuser.tv; SELECT * FROM viewuser.tv * ERROR at line 1: ORA-01031: insufficient privileges system@DEV> system@DEV> CONNECT system Connected. system@DEV> system@DEV> GRANT SELECT ANY TABLE TO viewuser; Grant succeeded. system@DEV> system@DEV> CONNECT proxy/&the_pw Connected. system@DEV> system@DEV> SELECT * FROM viewuser.tv; no rows selected system@DEV> system@DEV> SPOOL OFF Why can user proxy only select from the view when viewuser has SELECT ANY TABLE privileges How can I allow PROXY to SELECT from the view without granting SELECT ANY TABLE to VIEWUSER; minimum privileges and all that. I'm sure this is all basic stuff and I'm embarrased to say I don't really understand what's going on here
VIEWS ON THIS POST

234

Posted on:

Thursday 15th November 2012
View Replies!

Search a value into DB

Hi! I am looking for a script to search a value into every tables of a DataBase (Unix - Oracle 8.0.4).
VIEWS ON THIS POST

252

Posted on:

Thursday 15th November 2012
View Replies!

Scheduling a SP call

Hi , Can anyone please tell me how I can schedule the call of my stored preocedure. I want it to be called once every week. I did not want to use cron jobs. I am using Oracle 8i.
VIEWS ON THIS POST

252

Posted on:

Thursday 15th November 2012
View Replies!

RMAN - Learning Material

I am not aware of RMAN (for 8.0.x/NT) and its usage. Can someone suggest me some goodies on RMAN on Internet sites.
VIEWS ON THIS POST

220

Posted on:

Friday 16th November 2012
View Replies!

Replication for Standard Unleaded Edition

I have recently read in an article (by Prakash) regarding 'Simulating Multi-master Replication in Standard Edition'. With the difference of costs between Standard and Enterprise editions, customers are having large interest in this solution. Can you please give a prototype on how to do this. Examples and scripts would be appreciated. buddyl
VIEWS ON THIS POST

220

Posted on:

Friday 16th November 2012
View Replies!

re: do not know why the script does not run ??

hi! Guys , Heres the code .. ---------------------------------------------------------- declare type n_array is table of number index by binary_integer; type d_array is table of varchar2(10) index by binary_integer; cursor c1 is select empno,ename from emp; v_empno n_array; v_ename d_array; begin open c1; loop fetch c1 bulk collect into v_empno,v_ename; forall i in 1..v_empno.count update emp set sal = 0 where empno = v_empno(i); exit when c1%notfound; end loop; end ; / ----------------------------------------------------------- Here's my error message .. scott@ORACLE> @c:\ex.sql declare * ERROR at line 1: ORA-03113: end-of-file on communication channel ORA-24323: value not allowed Error accessing package DBMS_APPLICATION_INFO ERROR: ORA-03114: not connected to ORACLE scott@ORACLE> -------------------------------------------------------------- What is the problem and how can I fix it to run the program \t
VIEWS ON THIS POST

255

Posted on:

Friday 16th November 2012
View Replies!

problems with db_writer_processes parameter

Hallo, my DB is running with two CPUs. For more efective writing data to RAID 5 discs I try to start 4 db_writer_processes. I can start 2 but not 4 of them. Is there any rule I do not now Please help me! Christian Oracle9i Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production CORE 9.2.0.1.0 Production TNS for 32-bit Windows: Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production
VIEWS ON THIS POST

291

Posted on:

Friday 16th November 2012
View Replies!

PLS-00905 with remote stored procedure

Oracle 7.3.3 - 7.3.4 I was working on the problem when user called remote stored procedure (user - Oracle 7.3.3, procedure -- Oracle 7.3.4) using db_link. The code is like the next: DECLARE v_emply NUMBER(5) := 11111; v_var1 number(2) := 111 v_sqlcode number(8); v_sqlerrm varchar2(256); BEGIN CHECK_MSG@db_LINK.WORLD(v_emply,v_var1,v_sqlcode,v_sqlerrm); END; The return error code was:PLS-00905: object is invalid; DB_LINK connected to user VIEWUSER. This user did not have direct EXECUTE privilege on the called procedure. EXECUTE privilege was given thru the role. I gave direct EXECUTE privilege. It did not help. So I gave direct execute privilege to all procedures that were called inside of CHECK_MSG. It also did not help -- the same error code. So I recompiled all of those stored procs. The same result. Then I gave direct privilege on all tables that are accessed thru the stored procedure and after that procedure started to work. I can understand everything except of the last step. Does anybody can explain to me why I should perform that last step
VIEWS ON THIS POST

259

Posted on:

Friday 16th November 2012
View Replies!

Plan stability

My Database is on CHOOSE mode . the querries are working fine except one .It works good when we set the session mode as first_rows . Since this database is used by a third party application nothing can be done .How to go about only for this particular sql statement ..
VIEWS ON THIS POST

236

Posted on:

Friday 16th November 2012
View Replies!

Order by

I know this is very optimistic question but just want to check i have any luck. In our client ware house, there is a query running on 115 million rows table doing order by 29 columns. As we have a limited pga_aggregate_target (6G) this sort is going to TEMP table space and is consuming lot of time. The query in this example is run for 9 hours. The query is like this ... select col 1, col 2, .... col x from table order by col 1 ... col 29 Just trying to find is there any work around available to get rid of order by on such a big table. Your suggestions are highly appreciated.
VIEWS ON THIS POST

256

Posted on:

Friday 16th November 2012
View Replies!

orapwd vs OS authentication

Hi all, can any one suggests why one would prefer to use the OS authentication method compared to oracle's own password file generated by orapwd Which method is mostly used by industries
VIEWS ON THIS POST

316

Posted on:

Friday 16th November 2012
View Replies!

ORacle9i on SOLARIS 9: Same CD's?

Guys, we've just received our new db-test server, and it is preinstalled with Solaris 9. On the installation CD's of Oracle I only see Solaris 8, can I use these or do I need a new package of installation CD's
VIEWS ON THIS POST

206

Posted on:

Friday 16th November 2012
View Replies!

Oracle Text Clustering

I have to implement Oracle Text Clustering and am wondering if there is anyone that has done that. I am finding limited documentation up on the Oracle Technology Network as well as Metalink. What I find is just addressing the basic setup and the packages that are being used. I am going to have cluster keywords from multiple columns as well as external full-text documents.
VIEWS ON THIS POST

230

Posted on:

Friday 16th November 2012
View Replies!

Error :This OracleTransaction has completed; it is no longer usable.

I get this error sometimes/ocassionally and dont know why try { //initialize connection OracleConnection connection = new OracleConnection(ConnectionString); connection.Open(); OracleCommand cmd = connection.CreateCommand(); OracleTransaction trasaction; trasaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); cmd.Transaction = trasaction; string SQL = ""; cmd.CommandText = SQL; cmd.ExecuteNonQuery(); { //do some more stuff } trasaction.Commit(); } catch(Exception ex) { trasaction.Rollback(); }
VIEWS ON THIS POST

441

Posted on:

Tuesday 20th November 2012
View Replies!

parameter must be defined error on mysql with c#

the below code gives Parameter '@row' must be defined error on filling data adaptor what is wrong with my code ... best regards \t\t\t StringBuilder sql = new StringBuilder(); \t\t\t sql.Append("SET @row:=0;"); \t\t\t sql.Append("SELECT * From ("); \t\t\t sql.Append("Select @row:=@row+1 As Rec_No, _Col1, _Col2"); \t\t\t sql.Append("From _Tab1) As _T1"); \t\t\t sql.Append("WHERE Rec_No > 1 And Rec_No
VIEWS ON THIS POST

486

Posted on:

Tuesday 20th November 2012
View Replies!

date format problem

hiiiiii......i have mysql in which i have taken data type "datetime" which takes in this format "yyyy-mm-dd hh:mm:ss" and c# datatime datatype gives mm/dd/yyyy now plsss tell me hurrily that what should i do....its really urgent.....i have used tostring("yyyy-mm-dd") but when i use it it looses its month portion...
VIEWS ON THIS POST

318

Posted on:

Tuesday 20th November 2012
View Replies!

Asp.net with MYSQL DATABASE

Hi , i have one my sql database. what typoes of name space , conection string and anything. is use in our asp.ner with c# application.. plz help me
VIEWS ON THIS POST

224

Posted on:

Tuesday 20th November 2012
View Replies!

LINQ to Oracle Providers

I'm writing a new vs2008 app and wanted to know if there were any providers out there for LINQ to Oracle.
VIEWS ON THIS POST

261

Posted on:

Tuesday 20th November 2012
View Replies!

Establishing a database connection using Putty to tunnel over SSH

I currently have an ASP.NET web application that is using a MySQL database as the source data. I would like to connect to this database using the standard MySQL listening port of 3306 and tunnel the database connection over SSH (Port 22). I have Putty on my Web server and will configure Putty to do Port Redirection (3306 > 22 > 3306). I would like my web application to first open the Putty connection (somehow have my web application pass the credentials to the server via Putty) which would establish an SSH connection to the distant server. Once that SSH connection is established, I would then establish a standard MySQL database connection using a connection string. I would prefer not to leave the SSH connection open all the time and want Logi to handle both the SSH and database connection calls only when it is needed. This methodology of tunneling a database connection over SSH is not MySQL specific and could be used to securely tunnel any database connection. Has anyone done anything like this and if so could you provide me with how you had the web application establish these connection calls
VIEWS ON THIS POST

289

Posted on:

Tuesday 20th November 2012
View Replies!

How to insert fetched value in cursor in temp table and use that table to show the values in DataGrid

, Currently i am working on project which is having 1 requirement, in which user is selecting some time period to get all the existing data in Oracle 9i Database. This logic is written in stored procedure, this is the conditional query which is formed on the basis of user roles and how much access he is having to the other plants/business units. In the end after completing all the scenarios i used to execute this query by using Cursor. While using this some times it retrieves more than 10,000 rows. I need to channelise this by using either pagination or suggest me whats the best option to display these fetched rows. I don't know how to do the pagination on the cursor, hence requesting you to please help me out to solve this issue. v_select:='SOMESELECTCOLUMNSTATEMENT' v_common_where:='WHERECOLUMNCONDITIONS' IF(p_userNameISNOTNULL)THEN IF(p_userName=p_loginName)THEN v_common_where:=CONCAT(v_common_where,'ANDENTERED_BYIN('''||p_userName||''')'); ELSIF(p_userName='ALL')THEN --\t\tv_dyn_stmt:=CONCAT(v_dyn_stmt,'ANDENTERED_BYIN(SELECTDISTINCTENTERED_BYFROMT1)'); ELSE v_common_where:=CONCAT(v_common_where,'ANDENTERED_BYIN('||p_userName||')'); ENDIF; ENDIF; v_common_where:=CONCAT(v_common_where,'ANDTO_NUMBER(YEAR||MONTH)BETWEENTO_NUMBER('||p_from||')ANDTO_NUMBER('||p_to||')'); v_from1:='FROMT2'; v_from2:='FROMT3'; v_where2:='WHERESTATEMENT2'; v_from3:='FROMSTATEMENT'; v_where3:='WHERESTATEMENT3'; v_scenario1:=v_select||v_from1||v_common_where; v_scenario2:=v_select||v_from2||v_common_where||v_where2; v_scenario3:=v_select||v_from3||v_common_where||v_where3; --GetUserRoleanddependupontheroledisplaytherecords. v_role:=null; SELECTUSER_ROLEINTOv_role FROMT3 WHEREUSER_ID=p_loginName; --CreateSQLforRole==Site. v_site_from:=v_from1||',T4'; v_site_where:=\t'ANDUSER_ID='''||p_loginName|| '''\tANDLOC_ID=LOCATION_ID'; IFv_role!='AD'THEN v_scenario1:=v_scenario1||'ANDENTERED_BY='''||p_loginName||''''; ENDIF; IFv_role='AD'THEN v_dyn_stmt:=\tv_scenario1; ELSIFv_role='ST'THEN --\t\t\tv_dyn_stmt:=\tv_select||v_site_from||\tv_common_where||\tv_site_where; v_dyn_stmt:=\tv_scenario1||'UNION'||v_select||v_site_from||\tv_common_where||\tv_site_where; ELSE v_dyn_stmt:=\tv_scenario1||'UNION'||v_scenario2||'UNION'||v_scenario3; ENDIF; OPENcur_OUTFORv_dyn_stmt; End; This is how i am generating query and getting the results. I need to put pagination into this or insert these retrieved values in temp table and then access this table using pagination.
VIEWS ON THIS POST

251

Posted on:

Tuesday 20th November 2012
View Replies!

ReturnValue in a Procedure...

... i'm working with Oracle for the 1st time (i'm used to SQL Server)! I've researched but found no answer: is there anyway to retrieve a value from a Oracle Procedure I know this is possible (and easy) to execute in SQL. I'm devolping a Windows App and the returnValue is kind of important to manage bugs and possible errors!
VIEWS ON THIS POST

198

Posted on:

Wednesday 21st November 2012
View Replies!

Unable to connect to Oracle 8i (method: OLEDB)

. I'm new to ASP .net. I'm trying to build a ASP .net (2.0) webapps to connect to Oracle 8i. I don't have Oracle client (per se) installed my development server, but I have installed Oracle SQL Plus 8.0 and SQL Navigator 4. I can happily connect to the same 8i with Navigator 4 (home folder is C:\orant). My question is (in order) 1. Do I need to install Oracle client or is SQL Plus 8.0 sufficient for me to connect to 8i using OLEDB method 2. If I have similar code such as the one listed below, why is it that I can't connect to my 8i Private objConn as Data.OleDB.OleDBConnection Sub Page_Load() If objConn.State =0 Then objConn.ConnectionString = "Provider=MSDAORA.1;Data Source=ORADB;Password=pass;User ID=user" objConn.Open() End If objConn.Close() End Sub Do I still need to add in and I've added \LOCALPC\ASPNET user in Security section of my Oracle home folder (including subfolders as well).
VIEWS ON THIS POST

249

Posted on:

Wednesday 21st November 2012
View Replies!

Compare two string objects to display data

People, Iam accessing an oracle database to get a value from atable (varchar), this value is always astring of numbers separated by a coma EXAMPLE:12,39,10,14 each number representing an event category inthe database,once i get that value i have to compare it to a string valueinput by user in myweb application, (user input as string), finally i have to display only the events that match both strings values. Example: event category: 10,15,16,101,39 user input:\t\t\t\t 09,10,25,16 result:\t\t\t\t\t\t\t only events which categories are 10 and 16 must be display.Matching categories Connection to database is perfectly working, i am able to extract the stringvalue from the database.
VIEWS ON THIS POST

246

Posted on:

Wednesday 21st November 2012
View Replies!

Update requires a valid UpdateCommand when passed DataRow collection with modified rows

I use an OracleCommandBuilder to build the commands. For some reason, I get the error "Update requires a valid UpdateCommand when passed DataRow collection with modified rows" The code looks like this: OracleDataAdapter adapter = new OracleDataAdapter( selectquery, connection ); OracleCommandBuilder builder = new OracleCommandBuilder( adapter); return dbAdapter.Update(ds); I thought that the parameters were automatically inserted from the dataset, and I see that an updatecommand is generated. So, what am I not getting here
VIEWS ON THIS POST

428

Posted on:

Wednesday 21st November 2012
View Replies!

How can I save Pictures with ASP.NET C#

Hi I have a Web page where users can upload pictures, and this works fine. But I don't want to save the pictures in the SQL Database, becouse it's gone blow up this Database... I heard, that there are a other solution, where I save only the path from the pictures in the Database, and the pictues is actually saved on a folder on the server. I did some research on the Web and I tried to make it by my self, but with no success... Does somebody can help me, or can give me a tip where I can find a solution for that Tanks in advance
VIEWS ON THIS POST

272

Posted on:

Wednesday 21st November 2012
View Replies!

Problem with Connection of Informix .NET Provider with .NET.

, I am facing problem regarding connection of .NET with IBM Informix .NET Data Provider. I got a help from IBM siteof Manual toconnectwith .NET and accessing informix as a informix .net data provider you need to importIBM.Informix.data as a namespace to access .NET based informix classes as similar to access sql server using namespace system.data.sqlclient. In that manual written as following---- Before you use the IBM Informix .NET Provider, you must execute the script, cdotnet.sql, against the sysmaster database as the user informix. So, my problem is that howwe can execute procedure written in cdotnet.sql file using a client of informix or after executing it can i getable to import directly from .NET IDE - "importsIBM.Informix.data". Please reply soon.
VIEWS ON THIS POST

229

Posted on:

Wednesday 21st November 2012
View Replies!

HELP ME PLEASE

I IMPORT NAMESPACE System.Data.OracleClient in my web-page I see that in classes-browser but when i process the page i received an error message. I wrote the following code : Sub Button1_Click(sender As Object, e As EventArgs) --->> Dim conn As New OracleConnection("server=Oracle;Uid=uid;pwd=pwd") and the message error is : ERROR BC30002 Type 'OracleConnection' not defined Where is my error \t\t
VIEWS ON THIS POST

266

Posted on:

Wednesday 21st November 2012
View Replies!

why can't insert data into mysql database?

b4 i install mysql5, when i run in mysql4, it's ok. but after i install mysql5 store it in /mysql5 folder but not /mysql, i cannot insert record but can get record from the tables. i just wonder why\t\t
VIEWS ON THIS POST

261

Posted on:

Wednesday 21st November 2012
View Replies!

Handling NULL value in XML using getStringVal

I have below query which works fine if column 'XML_COL' has values. This select statement fails if the value is NULL for select xmltype(t.xml_col).extract('//fax/text()').getStringVal() from mytab t How to handle rows with NULL values in the column 'XML_COL'.
VIEWS ON THIS POST

374

Posted on:

Saturday 29th December 2012
View Replies!