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
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
321 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
193 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
775 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
833 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
355 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
496 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
190 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
145 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
312 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
224 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
122 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
1k 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
486 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
309 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
820 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
336 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
781 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
393 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
291 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
372 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
236 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
247 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 ...
9
votes
1answer
1k 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
244 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
205 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
909 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
7k 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
305 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
234 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
262 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
627 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
539 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
501 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
339 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
960 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
942 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
379 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
223 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:
...
5
votes
3answers
719 views
Alternatives to an INSERT Trigger
Looking for an example code in Oracle to do the same. i.e.
Remove an insert trigger on table T1 whose job is to create a row in T2 based on the derived data. How would this PL-SQL api look like?
...
6
votes
3answers
2k views
How to differentiate between SQL and PL/SQL?
I know the question might sound too stupid, but I never understood this part.
SQL*Plus works with both SQL and
PL/SQL. How do I know whether some
code is SQL or PL/SQL? If my code has
a for ...
4
votes
1answer
503 views
Using sys.diana and sys.pidl to parse PL/SQL
It seems to me that it should be possible to parse existing PL/SQL with the packages sys.diana and sys.pidl, but I have found nothing on the internet with the exception of a few scripts that seem to ...
4
votes
2answers
574 views
Identify which parameters were passed to a procedure (Oracle)
Procedures in PL/SQL can have default values for parameters, but the caller can pass values that are identical to the default values. Is there a way from within the PL/SQL to determine whether a ...
