Procedural Language/Structured Query Language is Oracle Corporation's procedural language extension for SQL. IBM supports PL/SQL for DB2 since version 9.7. Questions about PL/SQL should probably be tagged "oracle" or "db2" as well.

learn more… | top users | synonyms

1
vote
1answer
144 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
0answers
26 views

PL/SQL developer can't insert Unicode characters into oracle database

I've been meddling with charset/encoding problems of Oracle database and PL/SQL developer and these are what I have done: Change the server charset to AL32UTF8 Add system environment variable ...
1
vote
0answers
56 views

Can't run procedure on Oracle SQL Developer [closed]

Since yesterday, I have not been unable to run procedure on Oracle SQL Developer. When I click on Run button, progress bar with "Launching" text appears on the right bottom corner of the SQL Developer,...
1
vote
0answers
21 views

EXTPROC issue during 10g to 11g database

I am currently 10g to 11g database migration and trying to configure EXTPROC. EXTPROC32 was configured as part of 10g to execute C++ library and working fine. It appears that 11g does not provided ...
1
vote
1answer
50 views

Oracle - execute DML query as different user

I would like to be able to run DML queries (INSERT,UPDATE,DELETE) by executing a procedure on a given schema. Those queries need to change data on different schemas on the same instance. So, basically,...
1
vote
1answer
108 views

Run Oracle queries on Sublime Text 3

I'm trying to run some queries in Sublime Text3. I downloaded this plugin, and I set up the settings: { "connections": { "Connection 1": { "type" : "oracle", "...
1
vote
2answers
38 views

SQLPLUS: SELECT INTO prompting for variable value

Any idea why this is prompting me for the value? I'm calling the script via batch file: DECLARE precount NUMBER; nowcount NUMBER; BEGIN SELECT COUNT(*) INTO precount FROM ...
1
vote
1answer
43 views

/ Character in a stored procedure

I am trying to recompile/update a stored procedure in SQL Developer that uses a few division operations to populate tables. A sample section looks like this: Insert Into Production(A, B, C) ...
1
vote
2answers
52 views

Sync data between 2 databases Oracle 10g

I have 2 databases DB_LIVE and DB_NEW, both have different credentials. I have a Data Read access on DB_LIVE and I am owner of DB_NEW which is a completely blank and raw db provided to me. I want to ...
1
vote
0answers
709 views

How to drop invalid Java classes from Oracle?

I tried to load some jars into my database, but I think I messed up something, because I tried as DBA and then with some oracle user... This was my first command: call dbms_java.loadjava('-genmissing ...
1
vote
0answers
84 views

Whitespace Trimming on Before Trigger in PL/SQL [closed]

I'm looking to create a simple trigger that trims the whitespace on a piece of data prior to it writing to the table. I'm using PL/SQL with Oracle 11g. I am receiving the following error: PLS-...
1
vote
2answers
314 views

How to add a column from another table?

I'm starting to using SQL statements in my Oracle database XE (tables and relations made by me) and I'm having a hard time to understand how can I put a column from another table. The example in ...
1
vote
0answers
774 views

Clarification on usage of Serially Reusable Pragma for Oracle 10g

I need to make packages for Oracle 10g that can be changed while users are still logged in an using an application that makes database calls through PL/SQL. Unfortunately, anytime a package is updated ...
1
vote
0answers
33 views

Allowing users access to SQLPLUS and to compile PLSQL [duplicate]

As a DBA - assuming my users are knowledgeable with PLSQL and SQL - why would I not grant my users privileges to compile and run their own PLSQL code. We have an MI database environment where the data ...
1
vote
0answers
220 views

Adding custom meta data to a table in oracle

I'd like to know if there's a possibility to add some custom metadata to a oracle database table. The problem at hand is the following: I'm importing a table from another DB. It might happen that I ...
1
vote
0answers
436 views

PLSQL Compilation error for other users/ [closed]

I created a PL/SQL procedure that queries the database and dumps the result into a CSV file. I am an application developer and I have dba rights to my oracle database. I'm able to compile and run the ...
1
vote
1answer
356 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
1
vote
0answers
233 views

dbms_audit_mgmt.clean_audit_trail not purging

my current database version is 11.2.0.3.0. I'm trying to purge my audit trail with a procedure. In my procedure I got this part of code: SYS.dbms_audit_mgmt.clean_audit_trail( audit_trail_type ...
1
vote
1answer
91 views

How can I send an email when objects get invalidated?

I have couple stored procedures and a package. Whenever this stored procedure and package goes invalid, I need to send an email. How can I achieve this? Can you please give me an idea how this ...
1
vote
0answers
297 views

Redefining a table with a user-defined type using DBMS_REDEFINITION

The application which we developed has one schema, USER1, which has tables, types, packages etc. I have to move some of the user-defined types to a different schema, The reason for doing this is to ...
1
vote
0answers
487 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 25,...
1
vote
0answers
2k views

'oracle.jdbc.driver.OracleSQLException': ORA-01031: insufficient privileges. (SYS.DBMS_XMLSAVE)

I have this function (written here by someone), placed in the package ENVER.ENCODING_VERIFIER: FUNCTION loadxml_table (p_owner IN VARCHAR2, p_tablename IN VARCHAR2, p_format IN VARCHAR2, p_tmpClob IN ...
1
vote
0answers
4k 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
791 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
1answer
3k views

SELECT from Function that return CURSOR

I would like to select from a function that return a SYS_REFCURSOR Type value. for example : CREATE OR REPLACE FUNCTION my_funtion ( my_param IN VARCHAR2) RETURN SYS_REFCURSOR IS ...
1
vote
1answer
206 views

Oracle PLSQL code recovery

I cannot connect to my database as admin, even using connect descriptor neither via RMAN. I am getting TNS-12514 error. I consider to install new instance. Questions: 1) Can I restore/recover PLSQL ...
1
vote
1answer
112 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
3answers
170 views

Allowing users to compile / run PLSQL and SQL

As a DBA - assuming my users are knowledgeable with PLSQL and SQL - why would I not grant my users privileges to compile and run their own PLSQL code. We have an MI database environment where the data ...
0
votes
1answer
50 views

How to declare reserved words as variables in Oracle PL/SQL?

What is the best way to bypass the Oracle SQL reserved-word constraint for PL/SQL value declaration? I realize bind and substitution may be acceptable alternatives for an actual query, but what about ...
0
votes
2answers
736 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
205 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
2answers
517 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
23 views

Procedures In Oracle [closed]

--Procedures Exercise: create or replace procedure Display is cursor ABC is select empno, ename, sal from emp where deptno=10; emp_rec ABC%rowtype; Begin Open ABC; Loop fetch ABC into emp_rec; exit ...
0
votes
2answers
136 views

Duplicate data in a table given table name

For a performance testing application we need to have lot more data in our tables than what we have now. How do I duplicate the data in a table. In the process of duplicating I also need to set new ...
0
votes
2answers
731 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
2answers
80 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
746 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
222 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
968 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
3k 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
360 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
58 views

How to select sub query in pl/sql? [closed]

I want to select subquery in pl/sql by count where UPI debit card and VISA debit cards as screenshot below: Please helps to check script code as below : for r in (select BRANCHCODE,BRANCHNAME,cntupi ...
0
votes
2answers
83 views

PL/SQL block running without printing any output

I'm trying to exexcute a pl/sql block using dbms_output to obtain a console output but without reason the unique answer by SQL developer is 'Anonymous block finished'. declare v_count integer; ...
0
votes
1answer
40 views

PLSQL - getting real-time updates

I want a PL/SQL procedure to give me progress updates as it runs. However, DBMS_OUTPUT seems only to give me the output when the whole procedure is done. Is there a way to make it give me updates ...
0
votes
3answers
270 views

Will an update in my trigger trigger itself? Oracle PLSQL

Here's my PLSQL trigger: CREATE OR REPLACE TRIGGER ROOT_CHANGING_TRG BEFORE UPDATE OF ROOT_ID,PARENT_PHYS_ID ON UNIQUE_PHYSICIAN FOR EACH ROW DECLARE var_root number := :new.ROOT_ID; var_par number ...
0
votes
1answer
105 views

Problem with procedure performance

I'm having problem in Oracle, where I have two procedures that are executed one after the other. Where the first procedure, I have a cursor that does insert into a table. This insert has about 30 ...
0
votes
1answer
4k 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
1answer
4k 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
359 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
3answers
3k 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 ...