You will no longer receive e-mail notifications from this forum.
Log-In to post
These people make Oracle great and have the most points for this month!
NOTE: This forum is for non-product questions and discussions on the Oracle database platform. Please post Toad-family product questions in their respective product forums. Thanks!
Let me explain the question in detail.
Period1 - SEQ - Column1 - Column2
1- 1211212 - value1 - valu2
1- 122322 - value3 - value4
2 - 3434343- value5 - value6
2 - 344323 - value7 - value8
I want to pull the max(seq) for all the distinct periods. I need these details to compare to other table too. Please help me out.
I need a help in updating a table where by if we find similar pattern of data that should not be updated but the remaining should be updated. Say For Example . In the below data all data that are like ST00 should be treated as similar which I mean to say case sensitivity should be avoided. But if we come to k100 it should be treated as single item and should be converted to upper case. There can be any combination case of data in the stock_code. Lik rC00, Cr00, cr00 like that. Any help is much appreciated. Thanks
CREATE TABLE DEMOSYS(STOCK_CODE CHAR(4));
INSERT INTO DEMOSYS VALUES('ST00');
INSERT INTO DEMOSYS VALUES('st00');
INSERT INTO DEMOSYS VALUES('sT00');
INSERT INTO DEMOSYS VALUES('St00');
INSERT INTO DEMOSYS VALUES('k100');
Hi Greetings to All,
I'm new to Pl/SQL.
It's a description about a part so it really need to be fetched such a long part description. . Actually how we store this description into the above table is we split this into two separate row and store in two different rows. My task is this need to fetched into a single column while processing in the above function.
For example if the part has a description of length more 70 characters then we will store the first 65 characters into the first row with the sequence as "1" and the rest of characters will be stored into the second with the sequence of 2.What I try to accomplish is to combine both description row into one and send to the row variable.
The way I tried to get this done as below using a inline view facility.
(select ext1.description_text from extended_part_descriptions ext1
where ext1.part_number = '1') long_desc
then I tried to fetch the description once the lopping is performed as below.
IF part_history.long_part_number IS NULL THEN
pmrow.part_number := part_history.long_part_number;
--pmrow.part_description := get_long_part_desc(part_history.long_part_number);
pmrow.part_description := part_history.long_desc;
pmrow.part_type := part_history.part_type;
But got the error
To cut the long store into short. Two rows of a table of similar type to be joined to gather into one where I'm struck into.
To be specific If I have the table data
Table _ Extended_Descriptions.
PART DESCRIPTION_SEQUENCE DESCRIPTIONS
K200 1 CHECKOUTTHEFLAGBEFOREYOUPROCEED
K200 2 WELLTESGINGALWAYSMAKESPERFECTION
We need to join as
MY FUNCTION IS BELOW. sorry for the commented line and they are the tries I gave.
FUNCTION get_part_amendment_history( in_start_date DATE, in_end_date DATE ) RETURN ie92_tab pipelined AS pmrow ie92_row := ie92_row(); start_date DATE; end_date date; desc1 varchar2(65); desc2 varchar2(65); long_desc varchar2(160); partmaster_block exception; BEGIN
start_date := nvl (in_start_date, trunc (CURRENT_DATE) - 1); end_date := nvl (in_end_date, trunc (CURRENT_DATE));
for part_history in ( select distinct pm.long_part_number, --ext1.DESCRIPTION_TEXT as long_desc, --ex.description_text long_desc, -- ( SELECT description_text FROM -- extended_part_descriptions epd WHERE-- epd.description_sequence =1-- AND epd.description_sequence =2) d, CASE pm.part_type WHEN '0' THEN 'C' ELSE 'R' END part_type, pm.stock_group, pm.unit_of_issue, CASE pm.withdrawn_part WHEN 'Y' THEN 'N' ELSE 'Y' end withdrawn_part from part_master pm left join part_number_amendment_history pnah on pm.part_number = pnah.part_number- join extended_part_descriptions ext1-- on pm.part_number = ext1.part_number and -- ext1.description_sequence = 1 and -- ext1.description_sequence = 2-- (select ext1.description_text from extended_part_descriptions ext1-- where ext1.part_number = '1') long_desc-- left join extended_part_descriptions ex-- on pm.part_number = ex.part_number-- -- where ex.description_sequence =1 and-- --=1 and ex.description_sequence =2-- LEFT JOIN extended_part_descriptions ex-- on pm.part_number = ex.part_number --where-- ( ext1.description_sequence =1 and-- -- ext1.description_sequence =2) where pnah.history_date >= trunc (start_date) AND pnah.history_date < trunc (end_date) AND pnah.vduwka_field_name IN ('STMI02', 'STMI04', 'STMI08', 'STMI17', 'STMI05')) LOOP IF part_history.long_part_number IS NULL THEN RAISE partmaster_block; end if;-- select description_text into desc1 from extended_part_descriptions ext where ext.part_number = part_history.long_part_number and -- ext.description_sequence =1 ;-- select description_text into desc2 from extended_part_descriptions ext1 where ext1.part_number = part_history.long_part_number and -- ext1.description_sequence =2 ;-- pmrow.part_number := part_history.long_part_number; --pmrow.part_description := get_long_part_desc(part_history.long_part_number); --pmrow.part_description := desc1 || desc2; --'PARTEST1'; --part_history.long_desc; pmrow.part_type := part_history.part_type; pmrow.stock_group := part_history.stock_group; pmrow.uom := part_history.unit_of_issue; pmrow.block_status := part_history.withdrawn_part;-- select description_text into desc1 from extended_part_descriptions ext where ext.part_number = pmrow.part_number and -- ext.description_sequence =1 ;-- select description_text into desc2 from extended_part_descriptions ext1 where ext1.part_number = pmrow.part_number and -- ext1.description_sequence =2 ; PIPE ROW(pmrow); END loop; RETURN; EXCEPTION WHEN partmaster_block THEN pmrow.part_number := NULL; pmrow.part_description := NULL; pmrow.part_type := NULL; pmrow.stock_group := NULL; pmrow.uom := NULL; pmrow.block_status := NULL; END;
hi folks, im new in toad, someone knows how can identify deadlocks in Toad for mac? apreciatte ur help
I was in the process of configuring ASM Filter Driver (ASMFD) and it was failing with following errors.
-bash-3.2# $ORACLE_HOME/bin/asmcmd afd_configureConnected to an idle instance.AFD-620: AFD is not supported on this operating system version: '2.6.32-100.26.2.el5'ASMCMD-9524: AFD configuration failed 'ERROR: afdroot install failed'
When I googled, could not find a single match for this error.
I recalled a similar issue with ACFS as per Bug 10185375. As per the Bug description, ACFS is not supported on Unbreakable Enterprise Kernel and they did not mention a workaround too.
To verify, I have checked my OS release -bash-3.2# uname -r2.6.32-100.26.2.el5
Yes, I am using UEK.
Now, me being an impatient person; was looking for an alternative option as I was too determined to explore ASMFD.
Here, what I did.
Rebooted my Linux, went to Grub Menu, booted the system with Red Hat Compatible Kernel.
-bash-3.2# uname -r2.6.18-238.el5
Guess what!! I was able to successfully install ASMFD with a Red Hat Compatible Kernal.
-bash-3.2# $ORACLE_HOME/bin/asmcmd afd_configureConnected to an idle instance.AFD-627: AFD distribution files found.AFD-636: Installing requested AFD software.AFD-637: Loading installed AFD drivers.AFD-9321: Creating udev for AFD.AFD-9323: Creating module dependencies - this may take some time.AFD-9154: Loading 'oracleafd.ko' driver.AFD-649: Verifying AFD devices.AFD-9156: Detecting control device '/dev/oracleafd/admin'.AFD-638: AFD installation correctness verified.Modifying resource dependencies - this may take some time.ASMCMD-9524: AFD configuration failed 'ERROR: OHASD start failed'
I have ignored the error ASMCMD-9524 as it was not impacting the configuration.
-bash-3.2# $ORACLE_HOME/bin/asmcmd afd_stateConnected to an idle instance.ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'DEFAULT' on host 'labserver.home.com'
Now, I shall log a SR with Oracle for this ASMFD error.
Hallo alle Zusammen,
ist es möglich über PLSQL im Toad (for Oracle Professional V. 188.8.131.52 ohne Addons)...
eine SQL auszuführen (Cursor?!?) -> ermitteln wie lange diese SQL zur Ausführung benötigt hat und das Ergebnis auszugeben (DBSM?!?) -> SQL nach einer definierten Zeit wieder auszuführen (zB.: alle 5sec)
How can I copy my Oracle Toad connections from Toad 10 to Toad 12?
I tried to paste the User Files folder from toad 10 to toad 12 , but no luck :(
Currently, I am building up a transient type in Oracle (184.108.40.206 and 220.127.116.11) PL/SQL to encapsulate access of views in a table producing function. However, I encounter something I don't understand in the results of the ANYTYPE.GetAttrElemInfo. According to the documentation, it returns "the typecode of the attribute or collection element". These typecodes are listed in package DBMS_TYPES. The ony numeric type defined in here is the NUMERIC type (with integer value 2), no subtypes are present here.
However, when I call the method on an object attribute that is defined as an subtype (like float or decimal), I will get a number that is not present in DBMS_TYPES. Does anyone has any pointers to documentation describing this behaviour? Is this a known issue?
I have an example script that shows / reproduces this behaviour: First a type object is defined with a number, float and decimal attribute. The anonymous PLSQL block will display the results of the anytype.GetAttrElemInfo call on the three attributes.
CREATE OR REPLACE TYPE TESTTYPE AS OBJECT( ID NUMBER(38), ID2 FLOAT(126), id3 DECIMAL);/DECLARE adata ANYDATA; atype ANYTYPE; tt TESTTYPE; v_typenum PLS_INTEGER; v_typecode PLS_INTEGER; v_precision PLS_INTEGER; v_scale PLS_INTEGER; v_length PLS_INTEGER; v_csid PLS_INTEGER; v_csfrm PLS_INTEGER; v_attr_elt_type ANYTYPE; v_aname VARCHAR2(30);BEGIN tt := TESTTYPE( 1, 123, 10); adata := anydata.convertObject( tt); v_typenum := adata.gettype( atype); FOR i IN 1 .. 3 LOOP v_typecode := atype.GetAttrElemInfo( i, v_precision, v_scale, v_LENGTH, v_csid, v_csfrm, v_attr_elt_type, v_aname); DBMS_OUTPUT.put_line( 'data of attribute ' || i || ' is:' || v_typecode); END LOOP;END;/
Hopefully anyone can help me.
We're hoping you're coming to Oracle Open World that started on Sunday and goes through Thursday. I got a last minute ticket and would love to hear from you and meet you in person and hear what you think about Toad World . Come on by the Dell Booth #1401 in Moscone Center South Exhibition Hall or the Toad Customer Party on Tuesday, September 30th at Lucky Strike in San Francisco. See you there. -Chris
How to get results out of stored procedure returning Implicit Statement Results (Oracle 12c feature). Procedure executed from Sql Plus returns results, but same procedure executed from TOAD showing message as "PL/SQL procedure successfully completed." without any results.
Please refer this link for example : http://www.toadworld.com/platforms/oracle/b/weblog/archive/2013/10/25/oracle-12c-dbms-sql-return-result-procedure.aspx
I am frustrated at how Toad displays columns after I click on a column header to initiate a different sort. I first set up Toad to only display certain columns. There may be 80+ columnds I need to look through to pick the ones that are meaningful to my work. I get all that set, then initiate a resort by clicking on a column header, ALL 80+ columns are displayed.
Is there a way to select X number of columns and only display those X columns and then sort by any column desired (within the X columns) ?
Is there a way to see the history of session blocks caused by table locking? I can view current blocking sessions through the session browser, but would like to see a history if its possible. Thanks!
I am using Toad for Oracle Base 18.104.22.168.
I wish to display only certain columns from table xyz. I display table xzy, click on the column select button in the upper left area, un-select all, all become un-selected. I choose only the columns I wish to see, and then click outside the column select area. The table is re-displayed, and only the columns I wish to view are displayed.
I then wish to sort on column abc, and click on that column, select ascending or descending, and the table is redisplayed, sorted on that column, with ALL columns displayed.
It seems there is NO means to "permanently save" the selection of columns one wishes to see.
Just blink, and it all changes.
Is there a way to truly select ONLY certain columns for display ?
can we pass %row count as a parameter in cursor?
Very new to this forum. I've recently installed the above referenced software, but no clue how to use it. I use SQL daily, and would like to utilize Oracle functionality (specifically the TEXT features and FUZZY searching). I would then use TOAD for Oracle and SQL syntax to query the data.
Is there a link or post somewhere that shows me how to: a) load data; b) create necessary indexes; c) Link TOAD to db; d) Begin querying.