SQL & PL/SQL :: Member Procedure Insert With Subtype Info?
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
ADVERTISEMENT
Jun 24, 2012
If we insert a row in a database table then the new row stays at database buffer cache in SGA (until commit), right?. The target table is not affected (before commit). The new row is saved after commit.
I saw a concepts at Sybex oracle 10g oca book (Page 406) as follows:
" INSERT statements use little space in an undo segment; only the pointer to the new row is stored in the undo tablespace. To undo an INSERT statement, the pointer locates the new row and deletes it from the table if the transaction is rolled back. "
My question is If the row is not saved at table before commit, if we issue rollback then how oracle delete from table? I think the new row is deleted from database buffer cache in SGA.
View 2 Replies
View Related
Jun 10, 2013
I am trying to retrieve info from multiple DBs and insert into a central DB via DB LINKS.The links are retrieved via a cursor.
However I keep coming up against 'PL/SQL: ORA-00942: table or view does not exist'..how to handle db_links using a cursor in a pl/sql block? The code is as follows:
DECLARE
db_link_rec VARCHAR2(30);
CURSOR db_link_cur IS
SELECT DB_LINK
from MESSAGING_PROD_LIST;
BEGIN
OPEN db_link_cur;
LOOP
FETCH db_link_cur INTO db_link_rec;
EXIT when db_link_cur%NOTFOUND;
[code]....
View 1 Replies
View Related
Aug 8, 2011
I got error invalid use of type name or subtype name in collection.
create or replace PROCEDURE sp_load_dasstage_security
AS
cursor cur_stag is
select Asset_id,ID_ISIN
ID_SEDOL1,
ID_CUSIP,
LONG_ULT_PARENT_COMP_NAME,
ISSUER,
[code]....
View 10 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 13, 2009
create type employee is object(
name varchar2(30),
member procedure change_name(new_name varchar2)
)
[Code]....
Now how to use the change_name procedure to change the name of the employees in the table?
View 1 Replies
View Related
Jun 13, 2012
I wanna know if the redo log members are mirror copies.All member files from a same redo group have the same data?Are there any different in mirror or multiplex a file?
View 4 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
Sep 3, 2008
select
a.first_name,
a.agent_id,
a.birth_date
from
agents a
[code]........
from the above i cannot get the youngest one! this is giving me some mid age.
View 14 Replies
View Related
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
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
Sep 5, 2008
Do u want to get the youngest & oldest member at each location?
View 1 Replies
View Related
Jun 29, 2011
If one of the redolog member corrupted and overcome this problem, I had removed the corrupted redolog member. Later I had added a new member to this group.
I would like to know, is the newly added log member will get sync with existing log member? How the newly added log member get sync with existing log member?
View 1 Replies
View Related
Mar 28, 2013
have a req like below
CREATE TABLE TEST_BRK
( EMP_ID VARCHAR2(20),
BEG_DT DATE,
END_DT DATE,
DEPT_CODE VARCHAR2(10),
[code]...
COMMIT;i need to break down the each member range by record per month and make a record as first day of that month . and dept and branch should have the same value as what the value it was in the range of source.
excepted ouput like below
EMP_ID MONTH_DAY_1 DEP_CODE BRANCH
AAAA 01-JAN-2010 02 A
AAAA 01-FEB-2010 02 A
AAAA 01-MAR-2010 02 A
AAAA 01-APR-2010 02 A
AAAA 01-MAY-2010 02 A
AAAA 01-JUN-2010 05 B
AAAA 01-JUL-2010 05 B
.how can i code the logic to get my expected output above
View 3 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
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
Mar 25, 2013
I want to insert multiple records on a database using a stored procedure.
View 11 Replies
View Related
Feb 27, 2008
The size of redolog member is 12m . Can I increase the size of that member dynamically, without adding a new member to that group and dropping the old one.
View 8 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 17, 2011
I have created two types and a list of the first type:
create type type1;
/
create type type1_list as table of ref type1;
/
create type type2;
/
I have now just created the two types as follows:
create type type1 as object(
id# number
);
/
create type type2 as object(
attribute1 type1_list,
MEMBER FUNCTION function1 RETURN NUMBER
);
/
Ok, I've created the tables (I don't know if it's necessary to point out my problem)
CREATE TABLE type1_table OF type1;
/
CREATE TABLE type2_table OF type2
NESTED TABLE attribute1 STORE AS nested_type1_list_table;
/
And what I wanted to do now is to implement the member function1 and check something of the attributes of type1 in the list of attribute1... And that's where my question occurs, how does it work, I can't figure it out. I tried something like this:
Quote:
create or replace
TYPE BODY type2 AS
MEMBER FUNCTION function1 RETURN NUMBER AS
[Code]....
But I don't get the right way, it doesn't work
View 4 Replies
View Related
Oct 13, 2012
Using Oracle 11g...We have a table in our database of data with the following information:
MASTER_RECORD,
MEMBER_RECORD,
BUSINESS_UNIT,
GENDER,
DOB (date),
age [at time of month_record],
MONTH_RECORD (date) [31-MON-YEAR for recorded active month]
The table has ~55 million records. Existing index is only on MASTER_RECORD.There is now a need to create a view which is an aggregate count of member records, grouped by business_unit,gender, age per year. eg:
business_unit, gender, age, month_record, num_of_members -> for every combination
unit5, F, 25, 31-JUN-2011, 622
unit3, M, 18, 31-MAY-2011, 573
The view can be created now, but, is not fast enough to be reasonably considered a view. This table is re-created every month from a procedure, so there is flexibility on how it is created. Use interval partitioning by year( something I have not experienced using), create an index on the month_record,then create view.
View 2 Replies
View Related
Feb 20, 2013
i know the procedure "DBMS_SQL.DESCRIBE_COLUMNS" and the example,the example uses the "dbms_sql.open_cursor",not the ref cursor. so how to get the information of ref cursor by using describe_columns? or how to get the information of ref cursor by other?
View 4 Replies
View Related
Mar 10, 2011
Is there a way to retrieve information about a table through an sql query? In particular I need to retrieve the size of some varchar columns.
View 3 Replies
View Related
Feb 26, 2013
At my Workplace we have a large Orcle 11g Database with 30 different tables for production control issues.I try to get a couple of different information from the database, so i started with SQL Query's, but for this problem i was not able to write an working query.
In this case i have 2 tables:
Table 1:
ID ;ORDER_NR ;DESCRIPTION ;CREATE_DATE
1 ;A500236 ;CLEAN HOUSE ;02/20/2012
2 ;A623555 ;REPAIR CAR ;01/10/2012
3 ;A866944 ;MAINTAIN EQUIPMENT ;02/11/2012
Table 2:
ID;ORDER_NR;WO_STEP;STEP_DATE;EMPLOYEE
1;A500236 ;A;02/21/2012;W0010
2;A500239 ;F;02/21/2012;W0010
3;A500239 ;S;02/22/2012;W0027
[code]....
And the result of my Query should look like this:
ORDER_NR;DESCRIPTION ;CREATE_DATE;A_STAT_AGE;R_STAT_AGE;U_STAT_AGE
A500236;CLEAN HOUSE ;02/20/2012 ;5 ;3 ;1
A623555;REPAIR CAR ;01/10/2012 ;42 ;39 ;38
A866944;MAINTAIN EQUIPMENT ;02/11/2012 ;15 ;4 ;3
The age of my query result should be calculated from the Create date of the Order.I want to know 2 things, one is how old was the Order when they reached that status A, R and U.The second this ist, how long did the order remain on the stat A,R and U (and if possible all other status also)It could happend that not each order reaches each status, so it ca go directly from A to you in this case i want display a wildcard in this row/column
View 2 Replies
View Related
Apr 19, 2010
For each keeper, show the number of cages cleaned by the keeper, show the average number of animals in the cages cleaned by the keeper and the total cost of the cages cleaned by the keeper."
The data table is shown on the picture.i75.photobucket. com /albums/i297/lovebipbo/SIT103.jpg.. I can learn and do some similar task myself
View 4 Replies
View Related
Jan 26, 2007
How do I get my list of user session info? I thought there was a user_session_parameter view or something? Basically, I did an ALTER SESSION ... and want to verify it was set correctly.
View 8 Replies
View Related
