A PL/SQL package is a schema object that groups logically related PL/SQL types, items, and subprograms. Use this tag for questions referring to the PL/SQL packages usage and behaviour.
Questions tagged [plsql-package]
240 questions
1
vote
0 answers
Excel UTL_FILE.GET_LINE result encoded
I'm trying to build a package with a procedure which takes .XLSX file, parse and insert to a table in database.
What I have achieved so far is the code below:
create or replace package body pkg_name
is
procedure fetch_and_insert_data
is
…
Adi
- 311
- 4
- 19
1
vote
3 answers
select inside a case statement not works
I wanted to write a select statement inside CASE THEN statement in PLSQL but it throws error. Please advise if I could write select statement inside THEN Statement.
An example similar to my requirement looks like below
SET SERVEROUTPUT…
Vinoth Karthick
- 905
- 9
- 27
1
vote
2 answers
Oracle PL/SQL does not recognize a DATE input?
I have package:
create or replace PACKAGE overload_pkg IS
PROCEDURE what_am_i (p_x IN VARCHAR2);
PROCEDURE what_am_i (p_x IN NUMBER);
PROCEDURE what_am_i (p_x IN DATE);
END overload_pkg;
and body:
create or replace PACKAGE BODY overload_pkg…
dmark
- 13
- 5
1
vote
3 answers
Oracle PLSQL : How to use substitution variable based on each value?
Declare
v_test varchar2(30) := 'enter value for A';
v_temp;
Begin
v_temp := &v_test;
dbms_output.put_line('v_temp :='||v_temp);
end;
While executing above PLSQL block, for substitution variable &v_test instead of prompting for Enter value…
Shwe
- 65
- 1
- 1
- 3
0
votes
0 answers
How to pass json string with multiple record (Array) as an input parameter to pl/sql stored procedure
I have been looking so many posts over the google, but unable to accomplish. I just reviewd Sohail Anwar's post and I have exactley the same requirement. It looks like you have resolved the issues. Can you please provide me a sample package & body…
Arshad
- 7
- 3
0
votes
0 answers
how to export images into xlsx file from oracle database
I need an help to export images from an Oracle table into xlsx file while calling a procedure, I'm able to export strings,date and numbers but I could not find a way to export images into xlsx file
seeking suggestions
I want to export table data…
Sha
- 11
- 2
0
votes
1 answer
How do I create a package that contains a stored procedure with 2 parameters and selects data from a table?
I can't seem to be able to run a package that contains a stored procedure that contains two parameters and outputs a table based on those parameters. The goal is to create a stored procedure that takes in the inputs, new_date and new_entity, and…
ns102030
- 1
- 1
0
votes
0 answers
Unable to access package from non-sys user
When I am try to access a package from normal user/schema (non-sys user) I am getting below error:
/* ERROR WHILE READING SOURCE
SQL Error [942] [42000]: ORA-00942: table or view does not exist
*/
When accessing this package from SYS user/schema…
Rudra
- 1
- 1
0
votes
0 answers
Output columns from SELECT statement from stored procedure
Please forgive me, I am very green with PL/SQL stored procedures. I am writing a stored procedure that will take in an input parameter (OFF_Set) and run a SQL statement using OFFSET and FETCH functions.
This procedure is called from an Oracle…
Nick
- 268
- 8
- 33
0
votes
1 answer
Unzip file using AS_ZIP package and insert into table
I have a .zip file in a table and I'm trying to figure out how to unzip that file using the AS_ZIP package.
I know how to compress using that package but I don't know how to decompress, any ideas?
Cesar Tepetla
- 39
- 1
- 8
0
votes
1 answer
Oracle Apex PL/SQL Package Global Variable Reset
So I have a package that makes a rest 'POST' request to an api to get a new random number and date only when the current_var is null and the current_date time has expired:
create or replace PACKAGE "MAIN_PKG" AS
g_current_var varchar2(4000);
…
another apex user
- 21
- 3
0
votes
0 answers
reading a sample.xml file from the path in oracle and storing the content in the clob variable using plsql
I have a sample.xml file in the path '/test/FILE'. Now i want to get this file from the given path and read the file and store the contents in the variable v_clob which is of datatype clob using PLSQL.
The v_clob will be passed to another procedure…
Niki
- 17
- 3
0
votes
1 answer
DROPPING TABLE FROM DIFFERENT SCHEMA
I wrote a Procedure which essentially used to drop table.
It works as expected , but the problem is As this Procedure is in "DW" schema and dropping table is in "SCRATCH".
Because of this, we get insufficient privileges.
Any way we can achieve this…
Leverage
- 63
- 7
0
votes
2 answers
How to write a program for multiplication table PL/SQL ask the user input a number
How to write a program for multiplication table PL/SQL ask the user input a number
this is the code display just table without input
Declare
i NUMBER:=0;
x NUMBER;
Begin
loop
i := i+1;
x :=2*i;
…
jawaher
- 1
0
votes
2 answers
how to create index by using dynamic sql?
----------- it is giving below error ,where i did mistake ---------------
declare l1 integer;
begin execute immediate q '!
begin
execute immediate ' CREATE INDEX S_ACCNT_ATT_P1 ON S_ACCNT_ATT(ROW_ID) PARALLEL 4 ';
execute…
Aman
- 19
- 3