3 No Hello
4 No Hello
I want to print the output of this table as -->>
4 No Hello
To make it more clear, I just want that whenever the value f column "B" changes then only its value should be printed, else it should be NULL. And if "B" has same value for all the records then the value of "B" should be printed at the last.
DECLARE JOBSFILE UTL_FILE.FILE_TYPE; -- TAKE ALL JOB TITLES FROM JOBS CURSOR JOBSCUR IS SELECT * -- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID FROM TARGET_UCM ; [code].......
this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.
In Sales Order Form there is a print receipt button. In current situation On Clicking the 'Print Receipt Button' it submits a XML Publisher report(PDF output) to the concurrent manager. Currently to print that report i need to go view--> requests--> view output and then print. According to my new Requirement i need to print that report directly to local printer on one simple Print receipt button click in form.
I am trying to call the printers on server using custom.pll. The report is running successfully but it is not printing output to the local printer. I need to print the report to the local printers based on responsibility. Local printers are available on apps server.
Is it possible to print the document using custom.pll? Is there any other alternative to print the report on simple button click in form.
I searched many forums but i found customizations on form level. I am trying to customize the form using custom.pll. I found many examples like URL..... but i don't think i can use these practices in custom.pll. ADD_PARAMETER & RUN_PRODUCT dont work in custom.pll. I need to complete this requirement immediately.
Environment: Apps 188.8.131.52, Forms 6i
Here is the code that i am using in my custom.pll.
if (v_form_name = 'OEXOETEL' --and v_block_name = 'LINES_SUMMARY' and name_in('parameter.ACTIONS') = 'PAYMENT_RECEIPT' )THEN l_organization_id := Name_In('PARAMETER.OE_ORGANIZATION_ID'); l_order_header_id := Name_In('ORDER.header_id'); select to_number(oe_sys_parameters.value ('SET_OF_BOOKS_ID')) into l_sob_id from dual; xml_layout := FND_REQUEST.ADD_LAYOUT('XXAFP','XXAFPOEXPMTRCRTF','en','US','PDF'); [code]....
I've 2 identical tables of 300+ columns each,and each of table is having pk as combination of 2 fields.There are some fields where data is not matching. need a query which will use these two tables and return the following-
We are using the function dbms_utility.get_hash_value to create a unique identifier, the hash is created based on statement of creating an index. We see that for a different statement is being generated the same value, which in this cause errors because this value must be unique.
The Hash is to be calculated as follows HASH_VALUE = dbms_utility.get_hash_value (sql_text, a 10000)
We are using the correct the hash function or should we use another way to generate unique values?
Personally, I don't need the top value(s), but it would be nice.I was trying a few routes with rownum, and I can get it to pull 1 set of pairs with a where location= condition, but I can't seem to successfully combine the two.
I am facing a problem in leave_form report! want to show employee's previous leaves detail (leavedate,leavetype), i don't want to show leavetype 'PPP' repeating frame type is Across/Down, there are 22 records of leavetype 'PPP' and one record of leavetype 'CL'
problem is report is showing 22 records of leavetype 'CL' of same leavedate!
i want that report should show the actual leavedate and leavetype records.using 9i database 6i developer server2003
FUNCTION Cf_1formula RETURN CHAR IS v_leavetype CHAR(40); BEGIN SELECT LT.description INTO v_leavetype FROM hrm_attendance L, hrm_leavetype LT, hrm_employees E [code]....
In literal terms, I'm interested in two fields, I'll call them Field A and Field B. I want to find all situations where a single unique value of Field A has both values Z AND X in Field B (not either or, but both together).
To go into some detail -
I need to make a query that finds ONLY occurrences where one employee id has a certain set of values together (without going into specifics, I'll say PermissionA, PermissionB, PermissionC). I can easily make a query that returns all user id's and all permissions belonging to them, and I can use criteria to filter the results to Permission A B and C only so as to exclude other permissions from getting returned (since there are hundreds),
however my objective is to get ONLY results where the same employee ID has all of those permissions (not just any one or two of the three). However, I don't want to have any criteria that limits the employee ID (I want to search all employee id's, and get a list of those with permissions a and b and c, not just any combination thereof, but all of them). I'm currently able to organize the output using a pivot table by employee id > role,
so that I can easily look at each employee and the roles they have, but I want to undertake a project that will involve searching a much larger number of employees (a # that makes it impractical for me to look through the list, I need to have a query that limits the results to the combinations that I'm looking for, as in this example - permission a b and c together).
In my form i have a multi record block which is based on a table in which i am performing insertion, deletion, updation.
My requirement is as below,
While saving a records i want to check distinct value of specific item can not be greater than some value say 2. Then only my records should be saved. I am planning to populate those values in a collection table type and take the distinct values from it. How to populate record values in a collection table type.
DECLARE JOBSFILE UTL_FILE.FILE_TYPE; CURSOR JOBSCUR IS SELECT * -- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID FROM TARGET_UCM ; BEGIN JOBSFILE := UTL_FILE.FOPEN('FILES','JOBS.TXT','W'); UTL_FILE.PUT_LINE(JOBSFILE,'Action = insert'); FOR REC IN JOBSCUR [code]....
is there anyway to print the values of full table .i have used all column names to print .
1) Table emp_perm is a lookup table with 2 columns, temp VARCHAR2 & perm VARCHAR2.
Table emp_perm have below data in it:- temp perm 1064885 0349034 0099982 7399982 6476456 9170346 5252525 52525252)
Now table employee might have following combinations of rows.
Records for both Temps file (1064885) and perm file number (0349034) does exist in employee table, in this case Delete Temp File Number records i.e. 1064885 and keep Permanent File Number (0349034) records as it is.
Records for only temp file number(0099982) does exists and corresponding perm file(7399982) does not exists in employee table in this case convert these records i.e. update 0099982 with its perm file number 7399982 using lookup table emp_perm.
Records for only perm file number exists (9170346) and corresponding temp file number (6476456) does not then do not amend employee table, as we are interested in perm file numbers.
If file number does exist in lookup table in emp_perm and both of them are equal (5252525) then do not amend employee table as temp and per file number are same.
What I am trying to do is print out page that displays all of the column titles and the data under them for a query given by the user. It is then going to be put into an excel spreadsheet.
I've done this before with Java, simply by using the getMetaData function, but I can't seem to find an alternative for PL/SQL. It seems at the very least I need to know the number of columns in a query, but that would defeat the purpose of this.
Is what I am trying to do even possible or is knowing the column names an absolute necessity when printing table data?
I have created a table below, my TL asked me to create a local unique constraint for the below table.
I went through all sites and could not find the correct solution, how to create LOCAL UNIQUE CONSTRAINT ON SUB PARTITION TABLE and LOCAL UNIQUE INDEX ON PARTITION TABLE. Creating Local Unique constraint should take care of creating local unique index creation.
Unique key columns are DET,GDS,ARRIVE_DT
CREATE TABLE SUB_PAR_TAB ( ID VARCHAR2(100) NOT NULL, REGION VARCHAR2(40) NOT NULL, SOURCE VARCHAR2(80) NOT NULL, DET VARCHAR2(80) NOT NULL, GDS VARCHAR2(40) NOT NULL, ARRIVE_DTDATE, SYS_SOURCE VARCHAR2(25) , [code]........
I have a table with a non-unique index consisting of three columns. The first column is not null while the remaining two are nullable. Queries using this index will chiefly be made in two ways.
1. Column one and two having values. Column three is null. 2. Column one and thre having values. Column two is null.
In both cases I expect range scan will be used since it's non-unique. In the first case the scan will be on values in column one and two. But what happens in case two. Will the range be on colum one, column two(being null) and cxolumn three? Or will it be on just column one since the second column is null? I have done some testing. I can see , using EXPLAN PLAN, that range scan is used in both cases. how the index is used?
Is there any other drawbacks with an index like this?
Create a function which will indicate if a given record in a table is unique or not. Unique means the data is occurring only once in the entire table.
Function should be in this signature
function IS_UNIQUE (tableName in varchar2,tableAttribute in varchar2) return number ..... begin //logic to check if given data is unique return 0; //return 0 if data is unique else return 1; //return 1 if data is duplicate end;
Once I run this query
select attribute1 from table1 where IS_UNIQUE(table1,attribute1)=0
All records of attribute1 which are unique need to be fetched. Similarly, select attribute1 from table1 where IS_UNIQUE(table1,attribute1)=1 should return all records of attribute1 which are duplicates.
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?.