I have string variables and these variables differ in length. Let’s assume that I have the following strings: I want to convert my string variables to float. So, to do that, I call TO_NUMBER() function. The problem is when I call TO_NUMBER() function from my .NET web application instead of calling it di…
Tag: plsql
ORA-06502: PL/SQL: numeric or value error when concatenating
I am getting error as numeric or value error Answer The problem is with operator precedence: Oracle evaluates operators with equal precedence from left to right within an expression. … and as the table shows + and || have equal precedence. So in this statement: this is interpreted as ‘sum = ‘…
offset with parameter in PL/SQL procedure
I have a PL/SQL procedure. It executes a SQL statement and returns a json responce. I would like to limit the rows returned using the offset clause. eg: select * from wherever where something = …
Difference between Global temporary table (GTT) and collections
I wanted to know the difference between GTT and collections with scenarios where to use GTT and where to use collections. Answer Global Temporary Tables are permanent data structures. We can manipulate data in a GTT using SQL like any other table. What distinguishes them from regular heap tables is: Their dat…
Sort by status and effective date range
Before After Is there any way to make ‘Before’ to ‘After’? if a function or procedure is required, please write one for me. please help me drop table test; create table test (employee_code varchar2(8),…
PL/SQL No data found even there should be?
I’m currently learning PL/SQL atm and I have run into an issue with one of my homework questions. In the below code, I’m getting user input for a province and isolating select results using said province in the declaration of the cursor and trying to run the visitsandtotal procedure but all I̵…
Use v(‘APP_USER’) as default value for column in Oracle Apex
I am trying to use v(‘APP_USER’) as default value for a column. I get null when I use it in select like But when I use it as default in column, like below, I am getting error. Error Can anyone explain this or have a turnaround for this ?? Answer There are other options than V(‘APP_USER’…
PL/SQL procedure not compiling
I have a PL/SQL procedure that is not compiling. The errors are: Error(3,7): PLS-00103: Encountered the symbol “INTO” when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted deli…
Passing Multiple Values To PL/SQL Input
I have an Oracle package which contains a function. This function has 3 inputs. I need to pass multiple values to each input. I could automate a process which runs it multiple times with each combination of variables but I would like to only make one call to the database. Simplified Code Answer Pass it with q…
Is it possible to insert a table type collection within a table collection in PL/SQL
I have a requirement to send back a record type in a table collection which have 2 fields- Line_id Fulfillment_set_id A line_id is always unique but a line_id can have more than one Fulfillment_set_id. So I have created a record type Is the above collection structure possible or am I missing something because…