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.
Explanation:
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);
[code]......
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 PACKAGE BODY BILL AS
FUNCTION FILTERI RETURN BILL.refcursor IS testcursor BILL.refcursor;
ttstatus INT; iSuccess INT; returns INT; TruncatedSQL1 VARCHAR2(32767); BEGIN [code].........
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.
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 as
[Code].....
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!.
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.
PS:
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) is
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.
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:
Are there redo generate during truncate operation even my primary database is in FORCE LOGGGING mode? How this will effect on physical standby database.
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.
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
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.
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:
Country ALBANIA ARGENTINA AUSTRALIA .... CANADA .... USA ....
Now i want USA and CANADA on top in that order and then other in alphabetized order.
I have to create a table which contain history of a main table. like this:
if the main table is ======================== nametypelengthnot null Avarchar5Y Bvarchar5N Cvarchar5N Dvarchar5N ======================== [code]....
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):
BEGIN ..... FOR i IN 1..:result.COUNT LOOP if ld.colname[i] <> :new.colname[i] INSERT INTO history VALUES ( colname[i], ld.colname[i], :new.colname[i]); END LOOP; END;
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.
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.
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, change_program, task_code_01, task_code_02, task1.longdesc, task2.longdesc FROM work
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.
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.
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;
[Code]....
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-
[Code]....
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) IS 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); BEGIN
