I'm trying to execute a procedure within a Declare/Begin/End statement and using variables as input parameters in my procedure but I keep on getting an Invalid SQL Statement Error. My code is below:
declare
START_dt VARCHAR2(30);
END_DT VARCHAR2(30);
begin
SELECT '01-APR-2011'
INTO END_DT
FROM DUAL;
[code]....
The table the procedure is pulling data from doesn't have proper date/time stamps but my procedure takes the varchar dates above and turns them into dates in the procedure so the input date parameters are left as just string characters.
I am getting errors while executing the following block.
create TYPE c_Rec as object(a VARCHAR2(1), b NUMBER); DECLARE -- TYPE c_Rec as object(a VARCHAR2(1), b NUMBER); TYPE c_collection IS TABLE OF c_Rec; l_coll c_collection := c_collection(); BEGIN [code]........
error
06530. 00000 - "Reference to uninitialized composite"
How to execute Sql Server procedure from Oracle.I have created a dblink and when calling directly to that procedure it is throwing error.
--creationg sqlserver procedure create proc getRecords as print 'testing'; --creating db link create database link remote connect by ss identified by ss using 'remote'; [code]....
I have found DBMS_HS_PASSTHROUGH package is capable of doing it but not sure how to do. Nathan
I've the following package having a procedure with OUT parameter as refcursor .The following is the sample example having same structure with different table name,exception handling in my dev environment
CREATE OR REPLACE PACKAGE TEST_PACK IS Type refCursor is REF CURSOR; PROCEDURE TEST_PROC (out_data out refCursor); END; [code]...
i am getting the follwoing errror ORA-00932: inconsistent datatypes: expected - got.
I need to execute a procedure based on a value in a form. So the procedure name will be changing for value selected in a list.
I need to know a method where i could store the procedure name in a table and when ever i select a value from the list, the respective procedure needs to be executed.
I'm not attaching any tables / data..etc...I just want to know how to pass the record type to a procedure (which are actually obtained from a table) -- see ** below where I'm getting an error..Need to pass the whole record type "l_shl_order_msg"
CREATE OR REPLACE PROCEDURE CM_BUILD_MSG_PRC (P_IN_BLD_MSG_CURSOR IN SYS_REFCURSOR, P_OUT_BLD_MSG_CURSOR OUT SYS_REFCURSOR) IS l_shl_order_msg CRAE_INTERFACE.GLB_VAR_PKG.deid_SHELL_order_typ; V_MSG_SHELL_NAME VARCHAR2(1000); V_MESG_TEXT_SEGMENT VARCHAR2(1000); V_TEXT VARCHAR2(1000); V_MSG_TEXT VARCHAR2(4000); V_MSG_FINAL_TEXT VARCHAR2(4000); V_MSG_PROC VARCHAR2(1000); V_SQL VARCHAR2(4000); V_CNT NUMBER; L_STATUS VARCHAR2(100); L_REASON VARCHAR2(1000); [code]...
I get an error saying that "wrong number or types of arguments in call to ||"..Not sure how to pass record type dynamically...
Procedure: CREATE OR REPLACE procedure test (a number, b varchar2) is
begin dbms_output.put_line(a ||'->'||b); end;
Anyonymous Block:
begin exec test(1,'m'); end; / When i run this i am getting this error ORA-06550: line 2, column 7: PLS-00103: Encountered the symbol "TEST" when expecting one of the following [code]...
create or replace procedure test as stmt varchar2(2000); begin EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tt_Local(ID VarChar2(38)) ON COMMIT PRESERVE ROWS';
stmt := 'INSERT INTO tt_Local SELECT cardnumber FROM cards'; execute immediate stmt; end;
-- when am trying to execute this
begin test; end; -- showing ora-01031, insufficient privileges.
After creating this procedure...we r not able to execute it...
SQL> ed Wrote file afiedt.buf 1 create or replace procedure dyn_pro1 2 (tablename varchar2,colname varchar2, prodid number) is 3 cmd varchar2(100); 4 begin 5 cmd := 'delete ' || tablename || ' where'|| colname|| '=' ||prodid; 6 execute immediate cmd ;--using College_id; 7* end; SQL> /
Procedure created.
SQL> exec dyn_pro1('stu_4','COLLEGE_ID',200); BEGIN dyn_pro1('stu_4','COLLEGE_ID',200); END; * ERROR at line 1: ORA-00933: SQL command not properly ended ORA-06512: at "APPS.DYN_PRO1", line 6 ORA-06512: at line 1
I am trying to execute the SQLPLUS command (CONNECT) from the stored procedure.It is throwing below error message.
Stored Procedure:
SQL> select user from dual 2 ; USER ------------------------------ SYS SQL> create or replace 2 PROCEDURE PROCEDURE1 AS 3 BEGIN 4 sqlplus sys/sys@D as sysdba; 5 --execute immediate 'create user 'kkk' identified by 'kkk'; 6 END PROCEDURE1; 7 /
Warning: Procedure created with compilation errors.
SQL> show err Errors for PROCEDURE PROCEDURE1: LINE/COL ERROR -------- ----------------------------------------------------------------- 3/9 PLS-00103: Encountered the symbol "SYS" when expecting one of the following: := . ( @ % ; The symbol ":=" was substituted for "SYS" to continue. [code]....
PROCEDURE CALCULATE_CASH_REBAL( P_Account_id IN VARCHAR2, P_Txn_Ccy IN VARCHAR2, P_Allocation IN VARCHAR2, l_lty_id IN VARCHAR2 ) IS l_balance_fmt NUMBER := 0; BEGIN [code]....
it should be updating l_balance_fmt field for all the records in the loop. my question is when it loops to the next cash txn record, will it take the previous value of l_balance_fmt ? it should start again and take 0 as the balance_fmt and add to that..
In Database A I have a series of config tables and stored procedures/functions In Database B I have a lot of tables.
I would like to execute my stored procedures and all associated functions in database A on my data in database B.
Now I've figured out that creating a database link enables me to do SQL selects on data in both A and B...but how do I run SP/Funcs ? I've read something about packages but not sure if I'm heading in the right direction.
Do I need to create a simple synonym ? Can I use the existing DB link ? or is there a special way of calling them, or...
I like the A/B database set up since I can keep battle tested code in one location and have it work across multiple dbs...saves me having to create everything in every database.
create or replace procedure my_proc(p_user in varchar2) is l_cursor sys_refcursor; l_query constant varchar2(1000) := 'select a' || 'from ' || p_user || '.user_table' || 'where param0 = :x' || 'and param1 = :x' || 'and param2 = :x'
[Code]...
Suppose I execute my_proc many times and for multiple values of p_user. For performance reasons, will l_query be stored in the cache as I am using bind variables or it will not since I have the concatenation with p_user value ?
CREATE OR REPLACE PROCEDURE test IS CURSOR cusers IS SELECT user_name, user_date FROM users; uname users.user_name%TYPE; udate users.user_date%TYPE; BEGIN OPEN cusers;
[code].....
When I try to execute this procedure I get following error:
ORA-00900: invalid SQL statement
Compilation of procedure is successful.
SQL code for creating the USERS table is here:
CREATE TABLE "USERS" ("USER_ID" NUMBER(10,0) NOT NULL ENABLE, "USER_NAME" VARCHAR2(50) NOT NULL ENABLE, "USER_EMAIL" VARCHAR2(50) NOT NULL ENABLE, "USER_PASS" VARCHAR2(50) NOT NULL ENABLE, "USER_DATE" DATE NOT NULL ENABLE, CONSTRAINT "USERS_PK" PRIMARY KEY ("USER_ID") ENABLE )
The goal is to create a stored procedure that will retrieve multiple values from a table.
GUI is in Java and they will trigger our procedure to show list of all employees and their roles , doj etc.
So I wrote the following procedure. --------------------------------- create or replace PROCEDURE emp_test( c_cursor OUT SYS_REFCURSOR) AS BEGIN OPEN c_cursor FOR SELECT emp_name, emp_doj, emp_role FROM emp_table ; END; ---------------------------------
I'm using sql developer, stored procedure is compiled and I can manually run it by right clicking on the procedure and click 'Run'.
When I intend to run it by executing the script "Execute Procedure name ", I get errors.
In SQL Developer, I open new SQL file and key in
EXECUTE emp_test;
Highlight it and run the script, here is the list of errors that I get.
------------------------------------------- Error starting at line 18 in command: execute frm_lst Error report: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'emp_test' ORA-06550: line 1, column 7: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s: %s" *Cause: Usually a PL/SQL compilation error. *Action: --------------------------------------------
Issue 2:
Instead of using cursor, is there a way to use multiple parameters and insert the data retrieved from select column_name from table into these parameters and build it in a stored procedure. I tried something like below which did not work. ____________________________________________________
CREATE OR REPLACE PROCEDURE emp_test1 (e_name OUT emp_name%TYPE, e_dob OUT Edob%TYPE) IS BEGIN SELECT emp_nam, Edob INTO e_name, e_dob FROM emp_table END emp_test1; End; ______________________________________________________
Just so you know, there is no input parameter or input feed, when called the procedure should return all the values.
Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.
Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?
Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS BEGIN OPEN p_refcursor FOR select * from Test_tab WHERE item=p1; END; ------------ Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS v1 varchar2(100):=p1; BEGIN OPEN p_refcursor FOR select * from Test_tab WHERE item=v1; END;
I could execute a package for eg if i had a package with procedures related to statistics and i run them each night, could i just do an exec on the package and it would run all those procedures??
Its not possible but i could call each procedure from ONE procedure
how to use the MERGE Statement. actually I've used oracle Merge Statement before and it works very well. However today I tried to use and perform a command like that:
Merge into myTable mt using ( select 'data' field1, 'data2' field2, ect from dual union select 'data' field1, 'data2' field2, ect from dual union
[code]...
This has not worked.What am I doing wrong?What could I do to solve this problem and axecute this statement sucessfully?
there are 4 jobs scheduled in oracle dbms_job. 3 jobs will run everyday at 4.00AM. 1 job will run at every hour.Daily jobs are running fine. But hourly job is not executing automatically. If forced (exec dbms_job.run(<enter here job number>), this execute fine.
job_queue_processes=5 total jobs in schema=2503 total jobs in db = 2614
Even there are many jobs scheduled, next_date for 2234 jobs are lesser than the sysdate. Again in 269(2503-2234) jobs, 2265 are having NULL in the interval column.
Database 2(sm02): ============= oracle, 9.2.0.6
there are 4 jobs scheduled in oracle dbms_job. 3 jobs will run everyday at 4.00AM. 1 job will run at every hour.All the jobs are not running automatically. If forced (exec dbms_job.run(<enter here job number>), these execute fine.
job_queue_processes=5 total jobs in schema=7 total jobs in db = 7
I planning to follow the below steps to avoid the above issue.
1.) Restart the job queue process by executing alter system set job_queue_processes=0
2.) Increase the value for the job_queue_processes.
3.) Restart the database
But I got stuck in the 2nd step. what value I need to put for this job_queue_processes parameter?
How to dynamically execute a select command whether it is retrieved a single record or multiple record in oracle database 10g. i have tried with the command execute immediate but it was not successful.
is it possible can i delete or drop multiple tables in a single drop and delete statement.
