I want to run multiple sql select command after loggin to sqlplus and need to pass the value to variable to check the condition.
When I am running the below script I am getting the below error
----------------------
SP2-0734: unknown command beginning "return :va..." - rest of line ignored.
SP2-0734: unknown command beginning "return :va..." - rest of line ignored.
ID S_OWNER SOURCE_TABLE A_OWNER ARC_TABLE CONDITION_COLUMN PERIOD_VALUE PERIOD_UNIT
1 wedb Auction_table wedb Arc_Auction_tableAuction_date 15 Days 1 wedb Sales_table wedb ArcSales_table Sales_date 180 Days 1 hr Accounts_table hr Arc_Accounts_tableAccount_date 2 Years 2 concor Concur_table con Arc_Concur_table Last_update_date 4 Months
Like this 1000 entries are there in the control_table.
I want to pass all the columns from my control table dynamically.I am able to pass all the columns dynamically,except the PERIOD_VALUE.I have stucked up how to implement this condition.My aim is to get the data which is <= sysdate-PERIOD_VALUES based on PERIOD_UNIT.
For Example:
For Auction_date column I want to get the data which 15 days old. For Sales_date column I want to get the data which 180 days old. For Account_date column I want to get the data which 2 yers old. For Last_update_date column I want to get the data which 4 months old. [code]....
create or replace view spic_bags as select distinct(a.item_code) Material_Code,a.uom, (case when a.card_code = '60' then case when a.ccn <> '091' then [code]........
in this above view i have hardcoded dates, well how to pass parameter for this view without hardcoding? here i need to pass the date as a parameter from a select query to view the data from the above view!
I want pass the tablename to cursor. The tablename will be a dyanamic one, as we have the data in different sources tables[multiple systems - all systems will provide the data dump to oracle] with same set of columns.
I have inserted all the tablenames in a parameter table and based on conditions daat should be fethced from each system.
I need to pass the tablename to the cursor to fetech the coloumn list from the table(the table that is passed as parametr).
SQL> create table test(Amount number,Name varchar2(10)); Table created. SQL> insert into test values(100,'USA'); 1 row created. SQL> insert into test values(150,'NEWYORK'); 1 row created. SQL> insert into test values(200,'SCOTT');
[code].....
I would like to pass the operators from another table. How to pass the operators from other table.
In my task I am trying to pass a timestamp datatype as one of the input parameter to a procedure in the package.But I am not sure how to give data for it while executing and testing it from anonymous block.
CREATE OR REPLACE PACKAGE body ac IS PROCEDURE ac_ex_wbdb_keycats( In_Sale_Location_Id IN NUMBER, In_Start_Datatime IN TIMESTAMP, In_Stop_Datatime IN TIMESTAMP, v_refcur OUT sys_refcursor)
Is it possible to pass the file into CLOB column in real-time without DIRECTORY in oracle sp. I have not come across but would like to know is there anyway to pass the file directly from .net to oracle.
I wrote many functions before, but NEVER a function to pass through authentication...Here are some of the specs ...In this example, an institution will be accepting tuition and fee payments against bills that are stored in the our system. In addition to the minimum bill authentication parameters, the institution would like to pass us a full name to display when welcoming the user, control the users primary e-mail address, and restrict the payment method on a per user basis.
URL url This will be issued to you by your project manager. It should be of the form URL....
User Id user_id The unique identifier for the user within the institution. This is typically a student ID. (in our database)
Full Name full_name Users full name. Example "John Smith". (in our database) Email Address email Primary email address. in (our database) Payment Method paymentMethod Payment methods allowed for this user.
Allowed values: "none" :no payments accepted "ach" :ach is the only accepted "cc" :credit cards only accepted "ach_cc" :credit card and ach 16
Key key The shared key that is issued to you by your project manager.Time Zone tz The time zone you are located in. Valid values are:
"E" :Eastern time zone "C" :Central time zone "M" :Mountain time zone "P" :Pacific time zone 1 [code]....
I know I need to have cursor to check on the user_id, full name, email (those values are store in our DATABASE. The URL and the key are giving to me. Do I have to hard code the url in the cursor and stored in a variable so I can check that the url coming in (url in varchar2) is equal to the url in my cursor? The same with the key?
I'm migrating forms 6i to forms 10g. I have a mune which has parameters ,which are working in forms 6i. But when i try to comile in forms 10g its poping up error's.
i had a chart developed by graphics builder in which I am passing one parameter(ie year) and displaying the chart.Now i want to pass two parameter into chart.How to do this here i have attached my chart view and procedure which executes and display the chart from forms.
coding when button pressed
PROCEDURE CHART_DISPLAY IS pl_id ParamList; pl_name VARCHAR2(100) := 'tempdata';
[Code]....
In the above procedure i had one parameter directly am passing value of year '2009' now i want to pass two parameter like between 'year1' and 'year2' for getting chart between two years.
there is a way when doing the Rank Function in PL/SQL to pass the field that will be ranked as an override.
SELECT rank(p_ColumnAmt) within group (order by p_ColumnNm desc) rank INTO v_RnkNoAmt FROM Table_name WHERE ??????;
p_Column is the amount I am ranking p_ColumnNm is the actual field name to Rank.
When I pass the field name with an override I do not get the correct rank back. If I run the Select with the actual field name curr_1_mth_amt, I get the correct rank.
I have about 70 different field to Rank and do not want to make a procedure for each field.
If I want to pass lots of informations (100 parameter) from standard HR assignment form to my custom form. I am using fnd_function.execute('called form') to launch new form.
I thinking of creating 100 parameter object but not sure it has limitation of number or not. Another ways I googled are shared PL/SQL library or parameter list which I still doubt.
I had a requirement to execute a long running sql query. But the sql query had some parameters to be passed in and at some places i need to press "Enter" . I want to use nohup command and run the sql script using shell script concept. How can i pass the parameters and run the nohup command.
[URL]
I want to keep the sql script in a shell script and run it through nohup command.
how can i keep the above sql in a shell script and run through nohup command
declare cursor c is select employee_id from employees; type nst_type is table of employees.employee_id%type; emp nst_type; begin open C; loop exit when C%notfound; fetch c bulk collect into emp; end loop; close c; end;
Above is the sample code, and now i want to pass 'emp' as an input parameter to the procedure.
How can I do that, as emp is of nst_type type and I do not know how my procedure will recognize the datatype...
create FUNCTION Cov(p_str1_x IN VARCHAR2,p_str2_x IN VARCHAR2, p_str1_y IN VARCHAR2,p_str2_y IN VARCHAR2) RETURN NUMBER is TYPE ty_varchar_assoc IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100); v_x ty_varchar_assoc; v_y ty_varchar_assoc; v_inter ty_varchar_assoc;
[Code]....
Now i need to pass the values to the parameter from a table? How can i do that?
I am passing a string from vb.net through oracle store procedure. String consists of varchar values for eg '123456;I|N,2424424;O|A'. But how can i call these string values into array using oracle stored procedure? After getting these values into array , i wanted to loop through these values.
Create a PL/SQL block that declares a cursor called DATE_CUR. Pass a parameter of DATE data type to the cursor and print the details of all the employees who have joined after that date.
DEFINE P_HIREDATE = 08-MAR-00
Test the PL/SQL block for the following hire dates: 08-MAR-00, 25-JUN-97, 28-SEP-98, 07-FEB-99.
I don't know how to pass parameters. So far I have this:
SET serveroutput ON; DECLARE p_HIREDATE DATE := 08-MAR-00; CURSOR c_DATE_CUR(P_HIREDATE DATE) IS BEGIN OPEN c_DATE_CUR();
I am trying to perform a dml operation(insert) to insert data into a table. I created a procedure in which a insert statement is generated. In another procedure I am trying to execute this auto generated statement using execute immediate. For that I created 2 variables . First one contain the insert statement and other one contain the list of columns. I m passing these strings to execute immediate like this
Execute Immediate(v_query) using(v_col_list)
after execution i m getting the following error
ORA-01008: not all variables bound ORA-06512": at ' db_name.load_data' , line no 217
but when i tried it with list of column into using clause it is working like
Execute Immediate(v_query) using col1,col2,col3
I have more than 150 tables in the schema and i am creating a single procedure to load the data into base tables by using external tables. By passing the table name to procedure it generates the update and insert statements but i am getting error while executing the statements with dynamic sql.
1) how to pass date from .net using odp.net to oracle database for storage. so that it should not be affected if any one changes os(windows xp or 2003 /2008 server) date formate.
database and application both are on different server and can be on same server also. so if any one keeps different date formate on both OS then it should not get affected.
I have created a simple form & report based on 2 tables Cust & cntry.I just want to display all customers of region selected in the criteria form. I am uploading the files.
I am trying to pass many characters to the in mode parameter using procedure , but i am getting the below error.
ORA-06550: PLS-00172: string literal too long
CREATE TABLE USR_DETAILS ( LOAD_ID NUMBER NOT NULL, LOAD_DATE DATE, USER_VALUE VARCHAR2(4000 BYTE), USERID VARCHAR2(4000 BYTE) ) insert into user_details values('1','2/10/2011','PROD1','USER1'); insert into user_details values('2','2/10/2011','PROD2','USER2'); [code]......
I have written the code as below...
Procedure concept :
Here i will seperate the strings(input parameters) using comma(,) symbol . Here users may pass more than 5000 characters to single in mode parameter, so i have tried with clob datatype but no luck.
CREATE OR REPLACE procedure user_details_proc (user_value1 IN varchar2, user_value2 IN varchar2, user_value3 IN varchar2, user_value4 IN varchar2, user_value5 IN varchar2, userid IN varchar2 ) as [code].......
I want to pass Number of columns dynamically to a query. I got success in SQL.
SQL> select &column_list from emp; Enter value for column_list: empno,ename,sal
EMPNO ENAME SAL ---------- ---------- ---------- 7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300
14 rows selected.
But the same i need to achieve in pl/sql. I try with the Ref cursor, but not succeeded.
