Search Results

Search found 1530 results on 62 pages for 'killer pl'.

Page 9/62 | < Previous Page | 5 6 7 8 9 10 11 12 13 14 15 16  | Next Page >

  • Waiting for a submitted job to finish in Oracle PL/SQL?

    - by vicjugador
    I'm looking for the equivalent of Java's thread.join() in PL/SQL. I.e. I want to kick off a number of jobs (threads), and then wait for them to finish. How is this possible in PL/SQL? I'm thinking of using dbms_job.submit (I know it's deprecated). dbms_scheduler is also an alternative. My code: DECLARE jobno1 number; jobno2 number; BEGIN dbms_job.submit(jobno1,'begin dbms_lock.sleep(10); dbms_output.put_line(''job 1 exit'');end;'); dbms_job.submit(jobno2,'begin dbms_lock.sleep(10); dbms_output.put_line(''job 2 exit'');end;'); dbms_job.run(jobno1); dbms_job.run(jobno2); //Need code to Wait for jobno1 to finish //Need code to Wait for jobno2 to finish END;

    Read the article

  • How to access Oracle system tables from inside of a PL/SQL function or procedure?

    - by mjumbewu
    I am trying to access information from an Oracle meta-data table from within a function. For example (purposefully simplified): CREATE OR REPLACE PROCEDURE MyProcedure IS users_datafile_path VARCHAR2(100); BEGIN SELECT file_name INTO users_datafile_path FROM dba_data_files WHERE tablespace_name='USERS' AND rownum=1; END MyProcedure; / When I try to execute this command in an sqlplus process, I get the following errors: LINE/COL ERROR -------- ----------------------------------------------------------------- 5/5 PL/SQL: SQL Statement ignored 6/12 PL/SQL: ORA-00942: table or view does not exist I know the user has access to the table, because when I execute the following command from the same sqlplus process, it displays the expected information: SELECT file_name FROM dba_data_files WHERE tablespace_name='USERS' AND rownum=1; Which results in: FILE_NAME -------------------------------------------------------------------------------- /usr/lib/oracle/xe/oradata/XE/users.dbf Is there something I need to do differently?

    Read the article

  • postfix revived and delivered have the same values (?)

    - by thinkingbig
    I have configured my first server (Debian with ISPConfig). Generally i want to send bulk e-mails to our users, i configure postfix and turn on postfix... but... After 1 hour of sending emails i have logs like this: Grand Totals messages 21886 received 21883 delivered 0 forwarded 0 deferred 234 bounced 0 rejected (0%) 0 reject warnings 0 held 0 discarded (0%) 30805k bytes received 31280k bytes delivered 3 senders 3 sending hosts/domains 12588 recipients 3 recipient hosts/domains Per-Hour Traffic Summary time received delivered deferred bounced rejected -------------------------------------------------------------------- 0000-0100 0 0 0 0 0 0100-0200 0 0 0 0 0 0200-0300 0 0 0 0 0 0300-0400 0 0 0 0 0 0400-0500 0 0 0 0 0 0500-0600 0 0 0 0 0 0600-0700 0 0 0 0 0 0700-0800 0 0 0 0 0 0800-0900 0 0 0 0 0 0900-1000 0 0 0 0 0 1000-1100 0 0 0 0 0 1100-1200 0 0 0 0 0 1200-1300 0 0 0 0 0 1300-1400 0 0 0 0 0 1400-1500 0 0 0 0 0 1500-1600 15311 15306 0 168 0 1600-1700 6575 6577 0 66 0 1700-1800 0 0 0 0 0 1800-1900 0 0 0 0 0 1900-2000 0 0 0 0 0 2000-2100 0 0 0 0 0 2100-2200 0 0 0 0 0 2200-2300 0 0 0 0 0 2300-2400 0 0 0 0 0 Host/Domain Summary: Message Delivery sent cnt bytes defers avg dly max dly host/domain 21521 30353k 0 3.4 m 15.5 m wp.pl 355 919k 0 54.9 s 13.0 m mysenderdomainexample.pl 7 8477 0 1.7 s 1.9 s prokonto.pl Host/Domain Summary: Messages Received msg cnt bytes host/domain 21879 30786k mysenderdomainexample.pl 5 16196 mx4.wp.pl 1 3200 mx3.wp.pl Senders by message count 21783 root@mysenderdomainexample.pl 96 www-data@mysenderdomainexample.pl 6 from=< **So, my question is: 1) Why i have recived and delivered have the same values (approx)? 2) How can I check if an email has been delivered? 3) How to change default "root" and "www-data" user (FROM / RETURN PATH) to another? I have changed this in script, but postfix ignore scripting values and send every mail from root (we have .php send cron's in /etc/crontab) 4) WHY APPROX 100 % MAILS RECIVED HAS BEEN ADRESED TO MY SENDER HOST? Host/Domain Summary: Messages Received Waiting for respond, Regards TB**

    Read the article

  • Has "Killer Game Programming in Java" by Andrew Davison been rendered useless? What new tutorial should I follow? [duplicate]

    - by BDillan
    This question already has an answer here: Killer Game Programming [Java 3D] outdated? 5 answers Its pathetic how it was created in 2005 not so long ago when the Java 3D 1.31 API was released. Now after downloading the source code off the books website and implementing it onto my work-space it cant run. (The 3D Shooter Ch. 23) I downloaded Java 3D 1.5 API, installed it- went back to my source code and javax.media ect.., and EVERY imported class within the game simply couldn't be resolved... they are all outdated.. Please do not say how its meant for a positive curvature in your game development skills. No ~ I got the book to understand how to code 3D textures/particle systems/games. Without seeing results how can I learn? If there is still hope for this book please tell me. Otherwise what other books match this one in Game Programming? This one was very well organized, documented and long. I am not looking for a 3D game prog. book on Java. Rather one that has the same reputation as this one but is a bit newer..

    Read the article

  • How to install vmware tools?

    - by Tom
    I installed my Ubuntu in vmware, no I need install vmware tools, I got error: Searching for a valid kernel header path... The path "" is not valid. Would you like to change it?[yes] In CentOS, I run the following command to resolve this issue: yum install gcc-c++ yum install kernel-devel yum install kernel-headers yum -y update kernel But I don't know how to do in Ubuntu. Please help. Update I have tried the following command but nothing changed,still got error: Searching for a valid kernel header path... The path "" is not valid. Would you like to change it?[yes] sudo apt-get update sudo-get install build-essential linux-header-$(uname -r) sudo ./vmware-uninstall-tools.pl sudo ./vmware-config-tools.pl sudo ./vmware-install.pl Issue Changed: Run sudo ./vmware-uninstall-tools.pl, and delete the folder of /etc/vmware-tools then, run sudo ./vmware-install.pl Now I can successfully install vmware-tool.After restart, I can see folder of /mnt/hgfs, but can't see my shared folder.

    Read the article

  • getting error in perl :can't modify non-lvalue subroutine call

    - by dexter
    i have index.pl and subs.pl when i run the program user inserts date of birth and then it is passed to getage() subroutine in subs.pl . subs.pl has many subroutines. getage() than implicitly calls another subroutine called validate() which validates the date entered by user. when i run the index.pl user enters the date as: 03-04-2005 following error comes :can't modify non-lvalue subroutine call at subs.pl line 85, < line 1 and at 85th line of subs.pl i have list(my $val,my @value) = validate($dob); why such error comes any solutions

    Read the article

  • C to C++ Conversion [closed]

    - by Annalyne
    Can someone convert this code to C++, pretty please? :( #include <stdio.h> #include <stdlib.h> #include <time.h> #define WEAPON_ROPE 10 #define WEAPON_REVOLVER 20 #define WEAPON_LEADPIPE 30 #define WEAPON_CANDLESTICK 40 #define WEAPON_KNIFE 50 #define WEAPON_WRENCH 60 #define PEOPLE_MRGREEN 100 #define PEOPLE_MSSCARLET 200 #define PEOPLE_CONLMUSTARD 300 #define PEOPLE_PROFPLUM 400 #define PEOPLE_MISPEACOCK 500 #define PEOPLE_MISWHITE 600 #define PLACE_KITCHEN 1 #define PLACE_HALL 2 #define PLACE_POOLROOM 3 #define PLACE_STUDY 4 #define PLACE_LOUNG 5 #define PLACE_LIBRARY 6 #define PLACE_CONSERVATORY 7 #define PLACE_DINING 8 #define PLACE_BILLIARDS 9 int main() { int die = 0; int players[6][9] = {{0, 0, 0, 0, 0, 0}, {0, 0, 0, 0, 0, 0}, {0, 0, 0, 0, 0, 0}, {0, 0, 0, 0, 0, 0}, {0, 0, 0, 0, 0, 0}, {0, 0, 0, 0, 0, 0}, {0, 0, 0, 0, 0, 0}, {0, 0, 0, 0, 0, 0}, {0, 0, 0, 0, 0, 0}}; int allCards[] = {WEAPON_ROPE, WEAPON_REVOLVER, WEAPON_LEADPIPE, WEAPON_CANDLESTICK, WEAPON_CANDLESTICK, WEAPON_KNIFE, WEAPON_WRENCH, PEOPLE_MRGREEN, PEOPLE_MSSCARLET, PEOPLE_CONLMUSTARD, PEOPLE_CONLMUSTARD, PEOPLE_PROFPLUM, PEOPLE_MISPEACOCK, PEOPLE_MISWHITE, PLACE_KITCHEN, PLACE_HALL, PLACE_POOLROOM, PLACE_STUDY, PLACE_LOUNG, PLACE_LIBRARY, PLACE_CONSERVATORY, PLACE_DINING, PLACE_BILLIARDS}; int deckSize = 23; // number of cards in allCards array int count; for (count = 0; count < deckSize; ++count) { printf(", %d", allCards[count]); } // End for // These three array's are so you can put a card back, if need be... int weaponCards[] = {WEAPON_ROPE, WEAPON_REVOLVER, WEAPON_LEADPIPE, WEAPON_CANDLESTICK, WEAPON_CANDLESTICK, WEAPON_KNIFE, WEAPON_WRENCH}; int weaponDeckSize = 7; int peopleCards[] = {PEOPLE_MRGREEN, PEOPLE_MSSCARLET, PEOPLE_CONLMUSTARD, PEOPLE_CONLMUSTARD, PEOPLE_PROFPLUM, PEOPLE_MISPEACOCK, PEOPLE_MISWHITE}; int peopleDeckSize = 7; int placeCards[] = {PLACE_KITCHEN, PLACE_HALL, PLACE_POOLROOM, PLACE_STUDY, PLACE_LOUNG, PLACE_LIBRARY, PLACE_CONSERVATORY, PLACE_DINING, PLACE_BILLIARDS}; int placeDeckSize = 9; srand(clock()); // seed rand() using clock() which gives // the current tick your processor is at... int killer[3]; // no need to initialize yet. killer[0-2] will initialize int deckShuffle = rand() % weaponDeckSize; // picks one number out of the deck killer[0] = weaponCards[deckShuffle]; allCards[deckShuffle] = 0; // Card drawn. No longer exists in deck deckShuffle = rand() % peopleDeckSize; // picks another random card out of the deck killer[1] = peopleCards[deckShuffle]; allCards[deckShuffle + weaponDeckSize] = 0; // Card drawn. No longer exists in deck deckShuffle = rand() % placeDeckSize; // randomly picks the last card needed killer[2] = placeCards[deckShuffle]; allCards[deckShuffle + weaponDeckSize + peopleDeckSize] = 0; // Card drawn. No longer exists in deck int numberOfCards = 0; printf("CLUE\n"); printf("written by John Schintone\n"); printf("Origonal game delvoped by Hasbro\n"); int numberOfPlayers = 0; while ((numberOfPlayers < 3) || (numberOfPlayers > 6)) { printf("How many players are Going to play :\n"); printf("[number] > "); scanf("%d",&numberOfPlayers); // A very fast if statement which only uses integers/char's switch(numberOfPlayers) { case 6: { numberOfCards = 3; } break; case 5: { numberOfCards = 4; } break; case 4: { numberOfCards = 5; } break; case 3: { numberOfCards = 6; } break; default: { printf("You must enter a number between 3 and 6...\n"); } // End default } // End switch } // End while int index1, index2; // Note: ++index1; is faster than index1++; and will almost always // produce better code (index1++ happens after this statement line. // ++index1 increments index1 before this statement line) for (index1 = 0; index1 < numberOfPlayers; ++index1) { printf("Player %d", index1); for (index2 = 0; index2 < numberOfCards; ++index2) { // Remember that allCards[deckShuffle] == 0 because we removed that // card ages ago... works out well, just don't forget you did that : ) while (allCards[deckShuffle] == 0) { deckShuffle = rand() % deckSize; } // End while players[index1][index2] = allCards[deckShuffle]; allCards[deckShuffle] = 0; // Card removed for after loop... printf(", %d", players[index1][index2]); switch(players[index1][index2]) { case WEAPON_ROPE: { } break; // Add more... case PEOPLE_MRGREEN: { } break; // Add more... case PLACE_KITCHEN: { } break; // Add more... default: { printf("Program has caught player %d cheating...", index1); } // End default } // End switch } // End for printf("\n"); } // End for printf("The killer is %d, with the %d, and in the %d \n\n", killer[0], killer[1], killer[2]); printf("Type h for this help... \n"); printf("Type e to escape... \n"); printf("Type r to roll the die... \n"); char command = '\0'; // \0 represents zero, or the null character while (command != 'e') { printf("[one character] > "); scanf("%c", &command); if (command == 'r') { die = rand() % 6 + 1; printf("Your number is: %d \n", die); } // end while if (command == 'h') { printf("Type h for this help... \n"); printf("Type e to escape... \n"); printf("Type r to roll the die... \n"); } // End if printf("\n"); } // End while return(0); // Success. Program worked ok } // End main() Function

    Read the article

  • PL/PGSQL function, having trouble accessing a returned result set from psycopg2...

    - by Paul
    I have this pl/pgsql function: CREATE OR REPLACE FUNCTION get_result(id integer) RETURNS SETOF my_table AS $ DECLARE result_set my_table%ROWTYPE; BEGIN IF id=0 THEN SELECT INTO result_set my_table_id, my_table_value FROM my_table; ELSE SELECT INTO result_set my_table_id, my_table_value FROM my_table WHERE my_table_id=id; END IF; RETURN; END; $ LANGUAGE plpgsql; I am trying to use this with Python's psycopg2 library. Here is the python code: import psycopg2 as pg conn = pg.connect(host='myhost', database='mydatabase', user='user', password='passwd') cur = conn.cursor() return cur.execute("SELECT * FROM get_result(0);") # returns NoneType However, if i just do the regular query, I get the correct set of rows back: ... return cur.execute("SELECT my_table_id, my_table_value FROM mytable;") # returns iterable result set Theres obviously something wrong with my pl/pgsql function, but I can't seem to get it right. I also tried using RETURN result_set; instead of just RETURN in the 10th line of my plpgsql function, but got an error from postgres.

    Read the article

  • [CODE GENERATION] How to generate DELETE statements in PL/SQL, based on the tables FK relations?

    - by The chicken in the kitchen
    Is it possible via script/tool to generate authomatically many delete statements based on the tables fk relations, using Oracle PL/SQL? In example: I have the table: CHICKEN (CHICKEN_CODE NUMBER) and there are 30 tables with fk references to its CHICKEN_CODE that I need to delete; there are also other 150 tables foreign-key-linked to that 30 tables that I need to delete first. Is there some tool/script PL/SQL that I can run in order to generate all the necessary delete statements based on the FK relations for me? (by the way, I know about cascade delete on the relations, but please pay attention: I CAN'T USE IT IN MY PRODUCTION DATABASE, because it's dangerous!) I'm using Oracle DataBase 10G R2. This is the result I've written, but it is not recursive: This is a view I have previously written, but of course it is not recursive! CREATE OR REPLACE FORCE VIEW RUN ( OWNER_1, CONSTRAINT_NAME_1, TABLE_NAME_1, TABLE_NAME, VINCOLO ) AS SELECT OWNER_1, CONSTRAINT_NAME_1, TABLE_NAME_1, TABLE_NAME, '(' || LTRIM ( EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || COLUMN_NAME)), '/x/text()'), ',') || ')' VINCOLO FROM ( SELECT CON1.OWNER OWNER_1, CON1.TABLE_NAME TABLE_NAME_1, CON1.CONSTRAINT_NAME CONSTRAINT_NAME_1, CON1.DELETE_RULE, CON1.STATUS, CON.TABLE_NAME, CON.CONSTRAINT_NAME, COL.POSITION, COL.COLUMN_NAME FROM DBA_CONSTRAINTS CON, DBA_CONS_COLUMNS COL, DBA_CONSTRAINTS CON1 WHERE CON.OWNER = 'TABLE_OWNER' AND CON.TABLE_NAME = 'TABLE_OWNED' AND ( (CON.CONSTRAINT_TYPE = 'P') OR (CON.CONSTRAINT_TYPE = 'U')) AND COL.TABLE_NAME = CON1.TABLE_NAME AND COL.CONSTRAINT_NAME = CON1.CONSTRAINT_NAME --AND CON1.OWNER = CON.OWNER AND CON1.R_CONSTRAINT_NAME = CON.CONSTRAINT_NAME AND CON1.CONSTRAINT_TYPE = 'R' GROUP BY CON1.OWNER, CON1.TABLE_NAME, CON1.CONSTRAINT_NAME, CON1.DELETE_RULE, CON1.STATUS, CON.TABLE_NAME, CON.CONSTRAINT_NAME, COL.POSITION, COL.COLUMN_NAME) GROUP BY OWNER_1, CONSTRAINT_NAME_1, TABLE_NAME_1, TABLE_NAME; ... and it contains the error of using DBA_CONSTRAINTS instead of ALL_CONSTRAINTS...

    Read the article

  • Apache: set aside specific number of servers for a single "high priority" script

    - by Disco
    I have a bunch of scripts, but some of them are higher priority than others: /var/www/normal-priority/script1.pl /var/www/normal-priority/script2.pl /var/www/normal-priority/script3.pl and /var/www/high-priority/script1.pl /var/www/high-priority/script2.pl /var/www/high-priority/script3.pl All running under mod-perl. They reside in separate directories. From time to time, the normal-priority scripts get very busy, and the httpd servers "swamp out" the less frequently called high priority ones. Is it possible to set aside n httpd servers so that they only listen for the "high priority" scripts?

    Read the article

  • MVC + 3 tier; where ViewModels come into play?

    - by mikhairu
    I'm designing a 3-tiered application using ASP.NET MVC 4. I used the following resources as a reference. CodeProject: MVC + N-tier + Entity Framework Separating data access in ASP.NET MVC I have the following desingn so far. Presentation Layer (PL) (main MVC project, where M of MVC was moved to Data Access Layer): MyProjectName.Main Views/ Controllers/ ... Business Logic Layer (BLL): MyProjectName.BLL ViewModels/ ProjectServices/ ... Data Access Layer (DAL): MyProjectName.DAL Models/ Repositories.EF/ Repositories.Dapper/ ... Now, PL references BLL and BLL references DAL. This way lower layer does not depend on the one above it. In this design PL invokes a service of the BLL. PL can pass a View Model to BLL and BLL can pass a View Model back to PL. Also, BLL invokes DAL layer and DAL layer can return a Model back to BLL. BLL can in turn build a View Model and return it to PL. Up to now this pattern was working for me. However, I've ran into a problem where some of my ViewModels require joins on several entities. In the plain MVC approach, in the controller I used a LINQ query to do joins and then select new MyViewModel(){ ... }. But now, in the DAL I do not have access to where ViewModels are defined (in the BLL). This means I cannot do joins in DAL and return it to BLL. It seems I have to do separate queries in DAL (instead of joins in one query) and BLL would then use the result of these to build a ViewModel. This is very inconvenient, but I don't think I should be exposing DAL to ViewModels. Any ideas how I can solve this dilemma? Thanks.

    Read the article

  • Can I return values to PHP from an anonymous PL/SQL block?

    - by RenderIn
    I'm using PHP and OCI8 to execute anonymous Oracle PL/SQL blocks of code. Is there any way for me to bind a variable and get its output upon completion of the block, just as I can when I call stored procedures in a similar way? $SQL = "declare something varchar2 := 'I want this returned'; begin --How can I return the value of 'something' into a bound PHP variable? end;";

    Read the article

  • How can I execute pl/pgsql code without creating a function?

    - by Jeremiah Peschka
    With SQL Server, I can execute code ad hoc T-SQL code with full procedural logic through SQL Server Management Studio, or any other client. I've begun working with PostgreSQL and have run into a bit of a difference in that PGSQL requires any logic to be embedded in a function. Is there a way to execute PL/PGSQL code without creating an executing a function?

    Read the article

  • June IOUG events

    - by Mandy Ho
    Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} Independent Oracle User Group (IOUG) Regional Events: June 11-12, 2012 – Broomfield, CO 2-Day Seminar- “ High Performance PL/SQL & Oracle Database 11g New Features” Steven Feuerstein, generally considered the world’s leading PL/SQL expert, will be presenting his all-new, 2-day, “Higher Performance PL/SQL and Oracle 11g PL/SQL New Features” seminar on June 11 & 12 at Level 3 Communications in Broomfield, Colorado.  This will be Steven’s first Denver seminar in almost 4  years.  Who knows when he will offer another? http://www.rmoug.org/ June 14, 2012 – Ottawa, Ontario Pythian’s Gwen Shapira puts on 3 great presentations focused on NoSQL, making OLTP run fast and Big Data. http://www.oug-ottawa.org/pls/htmldb/f?p=327:27:1317735724699447::NO June 21, 2012 – Calgary, Alberta Big Data and Extreme Analytics Summit http://coug.ab.ca/ June 22, 2012 – Westborough, MA 10 Things You Probably Did Not Know? With Tom Kyte PL/SQL turns 23 years old this year. It was first introduced in 1988 with Oracle6 Database. This session looks at five technical things about PL/SQL you probably did not know: under-the-covers features that make PL/SQL quite simply the most efficient language with which to process data in the database. http://noug.com/  June 28/29, 2012 – Plano, Texas Jonathan Lewis Oracle Performance Seminars The DOUG (DALLAS ORACLE USERS GROUP) has invited SpeakTech to return to Dallas, and they’re bringing Jonathan Lewis! Topics are Beating the Oracle Optimizer – June 28, 2012, Trouble Shooting & Tuning – June 29, 2012 http://www.eventbrite.com/event/3082448687

    Read the article

  • How to pause Console Window in .pl and .bat file?

    - by Nano HE
    As I know, when I run cs myConsoleApp.cs from windows command line, I can pause the Console Window by add the code below: Console.ReadLine(); Then How can I pause Console Window in myConsoleApp.pl and myConsoleApp.bat? I just want to monitor the running result from the Console window. Thank you. Suppose myConsoleApp.bat like this: taskkill /f /im "E1.exe" taskkill /f /im "E2.exe"

    Read the article

  • How to identify what locked PL/SQL package (Oracle 10.0.4.2)?

    - by Roman Kagan
    I was trying to recompile PL/SQL package and no avail. because something obtained the lock and that wasn't released for long time. As soon as I kill all sessions I was able to recompile but encounter the same behavior (i.e. locked package) and I wonder what tools are avail to identify what could of obtain it and never release it? This happen on (Oracle 10.0.4.2). Greatly appreciate for your help.

    Read the article

  • How to generate DELETE statements in PL/SQL, based on the tables FK relations?

    - by The chicken in the kitchen
    Is it possible via script/tool to generate authomatically many delete statements based on the tables fk relations, using Oracle PL/SQL? In example: I have the table: CHICKEN (CHICKEN_CODE NUMBER) and there are 30 tables with fk references to its CHICKEN_CODE that I need to delete; there are also other 150 tables foreign-key-linked to that 30 tables that I need to delete first. Is there some tool/script PL/SQL that I can run in order to generate all the necessary delete statements based on the FK relations for me? (by the way, I know about cascade delete on the relations, but please pay attention: I CAN'T USE IT IN MY PRODUCTION DATABASE, because it's dangerous!) I'm using Oracle DataBase 10G R2. This is the result I've written, but it is not recursive: This is a view I have previously written, but of course it is not recursive! CREATE OR REPLACE FORCE VIEW RUN ( OWNER_1, CONSTRAINT_NAME_1, TABLE_NAME_1, TABLE_NAME, VINCOLO ) AS SELECT OWNER_1, CONSTRAINT_NAME_1, TABLE_NAME_1, TABLE_NAME, '(' || LTRIM ( EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || COLUMN_NAME)), '/x/text()'), ',') || ')' VINCOLO FROM ( SELECT CON1.OWNER OWNER_1, CON1.TABLE_NAME TABLE_NAME_1, CON1.CONSTRAINT_NAME CONSTRAINT_NAME_1, CON1.DELETE_RULE, CON1.STATUS, CON.TABLE_NAME, CON.CONSTRAINT_NAME, COL.POSITION, COL.COLUMN_NAME FROM DBA_CONSTRAINTS CON, DBA_CONS_COLUMNS COL, DBA_CONSTRAINTS CON1 WHERE CON.OWNER = 'TABLE_OWNER' AND CON.TABLE_NAME = 'TABLE_OWNED' AND ( (CON.CONSTRAINT_TYPE = 'P') OR (CON.CONSTRAINT_TYPE = 'U')) AND COL.TABLE_NAME = CON1.TABLE_NAME AND COL.CONSTRAINT_NAME = CON1.CONSTRAINT_NAME --AND CON1.OWNER = CON.OWNER AND CON1.R_CONSTRAINT_NAME = CON.CONSTRAINT_NAME AND CON1.CONSTRAINT_TYPE = 'R' GROUP BY CON1.OWNER, CON1.TABLE_NAME, CON1.CONSTRAINT_NAME, CON1.DELETE_RULE, CON1.STATUS, CON.TABLE_NAME, CON.CONSTRAINT_NAME, COL.POSITION, COL.COLUMN_NAME) GROUP BY OWNER_1, CONSTRAINT_NAME_1, TABLE_NAME_1, TABLE_NAME; ... and it contains the error of using DBA_CONSTRAINTS instead of ALL_CONSTRAINTS... Please pay attention to this: http://stackoverflow.com/questions/485581/generate-delete-statement-from-foreign-key-relationships-in-sql-2008/2677145#2677145 Another user has just written it in SQL SERVER 2008, anyone is able to convert to Oracle 10G PL/SQL? I am not able to... :-( This is the code written by another user in SQL SERVER 2008: DECLARE @COLUMN_NAME AS sysname DECLARE @TABLE_NAME AS sysname DECLARE @IDValue AS int SET @COLUMN_NAME = '<Your COLUMN_NAME here>' SET @TABLE_NAME = '<Your TABLE_NAME here>' SET @IDValue = 123456789 DECLARE @sql AS varchar(max) ; WITH RELATED_COLUMNS AS ( SELECT QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME) AS [OBJECT_NAME] ,c.COLUMN_NAME FROM PBANKDW.INFORMATION_SCHEMA.COLUMNS AS c WITH (NOLOCK) INNER JOIN PBANKDW.INFORMATION_SCHEMA.TABLES AS t WITH (NOLOCK) ON c.TABLE_CATALOG = t.TABLE_CATALOG AND c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME AND t.TABLE_TYPE = 'BASE TABLE' INNER JOIN ( SELECT rc.CONSTRAINT_CATALOG ,rc.CONSTRAINT_SCHEMA ,lkc.TABLE_NAME ,lkc.COLUMN_NAME FROM PBANKDW.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc WITH (NOLOCK) INNER JOIN PBANKDW.INFORMATION_SCHEMA.KEY_COLUMN_USAGE lkc WITH (NOLOCK) ON lkc.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG AND lkc.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA AND lkc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME INNER JOIN PBANKDW.INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc WITH (NOLOCK) ON rc.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG AND rc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA AND rc.UNIQUE_CONSTRAINT_NAME = tc.CONSTRAINT_NAME INNER JOIN PBANKDW.INFORMATION_SCHEMA.KEY_COLUMN_USAGE rkc WITH (NOLOCK) ON rkc.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG AND rkc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA AND rkc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME WHERE rkc.COLUMN_NAME = @COLUMN_NAME AND rkc.TABLE_NAME = @TABLE_NAME ) AS j ON j.CONSTRAINT_CATALOG = c.TABLE_CATALOG AND j.CONSTRAINT_SCHEMA = c.TABLE_SCHEMA AND j.TABLE_NAME = c.TABLE_NAME AND j.COLUMN_NAME = c.COLUMN_NAME ) SELECT @sql = COALESCE(@sql, '') + 'DELETE FROM ' + [OBJECT_NAME] + ' WHERE ' + [COLUMN_NAME] + ' = ' + CONVERT(varchar, @IDValue) + CHAR(13) + CHAR(10) FROM RELATED_COLUMNS PRINT @sql Thank to Charles, this is the latest not working release of the software, I have added a parameter with the OWNER because the referential integrities propagate through about 5 other Oracle users (!!!): CREATE OR REPLACE PROCEDURE delete_cascade ( parent_table VARCHAR2, parent_table_owner VARCHAR2) IS cons_name VARCHAR2 (30); tab_name VARCHAR2 (30); tab_name_owner VARCHAR2 (30); parent_cons VARCHAR2 (30); parent_col VARCHAR2 (30); delete1 VARCHAR (500); delete2 VARCHAR (500); delete_command VARCHAR (4000); CURSOR cons_cursor IS SELECT constraint_name, r_constraint_name, table_name, constraint_type FROM all_constraints WHERE constraint_type = 'R' AND r_constraint_name IN (SELECT constraint_name FROM all_constraints WHERE constraint_type IN ('P', 'U') AND table_name = parent_table AND owner = parent_table_owner) AND delete_rule = 'NO ACTION'; CURSOR tabs_cursor IS SELECT DISTINCT table_name FROM all_cons_columns WHERE constraint_name = cons_name; CURSOR child_cols_cursor IS SELECT column_name, position FROM all_cons_columns WHERE constraint_name = cons_name AND table_name = tab_name; BEGIN FOR cons IN cons_cursor LOOP cons_name := cons.constraint_name; parent_cons := cons.r_constraint_name; SELECT DISTINCT table_name, owner INTO tab_name, tab_name_owner FROM all_cons_columns WHERE constraint_name = cons_name; delete_cascade (tab_name, tab_name_owner); delete_command := ''; delete1 := ''; delete2 := ''; FOR col IN child_cols_cursor LOOP SELECT DISTINCT column_name INTO parent_col FROM all_cons_columns WHERE constraint_name = parent_cons AND position = col.position; IF delete1 IS NULL THEN delete1 := col.column_name; ELSE delete1 := delete1 || ', ' || col.column_name; END IF; IF delete2 IS NULL THEN delete2 := parent_col; ELSE delete2 := delete2 || ', ' || parent_col; END IF; END LOOP; delete_command := 'delete from ' || tab_name_owner || '.' || tab_name || ' where (' || delete1 || ') in (select ' || delete2 || ' from ' || parent_table_owner || '.' || parent_table || ');'; INSERT INTO ris VALUES (SEQUENCE_COMANDI.NEXTVAL, delete_command); COMMIT; END LOOP; END; / In the cursor CONS_CURSOR I have added the condition: AND delete_rule = 'NO ACTION'; in order to avoid deletion in case of referential integrities with DELETE_RULE = 'CASCADE' or DELETE_RULE = 'SET NULL'. Now I have tried to turn from stored procedure to stored function, but the delete statements are not correct: CREATE OR REPLACE FUNCTION deletecascade ( parent_table VARCHAR2, parent_table_owner VARCHAR2) RETURN VARCHAR2 IS cons_name VARCHAR2 (30); tab_name VARCHAR2 (30); tab_name_owner VARCHAR2 (30); parent_cons VARCHAR2 (30); parent_col VARCHAR2 (30); delete1 VARCHAR (500); delete2 VARCHAR (500); delete_command VARCHAR (4000); AT_LEAST_ONE_ITERATION NUMBER DEFAULT 0; CURSOR cons_cursor IS SELECT constraint_name, r_constraint_name, table_name, constraint_type FROM all_constraints WHERE constraint_type = 'R' AND r_constraint_name IN (SELECT constraint_name FROM all_constraints WHERE constraint_type IN ('P', 'U') AND table_name = parent_table AND owner = parent_table_owner) AND delete_rule = 'NO ACTION'; CURSOR tabs_cursor IS SELECT DISTINCT table_name FROM all_cons_columns WHERE constraint_name = cons_name; CURSOR child_cols_cursor IS SELECT column_name, position FROM all_cons_columns WHERE constraint_name = cons_name AND table_name = tab_name; BEGIN FOR cons IN cons_cursor LOOP AT_LEAST_ONE_ITERATION := 1; cons_name := cons.constraint_name; parent_cons := cons.r_constraint_name; SELECT DISTINCT table_name, owner INTO tab_name, tab_name_owner FROM all_cons_columns WHERE constraint_name = cons_name; delete1 := ''; delete2 := ''; FOR col IN child_cols_cursor LOOP SELECT DISTINCT column_name INTO parent_col FROM all_cons_columns WHERE constraint_name = parent_cons AND position = col.position; IF delete1 IS NULL THEN delete1 := col.column_name; ELSE delete1 := delete1 || ', ' || col.column_name; END IF; IF delete2 IS NULL THEN delete2 := parent_col; ELSE delete2 := delete2 || ', ' || parent_col; END IF; END LOOP; delete_command := 'delete from ' || tab_name_owner || '.' || tab_name || ' where (' || delete1 || ') in (select ' || delete2 || ' from ' || parent_table_owner || '.' || parent_table || ');' || deletecascade (tab_name, tab_name_owner); INSERT INTO ris VALUES (SEQUENCE_COMANDI.NEXTVAL, delete_command); COMMIT; END LOOP; IF AT_LEAST_ONE_ITERATION = 1 THEN RETURN ' where COD_CHICKEN = V_CHICKEN AND COD_NATION = V_NATION;'; ELSE RETURN NULL; END IF; END; / Please assume that V_CHICKEN and V_NATION are the criteria to select the CHICKEN to delete from the root table: the condition is: "where COD_CHICKEN = V_CHICKEN AND COD_NATION = V_NATION" on the root table.

    Read the article

  • How can fill a variable of my own created data type within Oracle PL/SQL?

    - by Frankie Simon
    In Oracle I've created a data type: TABLE of VARCHAR2(200) I want to have a variable of this type within a Stored Procedure (defined locally, not as an actual table in the DB) and fill it with data. Some online samples show how I'd use my type if it was filled and passed as a parameter to the stored procedure: SELECT column_value currVal FROM table(pMyPassedParameter) However what I want is to fill it during the PL/SQL code itself, with INSERT statements. Anyone knows the syntax of this?

    Read the article

  • move file from one location to another in putty

    - by dexter
    i have created folder on my server (ie finesse)- 'home' in which i have several perl(.pl) files as tt.pl, re.pl etc. now i have created new folder in 'home' folder called 'perl' and want to move tt.pl and re.pl in perl folder is there any command to do so (like cut-paste in windows)? note: i am using putty 0.60 on windows xp

    Read the article

< Previous Page | 5 6 7 8 9 10 11 12 13 14 15 16  | Next Page >