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

4
votes
3answers
11k views

What are the causes and solutions for mutating table errors?

I understand mutating table errors are caused by a design flaw or problematic query. An old query was recently put into production which throws a mutating table error. Our DBA solved the problem but ...
1
vote
2answers
158 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 ...
2
votes
2answers
564 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
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 ...
1
vote
1answer
139 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
2answers
841 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
734 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
442 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
1k 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 ...
3
votes
1answer
6k views

Referencing PL/SQL variables in FOR loop

I have written a PL/SQL script to find the size of a long column in a table. Just to make the script generic I am passing the table Name and column name as variables , but I am getting an error saying ...
2
votes
2answers
3k 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?
5
votes
2answers
566 views

Oracle Advanced Queuing LIFO

With Oracle's Advanced Queuing is there a way to dequeue messages in a LIFO (last in first out) order? There is an indication through a lack of information that this is not an option, but perhaps ...
1
vote
0answers
271 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: ...
5
votes
3answers
1k views

Alternatives to an INSERT Trigger

Looking for an example code in Oracle to do the same. i.e. Remove an insert trigger on table T1 whose job is to create a row in T2 based on the derived data. How would this PL-SQL api look like? ...
9
votes
3answers
6k views

How to differentiate between SQL and PL/SQL?

I know the question might sound too stupid, but I never understood this part. SQL*Plus works with both SQL and PL/SQL. How do I know whether some code is SQL or PL/SQL? If my code has a for ...
5
votes
1answer
901 views

Using sys.diana and sys.pidl to parse PL/SQL

It seems to me that it should be possible to parse existing PL/SQL with the packages sys.diana and sys.pidl, but I have found nothing on the internet with the exception of a few scripts that seem to ...
4
votes
2answers
966 views

Identify which parameters were passed to a procedure (Oracle)

Procedures in PL/SQL can have default values for parameters, but the caller can pass values that are identical to the default values. Is there a way from within the PL/SQL to determine whether a ...