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

2
votes
2answers
479 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
128 views

Can I exchange partition between two table with partition with same name?

I am trying to restore one partition from a backup table. Both tables have partitions with the same name. My goal is to grab the data from the backup table partition x and put it in original table ...
2
votes
1answer
144 views

Is there a way to pass to execute immediate only once a variable but use it several times?

I have a Cursor that gets its data from a script along these lines: OPEN myCursor FOR SELECT value1 AS Value1, myPackage.function1 (value1, :myId) AS Result1, myPackage.function2 (value1, :myId) ...
2
votes
1answer
1k 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 ...
2
votes
2answers
1k 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 ...
2
votes
1answer
3k views

how to create and insert into a table in an anonymous block

I have PL/SQL anonymous block that creates a table (using execute immediate) and then inserts data into the table. When the block compiles, it gives me an ORA-00942: table or view does not exist ...
2
votes
1answer
1k views

How to check parameter value existence in a procedure?

The following is a procedure that uses 2 parameters: customer_code and pay_amount. The procedure works as expected, however when I enter wrong cus_code I get this error instead of my custom checking: ...
2
votes
1answer
643 views

How to speed up a query with a lot of subqueries

I have a query which uses a series of functions to return the status of each document for a loan. SELECT loan_number, borrower_name, get_application_status(loan_number, ...
2
votes
2answers
758 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
1answer
120 views

PL/SQL doesnt return rows

Please help me debug this: Working: When I execute: set serveroutput on declare middle_name varchar2(30); begin middle_name := '0000000231'; for x in ( select ...
2
votes
1answer
1k 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), ...
2
votes
1answer
168 views

PLSQL Package - Catching Errors in procedure (Oracle)

I have a problem with my package (and procedure) not catching exceptions. I have the below code: CREATE OR REPLACE PACKAGE process_orders IS PROCEDURE add_order ( order_num NUMBER, ...
2
votes
2answers
11k 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. ...
2
votes
1answer
1k 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 ...
2
votes
2answers
1k 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
1answer
448 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 ...
2
votes
1answer
2k 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 ...
2
votes
1answer
2k 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 ...
2
votes
1answer
164 views

sql query - table@string meaning

I am trying to understand a query . When I run the below query , no rows are fetched. select * from Table But when I run the query , a result is returned having multiple rows. select * from ...
2
votes
3answers
2k views

how to change a user password while checking the current password in oracle?

In my application I have a change password form which users enter their current password and the new password. Then I change their password using the following command: alter user user_name ...
2
votes
1answer
1k views

Oracle Packages, Procedures and Functions Not Respecting Roles

When you create a package, sproc or function in one schema (say, SCHEMA1) that accesses objects in another schema (SCHEMA2), even when SCHEMA1 has appropriate permissions to the respective SCHEMA2 ...
2
votes
2answers
207 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 ...
2
votes
2answers
1k 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 ...
2
votes
1answer
875 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
14k 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( ...
2
votes
1answer
2k 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
1answer
53 views

SQL query to identify missing hinted indexes

We have a fairly large number of queries using index hints in our 11.2.0.4 database. I recently uncovered some performance issues and found that some of the indexes referenced by the query hints were ...
2
votes
0answers
34 views

Procedure to update tables when the difference of values is less than 10% [closed]

I use Oracle database. I have 2 tables. Table1 with the following details: Table-1 emp.no | salary 1 | 2000 2 | 3000 3 ...
2
votes
0answers
44 views

PLSQL using cursors or functions to query repeated data

Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)? Whats the difference? What are the best ...
2
votes
1answer
133 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 ...
2
votes
0answers
413 views

Transform XPath map into XML document using relational data [closed]

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
0answers
449 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
329 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
2k 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
1answer
674 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
12k 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
170 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
1k 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
2answers
54 views

Oracle regexp_like square brackets inside character set

This questions is in regards to Oracle, pl/sql, and the regexp_like function. I am trying to build a character set that will match on all typical special characters. My character set currently looks ...
1
vote
2answers
500 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
26 views

Parsing out date_entered field to capture TIME 'hh:mm:ss' based on time range

Based on the date_entered column, which is a data_type of 'DATE' how would I parse out the data to pull the time. Values in the DATE_ENTERED field (DATA_TYPE is DATE) looks like this 5/15/2015 ...
1
vote
1answer
41 views

How to update table1 field by using other table and function

I have two tables and one function: Table1 contains shop_code,batch_id shop_code| batch_id | regstriy_id 123 | 100 | 12 124 | 100 |13 125 | 100 |12 Table2 contains shop_code,shop_name ...
1
vote
1answer
168 views

How to use variable and drop in test script?

I have to modify certain Oracle Packages. Basically I will be extracting functionality and moving it to C. They provided me with some "Test" scripts that I shoud be able to run to test that my ...
1
vote
1answer
327 views

Pl/SQL web application

I have a PL/SQL web application,got the entire DB dump from remote DB. Its very old application, running on oracle 9i. I basically want to get the application up and running on my laptop,but i am not ...
1
vote
1answer
380 views

Slow join behaviour with 'or' in predicate

I'm faced with a situation which I can't understand and overcome. In short we have left-join query like: select from a left join b on a.key1=b.key1 or a.key1=b.key2 This works VERY slowly, while ...
1
vote
1answer
462 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
493 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
893 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
47 views

Rollback in Oracle

I am having a problem in Oracle rollback. In my stored procedure I have multiple insert and update statements. If every thing is fine I am sending success message and also I am tracing success status ...
1
vote
1answer
252 views

PL/SQL operating system call

I'm trying to lauch a system call from Oracle Apex, directly or indirectly. It seems to only accept PL/SQL procedures, so I iterated towards this: begin :p3_request_id := sequence.nextval; ...