tagged [plsql]
Get a list of all functions and procedures in an Oracle database
Get a list of all functions and procedures in an Oracle database I'm comparing three Oracle schemas. I want to get a list of all the functions and procedures used in each database. Is this possible vi...
Oracle SQL: Use sequence in insert with Select Statement
Oracle SQL: Use sequence in insert with Select Statement Basically I want to run the following query: ``` INSERT INTO historical_car_stats (historical_car_stats_id, year, month, make, model, region, a...
Sleep function in ORACLE
Sleep function in ORACLE I need execute an SQL query in ORACLE it takes a certain amount of time. So I wrote this function: and I call in this way ``` SELECT TEST_SLEEP(10.5
Query performance difference pl/sql forall insert and plain SQL insert
Query performance difference pl/sql forall insert and plain SQL insert We have been using temporary table to store intermediate results in pl/sql Stored procedure. Could anyone tell if there is a perf...
- Modified
- 20 Apr at 05:34
C#: Oracle Data Type Equivalence with OracleDbType
C#: Oracle Data Type Equivalence with OracleDbType --- ## Situation: I am creating an app in C# that uses Oracle.DataAccess.Client (11g) to do certain operations on a Oracle database with stored proce...
- Modified
- 17 Oct at 21:45
Convert varchar2 to Date ('MM/DD/YYYY') in PL/SQL
Convert varchar2 to Date ('MM/DD/YYYY') in PL/SQL I need to convert string from varchar to Date in 'MM/DD/YYYY' format. My input string is '4/9/2013' and my expected output is '04/09/2013'. i.e. 2 dig...
Default Values to Stored Procedure in Oracle
Default Values to Stored Procedure in Oracle I have a `stored procedure` as follows. When I execute the above procedure It will print `X--Y`. The input parameters are not defaulting to the speci
- Modified
- 25 Sep at 19:32
ORA-01438: value larger than specified precision allows for this column
ORA-01438: value larger than specified precision allows for this column We get sometimes the following error from our partner's database: The full response looks like the following: ``` ORA-01438: v...
Oracle Error ORA-06512
Oracle Error ORA-06512 Just can't figure out why it gives me ORA-06512 Error ``` PROCEDURE PX(pNum INT,pIdM INT,pCv VARCHAR2,pSup FLOAT) AS vSOME_EX EXCEPTION; BEGIN IF ((pNum 14)) THEN RAIS...
- Modified
- 19 Oct at 04:32
ORA-06508: PL/SQL: could not find program unit being called
ORA-06508: PL/SQL: could not find program unit being called I am using oracle 10g and toad 11.5. I am trying to call an api from an anonymous block. If I recompile the api after adding `dbms_output.pu...
How to Run the Procedure?
How to Run the Procedure? Here the Package.. Let's consider the PROC.. ``` Create procedure gokul_proc( pId in number, pName in varchar2, OutCur OUT G_PKG_REFCUR.rcDataCursor ) is BEGIN Open O...
Selecting Values from Oracle Table Variable / Array?
Selecting Values from Oracle Table Variable / Array? Following on from my last question ([Table Variables in Oracle PL/SQL?](https://stackoverflow.com/questions/1573326/table-variables-in-oracle-pl-sq...
PL/SQL print out ref cursor returned by a stored procedure
PL/SQL print out ref cursor returned by a stored procedure How can I fetch from a ref cursor that is returned from a stored procedure (OUT variable) and print the resulting rows to STDOUT in SQL*PLUS?...
- Modified
- 22 Dec at 22:34
Passing an array of data as an input parameter to an Oracle procedure
Passing an array of data as an input parameter to an Oracle procedure I'm trying to pass an array of (`varchar`) data into an Oracle procedure. The Oracle procedure would be either called from SQL*Plu...
- Modified
- 21 May at 21:5
PL/SQL ORA-01422: exact fetch returns more than requested number of rows
PL/SQL ORA-01422: exact fetch returns more than requested number of rows I get keep getting this error I can't figure out what is wrong. > DECLARE * ERROR at line 1: ORA-01422: exact fetch returns ...
collapsing NULL values in Oracle query
collapsing NULL values in Oracle query I often write queries wherein I pivot data and end up with NULL values that I want to collapse. E.g. data like the following: I then do an outer query like so: `...
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06502: PL/SQL: numeric or value error: character string buffer too small I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and whi...
- Modified
- 25 Mar at 11:44
Select * from Table and still perform some function on a single named column
Select * from Table and still perform some function on a single named column I'd like to be able to return all columns in a table or in the resulting table of a join and still be able to transform a d...
Printing the value of a variable in SQL Developer
Printing the value of a variable in SQL Developer I wanted to print the value of a particular variable which is inside an anonymous block. I am using Oracle SQL Developer. I tried using `dbms_output.p...
- Modified
- 25 Oct at 09:44
How to select into a variable in PL/SQL when the result might be null?
How to select into a variable in PL/SQL when the result might be null? Is there a way in to just run a query once to select into a variable, considering that the query might return nothing, then in th...
Create an Oracle function that returns a table
Create an Oracle function that returns a table I'm trying to create a function in package that returns a table. I hope to call the function once in the package, but be able to re-use its data mulitple...
UTL_FILE.FOPEN() procedure not accepting path for directory?
UTL_FILE.FOPEN() procedure not accepting path for directory? I am trying to write in a file stored in c:\ drive named vin1.txt and getting this error .Please suggest! HERE is the code