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

3
votes
2answers
1k views

How to get return value from pl sql procedure and assign it to variable?

Code: execute x.procedurename(row.accc_no, row.bill_no, 0, null, row.total_balance, 'A',assgn_scen_site_cv, :assgn_scen_site_cv); Its my procedure. How to get return value from this proc and ...
3
votes
2answers
2k views

DB2 UPDATE TRIGGER

I am trying to create a trigger in a DB2 database that runs on the update of a column in one table, and then fills in another table with certain values. For example, there is a power unit table with ...
3
votes
2answers
39k views

How to store single result in variable and reuse it in a query (Oracle)?

I have a query like SELECT id FROM xyz WHERE ...; which has a more or less complex WHERE clause and returns exactly one row with one column (ID). I need this ID for several later queries in a ...
3
votes
2answers
159 views

Stored procedure using cursors [closed]

I am attempting to create a procedure using cursors to list the average salary of all employees on a per department basis. I am using three tables, defined as emp (eid, ename, age, salary) works (...
3
votes
3answers
638 views

How to raise_application_error beyond a when others clause

Background: I've used a few Oracle articles to develop an error package, with some procedures. One of these is Log_And_Return which is called throughout the database. I've simplified it for brevity, ...
3
votes
1answer
187 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 ...
3
votes
1answer
600 views

Are there tips for writing Oracle DDL Triggers for auditing? Or should I just use AUDIT?

I've been tinkering with writing a trigger to log my table creation and manipulation for later tracking and review. The challenge is that my trigger logic is stored in the same schema on which I've ...
3
votes
1answer
1k views

PLSQL : DBMS Jobs - parallelization?

I am looping over a cursor and executing a stored procedure; how do I parallelize this process through dbms.jobs? for rec in select column from table1 loop execute stored_procedure( rec.column ); end ...
3
votes
0answers
52 views

UTL_MATCH.JARO_WINKLER_SIMILARITY - how can I set the prefix scale?

I'm using UTL_MATCH's Jaro Winkler similarity function and it seems to be performing well. However, I would like to adjust the prefix scale according to the situation. Is this possible? Is it possible ...
3
votes
1answer
674 views

How to avoid hardcoding a database link name in a package body

I have a PL/SQL package that copies data from a remote DB2 database using a database link. To keep the package configuration-independent I wrapped the remote table in a view. But a single field in a ...
3
votes
0answers
229 views

plsql procedure gets invalidated

procedure involving dblinks gets invalidated regularly .we need to recompile every time.How to find what is causing the problem in my case procedure p1()--resides in db1 is begin .... select col1 ...
3
votes
1answer
988 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
1answer
967 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
2answers
509 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
171 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
2answers
16k 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 ...
2
votes
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 ...
2
votes
1answer
2k 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
2k 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
4k 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
3k 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
786 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
1k 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
35 views

Could Not Be Accessed Value Of The For Loop Counter In PL/SQL

I try to learn PL/SQL for loops in tutorials. I learn flow of control in a for loops. (And i saw that it is same with most of the other programming languages) Here is the flow of control in a for ...
2
votes
1answer
152 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
2k 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
208 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
16k 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
490 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
3k 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
29 views

Return only one row from table with similar values in column that match a criteria

I want to run a query from a table with similar values in the same column that match a certain criteria, but only return 1 row per similar value. Let's say I have this simplified table: Column1 ...
2
votes
1answer
58 views

Create a table based on a table depending on the values in the initial table column

I have a table like: +----+--------+----------------+ | id | name | address | +----+--------+----------------+ | 1 | Ivan | Moscow,Moscow | | 2 | Petr | Samara,Samara | | 3 | Grigor ...
2
votes
1answer
59 views

I can't select directly from a table I created

My client created a user in his Oracle instance for me to install the objects I have created in my local instance. However, this strange behavior prevents me to deliver my project. I guess it's a ...
2
votes
1answer
2k views

Modify text file with Oracle PL/SQL

I'm trying to open and replace a string in a text file with the following procedure: BEGIN v_file_name := 'text_file.txt'; v_filehandle := utl_file.fopen(v_file_dir, v_file_name, 'W'); v_id_nextval :=...
2
votes
1answer
169 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 Table@...
2
votes
2answers
11k 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; /...
2
votes
1answer
2k 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
5k 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. ...
2
votes
2answers
220 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 doing,...
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
963 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
18k 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
0answers
72 views

Help with query to find intersection between arbitary rows of a table under conditions [closed]

CREATE TABLE "SYSTEM"."SAMPLE" ("E1" VARCHAR2(20 BYTE), "E2" VARCHAR2(20 BYTE), "E3" VARCHAR2(20 BYTE), "E4" VARCHAR2(20 BYTE) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 ...
2
votes
1answer
93 views

Fill Database With Recursive Factorial Function

I want to calculate table's all of the rows's factorials value. And then i want to insert to each row's factorial value in table. so that i create table like as below: TABLE:FACTORIALS ---------------...
2
votes
1answer
261 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
54 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
89 views

SQL - Optimize table population with dynamic queries

I need to optimize the following pl/sql procedure. So, fundamentally my question is: There is a way you can do a bulk insert in a pl/sql that could be more efficient than loop through an associative ...