SQL & PL/SQL :: Display Grid Output Using Execute Immediate Or SysRefCursor Constructs?
Mar 21, 2011
I am in a state of confusion and requires a solution for this urgently:I used to use 'Execute Immediate' statement or 'Sys_RefCursor' for forming parts of query dynamically based on user inputs and then finally execute them at runtime for updating database / saving results into a variable etc.
But as per the latest requirement, I wanted to have the resultset of such a query displayed in the grid area / results area of a database tool; say, SQL Navigator, Toad or PL/SQL developer.; just like you see the results of a normal 'SELECT * FROM TABLE1'; Is this really possible ??
see the below examples:
Eg: for Execute Immediate:
declare
vtest VARCHAR2 := NULL;
vdisplay VARCHAR2;
begin
vtest := 'SELECT FIELD_1 INTO vdisplay FROM TABLE_A';
vtest := trim(vtest) || 'WHERE FIELD_2 = ''XYZ''';
execute immediate TRIM(vtest);
dbms_output.put_line(vdisplay);
end;
---> This would display output into the 'Output' tab of the database tool / editor;
Eg: for SysRefCursor:
declare
vCur SYS_REFCURSOR;
vdisplay VARCHAR2;
begin
OPEN vCur for 'SELECT NULL' || var1 || 'WHERE FIELD_1 = ' || Value1;
FETCH vCur INTO vdisplay;
dbms_output.put_line(vdisplay);
end;
---> Even this would display output only into the 'Output' tab of the database tool / editor;
===> But my requirement is: Different from these, is there any way to get this output displayed into the normal output grid / result area of a database tool ? just like you see the results of a normal 'SELECT * FROM TABLE1' query ?
I've noticed that the backup files produced by RMAN (Database is 11.2.0.2.0 Enterprise Edition) )on a RHEL5.5. server which is running ASM have group permissions assigned to ASMADMIN (so the file permissions are assigned to ORACLE:ASMADMIN) as opposed to OINSTALL or any other group.
While this is not an issue (it's not currently impacting us in any way) I'm curious as to how and why this happens. I suspect it is related to the fact that the oracle executable has these permissions also but I can't find any material to confirm my suspicion.
I have some Javascript in an Apex page, running in 'Execute when page loads'. It does some stuff to a results grid. It works fine on page load, but then doesn't run when I page through, or sort the grid. I presume this is because the grid is being sorted / paged using Ajax, which means the page isn't being fully loaded. Is there anywhere else I can put the code so it gets applied to the grid on sorts and paging?
I am not sure if this is even possible however I will give it a try here. is there a way to take a value from an In List that doesn't exist in the table and get SQL PLus to output that value with an assigned decode construct. example.
1 select decode(count(object_name),1,'EXIST',null,'NOT EXIST',0,'NOT EXIST'), 2 object_name, object_type 3 from dba_objects where object_name in ( 4 'GV_$METRICNAME', 5 'GV$METRICNAME', 6 'GV_$METRICGROUP', 7 'GV$METRICGROUP', 8 'ABC' 9 ) 10* group by object_name, object_type SQL> /
having trouble displaying stuff on screen. It compiles the program successfully but just doesnt show any output Try using this (SET SERVEROUTPUT ON SIZE 4000) does nothing though. here my code
DECLARE /*This program coverts a date from one fomat eg 1/31/10 into another format eg January 31,2010 */ /*declaring variables assign a date that is in one format*/ EXAMPLE_DATE DATE := TO_DATE(' 1/31/06',' MM/DD/YY' ); /*finding the position of where the forward slash are*/ position_finder NUMBER := INSTR(EXAMPLE_DATE,'/');
I'm trying to install the Grid Infrastucture + Oracle db 11.2.0.3 on a Solaris 11. I'm used to work with Red-hat, so i don't remember in having such a problem. The issue is the following, when trying to run "runInstaller" as oracle user:
Checking Temp space: must be greater than 180 MB. Actual 1527 MB Passed Checking swap space: must be greater than 150 MB. Actual 2104 MB Passed Checking monitor: must be configured to display at least 256 colors >>> Could not execute auto check for display colors using command /usr/openwin/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<
Some requirement checks failed. You must fulfill these requirements before continuing with the installation,Continue? (y/n) [n] n
User Selected: NoDoing some researches, would be to install the SUNWxwplt package.i have installed all the required packages:
root@sol11:/mnt/sf_Compartilhamentos# pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibms SUNWpool SUNWpoolr SUNWsprot SUNWtoo SUNWlibm SUNWuiu8 SUNWfont-xorg-core SUNWfont-xorg-iso8859-1 SUNWmfrun SUNWxorg-client-programs SUNWxorg-clientlibs SUNWxwfsw SUNWxwplt system SUNWarc Lint Libraries (usr) system SUNWbtool CCS tools bundled with SunOS system SUNWfont-xorg-core X.Org Foundation X11 core fonts system SUNWfont-xorg-iso8859-1 X.Org Foundation X11 iso8859-1 fonts [code]....
xhost: unable to open display "192.168.0.20:0.0"I think i made all the necessary configurations (?).
When I execute multipath -ll command that time display only 3 path (orafra2,oradata2 and oradata1), not display other 2 path (orafra1 and data1). I have configure 5 path in /etc/multipath.conf file. What is the reason behind it.
(Oracle Grid & Real Application Clusters Oracle Grid Computing with RAC) from rampant tech press. I have already ordered it 3 months back and still have not received it as its not printed yet or as i am told is in press. I would be glad to have a copy soon if its published.
I'm working with old code that uses dbms_sql.execute to build/execute dynamic sql. In our case, the user can select varying columns(I think up to 20) with different where conditions as needed.
After building the sql, here's an example
WITH ph AS (SELECT ph.* FROM po_header ph WHERE 1 = 2), pf AS (SELECT DISTINCT pf.order_id, pf.fund FROM po_fau pf, ph WHERE 1 = 1 AND ph.order_id = pf.order_id
[code]....
Where table records for
po_header = ~567746 po_fau = ~2153570
and PK "order_id" is a NUMBER(10) not null and a snippet of the code looks like
nDDL_Cursor := dbms_sql.open_cursor; dbms_sql.parse(nDDL_Cursor, sSQLStr, 2); FOR x IN 1 .. nCols LOOP sCols(x) := ''; dbms_sql.define_column(nDDL_Cursor, x, sCols(x), 100); END LOOP; nError := dbms_sql.execute(nDDL_cursor);
why when the "execute" statement is fired off the elapsed time takes ~4.5 seconds but If I change "1 = 1" above to "1 = 2" it takes ~.2 seconds. If I run the above query interactively it takes ~.2 seconds. Shouldn't the above query when joining
ph.order_id = pf.order_id
return zero rows back instantly or does the "dbms_sql_execute" do some other type of parsing internally that takes cpu time.
DECLARE JOBSFILE UTL_FILE.FILE_TYPE; -- TAKE ALL JOB TITLES FROM JOBS CURSOR JOBSCUR IS SELECT * -- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID FROM TARGET_UCM ; [code].......
this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.
We can execute dynamic sql using both execute immediate and ref cursor..But what is the difference between the two and performance-wise which is better?
Currently I have a requirement where I need to create 2 more output rows using each result row.
In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.
I'm trying to test moving a single instance 11202 database to single instance w/ grid infra.
Here is what I've done:
1. Install a database (11202), single instance and create a database 2. Install Grid Software only (user: grid) 3. start the cluster 4. "srvctl add database -d orclsidb -o $ORACLE_HOME" to register the database with grid. 4.1> I was able to start/stop the database with srvctl command here onwards 5. configure disks using asmlib and start the asm 6. "srvctl add asm" to register asm with grid. 7. PROBLEM ... when I try to "backup as copy database format '+ASM_DATA_DG';" from oracle user it errors out as below:
RMAN> backup as copy database format '+ASM_DATA_DG';
Starting backup at 24-AUG-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=134 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00011 name=/fs0/oracle/oradata/orclsidb/sjc883p_indx_large_01.dbf RMAN-00571:
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/24/2012 08:53:29 ORA-19504: failed to create file "+ASM_DATA_DG" ORA-12547: TNS:lost contact ORA-15001: diskgroup "ASM_DATA_DG" does not exist or is not mounted ORA-15055: unable to connect to ASM instance ORA-12547: TNS:lost contactConsidering "TNS: lost contact" I tried to see if grid listener is aware of ASM instance:
[Code]....
All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=usracdb03.rwcats.com)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production Start Date 22-AUG-2012 06:08:53 Uptime 2 days 2 hr. 50 min. 48 sec Trace Level off
I Had installed 11g grid on 32 bit machine. Grid was working fine till yesterday and was also able to monitor agents on 4 diff boxes. Agents installation was smooth using the pull method. But from yesterday onwards grid is not working. OMS service on windows is not getting started. node manager,listener,database service, web tier all services are running fine but for some reasons the Windows OMS service is not coming up .
I also tried to start manually oms_home/emctl start oms but the service doesn't come up.
I am using OEM Grid control 10g for monitoring/reporting purposes and a bit new to OEM.I am able to generate database alerts sent via email say for DB_abc1 and also generate reports.I installed Oracle 10g agent on a another box and lets say with DB_abc2 running on it,and did not realise that there was a database control already installed on that box, so I
1.Uninstalled database control on DB_abc2 box. 2.Uninstalled Oracle 10g agent and re-installed it on DB_abc2 box. 2.Was able to configure the DB_abc2 database from OEM Grid control gui ie the dbsnmp user password and was able to connect to it.
But when I put in alert for things like ORA-XXXX errors I am not getting any emails, also when I put in database usage reports(to be sent via emails) the reports are not picking up any data, they are empty.
I checked the agent on the box in question and the heart beat is OK, and I manually uploaded using "emctl upload" and it is sucessful.The only alerts I am getting for DB_abc1 is the agent up/down thats it.
DB_abc1 is a 9208 database and DB_abc2 is 10201 database.
I need to install the new Oracle Grid on a new virtual machine. Currently the new Grid VM server has a different name and IP. After it is setup and tested it will eventually take the place of the old production server using its original IP and name. If I install the new Grid now on this new VM with different name and IP address would I have to reinstall the agents on each of the target database servers?
Can we collect information(generate report) about user sessions connected, database hit ratios, memory usage and other system metrics of all target databases from OEM grid control repository database. Which tables/views in repository DB will be holding these information.
what is JTF GRID functionality,i have use that grid in custom form. how to proceed with that and what are steps to be taken to display JTF GRID Functionality ?
Is the 12c version of the Grid Control only for Oracle Cloud? Not completely sure of exactly what cloud computing is. Looks like a conglomeration of shared resources. Anyway we are converting all of our servers over to vmware virtual machines except the ones we manage in other states. I am upgrading each database to 11 r2 as we convert the servers. Anyway my Grid server is next to convert to a virtual machine. I am using Grid Control 10.2.0.5.0 now. I want to upgrade to the latest version of the Grid control. 12c seems to stress and boast about managing the "Oracle Cloud" to manage regular single node Oracle servers as well as vmware virtual machines?
I'm trying to install Grid Infrastructure for Oracle RAC 11g. I followed each step of pre installation tasks, but when I run run Installer it blocks without any error message.
What I see on my shell is this: ./runInstaller Avvio di Oracle Universal Installer in corso...
Verifica dello spazio Temp: deve essere maggiore di 120 MB. Effettivi 17276 MB Superato Verifica dello spazio di swap: deve essere maggiore di 150 MB. Effettivi 23999 MB Superato Verifica del monitor: deve essere configurato per visualizzare almeno 256 colori. Effettivo 16777216 Superato Preparazione per l'avvio di Oracle Universal Installer da /tmp/OraInstall2012-01-11_03-35-57AM. Attendere...[oracle@ssts2cora1 grid]$
Everything seems ok. But the window of oracle installation does not appear. The java process is active:
but it does nothing!! Nothing appears to me. I attach the log file generated by the installation process.
Using paramFile: /home/software_ssts/grid/install/oraparam.ini
Checking Temp space: must be greater than 120 MB. Actual 17276 MB Passed Checking swap space: must be greater than 150 MB. Actual 23999 MB Passed Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
The commandline for unzip:
/home/software_ssts/grid/install/unzip -qqqo ../stage/Components/oracle.jdk/1.5.0.17.0/1/DataFiles/\*.jar -d /tmp/OraInstall2012-01-11_03-35-57AM Verr� utilizzato il valore di umask '022' disponibile in oraparam.ini
Execvp of the child jre : the cmdline is /tmp/OraInstall2012-01-11_03-35-57AM/jdk/jre/bin/java, and the argv is /tmp/OraInstall2012-01-11_03-35-57AM/jdk/jre/bin/java -Doracle.installer.library_loc=/tmp/OraInstall2012-01-11_03-35-57AM/oui/lib/linux -Doracle.installer.oui_loc=/tmp/OraInstall2012-01-11_03-35-57AM/oui -Doracle.installer.bootstrap=TRUE -Doracle.installer.startup_location=/home/software_ssts/grid/install -Doracle.installer.jre_loc=/tmp/OraInstall2012-01-11_03-35-57AM/jdk/jre [code].......
I got huge problem trying to install grid infrastructure and oracle db. Separately oracle db works fine and installation is going without any problems as for grid not so much.
i got vmware host 4.1 with several vm's on top. One of them i openfiler which gives 2 luns for installation. Two other vm's are host of windows 2008r2(comes only 64bits i know that many of you know but just to be clear). Those two windows should be connected in oracle cluster and should have one oracle db afterwards. I have already AD domain, free disks and all required staff arranged and prepared.
I wont bodder you ppl about previous problems but current one is that i have no clue what's going wrong. I got two logs file which i paste below but there's no error message just some warning which isn't described even properly as for me.
Attached File(s)
installtion_logs.zip ( 66.62K ) Number of downloads: 0