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.
2
votes
1answer
774 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
0answers
114 views
Transform XPath map into XML document using relational data
Background
Most modern databases have XML functions that can be used to extract data in an XML format. I want to avoid the task of manually calling XML functions to extract the data.
This problem ...
2
votes
2answers
180 views
Why can't we write ddl statement directly into the PL/SQL block
Why can't we write ddl statements directly in PL/SQL block, for example when i write
CREATE OR REPLACE PROCEDURE test IS
BEGIN
truncate table table_name; // error
END test;
/
But,
CREATE OR ...
2
votes
0answers
261 views
Where can I find a syntax reference for Oracle SQL Developer's “Generate DB Doc” function?
Oracle SQL Developer (I'm using v3.2) has a feature called "DB Doc", which generates documentation for database objects. I mostly want to use it to generate documentation for my stored procedures, ...
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 ...
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
941 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?
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.)
1
vote
2answers
110 views
Oracle insert max from two date
I want to create an insert statement in a PL/SQL Script that inserts the larger of two different values. Conceptually, what I want to do is something like
Insert into MYTABLE (DATE_INSERT)
VALUE ...
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
2answers
259 views
How to isolate general Oracle 10g issues through SQL
Background:
I've been working on a performance analysis on a system with major performance issues on a Oracle 10g server. Much of the SQL is badly written, or at least not written with performance in ...
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
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 ...
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 ...
1
vote
1answer
32 views
Testing AFTER INSERT Trigger
I have the following table:
CREATE TABLE Train(
Train_No integer PRIMARY KEY,
Loco_No integer REFERENCES Locomotive(Loco_No),
Back_Loco_No float REFERENCES Locomotive(Loco_No),
...
1
vote
1answer
84 views
need to use PLSQL to insert distinct rows
I need to take some data from Table A, use some logic, and then insert one or more rows into Table B. I have a PLSQL block that brings in data from Table A with a cursor, performs all the logic ...
1
vote
1answer
155 views
DB stored procedures: how do they help increase security in web systems?
The usage of Oracle PL/SQL procedures for controlling data access is often emphasized in PL/SQL books and other sources as being more secure approach. I'v seen several systems where all business logic ...
1
vote
1answer
484 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.
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 ...
1
vote
1answer
18 views
double date conversion when adding partition
I want to create a procedure that adds new partition in a table partitioned by time. For this purpose I use following commands:
s_until := to_char(sysdate+1,' SYYYY-MM-DD HH24:MI:SS');
...
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 - ...
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,
...
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 ...
1
vote
0answers
238 views
How can one reliably update a LOB column with data from another LOB column in Oracle?
I have tried:
UPDATE "SCHEMA".TABLE SET LOB_COLUMN = (SELECT LOB_COLUMN FROM "SCHEMA".TABLE)
I have tried:
CREATE OR REPLACE PROCEDURE COPY_LOB_DATA IS
OLD_BLOB BLOB;
NEW_BLOB BLOB;
...
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:
...
0
votes
1answer
27 views
Is the data fetched by an explicit cursor immutable in PL/SQL?
This seems like an obvious question, but Googling has so far failed me...
Say I have a view which delineates updates that I need to apply and, to this end, I have an SP that opens the view as a ...
0
votes
2answers
63 views
PLSQL: BEFORE INSERT Trigger
I am attempting to write my first trigger statement. I am trying to create a trigger that performs a SELECT to get my MAX_TOW_WEIGHT for a LOCO_CLASS, then compare that weight to the TRAIN_WEIGHT. If ...
0
votes
1answer
246 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 ...
0
votes
1answer
25 views
Dynamic where condition in a stored procedure
I am using Oracle 11g. I am trying to get the table data with this procedure:
CREATE OR REPLACE PROCEDURE test(p_table IN varchar2)
IS
v_sqltxt varchar2(4000);
BEGIN
v_sqltxt:='select count(*) from ...
0
votes
1answer
44 views
Oracle's UTL_FILE
I am learning Oracle pl/sql by myself.
I need to create a procedure that exports a table to a csv file. For that I am thinking of UTL_FILE.Is there any good book or a site to get me started? I did ...
0
votes
3answers
254 views
ORA-01562: failed to extend rollback segment number 11
I´m trying to execute this query into a SP
DELETE FROM table SC
WHERE SC.evaluationMonth= 'Some month';
COMMIT;
But for this month there are something like 10 million records. So I get the ...
0
votes
1answer
473 views
Oracle: Output not shown even if SERVEROUTPUT variable set to ON
I was wondering why the output is not being shown even if I set the SERVEROUPUT variable to ON.
I have the following annonymous block code:
DECLARE
v_rec_count number:=0;
BEGIN
for rec in ...
0
votes
1answer
27 views
I have a trigger that is giving me following error: *
CREATE OR REPLACE TRIGGER "TRANSACTION"
before
update of totalunitsbought on SIPHOLDER referencing new AS NEW old AS OLD
for each row
begin
declare
mportfolioname varchar2(20);
...
0
votes
1answer
39 views
Combine multiple queries into single query
I am trying to run the following query:
SELECT Freight.Freight_No,
Freight.Available,
Locomotive.Loco_No,
Locomotive.Available,
Driver.Driver_No,
Driver.Name,
...
0
votes
1answer
50 views
PL/SQL for Updating 1 Column in Table A Where PK = a row in table B
Using an oracle DB, 11g r2, I need to write a pl/sql which updates one column in table A where the PK exists in table B, with commits ever ~50,00 rows.
3200000 rows are involved in the update, and ...
0
votes
1answer
50 views
Invalid rowid error
I'm trying to see, how UPDATE Lock helps to minimize error while dml (delete/update) operations.
declare
cursor update_lock is select empno from emp where deptno=&no for update of sal;
num ...
0
votes
1answer
229 views
Massive update to Clob fields oracle 10g
I need to remove a string of characters from 2000+ clobs fields in oracle 10g. How would I do this in PL/SQL?
For example, in table t1 there is a clob field called VBase that needs to have the ...
0
votes
1answer
277 views
Find ID columns with null value in oracle database
I need a way to iterate through all tables in tablespace "T_ECOS" and find all ID with null value in oracle database
Any suggestion?
0
votes
1answer
584 views
Split CLOB into multiple VARCHAR2
I currently am working with some data that is being stored as a CLOB between 4000 and 6000 characters. I would like to perform the TRANSLATE function on it but I learned that it's not possible with a ...
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.
...
0
votes
2answers
494 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 ...
0
votes
1answer
126 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.
...
0
votes
0answers
36 views
Oracle PL SQL EXTREMEMLY slow FETCH performance
I have a stored procedure that includes (among many other things) a dynamic query with a LOOP and a FETCH. The fetch has to potential retrieve a large amount of data as one column contains spatial ...
0
votes
0answers
27 views
Passing parameters to a procedure executed by DBMS_SCHEDULER
I have a scheduler program that runs a stored procedure that requires input parameters.
BEGIN
DBMS_SCHEDULER.DROP_PROGRAM
(program_name => 'MYSCHEMA.EXPORT_SCHEMA_STARTING');
END;
...
0
votes
1answer
19 views
Migration of SQL Server to Oracle Database
In Sql server, My Procedure will create temporary tables where column names are dynamically comes from every user, then inserts the data into the temporary tables and i use the data further based on ...
0
votes
0answers
21 views
Error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
i've problem here. i wanna create a Stored procedure like below, but i encountered error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records.
create or ...
0
votes
0answers
48 views
Designing ERD1 and ERD2 with crows foot [closed]
What would be the perfect ERD1 & ERD2 for the following case study? And what are the attributes of the entities?
Five Aside Football Independent League (FAFIL)
The Five Aside Football ...
0
votes
0answers
174 views
12154 error when using PL/SQL Developer
I have installed a few versions of Oracle on my machine. I have tried 64 bit and 32 bit versions. I have installed 11G. I need Oracle 10G 32 bit.
Using the following guide I removed all versions of ...
