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.
Posted On: Tuesday 20th of November 2012 04:33:10 AM Total Views:  401
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




How to generate insert query script of data of table

Hi all How to generate insert query script of data of table i want to copy default data of table ok bye
VIEWS ON THIS POST

209

Posted on:

Wednesday 7th November 2012
View Replies!

Problem inserting special characters in MySQL database.

Greeting, I have an issue regarding inserting special characters in the database. The character I'm trying to insert is (Sign for euro currency). IfI executes the following line directly in my MySQL DB it works fine: INSERT INTO currency VALUES (''); But if I insert it through ASP.NET the value in the DB will be ''. How can that be Do I need to make some kind of conversion My code is like: string sign = ""; cmd.CommandText = @"INSERT INTO currency VALUES (sign)"; cmd.Parameters.Add("sign", SqlDbType.VarChar).Value = sign; cmd.ExecuteNonQuery(); Thank you in advance. BR Kenneth
VIEWS ON THIS POST

169

Posted on:

Wednesday 7th November 2012
View Replies!

how to fire insert, select query if 1 datatype is xml and other is normal?

I want insert selct query for following Mailed_from\t\t\t\t\t\t varchar Mailed_date\t\t\t\t\t\t Datetime Mailed_to\t\t\t\t\t\t\t\t Xml here xml is used to store more than 1 MAil id
VIEWS ON THIS POST

244

Posted on:

Wednesday 7th November 2012
View Replies!

Insert not inserting to Oracle db

I am just trying to learn ASP.NET but have run into a problem when attempting to insert data into my database. I am using Oracle 10g Express Edition, Visual Studio 2005 and the code is in C#. Here is the codeI am using to call the insert statement; stringsql="INSERTINTOoffices(office_number,city,address,tel_number)VALUES(45,'London','Tottenham','2342234234');"; OracleCommandcommand=newOracleCommand(); using(OracleConnectionmyConnection=new OracleConnection(ConfigurationManager.ConnectionStrings[ "connectionString"].ConnectionString)) { command.CommandText=sql; command.Connection=myConnection; try { myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close(); } catch(Exceptionex) { ... } } The connection string is; I have created a function that retrieves data from the database OK which is displayed in a grid view, but when I run the insert nothing is inserted into the database. I don't get any errors and I'm confident that the connection to DB is OK as I have already been able to retrieve data as mentioned, I just dont know if there is something I am missing such asa commit for example\t Have looked on the web and tried it a couple of different ways but still can't find a solution.
VIEWS ON THIS POST

127

Posted on:

Wednesday 7th November 2012
View Replies!

how to insert 'EH&S' into table in a varchar field

I am trying to update Varchar field and want to insert EH&S insted of EHS here is my code Set define off; update table1 set name='EH&S' where name='EHS' when i run this query itpopup the window andask for enter value for S. Can anyone help me for this issue
VIEWS ON THIS POST

280

Posted on:

Wednesday 7th November 2012
View Replies!

Query to retrieve the first 10 records inserted

I need to write a query/stored procedure where I want to get a specific number for records (say 10) from the database according to the time they are inserted into DB. I store the insert time using SYSDATE and I want to retrieve the first ten inserted records (i.e. having smallest SYSDATE value) and then delete those from DB in one transaction. If my table looks something like this: RecordNum RecordType InsertTime 10 A 10/08/2007 14:20 20 B 10/08/2007 14:22 10/08/2007 14:27 Please let me know how to go about it.
VIEWS ON THIS POST

135

Posted on:

Wednesday 7th November 2012
View Replies!

Get ouput Parameter following an insert

Tier I/UI userObejct.UserName = tbxUserName.Text.Trim(); userObject.AddUser(); \t How do I get the ReturnID here Tier II/BL private string _UserName; public string UserName \t\t\t { \t\t\t\t\t get \t\t\t\t\t { \t\t\t\t\t\t\t return _UserName; \t\t\t\t\t } \t\t\t\t\t set \t\t\t\t\t { \t\t\t\t\t\t\t _UserName = value; \t\t\t\t\t } \t\t\t } ...... public void AddUser() \t\t\t { \t\t\t\t\t _data.AddSomething( _UserName); \t\t\t } Tier III/DB public int AddUser(string UserName) \t\t\t { More Code....... OracleParameter userName = new OracleParameter("sUserName", OracleDbType.Varchar2); userName.Direction = ParameterDirection.Input; userName.Value = UserName; command.Parameters.Add(userName); OracleParameter parameter = new OracleParameter("result", OracleDbType.Int32); parameter.Direction = ParameterDirection.Output; command.Parameters.Add(parameter); \t\t\t\t\t command.ExecuteNonQuery();\t\t\t\t\t command.Dispose(); con.Close(); } WHERE to put this, and how to get it back to the UI return Convert.ToInt32(command.Parameters["result"].Value.ToString());
VIEWS ON THIS POST

125

Posted on:

Wednesday 7th November 2012
View Replies!

insert,update & delete problem for oracle db from vs.net2005 using c# & object data source

how i can refer to the varchar2 data type from vs.net 2005 how i can update, insert & delete the database ENTRY How i can oracle stored procedure from vs.net2005how can i pass parameters
VIEWS ON THIS POST

234

Posted on:

Wednesday 7th November 2012
View Replies!

[help]Error in inserting value into MySQL

, I am having problem in inserting a record into MySQL table using ASP.NET. I don't why it just keep inserting the NULL value into HP,System,and ADDDATE column. Below is my code: insert into compensation (HP,System,AddDate,Times) values (@HP,@System,@AddDate,1) MySQLODBCCmd.Parameters.Add("@HP", txtMobile.Text) MySQLODBCCmd.Parameters.Add("@System", SystemList.SelectedValue.ToString) MySQLODBCCmd.Parameters.Add("@AddDate", DateAdd(DateInterval.Day, -1, Now())) How to solve this probem\t
VIEWS ON THIS POST

235

Posted on:

Wednesday 7th November 2012
View Replies!

Problem inserting data into MySQL from a form

I'm stumped. I'm using ASP.NET written in VB and connecting successfully to a MySQL database on my domain hosted by DiscountASP.net. As a trial, I've set up a MySQL database with three columns: plantID, name_bot and name_common. Using SQLyog to set up the database, I successfully added rows by hard-coding the insert with a VB page as follows ... Dim objConn As New OdbcConnection("DSN=MYCONNECTION") Dim objCmd As OdbcCommand Sub AddPlant(s As Object, e As EventArgs) objCmd = New OdbcCommand("INSERT INTO wildplants (name_bot, name_common) VALUES ('Banksia serrata', 'Old Man Banksia')", objConn) objConn.Open() objCmd.ExecuteNonQuery() objConn.Close() End Sub That works fine - the data is added and the row is incremented, but when I try the following code pulling the data this time from textboxes on a form, the row is incremented, but no data is added .... Dim objConn As New OdbcConnection("DSN=MYCONNECTION") Dim objCmd As OdbcCommand Sub AddPlant(s As Object, e As EventArgs) objCmd = New OdbcCommand("INSERT INTO wildplants (name_bot, name_common) VALUES (@name_bot, @name_common)", objConn) \t objCmd.Parameters.AddWithValue("@name_bot", txtNameBot.text) \t objCmd.Parameters.AddWithValue("@name_common", txtNameCommon.text) \t objConn.Open() \t objCmd.ExecuteNonQuery() \t objConn.Close() End Sub Add a new plant: Botanical Name: Common Name: Am I trying to do something that cannot be done in MySQL with ASP.NET - or is there a problem with my code I have no trouble pulling data out of the MySQL database and displaying it with VB on ASP.NET. So the connection is working fine and I have a reasonable grasp of the code involved. Any help will be greatly appreciated as I'm making a determined effort of learn ASP.NET 2.0. I use a text editor for coding so I understand every bit of the code. Best regards.
VIEWS ON THIS POST

145

Posted on:

Wednesday 7th November 2012
View Replies!

need help-inserted value are upside down question marks

hello, i just created a web application that connects to oracle. when i try to connect using oleDB connection and insert data in hebrewto the DB the inserted data appears as upside down question marks. when i want to read data from the DB there is no problem. i can see hebrew. also i tried to connect using ADODB. this works well. i understand that ADODB takes the environment from the DB. and the oleDB takes it from . net. i would rather use oleDB. i tried using encoding but for now with no success. in need of help,
VIEWS ON THIS POST

234

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

208

Posted on:

Wednesday 7th November 2012
View Replies!

Best way to insert data when you have 0-many relationship

I am looking for the best way to accomplish the following: I have a project table. This table holds all of my project information. The Project table has 0-many relationship with a kpi table. For a given project, it has 0 or more rows on the kpi table. They are joined by the ky_project column. My question is, what it the best way to handle a web form that inserts a new project along with its kpi's When I insert the new project, I can use the "nextval" property in Oracle so it inserts the next value for the primary key (ky_project) into the project table. When I insert the kpi's associated with the project though, I need to know what the ky_project is that I just inserted into the project table. I know that I can run a select max(ky_project) query right after the project record's insertion, but it seems like there should be a better way.
VIEWS ON THIS POST

178

Posted on:

Wednesday 7th November 2012
View Replies!

Oracle insert statement

I am trying to insert string data in Oracle (field type = VARCHAR2). The string may have an apostrohe within it, and when it does I get an "ORA-01756: quoted string not properly terminated" error message. Any suggestions would be appreciated.
VIEWS ON THIS POST

402

Posted on:

Wednesday 7th November 2012
View Replies!

insert sql

hi, does anyone know how to insert a new record into the table and the all the values is set to null
VIEWS ON THIS POST

87

Posted on:

Wednesday 7th November 2012
View Replies!

slow insertion

i have a complex select statement which takes less then 1 second to execute and it returns almost 28000 rows, i have created a temporary table in which i need to insert the result returned by this select statement and now its taked 8 seconds to insert into temporary table. I tried to use APPEND hint but insertion time is same. Now what i did, i inserted the values in another table "TABLE_ONE" and it took same 8 seconds and then i wrote a select statemtn as follows insert into my_temp_table select * from table_one and it takes 1.5 seconds to insert. folllowing are the findings 1) SELECET statement takes 1 second (returns 28000 records) 2) insertion made using this select, takes 8 seconds 3) once table is populated with these 28000 records and then insertio of these records into another table using SELECT takes 1.5 sesonds. Why insertion using my select statement takes much time and if same records are in a table then its takes only 1.5 seconds How to solve this issue The execution plan of select statement is same and perfect if i see it individually or with insert statement. and can we use NOLOGGING clause in INSERT statement
VIEWS ON THIS POST

133

Posted on:

Thursday 15th November 2012
View Replies!

PUZZLE: Suppressing inserts into SYS.DUAL

In the light of a recent discussion about multiple rows in the SYS.DUAL table, here is a chalange: How to prevent users with DBA role or INSERT ANY TABLE priviledge from inserting new rows into SYS.DUAL Of course, without revoking them DBA role or INSERT ANY priviledge. The one who provide satisfactory sollution gets a bottle of champaigne (that is, he gets it from pipo - the one that I earned in the previous thread )http://www.dbasupport.com/forums/showthread.phpthreadid=20238
VIEWS ON THIS POST

80

Posted on:

Friday 16th November 2012
View Replies!

Problem inserting data into longblob field

Hi , I am using MySQL as my database.I am saving documents (Word,Pdf,excel and image files) in MySQL.I have created a Longblob column in MySQL for this purpose. I am first reading the document in bytestream and inserting the bytestream into the Longblob field.I am successfully able to insert the document of sizes approx 60MB into the column.But if the document size exceeds 150MB , i get the following error ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.18-nt]General driver defined error I am not able to understand what this error is about.Can someone tell me what this error is about and how i can rectify this.
VIEWS ON THIS POST

218

Posted on:

Tuesday 20th November 2012
View Replies!

problem occured in mysql databse when i insert arabic word or devnagari word

what i do then a column in mysql table accept hindi or urdu both word when i set chaset to utf8 then he work fine for Hindi but errror occured when i insert arablic [urdu] words. so i need a sollution that the column can be accept hindi or urdu both type word
VIEWS ON THIS POST

226

Posted on:

Tuesday 20th November 2012
View Replies!

Error coming in insert the date from asp net.2.0 to oracle database

Sir, As per our knowledge i use the code in asp .net 2.0 with c#, but in this code i got the error. So that way i need your help for solve the error, and my code. MY CODE IS:-- protected void Btn_submit_Click(object sender, EventArgs e) \t { \t\t\t int year = Int32.Parse(DDLYear.SelectedValue);\t\t\t \t\t\t int month = Int32.Parse(DDLMonth.SelectedValue);\t\t\t \t\t\t int day = Int32.Parse(DDLDay.SelectedValue); \t\t\t DateTime date = new DateTime(day, month, year); \t\t\t string dt = Convert.ToDateTime(date).ToString("dd-MMM-yyyy"); \t\t\t \t\t\t string connectionString = WebConfigurationManager.ConnectionStrings["inside1"].ConnectionString; \t\t\t OracleConnection con = new OracleConnection(connectionString); \t\t\t OracleCommand cmd = new OracleCommand("INSERT INTO ADMIN_FORM(FULL_NAME, USER_NAME, PASSWORD, E_MAIL, DATE_OF_BIRTH, GENDER, COUNTRY) VALUES (:FULL_NAME, :USER_NAME, :PASSWORD, :E_MAIL, :DATE_OF_BIRTH, :GENDER, :COUNTRY)",con); \t\t \t\t\t cmd.Parameters.AddWithValue(":FULL_NAME",TXTBX1.Text); \t\t\t cmd.Parameters.AddWithValue(":USER_NAME",TXTBX2.Text); \t\t\t cmd.Parameters.AddWithValue(":PASSWORD",TXTBX3.Text); \t\t\t cmd.Parameters.AddWithValue(":E_MAIL",TXTBX5.Text); \t\t\t cmd.Parameters.AddWithValue(":DATE_OF_BIRTH", dt.ToString()); \t\t\t cmd.Parameters.AddWithValue(":GENDER",RadioButtonList1.SelectedValue.ToString()); \t\t\t cmd.Parameters.AddWithValue(":COUNTRY",DDLCountry.SelectedItem.ToString()); \t\t\t try \t\t\t { ---------------------- ---------------------- } MY PROBLEM IS:-- I use the oracle databasae and in which I want to enter the date-of-birth from dropdownlist over the default.aspx page. In the Oracle database "DATE_OF_BIRTH Date;" On the time of run my pages this error has comeout Error:\t Year, Month, and Day parameters describe an un-representable DateTime. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.ArgumentOutOfRangeException: Year, Month, and Day parameters describe an un-representable DateTime. Please help me for solve this error:
VIEWS ON THIS POST

288

Posted on:

Tuesday 20th November 2012
View Replies!