Procedural Language/Structured Query Language is Oracle Corporation's procedural language extension for SQL. Questions about PL/SQL should probably be tagged "oracle" as well.
4
votes
2answers
2k views
How do I use the IF…ELSE condition in a WHERE clause?
I'm attempting to use the IF...ELSE construct in my WHERE clause to selectively apply conditions to my SELECT.
Should this work?
CREATE OR REPLACE package body If_Else_Pack IS
PROCEDURE Moving
...
3
votes
1answer
602 views
How far should bind variables go in Oracle?
I am currently examining all database users's SQL code in our department for not using bind variables. We do experience an ORA-04031 error within a constant period of time.
Now, I have a quite firm ...
3
votes
2answers
326 views
FORALL on Nested Collections
Suppose I have the following two tables in my Oracle database:
create table a (num number);
create table b (val varchar2(30);
and in my PL/SQL program I have declared a nested collection as ...
5
votes
1answer
83 views
Retrieve Indices of Associative Array as Collection
In PL/SQL, suppose I have some associative array defined as follows:
declare
type a_arr_t is table of PLS_INTEGER index by PLS_INTEGER;
a_arr a_arr_t;
I then, sparsely, populate the ...
2
votes
2answers
736 views
Dynamic access to record column in plpgsql function
How can I address a column from a record in a plpgsql function dynamically?
In the following snippet I have access to a variable entity.colname that contains the column that should be checked in the ...
0
votes
1answer
1k views
Why does dbms_scheduler max_run_duration not raise event JOB_OVER_MAX_DUR?
Starting from this question and this example I've tried to create an event based dbms_scheduler job, which waits for event JOB_OVER_MAX_DUR raised from jobs, which exceeded their max_run_duration.
...
4
votes
2answers
3k views
Oracle 11G - Index Rebuild VS GATHER_TABLE_STATS
Quick question, after populating my table, I noticed through my explain plan that indexes, in most cases, only seems to take effect after I perform GATHER_TABLE_STATS:
Exec ...
2
votes
0answers
370 views
Initializing Associative Array in PL/SQL
Using PL/SQL, suppose we've declared a nested table type and its instance as follows:
type nested_tab is table of pls_integer;
tab nested_tab;.
We are then able to initialize our nested table ...
1
vote
1answer
199 views
Help with getters & setters in PL/SQL
I am not a db guy, and I have a very beginner's knowledge bout PL/SQL. That being clear now the question:
I have 2 tables one file level & another batch level. In a day, only 1 file_id is ...
3
votes
1answer
1k views
Oracle 11: Change global index to local on huge production table
I have big partitioned table with global index. My case is drop old data from archived partitions. I can't simply drop partition because i have global index.
So I have idea to use INVISIBLE local ...
2
votes
1answer
817 views
SSIS PL/SQL task hangs with message “Multiple-step OLE DB operation generated errors.”
I've configured a Microsoft SQL Server 2008 R2 SSIS package with an Execute SQL Task to run a PL/SQL query on an Oracle 10.2 database.
The Execute SQL Task (named "SQL Update MY_STATUS flag") is ...
2
votes
2answers
995 views
Return many rows on a plsql Oracle10g
I'm doing a little project and I need to do a plsql to return many rows.
I have someting like:
Table(id, data1, data2, from)
And the user gives me the value of from and I have to return all that.
...
1
vote
1answer
360 views
Improve performance for my plsql block
First of all let me make kind of a disclaimer here:
1) i'm a developer, not an administrator;
2) and i dont have admin rights on my database.
3) table specs :
-- oulp_path_attendance_base_t - ...
0
votes
2answers
607 views
How do I manage column order when using PL/SQL's %ROWTYPE records?
I have a table with a large number of columns, which has data gathered from several other tables. I want to write some PL/SQL like:
DECLARE
TYPE t_toptab_list IS TABLE OF toptab%ROWTYPE;
l_data ...
1
vote
1answer
196 views
Error with Oracle trigger, invalid identifier
I have the following DML statement that is working perfectly, i have tried to make it into a trigger but it isn't working.
DML statement
UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL =
NVL((SELECT ...
1
vote
1answer
150 views
Update statement errors, parenthesis, failed validation ORACLE
i am writing an update query and it is giving me errors, what i am trying to do is to take a value multiply it added to another value.
Original statement
The statement below works flawlessly,
...
4
votes
2answers
335 views
Return a fully dynamic table from an Oracle function
I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of ...
3
votes
1answer
230 views
How to qualify variable inside trigger body (PL/SQL )?
I can explicitly qualify variable in the body of stored procedure or function :
create or replace
PROCEDURE ptest AS
int_val INT;
BEGIN
ptest.int_val:=0;
END;
/
How to do the same inside trigger ...
2
votes
2answers
123 views
Overhead of DBMS_Profiler
If I use DBMS_Profiler to profile a long running (5 hour) PL/SQL script, how much longer should I expect the script to run with profiling? I realize this will be dependent on what the script is ...
1
vote
2answers
2k views
ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes
I got that error when I tried to execute this query:
select *
from lc_history2 lc
where lc.view_name <> 'LC Main'
and lc.modifiedtime = (select max(modifiedtime) from lc_history2 l where lc.lc ...
1
vote
1answer
498 views
ORA-01427:single-row subquery returns more than one row
I wrote in PL/SQL Developer:
select * from lc_history2
and I got that error:
ORA-01427: single-row subquery returns more than one row
Can someone help? It's necessary.
2
votes
1answer
323 views
Alter in PL SQL Loop
I just started learning PL/SQL an hour ago... I'm trying to set profile settings dynamically... I select distinct profile names from dba_users and I'm trying to run an alter profile command for each ...
5
votes
3answers
946 views
PL/SQL: best way to count elements in an array?
Given this:
DECLARE
TYPE T_ARRAY IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
MY_ARRAY T_ARRAY;
V_COUNT INTEGER;
I would like to do:
BEGIN
-- ... some code filling the MY_ARRAY ...
2
votes
1answer
364 views
PL/SQL : query numeric data type by scale?
Wish to select rows with number fields having a certain amount of data after the decimal
eg
select number from table where number.scale > 4;
Is this possible to do directly, or must I cast to ...
4
votes
1answer
816 views
How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats
Hi there I was just wondering what would be the difference between these 2 METHOD_OPT parameter settings:
FOR COLUMNS SIZE 5 some_column
and
FOR COLUMNS some_column SIZE 5
both will create a ...
2
votes
1answer
420 views
Logging/profiling an unpredictably slow stored procedure
I have a stored procedure that sometimes runs significantly slower than others. I have two different timers measuring the execution time at different scopes. One is in the client code, measuring only ...
2
votes
2answers
297 views
Is there any prevalent logging packages in PL/SQL similar to java and .Net counterparts
Normally in java or C# , I would use a following construct when try and catch errors.
Bad Exception Handling in C# and Java
try
{
// do something
}
catch(Exception ex)
{
}
Good Exception ...
1
vote
2answers
391 views
Is Using Execute Immediate Bad Practice in Oracle?
So, I've been writing a script which creates a sequence (if it doesn't exist) then copies some data including a value from that sequence before deleting the original source data.
As the sequence ...
2
votes
1answer
241 views
Virtual Column - Using it in view (Oracle)
I have made a virtual column in Oracle, using the following code.
CREATE OR REPLACE function email_address (ID_ varchar2)
return varchar2
deterministic
as
lname varchar2 (256);
snumber varchar2 ...
0
votes
1answer
250 views
calculate and return the total estimated chargeable cost
I had a question regarding a small task I've been assigned. Below I have pasted the question and a screenshot of the database layout. Now I just don't understand what exactly I'm meant to create, a ...
10
votes
1answer
2k views
What are the benefits of packages over standalone procedures and functions?
Looking for a canonical answer to the question of why packages should be preferred in an Oracle database over stand alone procedures and functions.
2
votes
0answers
246 views
PL/SQL problem statement for homework study [closed]
Good afternoon,
I have a PL/SQL (&etc) exam in a few weeks, so I thought it would be good to do a couple of PL/SQL projects/assignments in preparation.
Can you give me a good resource of ...
1
vote
2answers
207 views
ORM-style server-side programming languages (OO replacement for PL/SQL?)
Good evening,
Is there an Object Oriented replacement for PL/SQL, allowing server side procedures to be written [and then called client or server side]?
(for MySQL, PostgreSQL, Oracle or etc.)
2
votes
1answer
966 views
Within a PL/SQL procedure, wrap a query or refcursor in HTML table
It seems really easy if you are using SQL*Plus to use
SQL> set markup html on;
and get some lovely results to the SQL*Plus window. we have an oracle job that runs overnight and sends an email ...
4
votes
1answer
8k views
PLS-00306 Error: How to find the wrong argument?
PLS-00306: wrong number or types of arguments in call to 'string'
Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The ...
3
votes
1answer
314 views
What is an Effective Way to Count the Number of Pages in Oracle?
We've implemented paging in our PL/SQL procedures by building a dynamic SQL query and then selection only a portion of results.
We build implement paging by putting the original query inside a ...
5
votes
1answer
240 views
DBA's Scope in datawarehousing Projects
As a DBA trainee, what needs to be known in order to be part of datawarehousing projects?
I mean what resources are suggested to have an overview knowledge of how PL/SQL and other concepts are used ...
2
votes
3answers
1k views
What are the causes and solutions for mutating table errors?
I understand mutating table errors are caused by a design flaw or problematic query.
An old query was recently put into production which throws a mutating table error. Our DBA solved the problem but ...
1
vote
2answers
137 views
Functioning of Oracle's Server result Cache
I am reading the Oracle concepts doc and have a doubt [about general cache mechanism ] :
PL/SQL function code can include a request to cache its results. Upon invocation of
this function, the ...
2
votes
2answers
271 views
Get timing on individual queries in Anonymous code block
I have a set of queries that I need to run in succession, carrying the result from the previous query into the next. My only need is the execution time on the last query in the list, but SET TIMING ON ...
2
votes
2answers
648 views
Piplelined Result Set over Database Link Alternatives
Since a pipelined result set cannot be returned over a database link (see MOS note 560743.1), what workarounds/alternatives are there. I can think of a few, but I'd rather not lead the question. I ...
0
votes
1answer
127 views
how to write pl/sql named programme for this [closed]
I have a table M_PARAMETER which has a column MMRUN.
When this MMRUN='Y' then the User can not modify any transaction in the app's front end. When MMRUN='N' then the User can do the modification.
...
1
vote
2answers
547 views
Table-name.column-name in PostgreSQL?
I would like to use the following syntaxis in my PL/pgSQL code:
INSERT INTO table (table.column1, table.column2) VALUES (1, 2);
But somehow it doesn't work. Does anybody know if such a notation it ...
2
votes
1answer
504 views
PL/SQL + after login seems Not see tnsnames.ora file on my PC
I get the following error
when I login in by PL/SQL - please advice how to fix this?
Data character set (UTF8) and client character set (IW8MSWIN1255) are different character set conversion may ...
2
votes
2answers
348 views
Use different Windows account in pl/sql
Is it possible to use/log in to a different account in pl/sql. For example, I would like to write to a file in a folder only a specific account has access to. Here's an example of what I mean:
Other ...
2
votes
1answer
989 views
Oracle implicit parameter casting - why doesn't it always work? (ORA-06502 error)
I have a Java (Spring) app that uses Oracle 10 as DB. Some legacy code has wrong parametrization of PL/SQL procedures. For instance, some input parameters are declared as OracleTypes.VARCHAR in Java ...
3
votes
1answer
3k views
Referencing PL/SQL variables in FOR loop
I have written a PL/SQL script to find the size of a long column in a table.
Just to make the script generic I am passing the table Name and column name as variables , but I am getting an error saying ...
1
vote
2answers
980 views
What does 'nls' in nls_date_format stand for?
For example:
alter session set nls_date_format = 'DD-MON-YYYY hh24:mi:ss';
changes the date format for the session.
What is nls here?
5
votes
2answers
388 views
Oracle Advanced Queuing LIFO
With Oracle's Advanced Queuing is there a way to dequeue messages in a LIFO (last in first out) order? There is an indication through a lack of information that this is not an option, but perhaps ...
1
vote
0answers
227 views
Which are the Oracle basics required for advanced Oracle.DataAccess usage? [closed]
Successful use of [Oracle.DataAccess.Client] is based on proper understanding of three separate parts of Oracle:
SQL
PL/SQL
sql*plus
Here on dba.exchange the following questions target this topic:
...
