All Questions
30 questions
0
votes
1
answer
184
views
ORACLE - Why does a dynamic SQL statement using DBMS_RANDOM fail when called from a stored procedure but not from an anonymous block
EDIT (8/25)
Thanks to @Alex Poole for providing the answer below but I wanted to share additional detail on these role limitations around PL/SQL objects as it helps explain not only how Oracle is ...
user2858650
0
votes
1
answer
76
views
How to Call a Pkg/Procedure Executing an API From a Pkg/Procedure. The API name needs to be Dynamic and Has In and Out Parms
We use APIs, baninst1.PP_DEDUCTION.p_update and baninst1.PP_DEDUCTION.p_create, to maintain our payroll tables of benefit/deduction data. Numerous packages utilize the APIs. We would like to create a ...
- 1
0
votes
1
answer
2k
views
Stored procedure using Dynamic SQL to create new table in Oracle
The question is as follows: create a stored procedure (NEW_TABLE) with two strings as input parameters:
String 1: Name of the table to be generated
String 2: Name of columns and their datatypes.
For ...
- 115
0
votes
1
answer
904
views
Insufficient INHERIT PRIVILEGES for a stored procedure ORACLE
I am having a stored procedure in user META. This SP truncates a table in user STAGE.
CREATE PROCEDURE META.sp_truncate_tablex
AUTHID CURRENT_USER
as
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE STAGE....
- 441
0
votes
3
answers
3k
views
How Can use Execute Immediate statement be nested inside in the Execute Immediate
I have a fixed Execute immediate narrative. The sql_text in it will only bring in one parameter to do a simple syntax query
example
EXECUTE IMMEDIATE sql_text USING SN RETURNING INTO MSG;
sql_text is ...
0
votes
2
answers
478
views
Pass a table name and time stamp variable to a PL/SQL
I am writing a below PL/SQL code in SQL developer to delete data from a table with a timestamp column in the where condition. How can I modify this code to pass the table name and the timestamp value ...
- 59
0
votes
1
answer
66
views
I want to create a table and insert a row in same table during run time using dynamic sql
I have created a procedure that accepts table name, 2 column names and 2 values that needed to be inserted on create table.
create or replace procedure SP_TABLE(P_TAB IN VARCHAR2,P_COL_1 IN VARCHAR2,...
- 79
0
votes
1
answer
1k
views
Insufficient privilege when create dynamic table in stored procedure while in PL/SQL block table has been created [duplicate]
When i execute stored procedure which contain EXECUTE IMMEDIATE along with create table statement inside the stored procedure then getting error while execute this Stored Proc.
Here is the Stored proc-...
- 503
1
vote
1
answer
210
views
Oracle Stored Procedure runs with ORA-01007 variable not in select list
I got a Stored Procedure (compiled with no error/warning) like following :
create or replace procedure my_schema.SP_UPDATE_MEMBER(noP in varchar2, nameP in varchar2, idNoP in varchar2, birthdayP in ...
- 3,190
1
vote
2
answers
157
views
Add a table name dynamically in a stored procedure
I've been trying to find ways to change the table name dynamically in a query but not found one so far that works. The query has repetitive code (around 150 lines of code inside each case statement) ...
- 171
1
vote
2
answers
865
views
Procedure Results with Variable Column Alias - PLSQL/Oracle
I've got this procedure working in TOAD/PLSQL but, would like the alias for the first column to be set to the field_name argument passed to the procedure. I think I need to build the query as a ...
- 39
1
vote
3
answers
130
views
DB Procedure to Dynamically update DB columns
Requirement is to update in db column only those column which are edited from frontend.
Logic i am using is to send 2 arrays from java code to DB procedire.
1st array is: Column_array which contains ...
- 83
4
votes
2
answers
7k
views
Getting Insufficient Privilege while creating Oracle sequence using procedure but not using plsql block
Begin
Execute immediate 'CREATE SEQUENCE seq1 MINVALUE 1 MAXVALUE 99 START WITH 1INCREMENT BY 1 CACHE 20';
End;
Sequence creating successfully.
Create or replace procedure proc_seq as
Begin
Execute ...
- 157
0
votes
1
answer
1k
views
Getting a ORA-00933: SQL command not properly ended error when I try to run an Oracle stored procedure that I wrote
As part of a project, I'm working on data mapping of the peoplesoft records and fields in use at our company. There are more than 25K fields that I have to document but it gets tedious and will take a ...
0
votes
1
answer
206
views
object outer does not exist
I have been trying to execute this procedure but it is giving this error and I have no idea what is causing it. Its been 2 days and I am still stuck in it. Basically, the procedure should run the ...
- 195