To save the breakpoints, you just need to click on the "Export" button in breakpoint window. You can use the saved XML file for the future and you can pass the same to other developers.
You can also save breakpoints based on the search on labels. Let's have a quick look inside the content of the XML File. Each breakpoints tag contains information about the particular breakpoint like line number , is enabled , etc.
If you delete all the breakpoints from your code at any time, you can easily import them by just clicking on the " Import " breakpoints button.
This will restore all of your saved breakpoints. Breakpoint Import depends on the line number where you have set your breakpoint earlier.
If your line number changed, breakpoint will set on the previous line number only, so you will get breakpoints on unexpected lines.
Breakpoint Hit Count is used to keep track of how many times the debugger has paused at some particular breakpoint. You also have some option like to choose when the debugger will stop.
By default, it's set to always. So, whenever the breakpoints hits, hit count will increase automatically.
Now we can set some condition as earlier mentioned. So based on that condition, breakpoint will hit and counter will be increased.
If you want your code to break when hit count is a multiple of 2, you need to select the third option from the dropdown list. After that, your code line should break only when the hit count is 2,4,6, Till now, I hope you are very much clear about breakpoints, labeling, hit count, etc.
Now have a look at what is "Breakpoint When Hit" option. This is apart from your normal debug steps, you want to do something else while breakpoint is hit, like print a message or run some macros.
For those type of scenarios, you may go for this option. You can open this window by just right clicking on breakpoint. The first thing that you need to notice is the symbol of breakpoint.
Breakpoint symbol has changed to a diamond and you can also check out the tool tip message which indicates what it is going to do when execution reaches here.
You can restrict the breakpoint to hit for certain processes or threads. This is extremely helpful while you are dealing with multithreading program.
To open the filter window, you need to right click on the breakpoint and select "Filter". I have described it in detailed uses in Multithreading debugging section.
Data tip is kind of an advanced tool tip message which is used to inspect the objects or variable during the debugging of the application.
When debugger hits the breakpoint, if you mouse over to any of the objects or variables, you can see their current values. Even you can get the details of some complex object like dataset , datatable , etc.
While debugging in Visual Studio, we generally used mouse over on the object or variable to inspect the current value.
This shows the current data items held by the inspected object. But this is for a limited time, as long as the mouse is pointed to that object those value will be available.
But in Visual Studio Beta 2, there is a great feature to pin and unpin this inspected value. We can pin as many of any object and their sub object value also.
Please have a look into the below picture:. When you mouse over on the inspect object, you will get pin icon with each and every object's properties, variable.
Click on that pin icon to make it pinned. Unless you manually close these pinned items, they will be visible in the IDE. This is quite helpful when you need to see some object value list in the bottom section of code.
You can easily drag those pinned data tips over there. You can add comments on the pinned data tip.
This will brings up an additional textbox to add comments. This is another great feature of Visual Studio debugging. If you pinned some data tip during the debugging, the value of pinned item will remain stored in a session.
In normal mode of coding, if you mouse over the pin icon, it will show the details of the last debugging session value as shown in the the below picture:.
Like Breakpoints, you can import and export pinned data tips values in an XML file. These saved data tips can be imported during any point of time for further debugging.
The XML file looks like below:. DataTips is also used to changed the value while debugging. This means it can be used like a watch window.
But if you want to delete the pinned data tips from a particular file, then you need to open that particular file, there you have the second option to select.
You can even delete particular pinned data tips by just right clicking on it and clicking "Clear". You can say it is an investigation window.
After breakpoint has been hit, the next thing you want to do is to investigate the current object and variables values.
When you mouse hover on the variable, it shows the information as a data tip which you can expand, pin, import which I have already explained.
There are various types of watch windows like Autos, Local, etc. Let's have a look into their details.
It automatically displays the list of variables within the scope of current methods. If your debugger currently hits a particular breakpoint and if you open the "Autos" window, it will show you the current scope object variable along with the value.
You can also use Drag and Drop to add variables in watch windows. If you want to delete any variable from watch window, just right click on that variable and select "Delete Watch".
From the debug window, you can also edit the variable value at run time. This is very much helpful when you want to monitor any object at any point of time even if it goes out of scope.
To create Object Id, from watch window you need to right click on a particular object variable and then need to click on "Make Object ID".
After adding Object Id with a particular object variable, Visual Studio adds a numeric number with " " with that object to indicate that one Object ID has been created.
Immediate window is very much common and a favorite with all developers. It's very much helpful in debug mode of the application if you want to change the variable values or execute some statement without impacting your current debugging steps.
It also supports Intellisense. During Debug mode, you can execute any command or execute any code statement from here.
These are very much common features for all the developers, so I am not going into details of each and every command of Immediate window.
In Call Stack window if you clicked on any of the rows, it will point you to the actual code of line of Visual Studio Code Editor. You can also customize the call stack row view by selecting different types of columns.
To customize, Right Click on the "Call Stack" window, and from the context menu, you can select or deselect the option.
Call stack is very much important when you have multiple methods call all across the application and one particular method throwing an exception on some particular case.
At that time, you can use call stack to see from where this method is getting invoked, based on that you can fix the defect. As of now, what I have discussed is all about fundamentals of debugging, knowing debugging tools and their uses.
Now let's have a look into the multithreaded scenarios. Here you will see how to work with multithreaded program debugging, where is your current thread, what is the thread execution sequence, what is the state of thread.
Before continuing with the demo, let's consider you have the following piece of code which you want to debug. In the sample code, you have three different threads - Main Thread, Thread 1, Thread 2.
I have given a thread name to make you understand better. Now set a breakpoint inside "Go " and run the application. Threads window will appeared on the screen.
At the start, execution pauses at "Main Thread". Category column indicates the category of threads, like main thread or worker thread.
Now to explore the next step by just pressing "F5" and see what are the changes in thread window. So after pressing F5, it jumped to the next step to thread 1.
Similarly the next step will move you to thread 2. From the Thread window, you can understand how easy it is to monitor your threads using this debugger tool.
There is another great feature available within the thread window. For example, if you expand the location for "Main Thread" , it will look like the diagram given below: The sample code which I have explained for the thread debugging is very simple.
What will happen if you have a huge code block with multiple number of threads. Then it will be very difficult for you to identify which thread is part of your code or which ones are not related.
Thread window gives you very easy features to set the " Flag " for all the threads which are part of your code. For that, you need to just flag your thread by option " Flag Just My Code ".
While discussing about breakpoint filter in breakpoint section, I said that breakpoint filter is very much helpful for Multithreaded debugging mode.
Now this is the time to explore it. In our current example, we have three threads Main Thread, Thread1 and Thread 2.
Now what if you want breakpoint to hit only for "Thread 2". What will you do? Here is the use of breakpoint filter. Right click on the breakpoint, select " Filter " from the context menu.
Now in breakpoint filter window, you need to fill the filter criteria. After setting the breakpoint filter, run the application and open the "Threads" window.
You will find your program execution has only paused during the execution of "Thread 2". This is all about the debugging with multithreaded application.
Hope you have learned something from it. Let's start with another most important topic "Parallel Debugging".
This is another great feature added to Visual Studio to debug parallel program. Parallel programming is the new feature coming with. If you want to learn more about parallel programming, please check here.
Now Debugging the parallel program is also a big topic. Here I will give you a basic overview to know about the debugging of parallel program. To discuss about it, let's consider you have the following piece of code:.
To understand the parallel program debugging, we need to be aware about two window options: Before continuing with parallel tasks and parallel stacks, you have to know about Threads Window which I have already covered.
In the given code, you have three different tasks which are doing something and after sometime, all the tasks are put on hold.
This is done intentionally to check the status of each task. To test, put a breakpoint on DoSomeWork method and run the application.
You will see your program execution paused on the breakpoint. I asked to open both at the same time only because you can visualize what is going on.
Parallel Task window will show you what are the different tasks that have been created for the program and what is their current status.
On the other hand, Parallel Stacks will show you the graphical view of all thread creation, containing tasks, how they are related. If you click on the thread from the Parallel Stacks, it will show you the code line related with the thread as shown in the picture with a Green Arrow.
To move ahead, press F5. Let's see what comes next. In the above diagram, you can find one of the tasks has been executed and the other two are remaining.
These topics is very interesting and long to discuss. Please check further study section of the article to know more details.
IntelliTrace Debugging is sometimes called as historical Debugging. IntelliTrace operates in the background, records what you are doing during debugging.
When you want the information of previous event or some particular event, you can easily get it from intelliTrace information, a past state of your application.
In this mode, you can navigate to various events, steps that are recorded. In this section, I will give you a basic overview of how to use IntelliTrace.
Here I am using one sample program by which I will show you what IntelliTrace does. Below is the sample code block:. Did you find any relationship with your code and with the IntelliTracker List view?
I guess you should. If not, let me explain about it. Let's have a look into the below picture:. From the picture, I am sure you have got the idea what IntelliTrace is doing.
If you call a method, it will capture. If code fired an event, it will trap. Yes at a single statement, I will capture each and everything.
If you can get the call stack, Local variables information are recorded. When you will select any block, it will expand automatically and that line will be highlighted.
As per the given picture, I have highlighted the first console section and you can see the corresponding code block has also been highlight.
You can now easily map them. I have already explained Locals and Stack Calls. But can you imagine what is the use of Locals and Call Stack over here.
Nested Tables and ODP. NET May 05, - 2: I'd like to perform SQL on a collection being passed into my package procedure as a parameter, so I made it a schema-level nested table type and cast it as you recommended in your initial response.
Is there any other way to accomplish what I want to do short of looping through the passed in associative array to populate my nested table?
NET May 05, - 3: NET at this time. I show a similar technique in Chapter 5 of my book - i. As Chris points out, it can be a bit of extra work.
Thanks Mark May 10, - 3: Thanks for the reply, but that was me on OTN to whom Chris replied. My post on OTN asked if anyone had any alternatives to looping through the Associative Array to populate a nested table.
I was just hoping that he was giving me one or that someone else found one. OTN Post May 10, - Hi Yuan, I had figured it was your post on the OTN forums, just wasn't sure if you had seen it yet or not.
Object support is probably one of the most requested features for ODP right now. Gabriel from Montreal, Canada. Hello Tom, The in list saved me a lot of time but I was wondering why not use reference cursors instead on in varchar2 parameters like so: Extension to array matching May 13, - 6: This link has been really helpful.
I have used some of things here to compare comma-delimited lists of strings, but I also need to compare wildcards within these strings.
June 14, - 4: Andy from Hyderabad, India. But any way here it goes I thought this might work But looking for a better solution if possible June 22, - 1: Matthias Rogel from Kaiserslautern, Germany.
I absolutely agree that "order siblings by" would "obviously" be considered "order by". June 24, - 7: I misunderstood the documenation?
Oracle returns the rows in the order shown in Figure In the diagram, children appear below their parents. For an explanation of hierarchical trees, see Figure , "Hierarchical Tree".
I would say, since we don't have siblings here, and children appear below their parents, and we select only level, we'll get the numbers 1 2 3 4 5 Just "parent, followed by child, in some order" June 24, - 8: List item July 10, - 6: John Binny from Middle East.
Hi Tom How r U? I am working in Oracle JDeveloper 10g. I want to change an item into List Item. Jeremy from Lansing, MI. Hey tom, just was looking over your str2tbl and i have two questions Would you see any benifit to declaring this function deterministic?
I know that this allows you to declare function-based indexes on it and use it in MV's, but could it also affect the plans generated by avoiding repeat runs of the function in the same statement, transaction, or session?
This is my REAL question Or even better - can I get the optomizer to look into the table and choose an appropriate plan based on it's contents?
Oops, now I'm the dolt who forgets to do a search before submitting a question. Thanks for the gracious link instead of just saying "RTFM" I'm really scratching my head on this one.
Secondly, from your example I'm almost dead positive that the reason STR2TBL was called times wasn't because it wasn't materialized, but because it was the outer rowsource in a nested loop join.
If you had a full table scan as the outer table then that scan would happen times too, wouldn't it?
Were you on 9i when you did that example? In fact it seems to make things worse. Appears to me that 10g is doing the opposite of your previous example!
When I get a chance I'll check out 9i, but I'm betting that the "VIEW" collection materialization isn't affecting how many times the procedure gets run so much as the plan -- maybe by materializing the subquery you made the CBO suddenly listen to the hint you have it and put the function on the inside of the nested loop.
Explode a column July 21, - Suppose I have a table like create table t pk int primary key, csv varchar2 ; insert into t values 1,'a,b,c' ; insert into t values 2,'x,y,z' ; Using the techniques described on this page, is there a way I can create a view on this table that "normalizes" the table i.
So if I have this view v, I would do select Explode a column July 21, - 5: Not sure I understand why you changed strtbl to be a pipelined function.
Can this be done without using pipelined functions? Explode a column July 21, - 8: Well, your original version of str2tbl presented on this site even for 9iR2 didnt have the "pipelined" and you suddenly added it in response to my question so I was wondering if it was "required" for things to work.
Would this work without the pipelined? Hi Tom, table t1 contains records for 'a' and 'b' in column col1 I have a IN string with values 'a','b','c','d' I would like to find which values contained in the IN clause are NOT present in the table column col1 e.
Try this July 25, - 5: Obviously it assumes comma delimited data. Connected to Oracle9i Enterprise Edition Release 9. I want to force an outline for query with query-1 is it possible?
My query is more complex like select a. September 15, - The Optimizer might be right With query-1 i see NL's. But one thing that is for sure is when i have a big variable inlist the optimizer takes a different plan.
September 15, - 1: September 15, - 3: Tom, Not sure whether you have seen this update: Faster version September 15, - 4: Ajay from Boston, MA.
For larger lists, the following statement is faster select substr: Ajay September 15, - 5: Ajay, you don't need the distinct. Just don't produce anymore rows than you need: September 15, - 7: And thanks for the compliment, dnd.
September 16, - 1: Hi Tom, Your answer is good. Faster version September 16, - 9: You have a query from dual returning rows that did physical reads and 5 gets in current mode.
September 16, - Gabe, I'm not sure I understand what you mean by isolating the tests. To get a clean trace file, these two statements and setting the value of mystring were the only statements run in the session.
Gabe, I've taken suggestions for you, Ajay and Anders and applied them to my original version. Its much cleaner and seems to perform well.
Sorry I'm being a little dense. The input was 'x,y,z' The String y z Missing the 'x' row. September 16, - 3: Event waited on Times Max.
My explanation is that one of the inline view gets materialized and goes to disk. Missed 1 in the connect by clause September 16, - 3: Frank Zhou from Braintree, MA.
September 16, - 4: Is there a predictable threshold for when the materialization would start happening?
Can it be influenced? I have written a pipe lined function to perform a variable in list that is essentially the same as what is listed in this thread.
However, when I use my inlist function instead of hardcoding a list of values, I am having trouble getting the sql to range scan an index instead of fast full scanning the index or simply performing a tablescan.
I have put together a small test case that appears below. I'll have a hard time getting developers to use bind variables for in-lists if I can't improve the performance of this method using bind variables.
December 22, - 9: Tom you are the man! When I applied this to a true application scenario for demonstration purposes, my version ran nearly 8X faster.
You just have to love those bind variables. It will be something like: Is it possible to execute this query in chunks of characters and store the results in a temp table or something similar?
But how do I define the chunks? The query field in table t can be characters! Kindly suggest an efficient way to do this. Intersection of queries March 23, - 6: Tom, Thanks for the reply.
This is for an 'Advanced Search' application being developed. User selects some parameters on screen and the resultant sql-query is passed to stored procedure to be stored in the database.
Then the same user selects another criteria and this also gets stored So they want all these queries to be stored and intersection of all these queries to be done by database procedures and a count returnd.
We're not inserting records, but only searching i. The inserts I wrote were to show sample data in the table and just for the purpose so that you could replicate the scenario easily please.
Actually, the users want those queries to be inserted in the table by the stored procedure. The application users opine that this is a very dynamic meathod -- since you're storing queries, so the count done at any time will be at that point of time.
Earlier we were thinking of storing the resultant seqnos as comma delimited CLOB instead of the queries -- but that didn't work out because the search on millions of seqnos as a CLOB was very slow, even after using context index They want two procedures like the following: ID is just a sequence number and is PK of the table t.
It is kind of a basic filter. They want me to concatenate the advanced search query to this sqlfilter as following: This database table will be used by Crystal Reports for reporting purposes.
Kindly advise how can I cater to the user requirements. March 24, - Hi Tom, I have the following requirement. I have a table t with structure as follows: ID 1 2 Now if you go back to my insert statements in the last one, I have assigned the value of col3 as 'Value for col1'.
In my current setup that is not possible because I am comparing pcol1 with col1, pcol2 with col2 and pcol3 with col3. Is there any way that I can change this a little bit such that I will only get one input parameter pcol and I should compare it against col1, col2 and col3 and return all matches so that when I execute my procedure I should 1, 2 and 5 as the results rather than just 1 and 2.
March 24, - 4: I am sorry, I did not follow you. Are you asking me a question? To be sure April 10, - Carl Bruneau from Montreal, Qc, Canada.
Hello Thomas, I read this thread but there is some things I am not sure to understand. Because if we don't use this approach we will have as many statement in the shared pool as we have possible combinations of element in the INLIST, rigth?
And it will not scale at all, rigth? One hard parse April 11, - 9: Hello Thomas, I finally verified and the answer seems to be Yes.
I traced many execution of the same generic statement: The other execution not: May 04, - 8: Recommendation to Oracle May 04, - RV from Every where.
The table variable in Sybase from very early version and carried to MS Sql Server is very handy for the database programmers.
These functionality is available in Oracle in a complex way as explained in this article from Oracle 8 onwards; still it is complex.
One more trick May 05, - 6: Matthias Rogel from Kaiserslautern Germany. Tom from Philadelphia, PA. Hi Tom, Many thanks for all the excellent examples and great solutions.
The approach works fine except when the IN list exceeds characters. The list is truncated. As we can see the context holds the correct value ie, greater than characters we assume that there is actually no limitation on the attribute value other than it must be a VARCHAR2.
Would you be able to explain why the value is truncated when the SQL is executed? We have to use dynamic sql as there are 17 fields which the user can search on or any combination of those 17 fields.
When the code is run the session context attribute value is the full string but when the sql is executed the value is truncated at characters.
Thank you for your assistance. June 01, - Steve Standish from Boston, MA. Hi Tom, Hopefully this is a simple mistake or mis-use: Steve from Boston, MA.
Hi Tom, thanks for your time. I provided the simplest example of the problem. Here I get the same problem?
Function created with compilation errors. OK, thanks, I get it now! Although this original expample is simple my intent was to encapsulate the types into the package not declare stand alone so I could make my package types like: This way I wouldn't have alot of standalone "types" sitting around in SQL.
If there is a way to do something like this then great Just to clarify June 01, - 1: Sorry, I think my statment was not clear: This is simply a code management approach.
This method allows for calling components to change versions when ready. Oracle won't understand a type definition in a package?
Good Stuff June 01, - 3: It works and gives me what I want see below , thanks. This type of thing will be incorporated into larger quieries.
Any words of wisdom on performance when using this type of function as part of a larger SQL? For COST there would be no stats on a returning function?
In our simple example here could this be a deterministic function? Excuse me as I have posted the same Qs in different loactions,but this the proper place to post June 02, - Is it unable to understand the lists in the "in" clause?
It was actually a Form 6i Trigger code for the control item: August 04, - 1: September 14, - Check for existance of Object before proceeding September 21, - If the table is not there for the 2nd value, then i just waste time for the 1st processing.
I want to check if objects exists for all values of select clause and if they are, then only proceed to the next. September 22, - 9: Apologize for the confusion.
Once it finds the table, then it proceeds ahead for doing further validations and insertions. What if it does not find the table during the 2nd loop.
It would do all the processing, validations for the 1st value in the loop and then proceed for the 2nd value in the loop and then generate exceptions.
September 22, - 5: Well it hurts much more because this table is a from a customized application that our client has and unfortunately it is going to remain the same in the near future.
So partitioning is ruled out. Supposing the distinct values for years are 03,04 and If yes, then proceed with the backups, if no then raise the flag.
This is the snipet of the code. September 25, - Here is the entire code. This code works and creates the copy of the table one by one for each value of loop j.
So where do i put in this exception logic. Confused September 28, - 5: Shankar Ram from Austin. Hi, I'm learning this new concept of pipelined functions.
I have a scenario. STEP 1 - select query is generated and a list of addresses are resulted. STEP 2 pipelined function is created and stores the value of this result.
STEP 3 Stored addresses are checked and status columns is updated for each address. How do I reset the pipelined functions. Is this scenario requires pipelined functions?
Do you have any other suggestion on this. November 03, - 6: A reader from India. This actually worked for me and so I am really curious.
Please bear with me as I am a bit of a newbie. Raj from bristol, USA. I don't know what is going on wrong.. But it is not happening.
Is something which I am missing. For one value it is working properly. When I am doing concatenation it is inserting zero rows.
Environment is Oracle 9i 9. Hi Tom, Thanks for your recommendation. Reply would be appreciated. Using collection in SQL January 30, - 2: Sunj from Plainfield, NJ.
Tom, My question is related, but I couldn't find my specific answer after searching through this and related threads. Why is the difference in performance, eventhough the result is same?
Using collection in SQL February 01, - Tom, Thanks for your insight. I compared the PLANs, there's big difference in cardinality. Another February 02, - 8: Venkat from Pittsburgh PA.
Sunj, There is also one more way in Oracle you can achieve this.. I dont know how would it suit your requirement but if you have 10G Using collection in SQL February 06, - 2: Tom, Thank you for interpreting the plan for me in detail.
Much appreciate your time. Can I use hierarchichal query for this? May 08, - Number of characters in S1 and S2 are always the same for particular record.
I'm trying to build query that returns this: I was able to implement it easily with pipe function that parses every input record and returns result records.
But I'd like to have it done by this elegant hierarchichal query solution using level etc. The reason is trying to stay in SQL - while processing couple of millions of records like this and real structure is more complicated performance is not acceptable: I tried, but failed.
May 11, - But unfortunately this level can be as deep as which actually increased processing time in comparison with pipe function: Heartfelt thanks and a pointer for others June 26, - 6: Stew Ashton from Paris, France.
Tom, I have used your "varying in list" solutions to great effect, but I had big problems with a complex query: Used together, they get me excellent plans and response times.
I felt I had to thank you for once again turning a frustrating problem into a finger-snapping solution. I'm adding a pointer to one of your followups in case it helps others find it faster than I did: Hi Tom, Please find the following query.
If I replace the inside query with single value 'a' then it works. Please help me to solve this problem.
In List variabe August 07, - 2: Dee from San Francisco, CA. Thank you for having this great site! My question involves passing a string of numbers to a select statement.
I have a stored procedure that concatenates a list of numbers using a comma to separate the values. I tried using ltrim and rtrim to remove the quotes from the string but so far I am unsuccessful.
Any suggestions is greatly appreciated as I am somewhat of a novice. I will continue to read this thread for further code suggestions that I can use.
How can I do a variable "in list August 16, - Hello Tom, Thank you for the great detailed solution on your Blog site: Varying in Lists - I always learn something new by implementing your suggested code!
I successfully applied the 8i solution to my Oracle package; I can use SQL to view the 9iR2 solution but received errors regarding not all variables being bound when I incorporated it into my package Question regarding performance on both these solutions In comparison to just an IN sql statement, they seem to run a little slower.
Is there anything further that can be done about performance? August 22, - 9: Is there some max length? August 23, - I am passing this string which contains 41 values but view returns me 27 values ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, August 24, - 1: Error in Cursor September 06, - SQL Statement ignored Line: Solved September 10, - And it works fine.
Hi Tom, I need to run an instr function against an array. How do I do that? I need to use the array as I have to check the same values against another col too.
Thanks in advance Sara. October 30, - Ashish from Boston, MA. Hi Tom, Do you know the limit of in clause in Oracle 10G or in other words how many items we can have in the in list Thanx.
November 05, - 3: Hi Tom, This is my proc. I cannot pass the values to the variable as '1','2','3','4'..
One Clarification January 02, - 3: Hi Tom, If the max no. Clarification January 03, - 2: Hi Tom, Sorry for not being clear.
What I meant was if I use: Val15 This is OK but if I change it to: Val15 can be null. Last Clarification January 05, - 2: Hi Tom, Can you please explain with example on how to do that?
Non-equivalence January 07, - 8: Clarification January 11, - I have tested this and it works fine. If i click on 'Preview Preview' and then click on 'Cancel', a login page appears.
Is this intended behavious? I think the control should go back to question. This may help January 14, - 1: Steve from Sacramento, CA.
I have used a technique in the past using regular expressions and a hierarchical query trick to turn a string separated by some character into a result set.
I use this when I want to open a result set from a stored procedure and the user selects several items from a list that I want to filter the result set on.
I am sure it has it's issues but it seems to work in a lot of situations. Regular SQL January 16, - 9: Michael Logan from Richmond, Virginia.
I have created everything just as you have. I'm using TOAD for development. However the line below in a package body procedure, doesn't work. However if I run just what's below in SQL it works just fine.
I'm using Oracle 9i. Regular SQL January 16, - 1: My procedure had parameters with Date types. This is how I use it: Here is your function: Hi Tom, Please do not mind for asking a simple question.
I know we can use select min val from select var1 val from dual union all select var1 val from dual But is there any other and more efficient method?
March 25, - How to use a cursor with this? Can you explain why Oracle can call pipeline function few time?
Really useful stuff as ever, but I am intrigued as to why I cannot use the 'implicit' sql types when defined in pkg as per the 'foo' example.
April 24, - 8: I was interested in your response earlier: Hi Tom, in 8i, is it possible to have a dynamic cur like the following? On Oracle 9i via T.
Another Variable IN List question? May 09, - I do not want to use so many OR's. I have a requirement to check if any of these columns [there are 5 of them currently] exists in that list?
Thanks for your valuable time. Hi Tom, I have a problem with connect by level clause. This is not the real problem but a simplified version of problem.
Here is the setup. Question July 16, - 5: Dear Tom, iam facing some problem in the Oracle Dtabase 11g while i issue the str2tbl function inside the select query.
The same function was worked in our 9i database. Nearly impossible to search for the THE keyword November 06, - 4: Mark Brady from Baltimore, MD.
Every search eliminates it until you quote it and then you're just sad. Can you link the documentation of THE or tell me what it's called?
November 14, - 8: Hi Tom, I think, this is best query to deal such issue but only applicable above oracle 9i version. I was successful, but I do not know how to get the optimizer to use an index.
Here's an example using the Emp table. Oracle Database 10g Enterprise Edition Release December 12, - 9: January 05, - 4: Nicole from Chicago, IL.
Hi Tom, regarding the original question, I also needed something like this and I tried the following: January 05, - 6: I need to insert in a table tb1 for each record meeting a certain criteria from a second table tb2 multiple records for different periods of time, something like: Thanks for your help..
January 06, - 3: Multiple column inlists April 03, - 5: C from Darlington, UK. I need to delete data from a number of tables based on values obtained from a different table.
I have provided a simplified version of where I'm up to below. As it's a large table I don't want to have to access it mutliple times. I have tried creating a type as a table of objects but it fails when I try to bulk collect into it.
Is it possible to do this or woud I be better using a gtt? Procedure created with compilation errors. Multiple column inlists April 03, - Hi Tom, This is a very useful page.
Can you please explain. Also, how to avoid full table scan in this scenario, with a small number of items in the in list?
Java beats the function technique July 15, - Here is another approach using Java. Say if the number of items in the in list is Then what we do is divide the big list into 10 batches, for each batch prepare a PreparedStatement with the sql like this: Then bind the 50 values onto the PreparedStatement, execute the query.
So, in this case we would execute the query 10 times for 10 batches with different in values. Store the results of each execution in to a collection and finally return the collection, once we are done with all the 10 batches.
I was under the impression that the function approach would be quite faster than the above mentioned java approach.
But surprisingly, the java approach beats the function approach by atleast 50 - ms. Java beats the function technique July 15, - 3: Ran the attached java code For 99 items in the list: Sorry, I don't have access to get tkprofs.
Please execuse me for this. I know this is insufficient information. For the query with the function: Here is the sample java code I have presented only the necessary code here Java beats the function technique July 15, - 4: Can you suggest the right way to compare these two?
Hi Tom, HOpe this doesn't classify as a new question. I have written a pipelined function that takes a delimited string and returns the strings themselves as rows.
Initially I did this using a SQL query but then changed it to your example above. Since you use APEX a lot, I thought I would ask you too, if you were aware of any problem that could arise from using a pipelined function this way?
Brief background as to why I'm doing the above. I'm using custom authentication using APEX user groups and users.
When a user logs in, I have an application item populated with all the groups they belong to and I have to check against this item to ensure that they are allowed functioanlity on the application.
I have tried the APEX forum but haven't had any response back yet. So thought I'd ask you. HOpe this is ok. Update for above question January 21, - 6: Have you come across the above exception?
Followup January 25, - 1: Hi tom, This is a followup to the query above. I was using this function as part of an exists condition within APEX.
It makes sense when you think about it I guess. I just never came across this exception before.