SQL & PL/SQL :: How To Get Left Padded Sequence Values In Variable To Insert It In Table For ID Creation
Feb 22, 2010
I have a stored proc SP_INSERT_TRAINEES.Here sTraineeNo is provided as input which has count of trainee needs to be inserted in table aaa_foc.user_profile. The sequence is used to generate ids as :
I want the values for ids to be inserted as 001,002,003,.......010,011,................099,100,101,............999.So that the values in the table would be like TRAINEE001,002.......
I have tried to use LPAD to it but the values are getting insertes as TRAINEE1,2,3...........
The code is given below:
CREATE OR REPLACE PROCEDURE AAA_FOC9.SP_INSERT_TRAINEES
(sTraineeNo IN NUMBER,
nReturned_O OUT NUMBER)
IS
ln_insert_cnt PLS_INTEGER :=0;
nSequence NUMBER:=0;
[code]......
i am trying to create Procedure which will create the partitions based on the other table date values one per each day.
CREATE OR REPLACE PROCEDURE PARTITION_TEST(PART_DATE_TABLE IN VARCHAR2, TABLE_NAME IN VARCHAR2,SCHEMA_NAME IN VARCHAR2) AS V_PART_NM VARCHAR2(20); V_PART_CNT NUMBER; V_DATE DATE; V_SCHEMA_NAME VARCHAR(15);
[Code]..
It is not creating the partitions and even not giving any errors.
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE i_e NUMBER(10); BEGIN FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL) LOOP FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
that I need to insert into Child using seq_parent but I want to insert the same sequence for each group of rRef. I dont know how to do that using SQL not PL/SQL.
I have a table EMP having columns User_Id, ENO,Org_ID, Dept_ID.Now I would like to insert values into this EMP table using below conditions.Insert into EMP
(user_seq.nextval,(select empno from employees where empno in(....(empnumbers), (select org_id from organizations where org_name=' XXXXXXXXXX'), (select dept_id from DEPT where dname in ('MANAGER','ANALYST','SALESMAN') ))
I am using left outer join to fetch PRSN_KEY .I need to find null values in B.PRSN_KEY. I am using below query but its giving me 0 count.
select count(*) from ( Select A.PRSN_KEY AS AKEY,B.PRSN_KEY AS BKEY from CD03955P.H_CM_EEST_EEOR A LEFT JOIN CD03955P.H_CM_EEST_EEOR B ON A.PRSN_KEY =B.PRSN_KEY where A.CAT_ID=111 AND A.DATA_SOURCE='PEN_CO' AND B.CAT_ID = 1 and B.DATA_SOURCE ='PEN_EEST' AND B.CAT_CD IN ('ACTIVE','LOA','LOAWP','LOAMLP','LOAMLN') AND B.EFBEGDT < A.EFBEGDT ) where BKEY IS NULL
Table Structure: Name of Table: info Column: ID, Name, Cell,Cell1,Ref
i want to insert these values in a table through ref (key next item). ----------------------------------------------------------- Key next Item Triger DECLARE A NUMBER; B NUMBER; BEGIN SELECT :CELL,:CELL1 INTO A,B FROM DUAL; [code]....... ------------------------------------------------------- but when i press enter(ref Key_next_item) it will return error:
FRM-40735: KEY_NEXT_ITEM RAISED UNHANLEDED EXCEPTION ERROR: ORA 40102
Insert values into a table. The table is called PurchaseOrder_objtab. Here is the type for the table:
CREATE TYPE PurchaseOrder_objtyp AUTHID CURRENT_USER AS OBJECT ( PONo NUMBER, CUST_ref REF Customer_objtyp, OrderDate DATE, ShipDate DATE,
[code]...
The LineItemList_ntab is a nested table.
Here is the create code for LineItemList_ntabtyp:
CREATE TYPE LineItem_objtyp AS OBJECT ( LineItemNo NUMBER, Stock_ref REF StockItem_objtyp, Quantity NUMBER, Discount NUMBER )
[code]...
In the above code, the LineItemList_ntab is an empty LineItemList_ntabtyp. I am wanting to add values to this nested table rather than it being empty in the INSERT INTO code.
Here is some of the code I have tried to insert values with:
INSERT INTO PurchaseOrder_objtab SELECT 1008, REF(C), SYSDATE, '12-MAY-1999', LineItemList_ntabtyp(1, REF(StckItem), 10, 1) FROM Stock_objtab StckItem WHERE StckItem.StockNo = 1004, NULL
[code]...
The first insert statement above produces the following error:
SQL Error: ORA-00933: SQL command not properly ended
The second insert statement above produces the following error:
SQL Error: ORA-00936: missing expression
successfully insert values into the LineItemList_ntab nested table?
How to create this pl/sql process to add elements to a nested table or varray within a loop. Here's the scenario: I have an apex package that has some pl/sql processes and some stored procedures. I am dealing with Inspection Areas. An Inspection Area has several sectors. I already have the loop that lists all the Inspection Areas and a loop inside that loop that lists all the sectors. There is an if statement that determines whether or not the sector name gets stored in the varray or table. I am not sure how to correctly do this and am not sure whether to use a nested table or varray. I've posted somewhat of a pseudo coded example below
If (you_belong_in_table) then variable := store_me_in_varray /* OR */ variable := array_type(sector.sector_name) i := i + 1; end if;
/* Now we output our varray or table */ start loop output(sector names one by one)
end loop I hope this makes sense. I more so just need the syntax to be able to continually added values to a table or varray while I'm already inside a loop; and also how to output those values end the end as well.
My Form consists two Hierarchical trees. When I select a node from first tree and I press Move Right (>>) Button the selected node should move to the second tree. Similarly when I select a node from second tree and press move Left button(<<) it should move to the first tree.
I also want to know want to insert values Into New Table using node values from The Displayed Hierarchical Tree. How to retrieve values using populate_Group_from_tree and insert into table.
SQL> create or replace type obj1 as object(v_ASSETID NUMBER(20), 2 v_ASSETTYPE varchar2(20)); 3 / Type created
--- >> create the package
SQL> create or replace package test_arr is 2 type nest_tab is table of obj1; 3 procedure insert_data(t_nest IN nest_tab); 4 end test_arr; 5 /
Package created
SQL> create or replace package body test_arr is 2 procedure insert_data(t_nest IN nest_tab) is 3 begin 4 for i in t_nest.first..t_nest.last loop 5 insert into asset(ASSETID, 6 ASSETTYPE) values (t_nest(i).v_ASSETID,
[code]....
-- >> a block to execute the above package:
SQL> declare 2 type ref_tab is table of obj1; 3 ref_tab1 ref_tab; 4 begin 5 ref_tab1 := ref_tab(1,'a'); 6 test_arr.insert_data(ref_tab1); 7 end; 8 /
But I am getting the below error when executing the package:
ORA-06550: line 5, column 13: PLS-00306: wrong number or types of arguments in call to 'REF_TAB' ORA-06550: line 5, column 13: PLS-00306: wrong number or types of arguments in call to 'REF_TAB' ORA-06550: line 5, column 1:
I am trying to write a trigger that will do an insert/delete/update into a audit table when a change has occurred on the primary table. The change will be recorded in the audit table by a incemental sequence number and the updated data.
there will be an extra column in the audit table
how to get a simplified version of this trigger.
the primary table will look like so Id_num varchar (20) code Integer desc varchar(20) sequence_num Integer
I created a Table with a single column varchar2.. in which I wanted to insert value like 'BBBBAB1'... till 'BBBBAB100'
Created a sequence starting with 1...
and inserted single row, and multiple rows using loop also; by using below code -
insert into Trans SELECT CONCAT('BBBBAB', Trans1.NEXTVAL) from dual;
but whenever I see the values they are not as required ... 'BBBBAB1' but one character 'B' is missing, and the values populating are 'BBBAB1'.. 'BBBAB100'
DECLARE v_seq_num NUMBER; BEGIN SELECT SEQ_ID.NEXTVAL INTO v_seq_num FROM DUAL; INSERT INTO TABLEA (COL1, COL2) VALUES (v_seq_num, 'test'); INSERT INTO TABLEB (COL3) VALUES (v_seq_num); END;
-- Option2 - Using sequence.NEXTVAL in INSERT USING RETURNING INTO clause
DECLARE v_seq_num NUMBER; BEGIN INSERT INTO TABLEA (COL1, COL2) VALUES (SEQ_ID.NEXTVAL, 'test') RETURNING COL1 INTO v_seq_num; INSERT INTO TABLEB (COL3) VALUES (v_seq_num); END;
I am using Oracle 10G version. I need a code base for new Sequence Trigger.
Requirement : As per the request, before INSERT trigger will generate the sequence ID from AA001 to AA999 value. But once the sequence is reached to AA999, the next sequence value will be generated normal (start from AB001 etc..).
DECLARE TYPE first_rec_1 is RECORD( var1 varchar2(30) := 'why not'); first_rec first_rec_1; TYPE SEC_REC IS RECORD( NESTED_REC FIRST_REC_1 := first_rec); BEGIN dbms_output.put_line( ' this is a test for checking nested records'||FIRST_REC.var1); END;
In the above code what NESTED_REC FIRST_REC_1 := first_rec means. Will NESTED_REC variable get the values of first_rec?
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
