SQL & PL/SQL :: Dynamic Categories Size And Names

Nov 29, 2010

I need to display dynamically a bunch of categories and I need the SQL algorithm to determine their size and names.

So, I have a table with one column and a variable number of records and I need to display them in GUI in pages, displaying also the page name which should be something like short name for first item to short name of last item in category.

Maximum items per page in 10 but I don't want to simply display blocks of 10 records. If the name of the 8th item starts with B and the name of the 9th item starts with C I want the first page to contain only the first 8 records. Minimum accepted items per page is 8, maximum is 10. The split should be based on the first 2 characters. Page name should be something like: first 4 chars of first item in category - first 4 chars of the last item in category.

View 18 Replies


ADVERTISEMENT

Dynamic Categories Size And Names?

Nov 29, 2010

I need to display dynamically a bunch of categories and I need the SQL algorithm to determine their size and names.

So, I have a table with one column and a variable number of records and I need to display them in GUI in pages, displaying also the page name which should be something like short name for first item to short name of last item in category.

Maximum items per page in 10 but I don't want to simply display blocks of 10 records. If the name of the 8th item starts with B and the name of the 9th item starts with C I want the first page to contain only the first 8 records. Minimum accepted items per page is 8, maximum is 10. The split should be based on the first 2 characters. Page name should be something like: first 4 chars of first item in category - first 4 chars of the last item in category.

View 3 Replies View Related

SQL & PL/SQL :: Dynamic Query For Swapping Names

Mar 25, 2011

writing dynamic sql query for swapping names:

the output shuld be like that

input output

a a
a b b a
a b c c b a
a b c d d c b a

i have writen it in static form by using instr,substr.But i m having difficulty in making it dynamic by using select statement.I have to make it for retrieving data from database.

View 13 Replies View Related

PL/SQL :: How To Use Column Names Generated From Dynamic SQL

Feb 8, 2013

I have a problem with Dynamic SQL.

I have written an SQL which will dynamically generate the Select statement with from and where clause in it.

But that select statement when executed will get me hundreds of rows and i want to insert each row separately into one more table.

For that i have used a ref cursor to open and insert the table.

In the select list the column names will also be as follows: COLUMN1, COLUMN2, COLUMN3,....COLUMNn

find below the sample code:

TYPE ref_csr IS REF CURSOR;

insert_csr ref_csr;

v_select VARCHAR2 (4000) := NULL;
v_table VARCHAR2 (4000) := NULL;
v_where VARCHAR2 (4000) := NULL;
v_ins_tab VARCHAR2 (4000) := NULL;
v_insert VARCHAR2 (4000) := NULL;
v_ins_query VARCHAR2 (4000) := NULL;

[Code]...

How to fetch the column names here
|| ');';

EXECUTE IMMEDIATE v_ins_query;
END LOOP;

View 8 Replies View Related

SQL & PL/SQL :: How To Use Column Names Generated From Dynamic Query

Feb 8, 2013

I have written an SQL which will dynamically generate the Select statement with from and where clause in it. But that select statement when executed will get me hundreds of rows and i want to insert each row separately into one more table.

For that i have used a ref cursor to open and insert the table.

In the select list the column names will also be as follows: COLUMN1, COLUMN2, COLUMN3,....COLUMNn

find below the sample

TYPE ref_csr IS REF CURSOR;
insert_csr ref_csr;
v_select VARCHAR2 (4000) := NULL;

[Code].....

View 5 Replies View Related

SQL & PL/SQL :: Rows To Columns With Dynamic Allocation Column Names

Nov 16, 2012

I want the output from two tables with rows to columns and generate dynamic basing on the columns.

For example: Table A (a1) , Table B(a1,b1)

Data: A B
------ ----
a1 a1 b1
--- ---- ----
1 1 x
2 1 y
3 2 a
2 b
2 c

o/p: Columnname col_1 col_2 col_3
-----------------------------------------------
a1 b1_1 b1_2 b1_3
----- ---------------------
1 x y
2 a b c

Columns should be generated based on the second table second column.

View 18 Replies View Related

SQL & PL/SQL :: Select Dynamic Column Names In Select Statement In Function?

Jul 4, 2010

i want to select dynamic column names in my select statement in my function.

View 4 Replies View Related

Application Express :: Unable To Size Pop Window That Redirects To Regular URL Using Dynamic

Nov 8, 2013

I have an instruction like this in a pluggin: begin    ...   

htp.p('<font size="3" color="FireBrick">

Cannot find? Search on </font>' || '<

[URL]....

<font size="4" color="FireBrick"><b>Google</b></a>');    ...
end;

 It displys the Google page in another window. How can size the wiindow? For example adding width=200, height=100 to the href tag doesn't  work.... Do I need to use JavaSvript?If yes, any example?.

View 1 Replies View Related

Creating Age Categories From Birth Date?

Oct 9, 2007

I have around 1 million users in my table. I currently have their birth date in the table and for reporting purposes i want to put them into three different columns.

For example, For User A, DOB is 01/01/2005,
User B, DOB is 01/01/2002,
user C,DOB is 01/01/1998.
user D, DOB is 01/01/1990
I want a view like below.

Cat1. Cat2. Cat3. User
"0-10" '0-5' '0-2' 'USER A'
"0-10" '0-5' '3-5' 'USER B'
"0-10" '6-10' '8-10' 'USER C'
"11-20" '16-20' '16-17' 'USER D'

View 5 Replies View Related

SQL & PL/SQL :: Display Count Of Employees That Belongs To Different Categories?

Sep 21, 2010

I have to display count of employees that belongs to different categories.

is the situatio There is a category table CATEGORY with three columns (PK,NAME,TREEPOSITION) and we have categories A, B, C these three categories can further have sub-categories so the treeposition for the sub categories will be followed by their root category with _ 'symbol'

Now I have table for the employees with 3 columns (pk,name,category_id), where employees.category_id=category.pk So I want to calculate the number of employees in each category or sub-category.

since the number of categories will be large and each will be having different names so going through names will be bad option left is grouping through the treepostion
the problem is I cant use like using IN for the TREEPOSITION. .

TABLE EMPLOYEES ( PK,NAME,CATEGORY_ID)
TBALE CATEGORIES (PK,NAME,TREEPOSITION)

View 2 Replies View Related

SQL & PL/SQL :: Find All Column Names In All 20 Tables That Have Same Names?

Jul 7, 2010

I have 20 tables. In all 20 tables, some of column names are same and some are different. I need to find all column names in all 20 tables that have same names.

create table t1 (
col1 varchar2(10),
col2 varchar2(10));
create table t2 (
col1 varchar2(10),
col3 varchar2(10));
create table t3 (
col1 varchar2(10));

View 1 Replies View Related

Server Utilities :: Estimate Size Of FlatFile Based On Table Size?

May 8, 2013

We are planning to export the table data to a file pipedelimited. How do i estimate the size of the FlatFile based on the table size? or avg rowlength

View 3 Replies View Related

Server Administration :: How To Reduce Size Of TEMP DBF File Size

Apr 13, 2011

I am using oracle 8.1.5 database and my temp01.dbf file size is increased upto 19.8 GB now i want reduce its size .

View 13 Replies View Related

PL/SQL :: Dynamic Ref Cursor With Dynamic Fetch

Apr 8, 2013

I'm using dynamic sql (DBMS_SQL) to define columns of ref cursor. It works Ok but the problem is when i'm using PL/SQL CURSOR in the REF CURSOR. Then,I'm getting :

Error at line 3
ORA-00932: inconsistent datatypes: expected NUMBER got CURSER
ORA-06512: at "SYS.DBMS_SQL", line 1830
ORA-06512: at "TW.PRINT_REF_CURSOR", line 28
ORA-06512: at line 9

Here is my code:
set serveroutput on
exec DBMS_OUTPUT.ENABLE(1000000);
declare
l_cursor sys_refcursor;
begin
[code]....

Is there a solution or bypass?

View 3 Replies View Related

How To Use Column Names In Array

Dec 2, 2012

I have a tmp table with 6 cols, and data to be inserted is from two other tables. My problem is there the cols of the table are

sid varchar2(10),
cob_dt varchar2(10),
deal_id varchar2(10),
new_val varchar2(10),
old_val varchar2(30),
amend_col v archar2(50),
i have return a proc
create or replace

[Code]...

View 1 Replies View Related

SQL & PL/SQL :: Count Of Employee Names

Oct 5, 2011

this is my code

DECLARE
V1 VARCHAR2(100);
V2 VARCHAR2(100);
V3 VARCHAR2(200);
BEGIN
[code]......

if i want to dynamically enter the three to four names then i want the count of those names .

View 5 Replies View Related

SQL & PL/SQL :: Column Names Are Changed

Jul 21, 2011

when we decribe one table structure, it shows the column name as below:

DUKEDMTS03DB01:SYS@TODS1> desc uet_rep.ECM_MAINTENANCE

Name Null? Type
----------------------------------------------------- -------- ------------------------------------
C1 NOT NULL VARCHAR2(15)
C2 VARCHAR2(254)
C3 NOT NULL NUMBER(15)
C4 VARCHAR2(254)
C5 NOT NULL VARCHAR2(254)
C6 NOT NULL NUMBER(15)
C7 NOT NULL NUMBER(15)
C8 NOT NULL VARCHAR2(254)

[Code]...

But, till yday it was showing the orignal column name..

View 3 Replies View Related

SQL & PL/SQL :: Using VArray For Known Column Names?

May 2, 2013

I am trying to build an array that contains known column names, 63 columns in all.The idea is to search backwards from 63 down to say 10 to find the first non-blank column.

Then using a loop with the known number of lines print from 1 to last non-blank column.The problem is that it only shows the column names and not the values of the columns.So I am getting 63 lines every time, instead of 20, 30, 40 or however how many fields actually have something.

Here is my
declare
to_num number;
field_name varchar2(15);
[code]....

View 9 Replies View Related

PL/SQL :: Display Names And Packages

Jul 17, 2013

NAME         package

  ---------- --------------

Anand         basic           Anand         pascal    Juliana       cobol           Kamala        dbase     Mary          oracle    Mary            c         Partick        c++             Qadir         assembly        Qadir            c               Ramesh         dbase     Remitha          c               Remitha        assembly        Revathi         pascal    Revathi         basic          

View 5 Replies View Related

Like Condition - Match Two Columns Containing Names?

Dec 28, 2011

I have a problem with a query I'm trying to run. I need to match two columns containing names, first column (NAME1) contains only the surname and the second column (NAME2) contains a surname and initials, with the initials turning up on either side of the surname.

Example:
NAME1: 'Bush', 'Thomas', 'Cook', 'Smith'
NAME2: 'Bush, B.B.', 'Thomas,C.' 'Cook', 'A.A. Smith'

Basically the code I'm trying only turns up complete matches and not partials. what I'm doing wrong or how I can improve it?

CODEselect *
from TEST
where NAME1 like ('%'||NAME2||'%');

View 4 Replies View Related

SQL & PL/SQL :: Return Column Names In Lowercase

Oct 11, 2011

I wonder if there is any way to return the columns of an select with its letters lowercase?

I have a piece of code that creates an script wich returns an SQL result to be confronted with some templates. My template have the column names in lowercase and because It is case sensitive the Uppercase returned by Oracle,

View 5 Replies View Related

Forms :: Dynamically Getting Column Names

Jul 20, 2010

i have list item populated with many table names from a schema. i have grid in oracle forms 10g and i want to fill the grid with at least four/more columns.I want to fire the list change trigger when each time any one table name is selected.

how can i find the columns names dynamically for filling the grid.

View 6 Replies View Related

SQL & PL/SQL :: Using Reserved Words As Parameter Names

Mar 18, 2011

When integrating your product with third-party APIs I've repeatedly come into conflict with PLSQL and Oracle's reserved words list. For instance while integrating the clickatell SMS Message Transmission notification API there are two variables they send.

[URL]......

How do you propose PLSQL handles these requests without using Apache to rewrite the variable names, or using the three and four variable pslql variable collection methods.

View 1 Replies View Related

SQL & PL/SQL :: Retrieving Column Names And Storing Them

Apr 2, 2010

I'm writing a procedure that takes a table name as a parameter and I would like to print out the column name with the supporting row entry for each row. I know the logic I'd like to use, but how do you query the metadata to return the column names and store them.

View 3 Replies View Related

SQL & PL/SQL :: Query To Transpose Column Names To Row

Mar 9, 2011

Query : select * from scott.dept where deptno=10;

Result
DEPTNO |DNAME |LOCATION
-------|----------|----------
10 |ACCOUNTING|NEW YORK

But i want the above resultset as below,

COL1 COL2
----------------------
DEPTNO | 10
DNAME | ACCOUNTING
LOCATION | NEW YORK

View 4 Replies View Related

SQL & PL/SQL :: Cursor To Fetch Column Names

Jul 25, 2013

I have the following piece of

CREATE OR REPLACE PROCEDURE COMP_RECORDS
IS
l_query VARCHAR2 (10000) := '';

CURSOR TBL1
IS
SELECT TABLE_NAME, COLUMN_NAME FROM COLS_TO_COMP WHERE TABLE_NAME='ACE_HIST';
TBL1_REC APP.COLS_TO_COMP%rowtype;

[Code]..

However I am getting an ORA-00923 exception with message as "FROM keyword not found where expected". know if I can/cannot use a cursor to fetch column names for a table?

View 9 Replies View Related

SQL & PL/SQL :: Convert English Names To Arabic?

Apr 17, 2011

I need Code or Procedure which can convert English Names to Arabic Names in Oracle.

Like Given Student ID it fetch Student Name But I need to Convert this name to Arabic.

View 39 Replies View Related

SQL & PL/SQL :: How To Insert All User Names Into A Table

Jan 1, 2012

How to Insert all user names into a table from sql for example when i use select * from all_users command it displays all user names i want that how we transfer these user names into a table.

View 11 Replies View Related

SQL & PL/SQL :: How To Query For Columns Without Using Column Names

Jun 5, 2013

I have limited permissions and am unable to create temp tables.So I would like to use a cursor to "create" a table of sorts then access/query it. But this "table"/cursor would have no column names so how do I refer to the columns? Is there a way to refer to a column by column number rather than column name in a query:

select column1 from tablename where column2 = 'abc'?

Is there a way in a query/update/insert to refer to a column by column number rather than column name?

declare
cursor c1 is
select 'abc', '8-Apr-2013', pk_id from EMPLOYEE where pk_id = '153'
UNION
select '1xyz', '4-10-2013', pk_id from EMPLOYEE where pk_id = '154'
c1_val number;

[code]....

View 4 Replies View Related

PL/SQL :: How To Get Constraint Name / Table And Column Names

Sep 12, 2012

I am a junior DBA. I want to check how many columns are under PRIMARY KEY constraint.

I used dba_constraints view. Please find below details.

SQL> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints
2 where TABLE_NAME='DSET_PRODUCT_S';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ----------------------------------------
SCOTT SYS_C10202456 C EMPLOYEE
SCOTT SYS_C234576 C DEPT

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved