string query = "DECLARE l_cursor SYS_REFCURSOR;ServiceName la_service_provider_detail.service_name%TYPE;Surname people.surname%TYPE;Forename people.forename%Type;"+ "Action Varchar(50);Rate ey_pupil_premium_log.rate%Type;"+ "Funding_Code single_funding_formulas.funding_code%Type;"+ "BEGIN EYPupilPremiumPackage.GetEyPupilPremiumLog(pLogId=> 6613, pPreview => 'T',EyPupilPremiumLog=> l_cursor);"+ "LOOP"+ "FETCH :l_cursor "+ "INTO ServiceName, Surname, Forename,Action,Rate,Funding_Code;"+ /*IF l_cursor%NOTFOUND"+ Then DBMS_OUTPUT.PUT_lINE('NO DATA FOUND'); EXIT WHEN l_cursor%NOTFOUND; END IF; "END LOOP;"+ "DBMS_OUTPUT.PUT_LINE(ServiceName || ' | ' || Surname || ' | ' || Forename ||'|' || Action|| '|' ||Rate|| '|' ||Funding_Code);"+ */ "CLOSE l_cursor;" + "End; /"; var cmd = new OracleCommand(query, connection.OracleConnection); cmd.BindByName = true; //cmd.Parameters.Add("pLogId", OracleDbType.Int32).Value = 6613; // cmd.Parameters.Add("pPreview", OracleDbType.Varchar2).Value ="T"; cmd.Parameters.Add("l_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output; cmd.ExecuteReader(); OracleDataAdapter ada = new OracleDataAdapter(cmd); DataTable dt = new DataTable(); …

Member Avatar
Member Avatar
+0 forum 3

Hi , here is my procedure where i want to return the open cursor to client using EyPupilPremiumLog parameter and then have to loop through for looging purpose which i cudnt able to do.. tried fetch /for etc but missing somewhere the nuanses .. Advise would be helpful. PROCEDURE GetEyPupilPremiumLog(pLogId IN ey_pupil_premium_log.log_id%TYPE, pPreview IN ey_pupil_premium_log.preview%TYPE, -- ErrorLog OUT NUMBER, EyPupilPremiumLog OUT SYS_REFCURSOR) IS -- type test is ref cursor return EyPupilPremiumLog%rowtype; l_ey_pp_full def_param.param_val%TYPE; l_cursor SYS_REFCURSOR; ServiceName la_service_provider_detail.service_name%TYPE; Surname people.surname%TYPE; Forename people.forename%Type; Action Varchar(50); Rate ey_pupil_premium_log.rate%Type; Funding_Code single_funding_formulas.funding_code%Type; limit_in PLS_INTEGER DEFAULT 100; --Row_total_Count Number; BEGIN SELECT param_val INTO l_ey_pp_full FROM def_param …

Member Avatar
+0 forum 0

Hi I have a procuder which return a recordset using cursor in output what am trying to do is i use cursor to get the data and same have to return it to output cursor .I can do like below PROCEDURE test(value_one IN someTabel.somecolumn%TYPE, valu_two IN someTabel.somecolumn%TYPE, Outputcursor OUT SYS_REFCURSOR) IS mydeclaration goes here output_value_one sometable.somecolumn%Type; --- cursor test_select is select statement begin for val in test_select loop fetch test_select into output_value_one; -- I want my cursor outputcursor to be return instead of output_value_one end loop; end;

Member Avatar
+0 forum 0

Hi, I am new in pl/sql programming and I need your help. I would like to make a procedure. To be more specific, I have tables like the folowing TABLE1 ================================================ |COL1 | COL2 | COL3 | COL4 | COL5 | COL6 |COL7| =============================================== |600 | 140 | 2 | 10 | 1300 | 500 | 1 | ----------------------------------------------- |600 | 140 | 2 | 20 | 1400 | 340 | 4 | ----------------------------------------------- |600 | 140 | 2 | 15 | 1400 | 230 | 3 | ----------------------------------------------- |600 | 140 | 2 | 35 | 1700 | 120 …

Member Avatar
+0 forum 0

Hello All, I want to write the plsql stored procedure to insert multiple rows in a table. Table t1, table t2. Table t1 column names(c1,c2,c3,c4,c5) table t2 column name (c2,c3,c4) column names c2, c3, c4 are same in both tables. BEGIN INSERT into t1(c1,c2,c3,c4,c5) select "a" as c1, c2, c3, c4,"b" as c5 from t2. Now it will insert the rows, but it will lead to duplication of records, if data already exists, It should just update that record instead of creating new row. how overcome this insertion anamoly? Thank you.

Member Avatar
+0 forum 0

Hello, I have one query with timestamp. I got below error. select users.login, FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(ticket_history.create_time)-UNIX_TIMESTAMP(ticket.create_time))) from ticket_history_type inner join ticket_history on ticket_history.history_type_id=ticket_history_type.id inner join ticket on ticket.id = ticket_history.ticket_id inner join users on users.id = ticket_history.change_by where ticket.create_time BETWEEN '2014-10-01' AND '2014-10-15' AND ticket_history.name ='%%new%%Fermeture réussie%%' group by users.id *`error: function unix_timestamp(timestamp without time zone) does not exist`* Thanks in advanced for your input.

Member Avatar
Member Avatar
+0 forum 8

Hi Folks, I am having problem with my web design. I am making a query that involves two table but it doesnt do anything when i click submit. do i make mistake in the query ? my code is like this, <?php if ($_POST['action'] == 'show'){ $requestCompSql = "SELECT REQUEST_COMPONENT_CUTTING.PROJECT_NAME, REQUEST_COMPONENT_CUTTING.BASE_PLATE, REQUEST_COMPONENT_CUTTING.THICKNESS, REQUEST_COMPONENT_CUTTING.QTY_REQUESTED, COMPONENT_CUTTING.QTY_REQUIRED, REQUEST_COMPONENT_CUTTING.REQUESTER, REQUEST_COMPONENT_CUTTING.REQUEST_DATE FROM REQUEST_COMPONENT_CUTTING INNER JOIN COMPONENT_CUTTING ON REQUEST_COMPONENT_CUTTING.PROJECT_NAME = COMPONENT_CUTTING.PROJECT_NAME WHERE REQUEST_COMPONENT_CUTTING.THICKNESS = COMPONENT_CUTTING.THICKNESS AND REQUEST_COMPONENT_CUTTING.BASE_PLATE = COMPONENT_CUTTING.BASE_PLATE AND COMPONENT_CUTTING.BASE_PLATE = '{$_POST["bp"]}'"; $requestCompParse = oci_parse($conn, $requestCompSql); oci_execute($requestCompParse); while($row = oci_fetch_assoc($requestCompParse)){ echo "<div class='table-responsive'>"; echo "<table class='table table-bordered'>"; echo '<table cellspacing = "0"'; echo '<thead>'; echo …

Member Avatar
Member Avatar
+0 forum 3

Hi folks, I have a question regarding auto-generate row oracle using sequence. So the deal is, I need to make a list of inventory based on its quantity. So I have a table with 3 columns, 1. Component ID 2. Quantity 3. Sub_component. So when user input a component id with quantity 3. I have to generate an oracle sequence of 3 that automatically inputs 3 items in the subcomponent id. for example, When user input component id : SX100 and the quantity is : 3 I need to generate 3 subcomponents : SX100-1, SX100-2, SX100-3 Please help me on …

Member Avatar
Member Avatar
+0 forum 1

Hi, I have created a parameter table, added 1 column to it and populated that column with 1 value as shown here: CREATE TABLE tbl_param( maxcount NUMBER); INSERT INTO tbl_param VALUES (5); My problem is I want to create a loop from 1 to 5, but instead of just using 5 as my terminator I would like to read in the value from my table tbl_param with the value being 5 (from maxcount record). I have tried the following: FOR i in 1..maxcount LOOP i := i +1; DBMS_OUTPUT.PUT_LINE('Count is: ' || i); EXIT WHEN i > 5; END LOOP; …

Member Avatar
+0 forum 0

Hello, I am hoping to get some help on a view which needs to be pivoted, I am not sure though. View is in following format: Case CASE_ORDER MANAGER CURRENT_MONTH_CASES FISCAL_YEAR_CASES ------------------------------------------------------------------------------- case_1 1 John 15 84 case_1 1 Jeff 10 80 case_2 2 John 20 90 case_2 2 Jeff 13 65 case_3 3 John 7 72 case_3 3 Jeff 17 70 My final chart should look like the following: case case_order John_current_month John_FY Jeff_current_month Jeff_FY ------------------------------------------------------------------------------ case_1 1 15 84 10 80 case_2 2 20 90 13 65 case_3 3 7 72 17 70 My problem is that managers …

Member Avatar
Member Avatar
+0 forum 2

Hello, I have the following table, for readability I broke down the after every manager for every case number. The last column is the cumulative sum of the second last column. Value for month should ideally run from 1-3 (Like you see for both the cases for Chicago). But from the table you can see in some cases some entries are missing (marked by <-----). CITY CASE CASE_NUMBER MANAGER MONTH MONTHLY_TOTAL FISCAL_TOTAL --------------------------------------------------------------------------- chicago case_1 1 John 1 2 2 chicago case_1 1 John 2 3 5 chicago case_1 1 John 3 5 10 chicago case_1 1 Jeff 1 4 …

Member Avatar
Member Avatar
+0 forum 1

have got this update script updating certain columns [CODE]update oppar set oppar_run_mode = 0, oppar_run_time = 0, oppar_interval_ind = 'N' , oppar_destination = '', oppar_run_date ='', oppar_run_interval='' where ( oppar_job_name, oppar_job_rec ) in ( ('CSCLM' , 'XYZ') , ('ARCLEVEXT' , 'LMN'));[/CODE] But there are cases where there is no record in the table oppar where the column oppar_job_rec is XYZ or LMN. Now I need to verify the existence of oppar_job_name=CSCLM then if that exists i need to check the existence of the Job rec coresponding to CSCLM i.e oppar_job_rec=XYZ and [B]if it does not exists i need to add …

Member Avatar
Member Avatar
+0 forum 1

please convert this below query to plsql format select (select count(*) from cadastredefunct t where t.areacode= 'PTH01' AND T.SECTIONNUMBER = 'D' and t.defunctnumber <> 'ONGD') as total, (select count(*) from cadastredefunct t where t.areacode = 'PTH01' AND T.SECTIONNUMBER = 'D' and t.status = 'COMP' and t.defunctnumber <> 'ONGD') as completed, (select count(*) from cadastredefunct t where t.areacode='PTH01' and t.sectionnumber='D' and t.status='INVALID') as Invalid, (select count(*) from cadastredefunct t where t.areacode = 'PTH01' AND T.SECTIONNUMBER = 'D' AND T.STATUS IS NULL and t.defunctnumber <> 'ONGD') as YTS, (select count(*) from cadastredefunct t where t.areacode= 'PTH01' AND T.SECTIONNUMBER = 'D' AND (T.STATUS …

Member Avatar
Member Avatar
-1 forum 5

The End.