SQL & PL/SQL :: Updating Table For Different Number Of Fields Via Single Stored Procedure?

Jun 14, 2012

I've a table with fields:

create table test
( f1 varchar2(10),
f2 varchar2(10),
f3 varchar3(10)
)
insert into test values ('d1','d2','d3');
insert into test values ('d10','d20','d30');

I want to update the fields of the table as per need i.e update only one field leaving all the data of the fields as it is. Suppose I want to update only f1 (from d1 to x1) field leaving f2, and f3 as it is. I've written stored procedure to update all the fields but do not know how to do it?

Quote:CREATE OR REPLACE PROCEDURE UPDATE_TEST
( U_F1 TEST.F1%TYPE,
U_F2 TEST.F2%TYPE,

[Code]....

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: Execute Stored Procedure With Table Of Number As In Parameter?

May 31, 2011

I have one stored proc with three in parameters as

number, varchar and table of number

what statement I need to write in pl/sql to execute it ...

execute getdetails(1,'xyz', ????????????)

View 5 Replies View Related

SQL & PL/SQL :: Build A Stored Procedure Which Compares Specific Fields In 2 Tables

Jul 24, 2013

I have table A.Security:

CouponIssue_nameQuantity_typeCurrencySec_TypeSec_alias
1 a 56 USD AB 676
2 b 45 INR AB 143
3 c 32 JPY BA 200

and Table B.Security:
CouponIssue_nameQuantity_typeCurrencySec_TypeSec_alias
1 a 33 USD OB 676
9 b 21 NZD AC 143
8 c 19 GBP CA 200

build a Stored Proc which compares sepecific fields in the 2 tables and reports into a new table as:

A.Security.sec_aliasB.Security.sec_aliastable fieldA-valueB-value Compare
676 676 Security Currency USD USD Match
143 143 Security Currency INR NZD Not-Match

Fields to be compared are:
Quantity_type
Currency
Sec_type

The join between the tables A and B can be made on Sec_alias.Also, this is just a single pair of tables. Further I need to compare 5 more pairs of tables after this and report out of the new table.

View 23 Replies View Related

Insufficient Privileges Error While Updating Through Stored Procedure

Oct 2, 2008

I have a update statement that works fine when its run in SQL window but when i try it as part of a Stored Procedure its giving me the Insufficient privileges error on that table.

If i dont have privilege to update that table wont it not let me update while doing it outside the stored procedure as well?

View 4 Replies View Related

ODP.NET :: Updating Dataset / Datarow If Data Is Populated From Stored Procedure

Mar 11, 2013

I am using:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Microsoft Visual Studio 2010 : VB Express
Microsoft .NET Framework : Version 4.0.30319 RTMRel

When I populate dataset using select query and use OracleCommandBuilder and update dataadapter, data is saved in database.

But if I put same select in store procedure and use this to populate dataset then data is not updated in db.

I get this error

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

at da1.Update(ds1, "req") where I am going wrong.

Dim conn As New OracleConnection
        Dim da1 As OracleDataAdapter = New OracleDataAdapter
        Dim ds1 = New DataSet
        With conn
            If .State = ConnectionState.Open Then .Close()

[Code]....

View 2 Replies View Related

SQL & PL/SQL :: Updating Fields Of History Table

Aug 25, 2011

pgit_policy is transaction table having producer code field.

pgith_policy is history table, on that table if any endorsement passed new records created with same polh_sys_id and increment on POLH_END_NO_IDX.

I am trying to update all records of the history table but its updating only higest POLH_END_NO_IDX only. i need to update all producer code.

update pgith_policy a
set a.polh_producer_code= (select b.pol_producer_code
from pgit_policy b
where b.pol_no=a.polh_no
--and b.POL_END_NO_IDX= a.POLH_END_NO_IDX and b.POL_END_SR_NO = a.POLH_END_SR_NO
and b.pol_producer_code is NOT NULL
and b.pol_class_code='10')
where a.polh_class_code='10'
and a.polh_producer_code is null
and a.polh_appr_dt between to_date('01-06-2011', 'dd-mm-yyyy') and to_date('30-06-2011', 'dd-mm-yyyy')

View 4 Replies View Related

SQL & PL/SQL :: Store All Rows Of Columns Into Single Variable / Use In Inside Of Stored Procedure

Mar 6, 2012

i want to store all rows of columns into single variable and then use in inside of SP

declare
CUR_REC SECURITY_TYPE%rowtype;
begin
select *
into CUR_REC
from SECURITY_TYPE;
[code]....

it return ORA-01422: exact fetch returns more than requested number of rows error. Is any chance to implemented above scenario in oracle 10g

View 4 Replies View Related

SQL & PL/SQL :: Stored Procedure - Character To Number Conversion Error

Mar 14, 2011

I have written a stored procedure that has started returning the error:

Error starting at line 1 in command:
call p_glpost('DSTUK', 'L', '2008-01-01', '2008-01-01', '2011-02-18', 1, 1, 1, 0, 'Hi there')

Error report:
SQL Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "CLARITY.P_GLPOST", line 173
06502. 00000 - "PL/SQL: numeric or value error%s"

I can't seem to find a tool that will let me step into the actual stored procedure line by line to see where the error occurs. It mentions line 173, which seems to be a red-herring, as line 173 is simply one of the 'END IF' lines within this block:

IF NVL(r_dist.transtype,'wild') = 'wild' THEN
NULL;
elsif r_wip.transtype = r_dist.transtype THEN
v_matchCount := v_matchCount+1;
elsif r_wip.transtype <> r_dist.transtype THEN
[code]......

Tell me if it is possible to trace through a SP, and which tool is best (I am trying to use Oracle SQL Developer).

View 8 Replies View Related

SQL & PL/SQL :: Oracle Stored Function / Procedure To Calculate Number Of Working Days Between Two Dates

Feb 17, 2010

I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.

View 7 Replies View Related

Precompilers, OCI & OCCI :: PLS-00306 / Wrong Number Or Types Of Arguments In Call To Existing Stored Procedure

Feb 1, 2010

I'm using an existing stored procedure in my C code. The stored procedure in question has been compiled and is proven to work without any errors. However, when I use the same in my C code, its failing with the above error.

The Store procedure definition looks like :

CREATE OR REPLACE FUNCTION SP(
srq_id integer ,
unid IN SPkg.arr_parmid,
parm_typ IN SPkg.arr_parm_typ,

[code].....

Type definitions

TYPE arr_parm_typ IS TABLE OF char INDEX BY BINARY_INTEGER;
TYPE arr_parmid IS TABLE OF tbl_parm.UNID%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_parm_lbl IS TABLE OF tbl_parm.PARM_LBL%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_parm_vlu IS TABLE OF tbl_parm.PARM_VLU%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_vlu_hint IS TABLE OF tbl_parm.VLU_HINT%TYPE INDEX BY BINARY_INTEGER;

My C code looks like :

typedef struct param
{
char lbl[30][81];
char vlu[30][256];
char typ[30];
ub8 seq_no[30];

[code].....

The way I invoke the stored procedure:

char command[250] = "begin
:retval := SSP_srq_parm_all(:srq_id,:unid,:parm_typ,:parm_lbl,:parm_vlu,:commit_flag,:vlu_hint,:create_flag);
end;";
OCIStmtPrepare2((OCISvcCtx *)svchp, (OCIStmt **)&(stmthp),
(OCIError *)errhp, (OraText *)command,

[code].....

OCIStmtExecute() fails with the above error.

View 3 Replies View Related

Temp Table Within A Stored Procedure

Jul 15, 2013

I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table. I created the script but when they try to run in on the server it wont run.

CREATE OR REPLACE PROCEDURE UpdateFIDB_SP
IS
BEGIN
CREATE GLOBAL TEMPORARY TABLE myAAAA
AS
(SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777,
DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C,
[code]........

View 1 Replies View Related

SQL & PL/SQL :: Take Stored Procedure Last Run Without Using DBA_AUDIT_TRAIL Sys Table

Nov 18, 2010

How to take a stored procedure last run without using DBA_AUDIT_TRAIL sys table.

View 6 Replies View Related

SQL & PL/SQL :: Temp Table Within Stored Procedure

Jul 15, 2013

I have been developing in MS SQL for about 15. So I'm still getting use to the syntax and features within Oracle.I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table.I created the script but when they try to run in on the server it wont run.

CREATE OR REPLACE PROCEDURE UpdateFIDB_SP
IS
BEGIN
CREATE GLOBAL TEMPORARY TABLE myAAAA
AS
(SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777,
DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C,
AAAA.1D1D
[code].....

View 7 Replies View Related

PL/SQL :: Temp Table Within Stored Procedure

Jul 15, 2013

I'm still getting use to the syntax and features within Oracle. I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table.I created the script but when they try to run in on the server it wont run.

CREATE OR REPLACE PROCEDURE UpdateFIDB_SP IS BEGIN  CREATE GLOBAL TEMPORARY TABLE myAAAA    AS  (SELECT  AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777,                       DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C,                       AAAA.1D1D               FROM mySchema.FFFF_07 FFFF               RIGHT OUTER JOIN mySchema.EEEE EEEE ON FFFF.9999 = EEEE.1B1B               RIGHT OUTER JOIN
[code].........

View 10 Replies View Related

Application Express :: Updating Fields On A Tabular Form

Oct 1, 2012

I have an application where I'm trying to match skillset supply to skillset demand. We have resources and we have work for resources to do. Resources possess a set of skills. Work requires a set of skills. Skillsets are described by three fields: Domain, Target, Skill. So, we are able to capture the supply of skills in a table:

RSRC_ID (FK to Resource table)
DOMAIN
TARGET
SKILL
PROFICIENCY  (this is the skill level 1 to 5)

The relationship of resources to skill supply is 1 to many. Conversely, work demands skills, and so we capture skill demand in a table:

WORK_ID (FK to Work table)
DOMAIN
TARGET
SKILL.

The relationship of work to skill demand is 1 to many also.

Users assign skills to resources using a master-detail form. It works fine
Users assign skills needed to work also using a master-detail form, and it works fine as well

A view then joins the skillset supply to skillset demand so that assignments can be made only where supply and demand match. This view is used in the LOV below.

I am using a mapping table to capture assignments of work to resources or resources to work. It is a many to many mapping of work and resources with matching skillsets, and it looks like this:

RSRC_ID  
WORK_ID
DOMAIN   (
TARGET   ( These are the skillsets where demand equals supply and are derived from the view and presented in the LOV )
SKILL      (I've created master-detail forms for the two types of assignments. An LOV in the detail form will present qualified resources or eligible work and then return the appropriate RSRC_ID or WORK_ID, but I must also populate the other three fields (DOMAIN, TARGET and SKILL) before I can insert or update the record. Here's the LOV from the form where resources are assigned to work:

SELECT sed.domain||'.'||
       sed.target||'.'||
       sed.skill||'/'||
       sed.skill_prof||'  '||
       r.first_name||' '||
       r.last_name "d",
       r.rsrc_id "r"
FROM   ri_resource r, ri_skill_supply_eq_demand sed
WHERE  r.rsrc_id = sed.rsrc_id
AND    sed.work_id = :P18_WORK_ID

order by 1It is not enough to just match the work to the resource, we must record for what skillset they are matched. I can't seem to see an easy way to populate the three other fields in the tabular form using data that I can retrieve with the LOV.

View 1 Replies View Related

Application Express :: DML Not Updating Fields With Post Calculations?

Jan 9, 2013

I am running APEX 4.0.2.I am having an issue where I have a page item that has a post calculation

My source used is "Always..."
My source type is "Database Column"

I have tried putting the code in the page item under "Post Calculation Computation", as a dynamic action, and as a Computation Process. It displays properly on the form no matter where I put the code, but when I select Apply Changes it is not updating the database. What am I doing wrong? I have provided a sample of the code, and yes the column name is in the database.

CASE
WHEN :P12_NUM_OF_BOOKED_DEALS>0 AND :P12_DATE_1ST_COMPLETE IS NULL THEN '(2) In Process'
ELSE '(D1) Open Draft'
END

View 2 Replies View Related

SQL & PL/SQL :: How To Go Ahead Of Another Line Of Stored Procedure If Value Is Not There In Table

Oct 16, 2012

i am running one store procedure and checking the value if it is there then it's storing in one variable. but by checking through breakpoint i got that if value is not there which condition it's checking it's stuck there.

is it not possible if value is not there it should ignore and don't store value in variable.

my query is like this:

BEGIN
SELECT 1 Into v_temp FROM MASTERPROCESSDAILYDATA WHERE Emp_ID = v_ReadEmpID AND PDate = v_ReadPDate AND STATUS='A';
IF v_temp = 1 THEN
BEGIN
DELETE MASTERPROCESSDAILYDATA WHERE Emp_ID = v_ReadEmpID AND PDate = v_ReadPDate;
END;
END IF;
End;

View 14 Replies View Related

SQL & PL/SQL :: Create Stored Procedure To Update Columns In Table

Dec 8, 2010

I have three tables fixtures, fixture_teams and team_tbl

fixtures consists of:

create table Fixture_tbl(
fixt_id varchar2(8),
fixt_date date,
fixt_time varchar2(10),
fixt_location location_t,
umpire_id varchar2(8),
player_of_match player_of_match,
home_team varchar2(20),
away_team varchar2(20),
[code]....

creating a stored procedure that updates the points column in the teams_tbl , the value that is updated in to the points column will be retrieved from the fixture_team table. so if team a has more goals than team b then the points column for team a will be increased by 6 else if the scores are equal they get 4 points each.

View 13 Replies View Related

SQL & PL/SQL :: Stored Procedure With Multiple Table Join With Aggregate Functions

Jun 15, 2013

I have 2 tables order_items and items.

Order_items Items
Item_id Item_id
Quantity Price

In normal sql statement: select sum(order_items.quantity*items.price) sales_price
from order_items,items
where order.item_id=items.item_id;

I have to put this logic in either a stored procedure or Function just to calculate sum(order_items.quantity*items.price) and store the aggregated value as Sales_price in DB. Then we have to call this from Informatica Stored procedure Transformation where we will have only one output port as Sales_price,this is to load into summary table. All the aggregate calculations and joining of 2 tables should be done on DB side and only one output should be populated when we execute this procedure.

View 2 Replies View Related

PL/SQL :: Couldn't Access Dba_segments Table From Named Stored Procedure

Feb 7, 2013

we couldnt access the dba_segments table from the named stored proc however its working fine if accessing from unnamed SP.

SQL> declare
  2  cnt number;
  3  begin
  4  select count(*) into cnt from dba_segments;
  5  dbms_output.put_line('cnt='||cnt);
  6  end;
  7  /
cnt=6746

PL/SQL procedure successfully completed.SQL> create or replace procedure test

  2  is
  3  cnt number;
  4  begin
  5  select count(*) into cnt from dba_segments;
  6  dbms_output.put_line('cnt='||cnt);
  7  end;
  8  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1      PL/SQL: SQL Statement ignored
5/31     PL/SQL: ORA-00942: table or view does not exist
SQL>oracle version
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production

View 4 Replies View Related

Stored Procedure For Searching Data From A Table By Passing Tablename As A Parameter

Feb 6, 2012

This procedure is not working properly.

create or replace procedure bank_search_sp
(
p_tablename in varchar2,
p_searchname in varchar2,
p_bankcode out varchar2,
p_bankname out varchar2,
p_dist_code out number
)
as
v_tem varchar2(5000);
begin
v_tem :='select bankcode,bankname,dist_code from ' || UPPER (p_tablename) || '
where bankname like '''|| p_searchname||'';
execute immediate v_tem into p_bankcode,p_bankname,p_dist_code using p_searchname ;
commit;
end bank_search_sp;

the Procedure is getting created but i dont know what actually happens when it was executed ,This is the error shown..ORA-01756: quoted string not properly terminated

ORA-06512: at "PENSIONS.BANK_SEARCH_SP", line 14
ORA-06512: at line 1

View 1 Replies View Related

PL/SQL :: Call More Than One Stored Procedure In New Stored Procedure

Dec 24, 2012

Execute sp1 param1...param6
Execute sp2 param1...param8
Execute sp3 param1...param4

All these stored procedures deals with insert/updated transactions . i need to create a new stored procedure to execute all this in a single stored procedure which will be something like

create procedure sp4(param1...param8)
as
begin
Execute sp1 param1...param6
rollback if any error
Execute sp2 param1...param8
rollback if any error
Execute sp3 param1...param4
rollback if any error
end;

View 6 Replies View Related

SQL & PL/SQL :: How To Create Procedure For Getting Number Of Records Of A Table

Jul 6, 2011

How to create procedure for getting number of records of a table

View 24 Replies View Related

SQL & PL/SQL :: Updating Multiple ROWS With Different Values Using Single Statement?

Feb 16, 2011

The requirement I have is :

I have two tables eim_asset and eim_asset1.I want to update the table eim_asset1 using the following update SQL (Or Logic)

update eim_asset1
set emp_emp_login = (select login from s_user where row_id in
(select row_id from s_emp_per where row_id in
(select pr_emp_id from s_postn where row_id in
(select position_id from s_accnt_postn where ou_ext_id in
(select row_id from s_org_ext where row_id in
(select owner_accnt_id from s_asset where owner_accnt_id is not null)))))

It gives me the ORA error : ORA-01427:single-row subquery returns more than one row.know why I am getting it, because of the one-to-many relationship between owner accounts and their assets.

View 1 Replies View Related

SQL & PL/SQL :: Updating Multiple Rows With Single Update Statement?

Aug 20, 2013

create table temp_tst
(
FILENAME VARCHAR2(200),
EDITED_BY VARCHAR2(50),
EDITED_TO VARCHAR2(50)
)

[code]....

Can I write a single update statement to update filename column replacing "_tst" with "_check"?

View 1 Replies View Related

PL/SQL :: Updating Project Details - Single Row Subquery Returns More Than 1 Row Error

Nov 13, 2013

I am trying to do this: 

UPDATE projectdetails SET projectdetails.projectname=(SELECT projectid FROM project WHERE projectdetails.projectname = project.projectname); 

but it returns with the error: single row subquery returns more than 1 row

I tried in the same PROJECTDETAILS table with another table A3, it works fine. but not for the PROJECT table. 

Below is the project table (i have 2055 rows) 

projectidprojectname1NEWEST2PARK WEST3THE VISION4PEOPLE'S PARK CENTRE5REFLECTIONS AT KEPPEL BAY6THE CLIFT7CLEMENTIWOODS CONDOMINIUM8HUNDRED TREES9PASIR VIEW PARK10CARABELLE11PARC IMPERIAL 

below is the projectdetails table: 

ProjectnameCORALS AT KEPPEL BAY20247502294883Strata101 to 05Jun-1399 yrs lease commencing from 20071CORALS AT KEPPEL BAY278688018901475Strata101 to 05Jun-1399 yrs lease commencing from 20071CORALS AT KEPPEL BAY15250502180700Strata101 to 05Jun-1399 yrs lease commencing from 20071ECHELON216298016471313Strata121 to 25Jun-1399 yrs lease commencing from 20121WHITEHAVEN135600012851055Strata101 to 05Jun-13Freehold1

View 14 Replies View Related

SQL & PL/SQL :: Left Align Number Fields

Mar 11, 2010

Is there any way to align the output data of sql plus.

for eg., if i give "select custname, custid, custage from cust;" the ouput am getting is,

custname custid custage
aaa 1 23
bbbbb 2 22
cccc 3 45
dddddddd 4 21

but i need to left align the custid and custage.

my output should look like,

custname custid custage
aaa 1 23
bbbbb 2 22
cccc 3 45
dddddddd 4 21

View 7 Replies View Related

SQL & PL/SQL :: To Calculate Minutes From Difference Of To Number Fields

Apr 9, 2013

I need the query to calculate minutes from difference of to number fields. Test case is as below.

DROP TABLE tmp;

CREATE TABLE tmp
(
code NUMBER(4),
stime NUMBER(4,2),
otime NUMBER(4,2)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
[code]......

CODE STIME OTIME
---------- ---------- ----------
1065 20 19.49
1082 20 18.57
1279 19.3 18.59
2075 19.3 15.32

Required output is

CODE STIME OTIME HR_MIN MINUTES
---------- ---------- ---------- ------------- --------
1065 20 19.49 00 HR 21 MIN 21
1082 20 18.57 01 HR 03 MIN 63
1279 19.3 18.59 00 HR 31 MIN 31
2075 19.3 15.32 03 HR 58 MIN 238

View 8 Replies View Related

SQL & PL/SQL :: Pattern Matching And Updating Of Number Values In String?

Nov 5, 2011

I have a table(PSUSEROBJTYPE) with a long field(PTCUSTFORMAT) containing a row value value in the form:

#1|0|0|0|0|#2|1|0|0|1|#3|1|0|0|0|#4|0|0|0|0

Here, I want to update the above field value to a value in the form:

#2|0|0|0|0|#3|1|0|0|1|#4|1|0|0|0|#5|0|0|0|0

This is nothing but finding each occurrence of (#n) in the above string and replacing it by (#n+1). (i.e #1 is replaced by #2,#2 is replaced by #3).

View 4 Replies View Related

Forms :: Auto Populating Records In Other Fields Based On Inventory Number Field

Jul 13, 2011

I am having a problem with auto populating different fields based on inventory no. field.. This is a bug giving to me to work on and i not able to figure out how to populate the other fields.

How to set any triggers for the items to auto populate and i am suppose to finish this work today.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved