I am new to PL/SQL, worked mostly on SQL server, I have to change the table name dynamically based on the parameter.and used a ref_cursor to display the results in a report. when I execute it throws me an error.
create or replace procedure test1 (
p_eod_date IN VARCHAR2,
p_link IN NUMBER,
c_rec IN OUT SYS_REFCURSOR)
[code]....
below requirement..
We have certain records like SQL, PL/SQL, Reports, Forms, OAF etc in a table. We wanted to capture rating for each of these criteria. So we want a form to be displayed dynamically..
SQL 1 2 3 4 (1,2,3,4 represent radio buttons)
PL/SQL 1 2 3 4
Reports 1 2 3 4
Forms 1 2 3 4
OAF 1 2 3 4
If we add another row, XMLP in that table additional radio button should be displayed automatically..
Need to access data in a table base on user parameter input where the data is stored like a spreadsheet with column headings JAN, FEB, MAR... and the rows are the years. Is there a way to create a generic SQL statement so that I don't have to have 12 if statements in the procedure?
View 5 Replies View RelatedCan i change the property of item dynamically in pl sql coding. Actually i want that whenever the user press some button then that field(textbox) become non-database item of the database block. put some value in it and press another button to restore its property like before.
View 10 Replies View RelatedI am currently on a project which has a requirement of changing the number of records dynamically. Oracle Forms version 10g.
View 2 Replies View RelatedIs it possible to change size of page dynamically , if yes the how ?? How do you dynamically change the page size of D2k Reports
View 2 Replies View RelatedI have following requirement. Let say i have to generate a range based on "start number" and "end number" dynamically.Some kind of hash buckets.
e.g Start Number : 1 and End Number : 1001.
Also i want to divide that range based on some dynamic value like for above example 10 ranges of 100 each. and both 1 and 1001 should be included only once and the next row start number cannot be same as previous row end number.
Means
Range 1 1 -101
Range 2 102-200
Range 3 201-300
...
Range 10 901-1001
Is there any way of doing it automatically. I tried with Model clause. it works fine for even cases but for odd i have issues also when i take small start and end number i get an error.
SELECT case when ranges=1 then ranges else ranges+1 end Start_Id, ranges+round((1001-1)/10) End_Id
from
(
[Code].....
I want this to be generic for any values here 1 is Start Number 1001 is end number and 10 is the bucket. I need these parameters dynamic and want's the same kind of results for any values.
I have created report with the collections. Report query has been changing dynamically but i got problem with report header names and headers names has not been changing dynamically it is always showing like c001,c002....etc so, i have created global item(G_ITEM) in shared components--->Applications items after that I have created process(before headers) and assigning some value to G_ITEM and used &G_ITEM to column headres in IR Report but here &G_ITEM is not showing any value.
How can i achieve dynamic headers names by using *&G_ITEM(global items)*
My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.
SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty,
a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':'
|| b.mnfst_qty
FROM vw_acao_critical a JOIN vw_acao_critical b
ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr
[code]......
What will be the case when I need to concatenate for more number of values.
like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.
I'm presently creating a Graph on a repeating frame.
I made a query with a group that pointing to the repeating frame.
The problem : I want to change dynamically the Y and X axis Title.
The graph accept user parameter inside the XML file. But how to make it dynamically based on SQL query?
I try with formula, but can't make it work with a group. I have to put the formula at Report Level, but to make it dynamically for each group i have to make it work at the group Level.
how to use different where caluse based on different in parameter. say I pass 1,2 as parameter if it is 1 then the where condition > 10 else if it is 2 then the where condition <5 the where conditions are placed in cursor for loops.
View 2 Replies View RelatedI need to check the condition based what string I am sending. Suppose I am calling with
1.'a,b,c' then it should check all the if blocks.
2.'a,b' then it should check only 'a' and 'b' if blocks.
Is it possible to do so?
DECLARE
a VARCHAR2(5):=0;
b VARCHAR2(5):=0;
c VARCHAR2(5):=0;
PROCEDURE p1(
p_str VARCHAR2)
IS
BEGIN
[code]....... I
I have created the Procedure P_GET_CURRENCIES. I want to sort the cursor query based on the Input parameter P_SORT_ORDER. For example if i pass 2 for P_SORT_ORDER then my query should be sorted by 2nd column. But i'm not getting correct Output.
PROCEDURE P_GET_CURRENCIES(P_START_ROW_NUM IN INTEGER
,P_END_ROW_NUM IN INTEGER
,P_SORT_ORDER IN number
,P_CURRENCY_DATA OUT SYS_REFCURSOR
,P_RETURN_MESSAGE OUT VARCHAR2 )
AS
[code]....
I have two databases runing on same server. Both databases are oracle 11g R2 and platform OEL 5.5 When I run "show configuration"
DGMGRL> show configuration
Configuration - DGConfig
Protection Mode: MaxPerformance
Databases:
prm - Primary database
stb1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:SUCCESS
Everything is going well.Now I want to change dbfilenameconvert parameter, but getting an error :
DGMGRL> edit database prm set property dbFileNameConvert ='/u01/app/oracle/oradata/stb1,/u01/app/oracle/oradata/prm';
Property "dbfilenameconvert" updated
DGMGRL> show configuration
Configuration - GConfig
Protection Mode: MaxPerformance
Databases:
[code]...
It's same error when I change this parameter in SQLPlus.
How to change db timezone parameter. My application team has asked me to change the db timzone parameter by following sql.
ALTER database SET TIME_ZONE = '+10:00';
Is this way is the correct one , and do we have any impact on database and does it require a db bounce.
Below is the code as currently written which works fine for Delta records processing (based on the field called activ_dt).
/*
Custom extract
Project: XYZ Price Data Extract
Product: EOWin 4.02 - Oracle database
Use : Script to create the above XYZ Extract and spool the results to a text file
Input Parameters:
[code]....
My problem is I am trying to add a 3rd parameter which will tell the extract to pull all of the data or just the deltas. So I added &3 to the comments and I have tried putting CASE or DECODE functions into the WHERE clause with no luck. I also tried to do a CASE or DECODE with two different SELECTS, again with no luck.
An example of my failed attempt to add &3 and handle F or D
/*
Custom extract
Project: XYZ Price Data Extract
Product: EOWin 4.02 - Oracle database
Use : Script to create the above XYZ Extract and spool the results to a text file
Input Parameters:
[code]....
I need to order the result set with different data types based on the input parameter.
select * from scott.emp sc order by decode('&input_parameter',1,sc.empno,2, sc.ename);
If the input_parameter is equals to 1 then,ordering should be based on EMPNO which is Number data type.
If the input_parameter is equals to 2 then,ordering should be based on ENAME which is Character Data type.
Above query was failed for input_parameter 2,as we know that decode should return same data type.
I am reading in a selection of parameters. I have created a new variable which I want to set according to the value of one of the input parameters.
I am doing this straight after declaring the variable, but before the cursors and BEGIN statement It is throwing an error when I do this - but I have to do it before the cursors.the variable I am setting is: v_fptransType you can see the IF statement towards the end of the code.
the error I am getting is:Error(28,3): PLS-00103: Encountered the symbol "IF" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor The symbol "begin" was substituted for "IF" to continue.
beginning of the
create or replace
PROCEDURE "P_GLPOST" (i_entity IN varchar2, i_transType IN varchar2, i_startDate IN VARCHAR2,
i_endDate IN VARCHAR2, i_accountPeriod IN VARCHAR2, i_includeInternals IN NUMBER, i_chargeable IN NUMBER, i_trialPost IN NUMBER,
i_postingReport IN NUMBER, TESTER IN VARCHAR2) is
--set serveroutput on size 1000000;
[code].....
I have created a custom form which fetches certain records. There is a column called invoice_create_flag which is check box. By default when we query this form, value of check box is 'Y'. Another column is Amount.
Initially when form is called, all the records are selected. Hence it should show the total of all the records. Now If the users unchecks any record, System should deduct the amount of that particular record from the total amount.
Attached is form screenshot for reference.
I have a need to change the LOV of a page item's select list based on the selection in another page item's select list. So in other words, for a page item that is a select list, I need to use one LOV normally, and a different LOV if the value of a different page item is set to X.
A dynamic action would be useful here (on change where Select List 1 = x), but I don't know how to take the action of changing LOVsfor Select List 2 based on that action.
I want to change cursor_sharing, open_cursors and session_cached_cursors parameter values for SPFILE (Only SPFILE) using SQL Query.
I have found following SQL Queries, but I don't know these queries are only for SPFILE or not.
ALTER SESSION SET cursor_sharing='EXACT';
ALTER system set open_cursors=2500;
ALTER SESSION set session_cached_cursors=70;
I need SQL Queries only for SPFILE.
i have the below query
select to_char(report_date, 'YYYY MM Mon'), count(1) no_of_times
from (
select to_date('&&YYYYMMDD', 'YYYYMMDD')+rownum report_date
, mod(rownum,14) mod_result
from all_objects
[code]...
need to convert as procedure based on input date parameter.I will pass the input date from java environment and need to see the sql query output in front end.
I have a combo box,If I select a value from this combo box it should populate another Combo box automatically.
Example:-
state
sno cno statename
1 1 newyork
2 1 boston
3 1 florida
1 2 brisbane
2 2 sydney
country
cno country name
1 usa
2 Aus
if i select usa in first combo box in next combo box i have to populate only states that belong to that country.
BY example above:
If in first combo box i select AUS
then in next combo box i.e., in
second combo== brisbane
sydney only must be there .
i need the exact code with trigger.
I need to accept value for some variables using substitution variable. I have a number of variables for which I need to take the value on run time. I need to prompt a parameter based on the value provided earlier.
For example;
There is prompt to enter country. If the user enters country as India it should prompt for the state else it should not prompt for state.
How can this be handled in SQL?
I wrote an sql script (spooler) which runs a select query and produces results into a file.
I want to have the filename with the year attached. Can i pass a parameter when i run the script from SQL*PLUS
@'B: Year Of FinancingPLSQLspoolersSpooler_File_Creation' 01/01/2010 2010;
the first parameter 01/01/2010 is passed to the select query and taht works fine.
Since the output file contains 2010 data, I want the spooled file to mention in its name the 2010, so User's can know the file contains 2010 data in it.
So i wrote the statement within the spooler script
SPOOL X:HARSOrganizationsFinancialDataFor&2.txt
&2 should take 2010 has its value and produce the file FinancialDataFor2010.txt
how to use Substitution variables to produce the Filename i want.
I have a requirement to change the instance parameter of the primary database and standby in the data guard. Any procedure to do the same. I have to do it directly on production and there is no test set up so I cannot experiment . The procedure I am thinking of is as below.
1. Stop the standby
2. backup the primary
3. Change the parameter and restart to make it effective
4. Start the primary
5. Go to stand by
6. Change the parameter and mount the database.
7. Check if both are in sync and apply logs if not.
I have a sample report at this url:
workspace: homeworldusername: test_reportpassword: test_report APEX version is 4.2.3, database 11g release 2... Application #: 202 : IR REPORTS TEST Page in question is page #2
I have the report setup and need to make the following change... I have 2 columns: Comm and bonus,
if the bonus > comm, I need to change the text color of the bonus color to Red if the bonus < comm
I need to change the text color of the bonus color to Green if the bonus = comm, I need to change the text color of the bonus color to Black I have tried using code with the select and am NOT wanting that since it will be bundled with the data when downloaded as CSV or Excel.. Had thought about using the built in highlighting but that doesn't allow comparing a column to another.. Was thinking of a dynamic action, but not clear as to HOW to do it.
In a pl/sql procedure code, I created a normal table (create table) using dynamic sql. Then I used that table in procedure for further processing. But while compiling, it gave error that table does not exist. I can understand that he table is not present in DB, so the error came. But at the same time I need to create a table dynamically, use it and drop it. Does it mean that I need to make every query referencing that table as dynamic ??
View 1 Replies View Relatedi need to create a replica of an existing table in same schema dynamically so as to reflect the modifications on replica table which has been done on primary table.this table we need to maintain as snapshot,like if a column is added or deleted then it should automatically be added or deleted on the replica table.how should i approach this.
View 4 Replies View RelatedI have 12 different tables
Record_jan
Record_feb
.
.
.
Record_nov
Record_dec
Based on the month i need to do some i/o operations from these table.
For eg: If it is january then Record_jan table should be accesed or for nov Record_nov table should be accesed.
If I try to store the table name in some variable and then give the variable name in place of table name then it gives error.
var:=Record_month;
Select * from var;
this gives error.