SQL Query question (count dates for each month)


Hope you can help me with the following, i have the following view availble:

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:  4201
View Complete with Replies

Related Messages:

windows authentication/local system logon - Oracle XA   (730 Views)
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.
what is the max. number of records in a table   (490 Views)
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
web application from DB   (480 Views)
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 Thank u all.. Sethu
Virtual Private Database Problem   (420 Views)
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
Value of a variable   (690 Views)
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.
Utl   (482 Views)
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.
unable to open DB   (625 Views)
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 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.
ultra search in oracle 9i   (713 Views)
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 ......
Tunning   (446 Views)
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
tuning private SQL area?   (687 Views)
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.
Tuning - Low RAM - Load/Performance issue   (616 Views)
Environment: Windows 2000, TNS for 32-bit Windows: Version 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
Tuening issues   (573 Views)
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.
trigger question   (608 Views)
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
To which Tablespace the user System be attached to ????   (546 Views)
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.
Statistics (URGENT)   (490 Views)
how does computing statistics improve performance in Oracle
Sqlnet encryption with SecureCRT port forwarding   (584 Views)
, 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.
sqlcode and sqlerrm   (425 Views)
Hi Friends I have and Oracle Error code, If I want to know the error message of that how do I find out. Shailendra
SQL Tuning   (509 Views)
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
sql functions to trim absolute path to display just filename   (624 Views)
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, "
SQL commands from SHELL prompt   (534 Views)
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)
single table ONLY recovery   (535 Views)
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.
SELECT ANY TABLE and VIEWS in different schema   (587 Views)
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 AS 2 SELECT * FROM tableuser.t; View created. system@DEV> system@DEV> GRANT SELECT ON 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; SELECT * FROM * 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; 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
Search a value into DB   (494 Views)
Hi! I am looking for a script to search a value into every tables of a DataBase (Unix - Oracle 8.0.4).
Scheduling a SP call   (544 Views)
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.
RMAN - Learning Material   (437 Views)
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.
Replication for Standard Unleaded Edition   (445 Views)
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
re: do not know why the script does not run ??   (494 Views)
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
problems with db_writer_processes parameter   (551 Views)
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 - Production PL/SQL Release - Production CORE Production TNS for 32-bit Windows: Version - Production NLSRTL Version - Production
PLS-00905 with remote stored procedure   (525 Views)
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
Plan stability   (450 Views)
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 ..
Order by   (1134 Views)
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.
orapwd vs OS authentication   (634 Views)
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
ORacle9i on SOLARIS 9: Same CD's?   (482 Views)
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
Oracle Text Clustering   (503 Views)
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.
Error :This OracleTransaction has completed; it is no longer usable.   (844 Views)
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(); }
parameter must be defined error on mysql with c#   (796 Views)
the below code gives Parameter '@row' must be defined error on filling data adaptor what is wrong with my code ... best regards StringBuilder sql = new StringBuilder(); sql.Append("SET @row:=0;"); sql.Append("SELECT * From ("); sql.Append("Select @row:=@row+1 As Rec_No, _Col1, _Col2"); sql.Append("From _Tab1) As _T1"); sql.Append("WHERE Rec_No > 1 And Rec_No
date format problem   (710 Views)
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... with MYSQL DATABASE   (527 Views)
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
LINQ to Oracle Providers   (510 Views)
I'm writing a new vs2008 app and wanted to know if there were any providers out there for LINQ to Oracle.
Establishing a database connection using Putty to tunnel over SSH   (681 Views)
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
How to insert fetched value in cursor in temp table and use that table to show the values in DataGrid   (626 Views)
, 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 -- v_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:= 'ANDUSER_ID='''||p_loginName|| ''' ANDLOC_ID=LOCATION_ID'; IFv_role!='AD'THEN v_scenario1:=v_scenario1||'ANDENTERED_BY='''||p_loginName||''''; ENDIF; IFv_role='AD'THEN v_dyn_stmt:= v_scenario1; ELSIFv_role='ST'THEN -- v_dyn_stmt:= v_select||v_site_from|| v_common_where|| v_site_where; v_dyn_stmt:= v_scenario1||'UNION'||v_select||v_site_from|| v_common_where|| v_site_where; ELSE v_dyn_stmt:= v_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.
ReturnValue in a Procedure...   (370 Views)
... 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!
Unable to connect to Oracle 8i (method: OLEDB)   (515 Views)
. 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).
Compare two string objects to display data   (464 Views)
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: 09,10,25,16 result: 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.
Update requires a valid UpdateCommand when passed DataRow collection with modified rows   (995 Views)
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
How can I save Pictures with ASP.NET C#   (615 Views)
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
Problem with Connection of Informix .NET Provider with .NET.   (473 Views)
, 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 as a namespace to access .NET based informix classes as similar to access sql server using namespace 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 - "". Please reply soon.
HELP ME PLEASE   (508 Views)
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
why can't insert data into mysql database?   (551 Views)
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
Handling NULL value in XML using getStringVal   (623 Views)
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'.