How To Check And Insert New Data Using Stock Procedure
Feb 11, 2007
i've a problem in using store procedure. My code is to get postcode id when i pass a postcode. First it will check the postcode that i pass if already exist it will get postcode id but if not it will insert new postcode and get a new postcode id created then pass into ASP system. When i try run this stock procedure i got error as below :-
SQL> exec INSERT_PCODE_GMDS
BEGIN INSERT_PCODE_GMDS; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'INSERT_PCODE_GMDS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
(
Postcode1 IN varchar2,
citiID IN Number,
county_ID IN number,
city_name IN varchar2,
sub_cityID IN number,
pcode OUT number
)
[code].......
in ASP to pass and get back the values i used code as below. but i think the problems occurs in my stock procedure
set cmd=Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = OBJdbConnection
cmd.CommandText="INSERT_PCODE_GMDS"
cmd.CommandType= 4
cmd.Parameters.append cmd.CreateParameter("@poskod",adVarChar,adParamInput,5,poskod)
[code].......
View 1 Replies
ADVERTISEMENT
Dec 21, 2010
i want to ask to all that, i have three table
(1)stock (2)issuance(3)recieve
i want to create as procedure when i issue in issuance table then update + stock in stock table. and when i recieve in recieve table then minus- stock in stock table.
hows can i create these procedure
i m using in oracle 9i and form 6i.
View 7 Replies
View Related
Aug 24, 2011
I know this is an old thread and I just started working with triggers. I want to check if the data being inserted is already in the table and if not insert it:
create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
values (:new.user_id, :new.location);
end if;
end;
what if I wanted to keep the user but only update location if the user is already in the table. I've tried doing it this way:
create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
[code]...
View 4 Replies
View Related
Apr 9, 2011
I'm writing a Procedure which Updates or Inserts data in Multiple tables. Selected fields of 10 tables need to be updated or Inserted. For this I created a table which comprises of fields related to all 10 tables. Then I write Procedure. Under this I create a Cursor which uploads the data from the newly created table which contains different fields of 10 tables. Then I write Update and Insert statements one by one for all 10 tables.
Sample Procedure below.
-------------------------------------------
Create or replace procedure p_proc as
spidm spriden.spriden_pidm%type;
cursor mycur is select * from mytable;
begin
for rec in mycur
[code]......
----------
Note: I created table on my server because data is coming from different server. They will upload the data in the table from there I pick and update the tables. Is updating or Inserting data in different tables one by one is correct?
View 15 Replies
View Related
Aug 30, 2012
i have one table ot_ins_item where user will enter the details of item, grade,item qty , later on user will go and update the same table the details of different grades received for the same item in different columns with qty breakup in 3 different fields it_qty_01 , it_qty_02,it_qty_03 respectively with different grades , what i need is i want is whenever he updates this table with different grades based on data entered in 3 different fields , a procedure or trigger should delete the initial record saved and insert three different rows based on newly updated values , it may be 3 or it may be 2 sometime depending upon input values that many records should be inserted same time controlling the qty's entered in breakup not exceeding the main qty.
CREATE TABLE ot_ins_item (it_ins_no NUMBER,it_no NUMBER,it_grade VARCHAR2(12),
it_code VARCHAR2(12),it_qty NUMBER,it_flex_01 VARCHAR2(12),
it_01_qty NUMBER,it_flex_02 VARCHAR2(12),it_02_qty NUMBER,it_flex_03 VARCHAR2(12),
it_03_qty NUMBER);
create sequence s_it_no start with 1 ;
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.NEXTVAL,'A','ITEM1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.NEXTVAL,'B','1TEM2',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.nextval,'C','ITEM3',NULL,NULL,NULL,NULL,NULL,NULL);
SELECT * FROM OT_INS_ITEM;
[code]....
View 5 Replies
View Related
Nov 1, 2011
I have an export dumpfile and when I am importing it in the new database I want to see all the SQL statements including "CREATE", "ALTER", and "INSERT". I am able to see CREATE and ALTER by using the option SHOW=Y in my import statement (IMP) but not able to see the INSERT commands.
Is there any way I can see INSERT statements as well.
View 2 Replies
View Related
May 23, 2013
I have a package,
PACKAGE PK_EXCEL_TO_DB IS
TYPE tKeyValue IS RECORD (
CROUTE VARCHAR2(255),
VROUTE VARCHAR2(1000),
[code]...
Package body is
PACKAGE BODY PK_EXCEL_TO_DB IS
PROCEDURE PR_DO_INSERT(i_lData IN tDataList) IS
vcInsert VARCHAR2(3500);
BEGIN
[code]...
what i want is: 'output vcInsert to a forms-item and check the syntax of the resulting insert'. How can I do this
View 7 Replies
View Related
Aug 12, 2012
Using a cursor and loop method records are inserted into the header and line tables. How do I code the IF statement prior to the INSert statement such that Insert if record does not exist else Update the record. If the record for insert fails in the line, the same record should be deleted from the header table as well and transaction should rollback. The Ora version used is 11.2.0.2.
CURSOR C1 IS
SELECT H.*
FROM rex_head_extract h
WHERE TRUNC(h.create_date) BETWEEN v_begin_date AND v_end_date;
FOR I IN C1 (v_begin_date, v_end_date)
[Code]....
--- Need to perform a check if record exists here prior to insert
INSERT INTO tran_head
(TRAN_SEQ_NBR, ORG_NUMBER,
STORE_NUMBER, TRAN_DATE,
[Code]....
View 9 Replies
View Related
Oct 17, 2012
how to check last modified function or procedure.
for example: INVO_75.PCK.PrintInvoices_fct
View 4 Replies
View Related
Jul 12, 2011
How can I calculate (by SQL) outstanding stock on a First In First Out basis per month?
Table A
Month Oustanding Stock Sales Expired Stock
January 200 0 0
February 100 100 0
March 100 0 0
April 50 150 0
June 50 0 100
July 50 0 0
Desired Results
Month Outstanding Stock
January 0
February 0
March 50
April 50
June 50
July 50
View 5 Replies
View Related
Nov 29, 2012
i want to get balanced amount based on max(suid ).
my query is
create or replace procedure prc_stk_upd_pur(v_comp_suid in i_purchase_h.pih_company_suid%type,
v_area_suid in i_purchase_h.pih_area_suid%type,
v_invoice_dt in i_purchase_h.pih_invoice_date%type,
v_pih_suid in i_purchase_h.pih_suid%type,
v_type in i_purchase_h.pih_type%type) is
[code]......
my output is
SEFF_SUIDSEFF_INV_DATESEFF_LOT_SUIDSEFF_LOC_SUIDSEFF_TRAN_SUIDSEFF_TRAN_TYPESEFF_COMP_SUIDSEFF_AREA_SUIDSEFF_STOCK_INSEFF_STOCK_OUTSEFF_CURRENT_STOCKCREATE_USERCREATE_TIMESEFF_RATE_USDSEFF_RATE_LCURNSEFF_PID_SUID
1121411/29/20121317151346LP11115720.00020.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14107665.30601382
2121511/29/20121317151346LP11115755.00055.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14097665.30001383
3121611/29/20121317151346LP11115730.00030.000SYSTEM29-NOV-12 06.53.45.000000 PM0.440729.08401384
my output should come
SEFF_SUIDSEFF_INV_DATESEFF_LOT_SUIDSEFF_LOC_SUIDSEFF_TRAN_SUIDSEFF_TRAN_TYPESEFF_COMP_SUIDSEFF_AREA_SUIDSEFF_STOCK_INSEFF_STOCK_OUTSEFF_CURRENT_STOCKCREATE_USERCREATE_TIMESEFF_RATE_USDSEFF_RATE_LCURNSEFF_PID_SUID
1121411/29/20121317151346LP11115720.00020.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14107665.30601382
2121511/29/20121317151346LP11115755.00075.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14097665.30001383
3121611/29/20121317151346LP11115730.000105.000SYSTEM29-NOV-12 06.53.45.000000 PM0.440729.08401384
View 3 Replies
View Related
Mar 5, 2012
I have to create a stored procedure having some 10 cursors and i have to display the data's fetched by select statement in cursors using dbms_output.put_line().
for ex:
-------
CREATE OR REPLACE PROCEDURE PROC AS
CURSOR C1 AS SELECT * FROM EMP;
BEGIN
FOR R IN C1
LOOP
[code].....
My question is while creating procedure i want to check if the object exist in database or not.
If EXIST
NO PROBLEM MY CODE CREATE THE PROC AND EXECUTED FINE
IF NOT EXIST
I don't want an exception to be thrown stating "OBJECT DOES NOT EXIST" I wanted PL/SQL engine not to execute the particular select statement itself and continue executing other select statements.
The reason why have such kind of wierd requirement is my program displays all the CEMLI objects in any 11.5.10 instance. I don't know whether they have installed discoverer or not. if they installed it no problem else my program have to eliminate.EXECUTE IMMEDIATE, REF CURSOR becoz i tried it and works fine.but not able to wrap in WRAPPER UTILITY 8.0 versions.
View 14 Replies
View Related
Sep 26, 2013
I have compiled some procedures, at the time of compilation i saw some warnings & errors but i did not notice, Now can we see that errors or warnings in db console or any other way.
View 7 Replies
View Related
Nov 8, 2012
I want to create a Stock Movement Report of a Health Management System in Oracle Report Builder 6i, as per following columns.
Sr. No Medicine Name Entry Date Opening Stock Stock In Stock Out Stock on-Hand
1 Mefnac DS 14-Aug-2012 50 450 50 450
15-Aug-2012 450 0 100 350
16-Aug-2012 350 50 400 500
Stock-In would be an aggregate sum of purchased quantity against particular medicine in a specific date.
Stock-Out would be an aggregate sum of issued quantity against particular medicne in a specific date.
View 1 Replies
View Related
Aug 23, 2011
how can i create procedure to check username and password.
View 10 Replies
View Related
May 15, 2010
how to insert data in oracle table without writing insert statement in oracle 9i or above. i am not going to write insert all, merge, sqlloder and import data.
View 2 Replies
View Related
Feb 27, 2012
when i tried to insert the details from oracle froms..the data inserts twice to the DB..
my table structure:
create table app_sri
(a_id integer primary key,
p_first_name varchar2(30),
p_last_name varchar2(20),
p_age number(3)
);
here a_id can be genarated through simple sequence(pid_seq)...
trigger on app_sri
create or replace trigger pid_trg
[Code]....
form insertion code..
Begin
insert into app_sri(null,'robo','Big',100);
commit
End;
the data inserted...but twice
what is the reason behind the double insertion?
View 8 Replies
View Related
Mar 12, 2010
, I'm trying to make a stored procedure in Oracle insertcion of records, but before you insert has to get the most code and generate a new one generated more than everyone else, I'm using Max, but as I assign to a variable as in SQL is:
Declare @ IDMax numeric
Select @ IDMax = Max (Code) From Members
Then I would make a:Insert into Users (Code, Name) values (@ IDMax, 'Victor');As serious for Oracle to perform com from declaring a parameter as the Code for me to store the value (Code Maximo)
View 2 Replies
View Related
Dec 7, 2011
Store procedure code, I want to insert data in a database in this fashion,I want to check first if the record exist, if not Insert or else Update.
View 2 Replies
View Related
May 27, 2011
i Have Write A SP But Show me Error when i Compile It.
Create or Replace Procedure PREPAIDEXPENSE(v_OperationType varchar2(1))
v_ATTM_TXN_TYPES_CODE ACC_TXN_TYPES_MST.ATTM_TXN_TYPES_CODE%TYPE;
v_ATTM_TXN_TYPES_DESC ACC_TXN_TYPES_MST.ATTM_TXN_TYPES_DESC%TYPE;
v_ATTM_STATUS ACC_TXN_TYPES_MST.ATTM_STATUS%TYPE;
v_ATSM_STAGE_ID ACC_TXN_TYPES_MST.ATSM_STAGE_ID%TYPE;
v_PP_ACCOUNT_GL ACC_TXN_TYPES_MST.PP_ACCOUNT_GL%TYPE;
[code]....
Error:SQL command not properly ended
View 5 Replies
View Related
Jun 22, 2010
i'm trying to make a sp/function for inserting a record and return the new index.The previous code used regular inserts and needed an additional round-trip to get the id before inserting,, since this is part of a import routine performance is an issue.
CREATE OR REPLACE PROCEDURE SaveAisHeader (
P_Id Out Number,
P_ImportedOn IN Date,
P_Aisimporttypeid In Number,
P_Description In Varchar2,
P_Importedby In Varchar2
[code].....
View 12 Replies
View Related
Dec 28, 2011
how to use ref_cursor as out parameter for insert procedure
This is the procedure iam having and when compiling it is througing error is
create or replace procedure proc_insert_policies_temp1(
i_policy_id in int,
i_corporate_name in varchar,
i_corporate_address in varchar,
i_divisionid in number,
[code]....
View 3 Replies
View Related
Jul 30, 2011
I want to put one check box to check all the check boxes.how can i do this?
View 5 Replies
View Related
Sep 20, 2013
how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me.
xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.
View 3 Replies
View Related
Mar 2, 2012
Im trying to generate a member procedure that allows the user to manual enter the required information via substitution variables. The manual INSERT INTO statements work but I cant seem to get it to work within a procedure.
Here is the code for the type, table andstandard insert:
CREATE TYPE toy_typ AS OBJECT
(toy_id NUMBER ( 5),
toy_name VARCHAR2 (20),
toy_cost NUMBER ( 4),
[code]...
the procedure complies but i get a warning error. When i also try to execute the code with either set values or substitutions i get either not enough or to many values error.
View 39 Replies
View Related
Jun 8, 2011
how can i make this script into a function or procedure which instead of user change the crime_id :=4 or 5 directly it actually can grab the id from a update statment like
UPDATE CRIME_STATUS SET CRIME_STATUS = 'open'
WHERE CRIME_ID = 9;
pick 9 and insert into the above statment so it runs as normal
[Code].....
View 20 Replies
View Related
May 12, 2009
I have an array of C structs say
struct S
{
int a, long b, double c;
};
S s[100];
Further suppose I have an Oracle table T like this:
create table T
(
a number(10),
b number(10),
c float
);
I want to bulk insert all 100 instances of S from a client application into T. I've seen code that does this for *one* field or column. The code defines a stored procedure which accepts a single argument which is a TABLE and then does a FORALL ... insert. The client application passes in the array of data.
What I need is N columns. In my example above struct S has N=3 fields which conform to the N=3 columns in T. In reality my N will be 50+. I am trying to avoid creating stored procedures which will take the 50 or so arguments it will eventually need.
So does my stored procedure need to accept N TABLE arguments? Or can I cajole OCI/OTL/ODBC and PL/SQL so that the stored procedure can take an array of rows which the type of row conforms to T by defining a record or something? That is, do I need:
Option 1: // declares one type and one argument each for N cols
create or replace procedure insert_S(
a_array IN A_TABLE, -- type A_TABLE is TABLE of number;
b_array IN B_TABLE, -- type B_TABLE is TABLE of number;
c_array IN C_TABLE) -- type C_TABLE is ...
begin ... end
Option 2: // this somehow accepts an array compatible with T
// if I could get a OCI/OCCI/OTL/ODBC application
// to send this data, this procedure would have
// only one argument
create or replace procedure insert_S(
row_array IN ?????????? type -- some sort of array of rows
)
begin ... end
Or should I pass the whole memory chunk of data in as an image or varchar array -- basically an opaque block of data -- and then internally decypher/decode the memory block inside the stored procedure as discussed on [URL].
best way to pass an array of N C-structs of M fields to a stored procedure for insertion into a table with M compatible columns? One TABLE per column? with an array of a custom type compatible with a row in T? As glob of data? Another option is to populate some host variables ... but, again, I'd need N host variables.
View 1 Replies
View Related
Mar 30, 2011
the moment my 11g database is connecting to a php web front end. this following procedure is the one I'm having trouble with.
CREATE OR REPLACE PROCEDURE "BSISSONS"."CREATE_EXCURSION" (
min_places IN excursion.min_places%TYPE,
max_places IN excursion.max_places%TYPE,
additional_charge IN excursion.additional_charge%TYPE,
[code]...
I can select into an output variable to return the value of the primary key of the newly inserted row back into the webpage, but i need to be able to 'select into' a temp variable to insert this value into another table on the same procedure. I get complie errors when i try to 'DECLARE' a variable after the 'AS' keyword
View 2 Replies
View Related
Mar 25, 2013
I want to insert multiple records on a database using a stored procedure.
View 11 Replies
View Related
Oct 30, 2012
I'm working on a feature that needs to check every table for data. At the moment I am roundtripping every time and running the query like this:
SELECT 1 FROM dual WHERE EXISTS(SELECT 'x' FROM table_name)
Is there a better way that allows me to get the result for all tables in one trip? I've tried a correlated subquery like this:
SELECT table_name, (SELECT 1 FROM dual WHERE EXISTS(SELECT 'x' FROM table_name) FROM all_tables
but I think that doesn't work because Oracle looks for a table called table_name rather than the value from the outer query.
View 13 Replies
View Related