SQL & PL/SQL :: Dynamic Function - Create Physical Table And Return Table Name In Out Variable

Aug 30, 2011

I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.


In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.

I need to query on this dynamic table and return the result as return result. But i am not able to do it.

Here T_web_loylty_report_table is a type.

CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);

View 2 Replies


PL/SQL :: Create Function To Return Table Type

Jul 25, 2012

I am trying to create a function which would return a nested table with 3 columns of a table as a type.

my query is like

select col1,col2,col3 from table_1;

View 4 Replies View Related

SQL & PL/SQL :: Return Ref Cursor To Temporary Table From Function

Feb 17, 2011

I have strange problem when i try to return a ref cursor holding data from a select on a oracle global temporary table. If i iterate through the cursor , i can see the values but the function as such returns nothing through the ref cursor. I tried the temporary table as both delete on commit and preserve on commit

create or replace

testcursor BILL.refcursor;

ttstatus INT;
iSuccess INT;
returns INT;
TruncatedSQL1 VARCHAR2(32767);

View 12 Replies View Related

SQL & PL/SQL :: Pipe Line Function Taking Time To Return Table Record

Mar 15, 2011

I want to use a function in join clause. so i go for pipelined function(using for loop to get record & 1 more loop to fetch in table type variable). i achieved what i required. but problem is it takes much time to fetch data. is there any other approach which returns table records without pipelined function.

View 2 Replies View Related

SQL & PL/SQL :: Function Returning Table Of Dynamic Structure

Apr 24, 2013

I have a requirement to be coded like this:

A function to return pl/sql table(cant use ref cursor) whose columns varies every time it runs i.e,

type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000))
type pl_tab is table of pl_tab_type

func f return pl_tab

note : pl_tab_type will vary for each run of function f

i.e.,for example, pl_tab_type can be changed to as follows:

type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000),col3 varchar2(1000))

how to return pl/sql table of dynamic type from func,

View 12 Replies View Related

SQL & PL/SQL :: Dynamic Create And Populate Table

Jan 27, 2012

I am dynamically creating a staging table my_stg, and then populate it. Seems simple, but not sure why i get this error,

create table gtest4(myid varchar2(10), mykey varchar2(10));
create table gtest5(myid varchar2(10), mykey varchar2(10));
insert into gtest4 values(1,3);
insert into gtest4 values(2,7);
insert into gtest5 values(5,3);
insert into gtest5 values (1,7);

TYPE rectype IS RECORD (
myid VARCHAR2 (100),
mykey VARCHAR2 (100)

TYPE tabtype1 IS TABLE OF varchar2(100)

TYPE tabtype2 IS TABLE OF varchar2(100)

rec1 tabtype1;
rec2 tabtype2;
cur sys_refcursor;

count current exists max min prior sql stddev sum varianc execute forall merge time timestamp interval date

<an alternatively-quoted string literal

View 11 Replies View Related

SQL & PL/SQL :: Create A Function That Return XmlType

Apr 29, 2010

i want to create a function that build a return xml (XmlType):

create or replace
function plainLanguageSummary(nip varchar2,id number,code_language varchar2) return XmlType


but in the compilation i got the following error :

Error(10,62): PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + case mod new not null others <identificateur> <identificateur entre guillemets> <variable bind> avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <un littéral de chaîne avec spécification de jeu de caractères> <un nombre> <une chaîne SQL entre apostrophes> pipe <constante de chaîne éventuellement entre guillemets avec indication du jeu de

he seems to not like the first "select" he encounter!.

View 9 Replies View Related

SQL & PL/SQL :: Create Table - Tablename With Data From Subselect (variable)

Sep 2, 2013

I wish to create a table with week-number as suffix _36 or _30


$WEEKNUMBER is this statement:

SELECT TO_CHAR (TO_DATE(SYSDATE, 'dd.mm.yyyy'), 'IW') from DUAL
CREATE TABLE doesn't accept subselect

How to do that with SQL?

View 8 Replies View Related

PL/SQL :: Create Record Variable That Refers Dynamic Query Assigned To Ref Cursor?

Nov 11, 2012

Just explaining what I am trying to achieve:

1) i have a hr.departments table that was loaded in hr schema on 1st oct 2012 with 4 columns(department_id, department_name, manager_id, location_id)

2) now I have a new schema by my name 'rahul' and I have loaded departments table but now an additional column has come into picture,ie created_date, this table got loaded on 1st-Nov-2012

3) Now going forward my columns could be dropped from the departments table (it can be a case), for example might be my departments table in my schema 'rahul' one day could comprise of only 3 columns(department_id,department_name,manager_id)

4) Now in the next step, I have managed to extract common column names(in a single line where columns are delimited using a comma) from both the tables(hr.departments and rahul.departments) which are (department_id, department_name, manager_id, location_id) using all_tab_cols table and I have written a function for it which i will be pasting below.

5) now going forward, using the above column names line with column names delimited using comma, I have used a ref cursor and assigned a query to it using the line of columns that I have extracted from the above point

6) Now I want to create a record variable which refers to my ref cursor, something like we do when we create a record variable by reffering to an explicit cursor defination that we give in the declaration block.


1) I have been out of touch with plsql for a long time so I have lost a lot of mmeory regarding plsql.

2) basically I need to compare data in hr.departments table with rahul.departments table for only columns that are common to both the tables, rest new or discarded columns information will go in one of the log tables that I have created(this is done already)

Code :
create or replace procedure p_compare_data(fp_old_table_name in varchar2, fp_new_table_name in varchar2)


View 5 Replies View Related

Enterprise Manager :: What Is The Difference Between Create External Table Vs Create Table

Apr 29, 2011

What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?


View 3 Replies View Related

SQL & PL/SQL :: Create View From Dynamic Query (or Function Returning Query)

Dec 5, 2012

I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.

View 1 Replies View Related

SQL & PL/SQL :: XML - Return New Row For Each Variable?

Oct 31, 2013

I have some XML being returned from a web service, and it returns almost 900 variables. Whilst I am familiar with how to return these in a single row, do I can return a row for each variable? My DBA is very uncomfortable with creating a table with almost 900 columns, for obvious reasons. However, we already have plenty of tables with tens of millions of rows, so he's fine with that. I'll try and expand on the requirement. Below is some XML from the data returned to us:

<APPLICANT app_no="1">
<MAIN W="ZZ" X="{ND}"/>
<COUNTS Z="3" AB="0" BB="3" CB="0" DB="3" EB="3" FB="3" GB="0"/>

I would like to be able to return a new row for each variable, For example:

W | ZZ
X | {ND}
Z | 3
And so on.

View 2 Replies View Related

How To Avoid Table Data To Physical Standby Database

Jun 28, 2013

Our client requirement is to avoid table data to physical standby database.

View 3 Replies View Related

Data Guard :: Truncate Table Effect On Physical Standby

Nov 20, 2009

Are there redo generate during truncate operation even my primary database is in FORCE LOGGGING mode? How this will effect on physical standby database.

View 6 Replies View Related

SQL & PL/SQL :: Create Complete Hierarchical Table From Table With Only Two Columns - Parent And Child

Aug 13, 2012

We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,

Hierarchy Table
Parent Child
Report1Table A
Table ATable M
Table ATable N
Table NTable Z

Requirement :

From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this

-ParentChild 1Child 2 Child 3
-Report1Table ATable M
-Report1Table ATable N Table Z

Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.

SQL Statements to create hierarchy table:

create table hierarchy (parent varchar2(20), child varchar2(20));
insert into hierarchy values ('Report1','Table A');
insert into hierarchy values ('Report1','Table B');
insert into hierarchy values ('Table A','Table M');
insert into hierarchy values ('Table B','Table N');
insert into hierarchy values ('Report2','Table P');
insert into hierarchy values ('Table M','Table X');
insert into hierarchy values ('Table N','Table Y');
insert into hierarchy values ('Report X','Table Z');

Approached already tried :

1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;

2)Using connect by path function :
select *
from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path
from hierarchy start with parent='Report1'
connect by prior child =parent) a where Leaf not in (0);

Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.

View 3 Replies View Related

SQL & PL/SQL :: Command Is Used To Create Table By Copying Structure Of Another Table Including Constraints?

Jul 14, 2012

what command is used to create a table by copying the structure of another table including constraints ?

View 2 Replies View Related

PL/SQL :: Static Table Creation In Oracle - Difference Between Static And Dynamic Table

Dec 11, 2012

Static Tables Creation In oracle & Diff Between Static table ,Dynamic table

View 2 Replies View Related

SQL & PL/SQL :: How To Return Last 2 Rows In History Table

Nov 23, 2011

I have a history table which has a new record written to it for every time static data is updated. What I want to do is return any record that has been changed on a daily basis returning both the new record and the previous record so you can see the 2 records one below the other and compare.I have written the below but it returns all records per account:

SELECT H.Timestamp,H.AccountNo FROM History H
where exists (select H2.AccountNo,count(*) from History H2
where H2.AccountNo = H.AccountNo
group by H2.AccountNo having count(*) > 1 )
Where H.Timestamp > '20111101'
order by H.AccountNo,H.Timestamp

View 18 Replies View Related

Insert Into Table If Not Exist Otherwise Return Error

May 7, 2011

I am using oracle 10g to create a user register application. what i want is to insert the user information (like Email ,username, password, etc.) into a table. i am able to insert the data into the table but what i want is to check before inserting that same email,and username doesnt exist. if it does it should return some error like the email or username already exist.

View 2 Replies View Related

PL/SQL :: Return The Rows Of The Table Where Column Contains Specific Value First

Feb 5, 2013

I want my query to return the rows of the table where a column contains a specific values first in a certain order, and then return the rest of the rows alphabetized.

For Example:


Now i want USA and CANADA on top in that order and then other in alphabetized order.

View 5 Replies View Related

Create Temp Table From Existing Table

Sep 2, 2010

I want to create temp table, for this i am using:

CODEcreate global temporary table help_temp
select * from help;

but this is creating only the table structure, not copying the table data.

View 5 Replies View Related

SQL & PL/SQL :: Create Table By Using Another Table With Specific Condition?

Jan 25, 2013

can we create table with copying of another table with some specific condition.

example.suppose we have one table which name is emp with three columns.


i want create a table emptemp by using emp table where empjoindate between two dates.

View 2 Replies View Related

Create A Table Which Contain History Of Main Table

Sep 8, 2008

I have to create a table which contain history of a main table. like this:

if the main table is
nametypelengthnot null

I've plan to so this by create a trigger in main_table. my problem is my main table have a lot of fields and I can't write a code to control it 1 by 1 like :

if old.A <> new.a
insert into history("A",old.A,new.a)
if old.B <> new.B
insert into history("B",old.b,new.b)

I decided to select column name from the data dictonary using this SQL:

SELECT column_name FROM user_tab_columns WHERE table_name = '<<Table Name>>';

and then do a loop over the resultset and use the column name I've got , like this (its just an idea, may be not a write syntax):

FOR i IN 1..:result.COUNT LOOP
if ld.colname[i] <> :new.colname[i]
VALUES ( colname[i], ld.colname[i], :new.colname[i]);

but I can't write a "old.colname". I try with " old.'colname' ", " ld.'colname' " but it won't work.how to create a history file like I've describe.

View 5 Replies View Related

SQL & PL/SQL :: Table As Out Variable?

Mar 2, 2012

I have plsql table as out parameter in one function.From another function i called that function ,its executing perfectly but while returning to called place its saying a character to numeric conversion error.I checked all the l

Note: i can not able post all that functions here.

View 12 Replies View Related

SQL & PL/SQL :: Dynamic Variable Based On Another Field?

Apr 26, 2010

In the query below, I'm attempting to replace task codes with task descriptions by left joining to a multi-purpose control table.

In our corp, the description for the task code varies based on the value of change_program in each record, so rather than referencing the value of code_index.tabl statically as I do in the query below (201), I need a dyanamic variable to be defined for each record based on the value of work.change_program, which would represent the value for code_index.tabl.

For each record
if change_program =1 then v_tabl = 201
elseif change_program =2 then v_tabl = 202
elseif change_program =3 then v_tabl = 203
else v_tabl = 201

how to declare and use variables.

SELECT account,
FROM work


View 4 Replies View Related

SQL & PL/SQL :: Table Variable Or Collection

Jul 11, 2012

Is there any table except (global temp table and permanent table) which can be used to store data and be used in inner, left and right join for a session.

View 7 Replies View Related

PL/SQL :: Selecting Table Name Under A Variable

Mar 21, 2013

correct this one.

v_ename varchar2(10):='emp';
for j in (select ename from v_ename)
end loop;

getting error v_ename table does not exists. i should use v_ename, as i dont know the table name.

View 4 Replies View Related

PL/SQL :: How To Use Value Taken In Variable As Table Name In Query

Jul 22, 2013

I am fetching a value in a variable as: 

<select application_short_name into l_appl_nm from fnd_application where application _id=:p_appl_id> 

Now I need to use the value fetched in variable "l_appl_nm" as a table partition name in next query. 

View 8 Replies View Related

SQL & PL/SQL :: SELECT From Dynamic Table Name?

Jul 12, 2013

I do not know the table name but have a query that for sure returns the table name and I want to select a column value from the table into my PLSQL variable.

Here is the code :

v_VerTabName VARCHAR2(30);
v_Minor NUMBER(2);


But when I compile this I get ORA-00936 : Missing expression at the EXECUTE IMMEDIATE line.

View 3 Replies View Related

PL/SQL :: Insert Dynamic Table

Nov 9, 2012

Oracle 11.2

I trying to create a procedure that could update one or more different table into clob data - but only those tables that contain your search (indexed) word.

I have a 3 tables: item, common_lookup, member

CREATE INDEX i_item_barcode ON item (item_barcode) INDEXTYPE IS ctxsys.context;
CREATE INDEX i_item_title ON item (item_title) INDEXTYPE IS ctxsys.context;
CREATE INDEX i_item_subtitle ON item (item_subtitle) INDEXTYPE IS ctxsys.context;
CREATE INDEX i_common_lookup_meaning ON common_lookup (common_lookup_meaning) INDEXTYPE IS ctxsys.context;
CREATE INDEX i_account_number ON member (account_number) INDEXTYPE IS ctxsys.context;


Now is the question.. If it possible to create the procedure search_execute which can add data depending on subbmited parameters ?
If it possible, can You give me an example (dbms_output/update_clob), for search_execute to recive something like that:

search_execute ('Wars', item, item_title)

select * from clob data :
1      1068     ASIN: B00003CX5P     1014     Star Wars - Episode I      The Phantom Menace      (CLOB)     (BLOB)          PG      MPAA     22-mar-2005     3     16-lut-2012 17:31:55     3     16-lut-2012 17:31:55     
2      1069     ASIN: B00006HBUJ     1014     Star Wars - Episode II      Attack of the Clones      (CLOB)     (BLOB)          PG      MPAA     22-mar-2005     3     16-lut-


I try write something like this (it is not compile - and I know where is mistake, I tried to figure it out in several different ways - This is just my way of thinking expression) :
CREATE OR REPLACE PROCEDURE search_execute (v_text IN VARCHAR2, v_column_name IN VARCHAR2, v_table_name IN VARCHAR2)
v_clob CLOB;
v_tmp_clob CLOB;
CURSOR c1 IS (SELECT * FROM v_table_name WHERE contains (v_column_name, v_text) > 0);
CURSOR c2 IS (SELECT column_name FROM user_tab_cols WHERE table_name = v_table_name);


View 0 Replies View Related

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