PL/SQL :: How To Create Unique Number On Each Call
Apr 10, 2013
i want to insert unique number with out gaps in a col of a table every time my query runs . it shouel generate numbers from 1 to n. I can do it using sequence , but sequence need reseting and other user can reset it also and it creats gaps too.
Any function/technique which can give me session or transaction specific unique number on each call to sp.
i want to inserting these numbers in a table and insertion is going on in a loop.
I have a table that has three columns. UserId - DateStamp - Action
An action contains user activities such as "logon". A user may logon multiple times in a day.I'm trying to create a query that displays the number of unique logons for each user per day.
By doing the following:
select userid, to_char(creationtimestamp,'YYYY-MM-DD'), count(*) from useraction where action = 'logon' group by to_char(creationtimestamp,'YYYY-MM-DD'), userid order by to_char(creationtimestamp,'YYYY-MM-DD') desc;
I can get the following results userid - date - count 1 - 2/21/2010 - 8 1 - 2/22/2010 - 3 2 - 2/22/2010 - 2 1 - 2/23/2010 - 1 4 - 2/23/2010 - 6 6 - 2/23/2010 - 1
What I'm trying to get is 2/21/2010 - 1 2/22/2010 - 2 2/22/2010 - 3
I need generate an unique number without sequence. I am using Oracle 11.2, here is the details. The column idSeq is unique withing one specific idType. I don't want a sequence for each idType.
i'm trying to import data to QUANTUM "oracle database" from Oracle database import assistant using OCDia.exe with SQL statement below and i'm getting this error message "Part Number and manufacturer combination must be unique"
------------------------------------------------------------------------------------------------------------------------------------- PROCEDURE ACE_LISTPRICE_LEADTIME (P_IMP NUMBER) IS C DIA_RL_PKG.CURSOR_TYPE ;
I need to generate random and unique 6 digit number in Oracle. I need to insert these numbers into a table. I tried using DBMS_RANDOM package, which generates random 6 digit numbers, but fails to generate UNIQUE numbers.
11.2.0.1...How do I create an index on a view or any workaround that my view won't get duplicates?
SQL> create unique index indx01 on db_backup_details_vw(id); create unique index indx01 on db_backup_details_vw(id) * ERROR at line 1: ORA-01702: a view is not appropriate here
i am getting PLS-00306: wrong number or types of arguments in call to 'SECURITY_AUDIT_DTL_TYPE' error below code.
CREATE OR REPLACE PROCEDURE load_data_audit_trail_dtl AS TYPE security_type IS TABLE OF SECUIRTY%ROWTYPE INDEX BY PLS_INTEGER; security_type_var security_type;
When I try to run: select xa_time_cnv.utc_to_loc(sysdate ,('yyyy/mm/dd hh24:mm:ss') ) Fecha from dual; This message appears:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'UTC_TO_LOC'
the function is: /* utc_to_loc ** Purpose: utc_to_loc is a function written to convert a utc time, to the local time zone. */ FUNCTION utc_to_loc(utc_datetime in DATE) return DATE IS CURSOR c_get_utc_offset(utc_datetime DATE) IS SELECT Offset FROM TimeTran WHERE utc_datetime BETWEEN UTCStart and UTCStop; [code]...
My database is on GMT -3 and I try to view convert UTC-TO LOCAL.
I wrote function Quantity(p_item_number in varchar2)RETURN NUMBER, i called This function in POST_QUERY ,Error like this "wrong number or type of arguments in call to Quantity", i tried like as " Quantity(p_item_number in varchar2)RETURN NUMBER" , but it gives an error "encounterd the symbol VARCHAR2 when expecting one of the following ( "..
I am getting the below error while trying to refresh the materialized view:
BEGIN DBMS_MVIEW.REFRESH('TOP_MVIEW','C'); END; * ERROR at line 1: ORA-06550: line 1, column 9: PLS-00306: wrong number or types of arguments in call to 'VERIFY_LOG' ORA-06550: line 1, column 9: PL/SQL: Statement ignored ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2429 ORA-06512: at line 1
I'm trying to use MULTISET UNION to append several collections into a single recordset.The select queries shown for the CURSOR are simple beta versions of the real queries which have about 350 fields to each of them. I will be appending data from 15 of these queries which each will with no more than 20 records per query into a new single recordset (not sure if 'recordset' is the correct phrase or term for what I mean, it could be ARRAY, OBJECT, COLLECTION or something else all together).
Here is text of the ERROR message:
ORA-06550: line 63, column 1: PLS-00306: wrong number or type of argument in call to 'MULTISET_UNION_ALL'
ORA-06550: line 62, column 1: PL/SQL: Statement ignored
ORA-06550: line 71, column 36: PLS-00487: Invalid reference to variable 'VARCHAR2'
ORA-06550: line 71, column 5: PL/SQL: Statement ignored
I think the error has to do with some defect in my declare statements.
DECLARE MyTID varchar2(10);
CURSOR Cursor_rst1 IS SELECT d_owner_internal_id, d_internal_id, d_tid, d_entity_name, d_form_seq FROM rtns.itas_rtn_ct_1120_cor tblRecords WHERE d_form_seq = '2710' --Tax Year =2003 AND D_TID = MyTID; [code]....
When My C# program executes the cmd.ExecuteNonQuery(), the following exception is thrown:
[System.Data.OracleClient.OracleException] = {"ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'VALIDATE_PATIENT_NEW' ORA-06550: line 1, column 7: PL/SQL: Statement ignored "}
Here is my Stored Procedure:
CREATE OR REPLACE PROCEDURE VALIDATE_PATIENT_NEW ( P_LAST_NAME IN ogen.gen_m_patient_mast.last_name%TYPE, P_FIRST_NAME IN ogen.gen_m_patient_mast.first_name%TYPE, P_DOB IN OGEN.GEN_M_PATIENT_MAST.BIRTH_DATE%TYPE, -- timestamp DEFAULT NULL, c_dbuser OUT SYS_REFCURSOR
insert into call values(1,9818764535,9899875643,'IN','24-APR-13 02:10:43',10); insert into call values(1,9818764535,9898324234,'IN','24-APR-13 05:06:78',10); insert into call values(1,9818764535,9215468734,'IN','24-APR-13 15:06:78',10); insert into call values(1,9818764535,9899875643,'OUT','25-APR-13 01:06:78',10); insert into call values(1,9899875643,9899875643,,'OUT','25-APR-13 22:06:78',10);
Query : need to find first and last call of '9818764535' mobile number and of call_date between '24-apr-13' and '25-apr-13';
I made this script but I still don't quite understand if the syntax is correct. I just wanted to create a function and call it in an anonymous block. I then wanted it to use a variable as a parameter in an iteration and output the variable every iteration. It's done basically but I know it's not 100% right. The fibonacci function looks like its going to loop an infinite number of times if the parameter is greater than 2.
CREATE OR REPLACE PACKAGE myPACKAGE AS CREATE OR REPLACE FUNCTION fibonacci (n BINARY_DOUBLE) RETURN BINARY_DOUBLE IS BEGIN IF n <= 2 THEN RETURN 1; [code]........
- To create one trigger on the table and make it call two unrelated procedures OR - To create 2 triggers and each trigger will call only one procedure.
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;
Using Oracle 11g, below is the table, partitions, unique and non-unique local index:
CREATE TABLE DOCA( DOCA_ID NUMBER NOT NULL , DOCA_BKG_PAX_ID NUMBER NULL , ROW_PURGE_DATE DATE NULL ,)PARTITION BY RANGE(ROW_PURGE_DATE)INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))( PARTITION P2007 VALUES LESS THAN (TO_DATE('01/01/2008', 'dd/mm/yyyy')), PARTITION P200801 VALUES LESS THAN (TO_DATE('01/02/2008', 'dd/mm/yyyy')),) TABLESPACE T0; ALTER TABLE DOCA ENABLE ROW MOVEMENT;
CREATE UNIQUE INDEX XPKDOCA ON DOCA( DOCA_ID ASC, ROW_PURGE_DATE ASC)LOCALREVERSE TABLESPACE I0; ALTER TABLE DOCA ADD CONSTRAINT XPKDOCA PRIMARY KEY (DOCA_ID); CREATE INDEX XFKDOCA_DOCA_BKG_PAX_ID ON DOCA( DOCA_BKG_PAX_ID ASC)LOCALREVERSETABLESPACE I0;
I would like to know the difference between the performance of the unique and non-unique local indexes?.
I have a plsql Proc, which accepts a few parameters and inevitably loops through a cursor and runs a bunch of insert statements. With quite a few IF conditions.
Each insert statement has a value which i want to increment by (+1) every time an insert statement is executed in the same loop.. This is for a student housing database and this is for their room preferences so 1 is the first, 2 is there second preference e.t.c.
Please take a look at the code below: in the Insert values() I have put a? Where I want the number to increment from.
There are a lot more inserts which I haven't put below. I hope I have made myself clear as this has been quite difficult to explain. So for example if the 2nd two inserts are run, then I was the first one to insert with a 1 and the second with a 2.
BEGIN FOR rec IN c1 LOOP IF c1%FOUND THEN INSERT INTO table (PK_A, fk_rms_id, application_type, application_person_type) VALUES (NULL, rec.pk_rms_id, app_type, app_person_type) RETURNING PK_APPLICATION_NO INTO x; [Code] ........
I am switching database from access to oracle 11g. I have create all the required tables, but I am stuck at one point. The previous person who created access database had auto increment with SG0101, SG0102,........ In oracle I know we can auto increment primary keys but only with the numbers not with characters.
So I have customerid which is a primary key and it automatically increments the number, but I have one more column with memberid where I am inserting all the ids that start with SG0101 bla bla.....
I already have 800 member ID's that start with SG, but that value doesnt automatically increment because I dont have any sequence or trigger to do that.
So how do I create a sequence and trigger that will automatically start value with SG and keeps auto incrementing?
I would like to create a hierarchical tree with Department number as a node and all the employees (only employee names) under that department as shown below:
[-]Department - 10 KING JAMES ----- [-]Department - 20 MARY | | and so on...
I have created a hierarchical tree 'HT_DEPTNO' under block 'BL_EMP'. I also created a Record Group 'RG_HTREE' with query as shown below:
SELECT 1, LEVEL, E.ENAME, D.DEPTNO||' - '||D.DNAME DEPARTMENT, D.DEPTNO FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO START WITH E.DEPTNO = 10 CONNECT BY PRIOR E.EMPNO=E.MGR
I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically
