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;
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.
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;
is it possible to use the records returned by a query as column names in a select query.
select (select column_name from dba_tab_cols where table_name='V_$DATABASE' and column_name like '%CONTROL%') from v$database; * ERROR at line 1: ORA-01427: single-row subquery returns more than one row
After the Data is loaded, we see data look like the above.
(1) Always COL3 column name have data value as 'Gen1' which is the indication for us from where data starts. But Gen1, Gen2, Gen3 etc... is dynamic. ie. This month we get gen1 and gen2 columns followed by null value in a column. Next month we get gen1,2,3,4 followed by null column. (2) Null Column indicate us that there is a break in the column. (3) Then next we need to look for next group of data (Monthly) and then insert into the same table again with different sheet_name column. (4) Next for Quater and then YTD. None of the column Values are fixed and its all dynamic.
If you load the below data, you will come to know what i am looking for. I tried using UNPIVOT. But couldnt able to achieve it. Is there an option to do it in sigle query? or Do I need to go for Stored Procedure?
Insert into TST_TBL (JOB_DETAIL_ID, SHEET_NAME, COL1, COL2, COL3, COL4) Values (100, 'Wire_1', 'Gen1', 'Gen2', 'Gen3', 'Gen4'); Insert into TST_TBL
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.
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.
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.
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
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..
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]....
Our production DB version is 11.2.0.1 and we do use DBArtisian. Everyday morning we check table space usage using that tool and it generates the below listed query. It used to run in 15 secs. But since one week it is running for 5/6/8 mins. I have updated the statistics on sys objects and tuning advisor created the execution plan. So, now it is running in 2 mins. Nothing has been changed in the DB configuration. I see the same query running in secs in dev environment. understand what might be the issue and how could I improve the performance of this query.
SELECT SUB.TABLESPACE_NAME, SUB.STATUS, SUB.EXTENT_MANAGEMENT, SUB.SEGMENT_SPACE_MANAGEMENT, SUB.TOTAL_SPACE_MB, SUB.USED_SPACE_MB, SUB.FREE_SPACE_MB, SUB.PERCENT_FREE_SPACE, SUB.CONTENTS, SUB.TABLESPACE_GROUP, CASE WHEN SUB.TABLESPACE_NAME = P.VALUE AND SUB.CONTENTS = 'UNDO' THEN 'YES' ELSE 'NO' END
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
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,
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.
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.
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?
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
I need to rename only two names in column report_headings1. But in order for me to see all other names I need to use all of them in decode function. Is there an easier way to write this Select Statement.
select days_start, days_to, decode(report_heading1,'31 Or More days', 'Future', '00 Dispute/Pend', 'Dispute or Pend', '1 to 30 Days', '1 to 30 Days', '31 to 60 Days', '31 to 60 Days','61 to 90 Days', '61 to 90 Days', '91 Or More Days', '91 Or More Days' ) report_heading1, report_heading2, type from ar_aging_bucket_lines lines, ar_aging_buckets buckets where lines.aging_bucket_id = buckets.aging_bucket_id;
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit PL/SQL Release 10.2.0.5.0 - Production "CORE10.2.0.5.0Production" TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production
See attachment for table creation and table data.
The table tbl_completed has two columns, id and completed. I have created a query that does provide me with the result set that I need, but I need to pivot the rows into columns. So instead of a result set to look like this:
select completed, count(completed) as theCount from tbl_completed group by completed;
COMPLETED THECOUNT Y 772 N 720
I need a result set to look like this:
COMPLETED,Y,N THECOUNT,772,720
The best solution that I have discovered is this, but I figuring their might be a better way.
select (select 'THECOUNT' from dual) as COMPLETED, (select count(completed) from tbl_completed where completed = 'Y') as Y, (select count(completed) from tbl_completed where completed = 'N') as N from dual;
I am working on convertion of triggers from SQL SERVER to Oracle. I got them converted using a tool. But not sure how to correct the below trigger which uses '*' in sql server.
Code in SQL SERVER :-
CREATE TRIGGER INSERT_AUDIT ON T_AUDIT FOR INSERT AS INSERT INTO T_AUDIT_LOG SELECT 'INSERT','AFTER', CURRENT_TIMESTAMP, NULL,* FROM INSERTED
Converted into Oracle:-
CREATE OR REPLACE TRIGGER INSERT_AUDIT BEFORE INSERT ON T_AUDIT FOR EACH ROW INSERT INTO T_AUDIT_LOG VALUES ( 'INSERT', 'AFTER', SYSTIMESTAMP, NULL, * );
END;
how can I remove the *. I tried replacing the * with the rest column names prefixing with :NEW.
the last 3 columns when are c_data1,c_data2,c_data3 as :- INSERT INTO T_AUDIT_LOG VALUES ( 'INSERT', 'AFTER',SYSTIMESTAMP NULL, :NEW.c_data1,:NEW.c_data2,:NEW.c_data3); But this gives error:- PLS-00049: bad bind variable 'NEW.C_DATA1' PLS-00049: bad bind variable 'NEW.C_DATA2' PLS-00049: bad bind variable 'NEW.C_DATA3'
I have a requirement that i should list out all the table names which are all using timestamp datatype in a specified schema. Is there any way to find those table names by using any system tables.
