I am trying to load one parent-child hierarchy table. I have a table XXX, which contains the columns containing parent, child , level and many more columns in it. Now I need to use the table XXX to load my parent-child hierarchy table. How can I perform this using SQL.
Printing a bar code (such as a pick slip number e.g. 223456) in Oracle Applications 10.7 char, reports 2.5 on a genicom (dot matrix)..I was unable to find any fonts for this when I searched.
I want to create a block of code that would search in all tables in a schema for a column_name where its data_length is like 4000 let's say. This data_length is actually dedicated for a comment column. If found, all not null column with like 4000 data_length will be changed by string "Comment has been removed". I have the following script below but it seemed lacking.
begin for rec in (select table_name, column_name from user_tab_columns where column_name like ?data_length? = 4000 order by 1,2) loop begin execute immediate 'update '||rec.table_name||' set '||rec.column_name||' = ''Comment has been removed'' where '||rec.column_name||' is not null'; commit; [code]........
I would like to create a trigger that will execute a stored procedure when a package/function/procedure is compiled. I tried creating an update trigger on user_objects, but it statues aI cannot create that trigger tyoe on views.
I have no knowledge about Barcode. The problem is an issue of Loyalty Cards of a Hotel and Restaurant to various customers and then these cards will be presented by the customers time to time in the Hotel as well as Restaurant. The Owner of the Hotel and Restaurant wants to generate separate barcode for each card and when this card will be presented then the bar code reader will readout the code and the system will calculate the amount of discount/rebate. Because if the data entry operator enter the code of the card through key board the it will be a chance of leakage or misuse of that card.
I have to compare my SVN source code (packages, views etc) with the production code in the database like views etc (actually we are not sure that what we have in the svn is the final version of production code, we have objects created in the production database, but we don't have latest scripts for that. we have to deploy the svn code in the UNIX box).
So here the comparison is between the OS files and the database objects.
I thought I would get scripts of all the packages, views etc from the production database by using DBMS_METADATA or some utility and save the code in OS files then compare one svn file with OS file manually by using some comparison tools e.g toad provide one comparison tool.
Snapshot_date is last working date of that month, and is recorded only once in the table for a matrl_num & plant combination.
Inserting records in the table: Refer to insert_extended_values.sql Note: This is just a sample table , having records for only two matrl_num.
after inserting records , you can see the records are for two different matrl_num's. For each matrl_num , we can have multiple values of plant and for each plant we can have different value of std_Cost(eg:matrl_num='0023173556').
Moreover for each matrl_num , I have some records in each month , based on the number of plants(with std_cost). Std_cost for a plant is updated in the table whenever there is a change in it from the previous value.In case of no change in value of std_cost over previous month's value , we have 'Null' std_cost for that matrl_num, plant & month(next month).
I want to update these 'Null' values with latest(last month's 'not null' - std_cost) std_cost for that matrl_num and plant. Such change in std_cost can occur multiple times , so we need to update the 'Null' std_cost till the time we have encountered a change in std_cost and so on.
i have a existing table called table_1 to did some changes to it, but i need to do a source search to find where all in the code that we reference this code to ensure that there is not a variable declaration that sets this to a specific number. how do i do a source search. i alter a existing column (overbook_max) to number(2) tonumber (3)
I downloaded oracle sql developer, i type my code into a worksheet but if i use the run statement option, it asks me to make a connection. I dont want to make a connection, just test the data locally.However, even if I do try and make a connection, i get ora-12560 error (local connection).
I just want to type up some data to make some table and test to retrieve or manipulate the data. I'll use any program, command line or gui.
I got html source code inserted into the table as CLOB (or BLOB). And I would like to search a some word from that.When I find a some value I can write this one into the column.It would be easy if this code is xml but isnt.
I have question on the following, that gets defined for the bad file and the log file
BADFILE 'bad_%a_%p.bad' LOGFILE 'log_%a_%p.log'
What does the %a and % p indicate? Also if I wanted to get the value of %p and %a into a variable how would I do it? I want to be able to append %p and %a to the below variable, but unsure how to achieve it..
I need to write a code which dynamically reads the table name from user_tables (starting with cd) & load the data from remote database which has the same table name & structure based on the load number..
Static PLSQL statement would be :
declare BEGIN INSERT INTO "cd_patient" C-- DYNAMICALLY GET THE TABLE NAME SELECT C1.PAT_MRN -- DYNAMICALLY GET THE COLUMN NAME FROM REMOTE_DB.CD_PATIENT@XXX C1 WHERE C1.LOAD_NUMBER > 2;
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]......
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 am searching the simplest way for ad hoc MINUS.I do:
SELECT * FROM uam_rss_user_XXXXXXX WHERE host_name IN ('XXX0349', 'XXX0362', 'XXX0363', 'XXX0343', 'XXX0342', 'XXX0499', [code]....
and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?
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 want to create a new table 'b' from table 'a'.I know the query will be " create table b as select * from a;" But there is some twist in my question. I do not know how many table are there in table 'a' all I know is that there is one column named "timestamp" in table 'a'.
Now I want to create table 'b' with the difference that the column "timestamp" should contain the value "sysdate" instead of the earlier values which were there in table 'a';
Note: I have to do this in single query. I can not first create table 'b' from 'a' and then update the values of column "timestamp" to sysdate.
