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.

learn more… | top users | synonyms

1
vote
0answers
130 views

How to display '0' where no data found in PL/SQL Oracle Reports

I have an Oracle Report (11g) in which I have selected a number field containing amount and have mentioned the format mask as 999,999,999,999. The data is displayed as follows. Data1 ...
1
vote
1answer
104 views

How do I delete a file that has been locked by UTL_FILE?

I wrote a flakey PL/SQL script that crashed out and forgot to close the file handle (type UTL_FILE.file_type). Now I can't delete the file. Short of restarting the instance or rebooting the box, is ...
1
vote
2answers
4k views

insufficient privileges while executing oracle stored procedure?

Im getting insufficient privileges error while executing the following oracle stored procedure. Im using Oracle Database 10g Express Edition. CREATE OR REPLACE PROCEDURE sp_update_acounts( ...
1
vote
0answers
1k views

PL/SQL extremely 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 ...
1
vote
0answers
401 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
255 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: ...
1
vote
1answer
43 views

Mutating Error , How to solve it

CREATE OR REPLACE TRIGGER LOAN_RP AFTER INSERT OR UPDATE ON FUND_DISBURSE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE V_FA FUND_DISBURSE.FUND_AMT%TYPE; V_RC NUMBER (10); ...
0
votes
2answers
142 views

table does not exist

CREATE OR REPLACE PROCEDURE ProcedureX() TYPE AVERAGE_COUNT_TBL IS TABLE OF INTEGER; AVERAGE_COUNT AVERAGE_COUNT_TBL; BEGIN INSERT INTO AVERAGE_COUNT VALUES(10); END ProcedureX; It ...
0
votes
2answers
99 views

Cycle or something similar in PL/SQL

Quite often it is necessary to do some repetetive operations / few line queries against set of objects. Let's say you have a list of accounts to unlock via alter user %username% account unlock and ...
0
votes
1answer
136 views

converting varchar to number while passing to IN clause

I am really stuck here.. Given below is the miniature of what I am trying to achieve. set serveroutput on; declare rajesh varchar2(20); x number(10); begin select wm_concat(stocks_id) into rajesh ...
0
votes
1answer
274 views

How to run procedure in Oracle SQL developer

Hello I have a table named CITY in oracle SQL developer, my problem is how to run the procedure -- start the script SET SERVEROUTPUT ON SET LINESIZE 400 SET TIMING ON CREATE OR REPLACE PACKAGE BODY ...
0
votes
2answers
183 views

Example optimisic offline lock in SQL/PL?

Can someone show me an example of an optimistic offline lock in an Oracle Stored Procedure?
0
votes
1answer
81 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
1answer
437 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
2answers
238 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
311 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
2answers
54 views

Decode Function in Oracle

I have a table named as transaction,in that table trans_type column is there.I want to check the trans_type like bellow if(trans_type = 'AR','AD','AF','AG') then trans_type:='A'; elsif trans_type = ...
0
votes
1answer
431 views

ORA-01410: invalid ROWID

I get ORA-01410: Invalid ROWID error when executing the following piece of code: DECLARE CURSOR c1 IS SELECT e.empno, e.ename, e.sal, d.dname ...
0
votes
2answers
143 views

How to fetch file path from a folder through PL/SQL

We upload files on our library on weekly basis. I wanted to fetch the latest uploaded file path/URL from a folder through PL/SQL, but not sure if it's possible. If possible, please suggest what ...
0
votes
1answer
132 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
3answers
896 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
2k 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
25 views

How to add a column in table via stored procedure? [closed]

create or replace procedure SP_PARAMETER_TEST -- this is my table ( NAME_KEY in VARCHAR2 -- this contains a value assume NAME_KEY contains "Gypsum" ) is begin ALTER TABLE PARAMETER_TEST ADD ...
0
votes
1answer
79 views

How to reference a table from another schema inside a package?

I am using TOAD to create a package in a Schema that I have am owner. My package uses tables of another schema. The package has a procedure that uses a cursor. When I create the package I get ...
0
votes
2answers
67 views

How to reuse code in Oracle Script?

I have a script where I am creating some test data. At the beginning of the script I create some tables where I have to insert the data. Before creating them I check that they do not exist. declare ...
0
votes
1answer
200 views

Using EXECUTE IMMEDIATE inside PL\SQL Block

Using EXECUTE IMMEDIATE inside PL\SQL block makes the whole block commit immediately. begin INSERT INTO Customer ( GUID, STATUS, NAME) VALUES (1,1,'xx'); EXECUTE IMMEDIATE 'CREATE TABLE Shop ( ...
0
votes
1answer
42 views

how to correctly return values from a function

I would like to create a function that queries a user table by passing a username and a password, the function is supposed to return 'Y' if the username / password are correct and 'N' if they're not. ...
0
votes
1answer
61 views

Stored Procedure with ROW instead of a list of parameters

i have a table with 15 columns, and trying to create a stored procedure in PL SQL that will only insert a row in a table using the INSERT statement. i would like to know if it is possible to declare ...
0
votes
2answers
71 views

Optional parameter in where condition

I have the following where condition: lr."dateEnd" >= to_date(?, 'dd.mm.yyyy') and lr."dateEnd" <= to_date(?, 'dd.mm.yyyy') All works fine. But the second unnamed parameter is optional. How ...
0
votes
1answer
75 views

Is using dbms_utility.format_error_stack to filter errors in the exception block a bad idea?

I'm trying to handle two different exceptions could trigger in an external table that reads a CSV. The code works great, but I'm concerned I'm doing it wrong. SET SERVEROUTPUT ON; DECLARE records ...
0
votes
1answer
99 views

Error wile debugging (signature buffer too small)

I'm trying to debug a package in SQL Developer, but I keep getting error: ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [], [], [], [], [] ORA-06544: ...
0
votes
1answer
60 views

Dividing a column into three columns [duplicate]

I have this table: Name Address Code James CT Homeaddress Mark mark@goole.com Email John John@hotmail.com homeemail ... This is ...
0
votes
1answer
511 views

How would I store the result of a select statement so that I can reuse the results to join to different tables?

How would I store the result of a select statement so that I can reuse the results to join to different tables? This will also be inside a cursor. Below is some pseudo code, in this example I have ...
0
votes
1answer
2k views

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

I need to figure out a way to embed an Oracle PL/SQL sub-procedure call in a SELECT statement, within another procedure in the same package. I am using SQLDeveloper 3.0 with an Oracle 11g database. ...
0
votes
2answers
1k views

How to forcibly flush the undo log/tablespace of UNDOTBS1

I'm looking for a way to flush the undo logs so that the UNDOTBS1 doesn't give me an error saying that my tablespace isn't large enough. I tried using a commit, but without success, it still gives me ...
0
votes
1answer
228 views

Error in trigger: table is mutating

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
69 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
307 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
221 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
501 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
677 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
2k 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
2answers
2k 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
34 views

Oracle Triggers - Before Update and Before Insert - Separate or Combined?

I'm running Oracle DB 9i. I have a table with various constraints to ensure data integrity. In addition to the constraints, I have triggers on BEFORE INSERT and BEFORE UPDATE to ensure that ...
0
votes
0answers
62 views

execute .sql script inside an anonymous pl/sql block which is invoked by a shell script

I got a shell script which connects to SQLPLUS and executes the .sql file like below: read a x=`echo $a | cut -d , -f 1` y=`echo $a | sed 's/.*,//'` c=`echo $USER | cut -c -2`"OPR"`echo $USER | ...
0
votes
1answer
27 views

Migrating stored procedure from Sybase to Oracle having temporary tables

I am migrating a few stored procedures from Sybase to Oracle using Oracle Developer tool. The tool migrates alomost 70%-80% of the stored procedures except a few cases where there is a temporary table ...
0
votes
1answer
42 views

Insert Into does not work with multiple rows

Here Is my code : CREATE OR REPLACE TRIGGER LOAN_RP AFTER INSERT OR UPDATE ON FUND_DISBURSE REFERENCING NEW AS NEW OLD AS OLD DECLARE V_FA FUND_DISBURSE.FUND_AMT%TYPE; V_RC NUMBER ...
0
votes
1answer
39 views

How to create a function(table_name) that return a collection in Oracle?

I need to create a function in Oracle that accepts a table name and return a collection the content of which is based the table that accepted. I've been doing some search, many examples are of the ...
0
votes
0answers
25 views

Running DBMS_AUDIT_MGMT package under other user than SYS

I want to run DBMS_AUDIT_MGMT package under user CENTRUMADMIN. But error occurs even when I try to run this part of code: BEGIN DBMS_AUDIT_MGMT.init_cleanup( audit_trail_type => ...
0
votes
1answer
42 views

Specific issue on data pump API in oracle

I have a client/server architecture. Using an Oracle dbms on the database server side. I need to perform a user-triggered (from client side) backup of the database, where the best way to perform that ...