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!
I'm trying to create an error log table that displays package name, function name, error message, and a timestamp.
EXCEPTION WHEN OTHERS THEN ERROR_MSG := SUBSTR(SQLERRM, 1, 100); INSERT INTO ERROR_LOG (PACKAGE_NAME, FUNCTION_NAME, ERROR_MESSAGE, ERROR_TIMESTAMP, USER_ID) VALUES ('USER_PKG', 'INSERT_FUNCT', ERROR_MSG, CURRENT_TIMESTAMP);
It's saying ERROR_MSG must be declared by I'm not sure how to do that
As we know ,we can create a database by method DBCA,OMF and MANUALLY, I want to know there is any another method to create a database and also I want to know what is advantage
HiI'm trying to use debug option with a procedure. But when I press execute, it shows this message "UNABLE TO FIND RAC CONNECTION INFORMATION FOR RACCELR (INST_ID=2)"I found some information and it says that Oracle doesn't support debugging accros RAC instancesWe have a RAC instances and It works without any problem. What can I do to sort it out? This is the tnsnames that i'm usingRACCELR.CLINICAROSARIO.COM.CO = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = raccelr) ) )Thank you so much por your help
Anyone have any idea what the code looks like for an amortization schedule in pl/sql? Does SQL Navigator even have a PMT function similar to excel? I like to think it would but cant seem to find it.
I am using Toad for Oracle Base 188.8.131.52.
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 ?
I have an XML document that I would like to import into a XMLType column in an Oracle table.
Is there a standard import utility to perform this function in TOAD? If not, how is this achievable?
Thanks in advance
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. 184.108.40.206 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)
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;
Currently, I am building up a transient type in Oracle (220.127.116.11 and 18.104.22.168) 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
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!