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!
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
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;
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 22.214.171.124.
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.
Can someone explain to me why "Rename Identifier" is greyed out in Toad, when I am trying to refactor my code?
I want to do just one rename for my whole code.
I prefer to use the oracle net services connection type, but I have to specify the location of the tnsnames.ora on my MAC each time I add a new database connection, is there a way to let toad know automatically where this file is located?
Mine is located in ./Applications/oracle/product/instaclient_64/126.96.36.199.0/network/admin
and my .bash_profile configures relevant environment variables as:
export ORACLE_HOME=$HOME/Applications/oracle/product/instantclient_64/188.8.131.52.0/export PATH=/usr/local/bin/athena:$ORACLE_HOME/bin:$PATHexport DYLD_LIBRARY_PATH=$ORACLE_HOME/lib
Oracle 9i Release (184.108.40.206.0) for Windows and TOAD for Oracle 220.127.116.11. I am aware of being able to run reports via database. Can anyone please tell me the steps to do that?
Thanks in advance.
I need to copy several ORACLE tables on schemas on different servers.
I open Schema Browser, right click on the table I want to copy and select Copy Data to Another Schema.
I select other Schema I'm connected to and press execute.
I get an error message "Error Message:ORA-00942: table or view does not exist"
NO KIDDING!!!! THAT'S WHY I'M COPYING THE TABLE!!!!!
Second option is to right click on the table in the Schema Browser
again, and select "Create in Another Schema".
I select other schema, select execute and get an error message:
"ORA-00942: table or view does not exist"
REALLY???????????????? OF COURSE IS DOESN'T EXIST, THAT'S WHY I'M COPYING IT!!!!!!!!!
I just to select a table from one schema drag-and-drop it to the other schema.
Is that possible?
In our production we have setup RAC
Can anyone Please share steps to test RAC settings.
My requirement is to create DB link between Oracle 8i and 11g.
as mentioned in interopertability matric connectivity between 8i to 11g is not supported, workaround is to create one intermidiate database of 10g .
is there any other workaround like can ODBC or JDBC (JDBC thin driver) connectivity is possible between 8i and 11g.
I have an sql stmt I need to modify to retain leading zeros and I'm not sure how to modify it. Here is the code:
DECLARE -- declare variables and cursors
startDt VARCHAR2(10) := '&&BeginDate';
stopDt VARCHAR2(10) := '&&EndDate';
mmy_schema varchar2(200) := '&&mmy_schema'
---------THIS poliycNo IS THE VARIABLE I WANT TO RETAIN LEADING ZEROS....HOW??? IT IS A VARCHAR2(10BYTE) data type in the schema.
The cursor is defined after this, with FETCH and loop stmts.